Update SQL, no errors, no update

Question

I am attempting to update a table from data filled into a form.
The variables are pulled from the form like this:

_JEBatchDate = IIf(Me.textJEBatchDate.Text.Trim.Length > 0, Me.textJEBatchDate.Text.Trim, "")

The update sql looks like this...

UPDATE [MAIN] SET [CheckNumber] = @checknumber, [CheckDate] = @checkdate, [CheckCashDate] = @checkcashdate, [CheckAmount] = @checkamount, [PayeeNumber] = @payeenumber, [AccountNumber] = @accountnumber, [Bank] = @bank, [ToTheOrderOf] = @totheorderof, [CheckType] = @checktype, [DatePaperworkSentToPayee] = @datepaperworksenttopayee, [DatePaperworkSentToBank] = @datepaperworksenttobank, [IncompleteReason] = @incompletereason, [RejectReason] = @rejectreason, [CaseNumber] = @casenumber, [BankStatus] = @bankstatus, [CorrWithPayee] = @corrwithpayee, [Comments] = @comments, [BankCredit] = @bankcredit, [Refund] = @refund, [DateFundsRecdFromBank] = @datefundsrecdfrombank, [DateRefundRecd] = @daterefunded, [DateTargetSent] = @datetargetsent, [NumberOfTargets] = @numberoftargets, [DateCreditPostedToCLI] = @datecreditpostedtocli, [DateSentToSGForRepayment] = @datesenttosgforrepayment, [DateClaimWasRepaid] = @dateclaimwasrepaid, [NewCheckNumber] = @newchecknumber, [NewCheckAmount] = @newcheckamount, [ARCHIVED] = @archived, [JEBatchName] = @jebatchname, [JEBatchDate] = @jebatchdate WHERE [ID] = @ID;

All of the values are constructed in this manner:

strValues = strValues & "[JEBatchDate] = @jebatchdate "

Inside a Try...Catch, parameters are constructed this way:

 Try
        Using updateCmd As New OleDb.OleDbCommand(UpdateSQL, HMOConnection)
            updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)
            updateCmd.Parameters.AddWithValue("@checknumber", _checkNumber)
            updateCmd.Parameters.AddWithValue("@checkdate", _checkDate)

I use updateCmd.ExecuteNonQuery() to execute the query.
The code runs through the Try...Catch without error but no update is made to the record. I've double checked all of the names, spellings, connection but still no update is made. Any ideas or suggestions are appreciated!!

EDIT
If I move this line from the top to the bottom I get a data-type mismatch error:

updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)

How do you verify that the update doesn't work? Did you use the |DataDirectory| shortcut in your connectionstring? — Steve, Nov 08 '16 at 14:13
have you profiled the database to see that the statement is hitting the database you would expect it to, and if so, the script looks as it should? Additionally, have you tried running the profiled query directly in SSMS to confirm that the query in fact updates a row? — Kritner, Nov 08 '16 at 14:24
I _think_ with OleDb the parameters are set in order of where they come in the command. Try placing them in the same order so ID at the end of your `updateCmd.Parameters.AddWithValue` list. — Bugs, Nov 08 '16 at 14:26
yes and yes. first I made sure all of the parameters were correct, matching the parameter name with the variable. then made sure the query details were correct, table and database. ran the query outside of the code and it does indeed update the specified row, but it doesn't update when done in code. — justin26, Nov 08 '16 at 14:28
I think it's the order of the list. Place `updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)` at the end of your list rather than at the start. — Bugs, Nov 08 '16 at 14:29
Have a look at [this](http://stackoverflow.com/questions/7165661/is-order-of-parameters-for-database-command-object-really-important) which yes is in C# however explains that parameters have to be in the same order they appear within the command. I'd also look at `.Add` rather than `.AddWithValue` and specify the data type for each parameter. — Bugs, Nov 08 '16 at 14:35
`Dim rows = updateCmd.ExecuteNonQuery()` If rows is non zero, the query does execute and update. If you still cant see the rows, the issue may be: [Why saving changes to a database fails?](http://stackoverflow.com/q/17147249/1070452) You should also use `Add` rather than `AddWithValue` which leaves the Provider to guess what you want. And yes, in OleDB parameters are positional, it doesnt use the name at all — Ňɏssa Pøngjǣrdenlarp, Nov 08 '16 at 14:37
`updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)` fails because your `ID` column is probably `integer` on db but you try to set it to a string via `labelID.Text`. See @Plutonix comment how to use `Add` rather. — Alex B., Nov 08 '16 at 14:45
[Stop using AddWithValue](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) - basically you are also using the *type* of the "value" - which may not be what is wanted, such as a string ID into an integer parameter — Hans Keﬆing, Nov 08 '16 at 14:50

score 3 · Accepted Answer · edited May 23 '17 at 11:46

There are various issues:

No Update

There isn't enough code shown to know what is is going on (like maybe an empty Catch). This will tell you if the Provider succeeded:

Dim rows = updateCmd.ExecuteNonQuery()

If it is non zero, it did update. If you can't see the change(s), and the DB is part of the project, you are likely looking at the wrong DB copy. See Why saving changes to a database fails?

Datatype Mismatch

Do not use AddWithValue ever (unless your name is Gordon Linoff or Gord Thompson...or maybe Steve). This causes the provider to assume the datatype based on the data:

updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)

If the ID column is integer, you are passing a string - which is a data mismatch. Things can go very, very wrong with other DBs; use Add:

updateCmd.Parameters.Add("@ID", OleDbType.Integer).Value = Convert.ToInt32(labelID.Text)

I made sure all of the parameters were correct, matching the parameter name with the variable.

Next, OleDB does not use named parameters as such - you must supply each in the exact order that they appear in the SQL. Your SQL:

UPDATE [MAIN] SET [CheckNumber] = @checknumber, 
       [CheckDate] = @checkdate, 
       [CheckCashDate] = @checkcashdate,...

But you dont supply the parameters in that order:

updateCmd.Parameters.AddWithValue("@ID", Me.labelID.Text)
updateCmd.Parameters.AddWithValue("@checknumber", _checkNumber)
updateCmd.Parameters.AddWithValue("@checkdate", _checkDate)

The @ID should be last because it appears last in the SQL

Don't Use IIF

_JEBatchDate = IIf(textJEBatchDate.Text.Trim.Length > 0,
                   textJEBatchDate.Text.Trim, 
                   "")

The "new" If operator is short-circuited so that only the true or false part is executed:

_JEBatchDate = If(textJEBatchDate.Text.Trim.Length > 0,
                       textJEBatchDate.Text.Trim, 
                       "")

Update SQL, no errors, no update

1 Answers1