SQL Error With UPDATE
I have a section of code that issues and UPDATE and got the following error -2147467259 when I logged the Err to a file. How do I find out what this error was?
Public Sub Update_SalesOrderDetailStatus(SavSLSOD_TruckId, SavSLSOD_DriverId, SavSLSOD_Number, SavSLSOD_LineItemNumber)
'
On Error GoTo Update_SalesOrderDetailStatus_Error
'
If Sav_SL_Mode = "PURE" Then
cnn.Execute "UPDATE SLSalesOrderDetail SET " & _
"SLSOD_AssignedDriverId = '" & SavSLSOD_DriverId & "', " & _
"SLSOD_LineItemStatus = '7' " & _
"WHERE SLSOD_Number = '" & SavSLSOD_Number & "' " & _
"AND SLSOD_LineItemNumber = '" & SavSLSOD_LineItemNumber & "' "
End If
'
Exit Sub
'
Update_SalesOrderDetailStatus_Error:
Call GeneralErrorRoutine(Err, "Update_SalesOrderStatus " & SavSLSOD_Number)
Resume Next
'
End Sub
[1058 byte] By [
Don] at [2007-11-11 7:20:24]
# 1 Re: SQL Error With UPDATE
Google: http://www.google.com/search?q=sql+server+error+2147467259
# 2 Re: SQL Error With UPDATE
Thanks but unfortunately this tells me it could be for any number of reasons. Would wrapping with BeginTrans and CommitTrans help? It seems this update works 95+% of the time. Same data and it fails ever once in a while. Is the CommandTimeout something I need to look at?
Don at 2007-11-11 17:28:02 >
# 3 Re: SQL Error With UPDATE
Do you get any text with that error number? Usually there is some...
By wrapping with BeginTrans and CommitTrans, you are making the code into a Transaction. A Transaction is a section of code in which either all the code executes correctly, or everything is rolled back and it's as if none of the code executed. Whether this helps you depends on the situation. If it's OK to merely blow off the entire section of code on an error, then fine. But in most cases, this is not an option- because basically the data gets thrown away and you never know it, unless you write code to tell you the transaction failed.
Perhaps you could modify the code, so that if the transaction fails, your code tries again? I would be interested to know if repeating the same code eventually works. If so, this would indicate a transient condition that is causing the code to fail- such as a Lock being temporarily placed on the table or row you are trying to access, by another process or program. Are other programs running against the DB? You may have a Deadlock situation.
More info, please!
-Andrew
# 4 Re: SQL Error With UPDATE
Your error code is 80004005(hex), and because it is an sql error, VB does not know what it is (in fact, the error string is "Unspecified error")
To get more error information, you can use the events from the connection object (like the Execute_Complete) that have an Error object as a parameter, but better yet is to use the Connection property Errors, that returns a collection of Error objects. In case of error, iterate through the collection, and you will have all the information you need. Something like:
dim e as ADODB.Error
foreach e in cnn.Errors
debug.print e.source,e.Description
for a complete description go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_59pr.asp
Marco
mstraf at 2007-11-11 17:30:14 >
# 5 Re: SQL Error With UPDATE
friend,
I always get a string variable (str1) to hold my query statement .
while debugging my project, I add str1 to watch and then copy it
to query analyzer and test it...
so why don't you try my method to find out what cauese the err?
Sonia
Sonia at 2007-11-11 17:31:13 >
# 6 Re: SQL Error With UPDATE
In the table SLSalesOrderDetail do the columns SLSOD_Number and AND SLSOD_LineItemNumber allow nulls?
Check that null from a column is not compared with a value from your parameter in the WHERE clause , Given the fact your code executes successfully 95% of the times, and my previous experiences the probability of the data base having nulls or any incorrect data in the column is high..
# 7 Re: SQL Error With UPDATE
Thanks for the replies. One thing is that the routine does not always error but does not always update either. It updates most of the time but again not always. When it does not I can find the message passed to it and the values are correct. Yes the columns allow NULL but there are never NULLs present. I added a cnn.CommandTimeout = 60 before the update in case it was timing out and that took care of the error. Again though it does not always update and when it does not there is no error. Only thing left is to do a select and test for the value to be present. Ihave even tried
strSQL = "SELECT * " & _
"FROM SLSalesOrderDetail " & _
"WHERE SLSOD_Number = '" & SavSLSOD_Number & "' " & _
"AND SLSOD_LineItemNumber = '" & SavSLSOD_LineItemNumber & "' "
SLSalesOrderDetail_Tb.Open strSQL, cnn, adOpenStatic, adLockOptimistic
before the update to make sure the record was there and I still do not always get an UPDATE. This is not a very positive for using this method.
Though I do not expext there to be a record lock, the idea of a lock on the DB could be possible. My connection is as follows:
strConnect = "Provider=SQLOLEDB;Data Source=" & Sav_SLD_Server & ";Initial Catalog=SMARTLynx_V501;User id=auser;Password=apass;"
cnn.Open strConnect
Should I change this to help reduce any lock conditions? I do the
SLSalesOrderDetail_Tb.Open strSQL, cnn, adOpenStatic, adLockOptimistic
on the SELECT. Should I change the UPDATE string?
Don at 2007-11-11 17:33:16 >
# 8 Re: SQL Error With UPDATE
So you've tried Marco's method, using the Errors property of the Connection object, and there is no error when the update fails? Hmm. That's weird.
Have you tried making the whole section of code into a Transaction, as we discussed earlier? Seems like that might help after all. See if that does the trick; if not, try writing your code so it tries again on Transaction failure.
Let us know what happens.
-Andrew
