Posted by Erland Sommarskog on 02/05/07 22:28

Ben (pillars4@sbcglobal.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.OpenRecordset(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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]