SQL Pass-through ODBC call failed

I have an access front end to a sql database. To update stock I call a sql pass-through query that subtracts the quantity from stock. I have attached an image of the query. I have aslo attached the function that executes the query.
I have found out that the stock update has failed sometimes and the log file contains this error.
"19/03/2010 13:28  ODBC--call failed.  2-SqlUpdateStock   LN: 3"
This is the stored procedure.
ALTER PROCEDURE [dbo].[UpdateStock] @ProductCode varchar(20), @Qty float
AS
Begin tran
Update TblProducts set QuantityInStock =   round(isnull(QuantityInStock,0) + @Qty,4), Active = 1 Where ProductCode =@ProductCode;
Commit tran

Return

Is there anyway for me to get a more meaningfull error message or better still stop the problem. My guess it is some problem locking the record or something like that but the ODBC--call failed message is not very helpful.

I have now added a message so the user is aware there is a problem but it is unacceptable that stock is not updated and incorrect.

Thanks

(Sql Server is express 2005)







Public Function SqlUpdateStock(ProductCode As String, Qty As Double) As Boolean
On Error GoTo errhandler

Dim Ln As Double

Ln = 1

Dim qd As QueryDef

SqlUpdateStock = False


Ln = 2
Set qd = dbserver.QueryDefs("SqlUpdateStock")
qd.SQL = "UpdateStock '" & ProductCode & "', " & Round(Nz(Qty, 0), 4)

qd.ReturnsRecords = False


Ln = 3
qd.Execute

qd.ReturnsRecords = False

Ln = 4
SqlUpdateStock = True
    

Ln = 5.01
Open "c:\SqlUpdateStock.log" For Append As #3
Ln = 5.02
Print #3, Format(Now(), "dd/mm/yyyy hh:nn") & "," & ProductCode & "," & Round(Nz(Qty, 0), 4)
Close #3

    
Exit Function
errhandler:
    'MsgBox Err.Number & ": " & Err.Description, , "Error In SqlUpdateStock"
    'Open TILLLOGPATH & "Till" & gblTillNo & ".log" For Append As #2
    Open "c:\SqlUpdateStock.err" For Append As #2
        Print #2, Format(Now(), "dd/mm/yyyy hh:nn") & "  " & Err.Description & "  POS 2-SqlUpdateStock   LN: " & Ln
    Close #2
    On Error Resume Next
    Close #3
    
    If 1 = 2 Then
        Resume
    End If
End Function

Open in new window

Query.jpg
LVL 1
EamonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
corptechConnect With a Mentor Commented:
You can add this after your update statement:

SELECT @err = @@error
IF @err<> 0
   INSERT INTO tblErrorLog (errNumber, errDate) VALUES (@err, getdate())

This will update a table for any error numbers that occur.  Lookup these errors in sysmessages table for a description.
0
 
sgvillCommented:
You might try increasing your ODBC Timeout to something like 180 or so
0
 
EamonAuthor Commented:
is that 3 minutes. If so that will be no good as this is a point of sale + there may be ten items to update.
Even one minute would be way to long. What I am more concerned about is what is actually causing the update to fail.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
corptechCommented:
Access has handed processing off to SQL Server and then just waits.  When it doesn't get the response it is expecting, it provides this generic error.  Try running the procedure directly in SQL server and see if a more detailed error occurs.  Some people have had trouble with the round function for values that are decimal only (<1 and >0).  Try using cast(isnull(QuantityInStock,0) + @Qty as decimal(10,4)).  
0
 
EamonAuthor Commented:
I cant really test it as it only happens every now and again. From looking at logs I think sometimes it happens when a sql backup is running, but again not always.

Is there anyway to log the error in SQL. Could I catch the error in the stored procedure and save into a table?
0
 
EamonAuthor Commented:
will try this but as I said may take time as will have to wait for error to happen.
0
 
EamonAuthor Commented:
This is my new stored procedure. I will just have to wait and see how it goes.
PROCEDURE [dbo].[UpdateStock] @ProductCode varchar(20), @Qty float
AS

BEGIN TRY
	
	Update TblProducts set QuantityInStock = round(isnull(QuantityInStock,0) + @Qty,4), Active = 1 
	Where ProductCode =@ProductCode;
    
END TRY
BEGIN CATCH		

		INSERT INTO TblErrors (errNo, errDate, errDesc, errMsg) VALUES (ERROR_NUMBER(), getdate(), left(@ProductCode + ' : ' + CONVERT(varchar, @Qty),20) ,  left(ERROR_MESSAGE(),50))
		Declare @Msg varchar
		Set @Msg = 'UpdateStock Failed. Err:' + ERROR_MESSAGE() --CONVERT(varchar,ERROR_MESSAGE())
		raiserror( @Msg,16,1)

END CATCH;


Return

Open in new window

0
All Courses

From novice to tech pro — start learning today.