CODEWITHSUNDEEP
c#sqlparameters
marcmiller2007
marcmiller2007

Reputation: 119

Another SQL parameter - UPDATE statement

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.

Upvotes: 0

Views: 1895

Answers (2)

HatSoft
HatSoft

Reputation: 11201

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

Upvotes: 0

Akos Lukacs
Akos Lukacs

Reputation: 2047

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

Upvotes: 1

Related Questions