"ODBC--call failed" Error

[Ben]

Hi!
It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:

SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"

Set InvP = CurDB.OpenRecor dset(SQLStmt, DB_OPEN_DYNASET ) '
==>it gets the error here

I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.
Any guidance would be helpful.
Thanks

[AlterEgo]

Ben,

The error is not very intuitive. You might want to try a SQL Trace and an
ODBC trace to get more information. Profiler has an Errors and Warnings /
Exception event you can trace, and the following link will show you how to
set up an ODBC trace:

http://support.microsoft.com/kb/274551

-- Bill

"Ben" <pi******@sbcgl obal.netwrote in message
news:C7******** ***********@new ssvr13.news.pro digy.net...

Hi!
It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:

SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"

Set InvP = CurDB.OpenRecor dset(SQLStmt, DB_OPEN_DYNASET )
' ==>it gets the error here

I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.
Any guidance would be helpful.
Thanks

[Erland Sommarskog]

Ben (pi******@sbcgl obal.net) writes:

It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:

SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"

Set InvP = CurDB.OpenRecor dset(SQLStmt, DB_OPEN_DYNASET ) '
==>it gets the error here

I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.

It would be interesting to see a little more of the code. How you set
up the command and so. Particularly when you do it with ADO. (Since I know
ADO better this other thing (DAO?)).

Have you extracted what is in SQLStmt an tried to run that in Query
Analyzer? Maybe there is some simple error?

Also, an advice on how you get ItemNum into the query string. Most APIs
support parameterised commands, for instance ADO does. Parameterised
is much simpler to use than interpolated strings, and it protexts you
against SQL Injection. It also uses the query cache in SQL Server better.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx