CODEWITHSUNDEEP
sqlms-accessasp-classicodbcdatabase-connection
Ben Hamilton
Ben Hamilton

Reputation: 975

Classic ASP bizarre SQL error

I am connecting to a Access DB, with following Conn String

Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\\server\share\Data\PFWTRAN.MDB"

The following SQL works fine;

SQLIn =     "SELECT Date, Time " & _
            "FROM Transactions " & _
            "WHERE TokenNumber = " & TokenNo & " " & _
            "AND Date >= " & FromDateG & " " & _
            "AND Direction = -1 " & _               
            "ORDER BY Date, TransactionNumber;"

However, I want rows where Transactions.Exception = 0, yet when I add this AND condition, the script fails when the RS is opened;

error '80004005' /path/.../.../...asp, line 97

If I remove the AND condition, it works again.

Even If I try and put 'Exception' in the SELECT section, it won't run and gives me that error.

Why would the inclusion of one field cause such an error? I read the error is due to permissions, but my permissions are fine as the SQL works without this one field in it.

Any clues?

It is a very old Access 95 database (or even earlier), perhaps I need to change connection provided?

Upvotes: 1

Views: 231

Answers (1)

HansUp
HansUp

Reputation: 97101

"However, I want rows where Transaction.Exception = 0, yet when I add this AND condition, the script fails when the RS is opened"

But Transaction.Exception refers to a different table than the one your query uses.

FROM Transactions

Date, Time, and Exception are Problem names and reserved words in Access. Enclose those names in square brackets, or prefix them with the table name/alias.

Consider switching your approach to use a parameter query ... and feed it TokenNo and FromDateG values as parameters, rather than building their values into the SELECT statement.

Upvotes: 1

Related Questions