Can't Update with sqlcommand and Parameters

Question

I have some trouble to update my sql server 2005 database when i use parameters.Here you can see the code that normally has to work.I precise that i already make others treatments such as insert into and it worked perfectly.

myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);
myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";

//Execute la commande
myCommand.ExecuteNonQuery();

EDIT:When i use hard code such as:

myCommand.CommandText = "Update Action set titre='title' where pk=@Pk";

it works...

Code seems fine, no errors in it - on the 1st look. Tell us the exception (if no errors, us try, catch block). — Mitja Bonca, Apr 25 '12 at 14:07
No,I don't have an execption with this code.But I have an other execption in the project:System.Threading.ThreadAbortException — Quentin91360, Apr 25 '12 at 14:09
Do you also declare a "CommandType" anywhere in your code? I've seen weird things happen when this is left off, try adding it "myCommand.CommandType = CommandType.Text". — Zachary, Apr 25 '12 at 14:45
@Zachary Yes I already use : myCommand.CommandType = CommandType.Text — Quentin91360, Apr 25 '12 at 14:54
What is your value for this.titre? Debug to make sure you have a value going into it. — Mark, Apr 25 '12 at 15:21
Yes i Have a value in this.titre and Yes My connection State is Open. — Quentin91360, Apr 25 '12 at 15:29

score 1 · Answer 1 · answered Apr 25 '12 at 14:47

I've seen weird results when you forget to include the "CommandType" parameter. Since you using inline SQL, it should be set to "CommandType.Text".

myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);
myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";

// Added CommandType //
myCommand.CommandType = CommandType.Text;

//Execute la commande
myCommand.ExecuteNonQuery();

Thanks you but nothing change =( – Quentin91360 Apr 25 '12 at 15:05 — Quentin91360, Apr 25 '12 at 15:05

score 1 · Accepted Answer · answered Apr 25 '12 at 16:04

I don't know where you went wrong this is the working code for me

string strCon = @"Data Source=SYSTEM19\SQLEXPRESS;Initial Catalog=TransactionDB;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection cn = new SqlConnection(strCon);
            SqlCommand cmd = new SqlCommand("select * from tblTransaction1", cn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                txtName.Text = ds.Tables[0].Rows[i]["FirstName"].ToString();
                txtName1.Text = ds.Tables[0].Rows[i]["LastName"].ToString();
            }
        }
    }

Button click code

protected void btnInsert_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(strCon);
    obj1.FirstName = txtName.Text;
    obj1.LastName = txtName1.Text;
    if (obj1.upDate(cn))
    {

    }
}

Sample class code file

private bool m_flag = false;
        private string strFirstName;
        private string strLastName;

        public string FirstName
        {
            get { return strFirstName; }
            set { strFirstName = value; }
        }

        public string LastName
        {
            get { return strLastName; }
            set { strLastName = value; }
        }

public bool upDate(SqlConnection con)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }

        try
        {
            cmd.Parameters.AddWithValue("@Fname", FirstName);
            cmd.Parameters.AddWithValue("@Lname", LastName);
            cmd.CommandText = "Update tblTransaction1 set LastName=@Lname where FirstName=@Fname";
            if (cmd.ExecuteNonQuery() > 0)
            {
                m_flag = true;
            }
        }
        catch
        {

        }
        return m_flag;
    }

Sample Images

Form With Data from SQL

Database data before update

Changing Lastname

after update

score 1 · Answer 3 · answered Jan 17 '19 at 14:52

I have noticed that copying the entire code into a new project helps. I have ran into many times my code would work and then the next day would not, or would only work for someone else and not me. Usually this is due to the designer side of the project when adding and removing code from your project. Just because you delete specific code does not mean the program can update the entire class/project.

score 0 · Answer 4 · answered Apr 25 '12 at 14:06

0

If you do :

Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);

What does it say ?

Try also to prefix your Action table, with the schema name, for example :

myCommand.CommandText = "Update MySchema.Action set titre=@Titre where pk=@Pk";

Because sometimes it can depend on the schema and the user's rights to update this schema.

answered Apr 25 '12 at 14:06

LaGrandMere

10,265
1
33
41

When i do what you said it prints one.So that's good.When i put your last line I obtain a 'MySchema.Action' invalid.Normal because i don't create schema in sql server 2005? – Quentin91360 Apr 25 '12 at 14:16
@Quentin91360 - By `MySchema.Action` he meant that you need to prefix **your** schema. If you don't create schemas, then most likely it should be `dbo.Action`. In any case, by your comment, it seems that the query is executing just fine – Lamak Apr 25 '12 at 14:23
Thx @Lamak, it's exactly this. Quentin91360 : if the result is 1, then it's updating and your query is working fine, no need for Schema. Try to close your connection after this command, or put this command into a Transaction and commit it. – LaGrandMere Apr 25 '12 at 14:30

score 0 · Answer 5 · answered Apr 25 '12 at 14:53

You could try this: instead of adding the parameters like that

myCommand.Parameters.AddWithValue("@Titre", this.titre);

you should add them with data type.

myCommand.Parameters.Add(new SqlParameter("@Titre", SqlDbType.VarChar, 50));
myCommand.Parameters["@Titre"].Value = this.titre;

That way, the final SQL will be Update Action set titre='titre' instead of Update Action set titre=title. Look that in the second statement titre is not inside quotes ''.

score 0 · Answer 6 · answered Apr 25 '12 at 15:38

Try adding the parameters after declaring the command.

myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";
myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);


//Execute la commande
myCommand.ExecuteNonQuery();

I found something similar (not identical) here: http://forums.asp.net/t/1249831.aspx/1

Can't Update with sqlcommand and Parameters

6 Answers6