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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
corptechCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.