SQL Parameter not updating table-CodePudding

protected void btnSave_Click(object sender, EventArgs e)
{
    string sql = @"UPDATE Users 
                   SET Initials = @Ini
                   WHERE Common_Name = @cn";
    
    using (var con = new SqlConnection("***"))
    {
        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        cmd.Parameters.AddWithValue("@cn", ddlUser.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@Ini", txtInitials.Text);

        cmd.ExecuteNonQuery();
        con.Close();
    }
}

Not really quite sure what is going on, I am unable to get this to update the database.

The only way I was able to get this work was not using parameters and hard coding it which is not what I'm looking to do.

I can provide more code upon request, wasn't quite sure if it is necessary.

CodePudding user response：

You need to remove unnecessary @ characters in the lines AddWithValue.

protected void btnSave_Click(object sender, EventArgs e)
{
    string sql = @"UPDATE Users 
                   SET Initials = @Ini
                   WHERE Common_Name = @cn";
    
    using (var con = new SqlConnection("***"))
    {
        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        cmd.Parameters.AddWithValue("cn", ddlUser.SelectedItem.Text);
        cmd.Parameters.AddWithValue("Ini", txtInitials.Text);

        cmd.ExecuteNonQuery();
        con.Close();
    }
}

CodePudding user response：

actually it should work! the code is Ok. look at sql profiler to find out which query is generated. it should look like:

exec sp_executesql N'UPDATE Users 
               SET LastName = @LastName
               WHERE Id = @Id',N'@Id int,@LastName nvarchar(64)',@Id=N'90',@LastName=N'testtesttest'