Another SQL parameter - UPDATE statement

Question

I'm not seeing why my update statement isn't actually updating. Here is what I have:

    private void submit_button_Click(object sender, EventArgs e)
    {
        string insert = insertbox.Text;
        SqlParameter param2 = new SqlParameter("@param2", SqlDbType.Text);
        param2.Value = insert;
        var connlink = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.2\\MSSQL\\Data\\Inserts.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        var cmd1 = new SqlCommand(@"SELECT qty_onhand FROM [insert] WHERE (Name LIKE @param2)", connlink);
        connlink.Open();
        cmd1.Parameters.Add(param2);
        var onhand = Convert.ToInt16(cmd1.ExecuteScalar());


    // The param2 in the statement passes fine and returns the value into "onhand".
    // Below, the parameters don't seem to be passed. There is no error but the record isn't updated.

        int new_onhand = Convert.ToInt16(qtybox1.Text);
        Convert.ToInt16(onhand);
        new_onhand = onhand - new_onhand;
        SqlParameter param1 = new SqlParameter("@param1", SqlDbType.SmallInt);
        param1.Value = new_onhand;
        SqlParameter param3 = new SqlParameter("@param3", SqlDbType.Text);
        param3.Value = param2.ToString();
        var cmd = new SqlCommand(@"UPDATE [insert] SET qty_onhand = @param1 WHERE (Name LIKE @param3)", connlink);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(param1);
        cmd.Parameters.Add(param3);
        cmd.ExecuteNonQuery();
        connlink.Close();
    }

I'm not sure of the difference of why one works and the other doesn't.

Check out this post: http://stackoverflow.com/questions/695578/creating-table-names-that-are-reserved-words-keywords-in-ms-sql-server — Nick Vaccaro, Jun 22 '12 at 20:07

score 1 · Answer 1 · answered Jun 22 '12 at 20:16

you set the value of param3 by calling ToString on param2: param3.Value = param2.ToString();

Calling ToString on a SqlParameter returns the parameter name. In our case, it returns "@param2" as a string, and not the value of it. Try using param2.Value.

Or actually to insert, since you wrote param2.Value = insert;.

score 0 · Accepted Answer · answered Jun 22 '12 at 20:17

0

The lines below looks incorrect to me because param2 is of type SqlParameter and you using param2.ToString() as value for param3.value

SqlParameter param3 = new SqlParameter("@param3", SqlDbType.Text);
    **param3.Value = param2.ToString();**

answered Jun 22 '12 at 20:17

HatSoft

11,077
3
28
43

I just changed it to **param3.Value = param2.Value; and its still not changing the value. – marcmiller2007 Jun 22 '12 at 20:18
Got it. It was the **cmd.Parameters.AddWithValue** that a ToString() in it. I changed that to *.Value and it works. Thank you guys! – marcmiller2007 Jun 22 '12 at 20:21
Huh? I don't see any `cmd.Parameters.AddWithValue` in your code. – comecme Jun 22 '12 at 20:24

Another SQL parameter - UPDATE statement

2 Answers2