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
– Mitja Bonca, Commented Apr 25, 2012 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
– Quentin91360, Commented Apr 25, 2012 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
– Zachary, Commented Apr 25, 2012 at 14:45
@Zachary Yes I already use : myCommand.CommandType = CommandType.Text — Quentin91360
– Quentin91360, Commented Apr 25, 2012 at 14:54

Developer · Accepted Answer · 2012-04-25 16:04:17Z

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

Zachary · Accepted Answer · 2012-04-25 14:47:41Z

1

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();

answered Apr 25, 2012 at 14:47

Zachary

6,54224 silver badges34 bronze badges

1 Comment

Quentin91360 Over a year ago

Thanks you but nothing change =(

FUSION CHA0S · Accepted Answer · 2019-01-17 14:52:24Z

1

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.

answered Jan 17, 2019 at 14:52

FUSION CHA0S

931 gold badge3 silver badges7 bronze badges

Comments

LaGrandMere · Accepted Answer · 2012-04-25 14:06:40Z

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, 2012 at 14:06

LaGrandMere

10.4k1 gold badge37 silver badges42 bronze badges

3 Comments

Quentin91360 Over a year ago

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?

Lamak Over a year ago

@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

LaGrandMere Over a year ago

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.

daniloquio · Accepted Answer · 2012-04-25 14:53:25Z

0

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 ''.

answered Apr 25, 2012 at 14:53

daniloquio

3,9232 gold badges41 silver badges57 bronze badges

Comments

Mark · Accepted Answer · 2012-04-25 15:38:14Z

0

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

answered Apr 25, 2012 at 15:38

Mark

1,5375 gold badges28 silver badges53 bronze badges

Collectives™ on Stack Overflow

Can't Update with sqlcommand and Parameters

6 Answers 6

Comments

1 Comment

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related