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)

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
– Kritner, Commented Nov 8, 2016 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
– Bugs, Commented Nov 8, 2016 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
– justin26, Commented Nov 8, 2016 at 14:28
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? 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
– Ňɏssa Pøngjǣrdenlarp, Commented Nov 8, 2016 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.
– Alex B., Commented Nov 8, 2016 at 14:45

Community · Accepted Answer · 2017-05-23 11:46:28Z

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, 
                       "")

Collectives™ on Stack Overflow

Update SQL, no errors, no update

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related