SQL UPDATE not working and no error is thrown

Question

I'm coding a program in Visual studio windows forms, and my SQL UPDATEstatement isn't working, and there are no errors thrown. Here are my codes:

  string strConnection = ConfigurationManager.ConnectionStrings
         ["Brownie.Properties.Settings.BrownieDB2ConnectionString"].ConnectionString;

        SqlConnection sqlconnect = new SqlConnection(strConnection);

        string strSQL = "UPDATE Cargo SET Jobdone = 'Yes' WHERE (DriverID IN (SELECT DriverID FROM Driver WHERE (CardID = @a)))";

        SqlCommand cmd = new SqlCommand(strSQL, sqlconnect);

        cmd.Parameters.AddWithValue("@a", momentcardid);

        try
        {
            //step 3: open connection
            sqlconnect.Open();
            //step 4: execute command
            int result = cmd.ExecuteNonQuery();

            if (result > 0)
                MessageBox.Show("Job has been successfully updated");
            else
                MessageBox.Show("Job update is unsuccessful");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            //step 5: close connection
            sqlconnect.Close();
        }

I tested out this code on the Query Testing function provided by Visual studio under Server Explorer (Right click database > Right click table name > New Query), placed the value momentcardid for testing and it was able to update successfully into the database.

However when I tried to run the codes on winform, it does not update into the database. There is no error thrown as well. It even displays the message box Job has been updated successfully

I'm really not sure what is wrong, I hope someone can help me figure out!

Answer 1

Great point Mr. Marc Gravell

I've been struggling with this issue for over a week...

and for that exact reason, another thing to note is also the edit page/form. I had the same issue but noticed that this only occurred when I didn't include all the fields from the table, that are being updated in the edit form. So if your table consists of the fields (Id, CargoName, Jobdone) then your form should look like this:

<form asp-action="ProcessEdit">
                <div asp-validation-summary="ModelOnly" class="text-danger"</div>
                <div class="form-group">
                    <label asp-for="Id" class="control-label"></label>
                    <input asp-for="Id" class="form-control" />
                    <span asp-validation-for="Id" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label asp-for="CargoName" class="control-label"></label>
                    <input asp-for="CargoName" class="form-control" />
                </div>
                <div class="form-group">
                    <label asp-for="Jobdone" class="control-label"></label>
                    <input asp-for="Jobdone" class="form-control" />
                </div>
                <div class="form-group">
                    <input type="submit" value="Edit" class="btn btn-primary" />
                </div>
            </form>

I still don't know why exactly you need the other fields in order to get the UPDATE strSql to work, but this worked for me without having to change anything in my method.

Answer 2

There are two possible things going on here:

1. the values of @a and CardId simply aren't matching, due to not being quite what you expect; common reasons:
   • case sensitivity
   • whitespace
   • fixed width (char/nchar) vs dynamic width (varchar/nvarchar)
   • invisible characters (unicode characters or escape characters) in one of the two values
   • there simply isn't matching data for that value!
2. it is working, but you're looking in the wrong place
   • this is common when using file-based databases rather than dedicated servers - you need to be sure to look in the database file in the executing folder (often /bin/debug etc) rather than the source folder

To determine which, you might add select @@rowcount; after the update statement and use var rowsUpdated = cmd.ExecuteScalar. By reading this value, you can see how many rows were updated. If it is zero, then the first bullet applies; if it is non-zero, then the second bullet applies.

Assuming it is the first bullet, you'll need to look very carefully at the value of momentcardid, including any whitespace and hidden characters. momentcardid.Length would be a good place to start, but momentcardid.ToCharArray() would be the next step (and look carefully at every character)