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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt Harris
  • 7
  • 3
  • 1
    Unless I'm missing something, this looks correct. (Well, [mostly...](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/)) When you debug, what specifically do you observe? Are the runtime values used for these parameters what you expect them to be? Can you profile the SQL database and observe the query/parameters it receives? Is any exception thrown? You're going to need to dig a little deeper here. – David Feb 01 '23 at 19:31
  • Does it throw any exceptions? – Fildor Feb 01 '23 at 19:36
  • 4
    Specify the type of the parameters instead of [letting `AddWithValue` do it](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – GSerg Feb 01 '23 at 19:40
  • It's always nice to see others link to my writing :) – Joel Coehoorn Feb 01 '23 at 19:47
  • @GSerg Is `AddWithValue` really the problem here if the parameter values and columns are strings? – D Stanley Feb 01 '23 at 19:49
  • 1
    Side note: You don't need to call `con.Close();` because the using-block already closes it for you. Use `var r = cmd.ExecuteNonQuery();` to see if the query worked. 0 means it didn't, > 0 means it did. Make sure your drop down control has a selection. What is the actual value of `ddlUser.SelectedItem.Text` when you run it? – LarsTech Feb 01 '23 at 19:49
  • If you still use parameters but hard-code the _values_ does is still work? are you certain the value for `@cn` exactly matches a value in the database, including whitespace and casing if applicable? – D Stanley Feb 01 '23 at 19:51
  • 1
    @DStanley It won't cause the error, but it **can** have a HUGE impact on performance if the database column is `varchar` instead of `nvarchar`. SQL Server will guess nvarchar for the parameter type, and instead of converting the input to varchar to match the column it will convert the column to nvarchar to match the input... _for every row in the table_, even if you only need one. Worse, the column values no longer match any index you might have. – Joel Coehoorn Feb 01 '23 at 20:04
  • @DStanley I thought that it [might be](https://stackoverflow.com/q/35800222/11683). – GSerg Feb 01 '23 at 20:15
  • @JoelCoehoorn Fair enough -and I can see where that might actaully break the query even if both are "strings". – D Stanley Feb 01 '23 at 20:20
  • Im pulling the data from Common_Name to put into the ddlUser, then using the selected index to update whichever user that is selected. There is not any issues with whitespace. – Matt Harris Feb 01 '23 at 20:30
  • 1
    Try putting `MessageBox.Show(ddlUser.SelectedItem.Text);` above your `string sql...` line. Are you getting the name you think you're getting when you run it now? – LarsTech Feb 01 '23 at 20:36
  • I used a label to debug and inspected the label. It is all correct. I'm just quite stumped – Matt Harris Feb 01 '23 at 20:44
  • 2
    No, that's not how you debug. Put a debug stop on `con.Open();` line and inspect the values of those objects. – LarsTech Feb 01 '23 at 20:58
  • It might be a good idea to extract the value of `ddlUser.SelectedItem.Text` into a `cn` variable so that you can test its content before passing it into the parameter, and make sure it doesn't contain whitespace, uppercase, etc. since it will be the sensitive comparer in the `where` clause. Also note that there are different types of text fields in the database and that it may be good practice to specify the data type to apply in the parameter: `cmd.Parameters.Add("@cn", SqlDbType.YOURFIELDTYPE).Value = cn;` – iggict Feb 03 '23 at 08:27

0 Answers0