user3795810 user3795810 - 1 month ago 18
Vb.net Question

Update SQL, no errors, no update

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)

Answer

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 OleDB updated:

Dim rows = updateCmd.ExecuteNonQuery()

If it is non zero, it updated. If you can't see the change(s), you might be 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)

Of course, long before this, you should have tested that it is a valid integer entered.

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 parameter in the order 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, 
                   "")