Paolo Duhaylungsod Paolo Duhaylungsod - 6 months ago 15
SQL Question

Update SQL statement not firing to database

I was wondering what am I missing here in my code. All I want to happen is to get a specific value,

creditrequest
, from
CreditRequest
table and put it in
UserData
table,
creditrequest
column. Somehow my code is not performing the task.

Here is my code:

using (SqlConnection scn = new SqlConnection("Data Source = 'PAULO'; Initial Catalog=ShoppingCartDB;Integrated Security =True"))
{
scn.Open();
SqlCommand cmd = new SqlCommand(@"UPDATE UserData SET CreditRequest =
(SELECT CAST(REPLACE(c.CreditRequest, ',', '') as int)
FROM CreditRequests c Where c.Username=@Username", scn);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = Session["New"];

cmd.ExecuteNonQuery();
}

Answer

Let look at the code line by line.

using (SqlConnection scn = new SqlConnection("Data Source = 'PAULO'; Initial Catalog=ShoppingCartDB;Integrated Security =True"))
//I hope this is correct. Else "Server=PAOLO;Database=ShoppingCartDB;Integrated Security=True"
{
    scn.Open();
/*
    SqlCommand cmd = new SqlCommand(@"UPDATE UserData SET CreditRequest = 
    (SELECT CAST(REPLACE(c.CreditRequest, ',', '') as int) 
    FROM CreditRequests c Where c.Username=@Username", scn);
*/
    //SQL statement is all wrong and can update all rows
    //Correct is
    SqlCommand cmd = new SqlCommand(@"UPDATE UserData SET CreditRequest = "
        + "CAST(REPLACE(c.CreditRequest, ',', '') as int) "
        + " FROM CreditRequests c inner join UserData u "
        + "on c.Username=u.Username Where c.Username=@Username", scn);
    cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = (string)Session["New"]; //This is good

    cmd.ExecuteNonQuery();
}
Comments