Multiple Queries on same asp page..

[David]

Hi,

I have an ASP page which runs a select statement such as

QueryA = "SELECT............ ;"

Set RS_A = adoDataConn.Execute(QueryA)

(adoDataConn is set in a hidden include file).

If I run RS_A and pull out the records, I then want to run another
query on a seperate table but where one field is common.

QueryB = "SELECT.........;"

Set RS_B = adoDataConn.Execute(QueryB)


I am always getting the following error:

ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near
'ProductPackREC.JobNumber = 5578' at line 1

I cannot see any error in my SQL apart from the fact that I am calling
adoDataConn twice without doing anything in between it ?
What do I need to do to run both queries happily on the same page ?

Appreciate your help


David.

 

[Turkbear]

Hi,

I have an ASP page which runs a select statement such as

QueryA = "SELECT............ ;"

Set RS_A = adoDataConn.Execute(QueryA)

(adoDataConn is set in a hidden include file).

If I run RS_A and pull out the records, I then want to run another
query on a seperate table but where one field is common.

QueryB = "SELECT.........;"

Set RS_B = adoDataConn.Execute(QueryB)


I am always getting the following error:

ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near
'ProductPackREC.JobNumber = 5578' at line 1

I cannot see any error in my SQL apart from the fact that I am calling
adoDataConn twice without doing anything in between it ?
What do I need to do to run both queries happily on the same page ?

Appreciate your help


David.

Please post the Sql for each statement...Cannot tell what is wrong without seeing it..

Are you attempting to use a value in RS_A in the where clause of the sql to create RS_B?

 

[David Gordon]

strQuery works 100%

___________________________________

strQuery = "SELECT OrderLines.PSLrepnotes, OrderLines.OrderNotes,
Customers.CustomerName, Orders.PONumber, OrderLines.JobNumber,
OrderLines.OrderQuantity, Products.ProductName, Products.BBProductName,
OrderLines.HoldonSD, OrderLines.ShipfromPSL "
strQuery = strQuery & "FROM StockMovements INNER JOIN OrderLines ON
(StockMovements.JobNumber = OrderLines.JobNumber) INNER JOIN Products ON
(OrderLines.ProductID = Products.ProductID) INNER JOIN Orders ON
(OrderLines.OrderID = Orders.OrderID) INNER JOIN Customers ON
(Orders.CustomerID = Customers.CustomerID) "
strQuery = strQuery & "WHERE OrderLines.JobNumber = " &
Session("SerialSearchJob") & ";"

Set RS = adoDataConn.Execute(strQuery)

if RS.EOF then ...........

____________________________________


PackQuery = "SELECT ProductPackREC.JobNumber,
ProductPackREC.packitemNAME, ProductPackREC.packitemDESC,
ProductPackREC.packitemQTY, ProductPackREC.packitemCODE"
PackQuery = PackQuery & "FROM ProductPackREC"
PackQuery = PackQuery & "WHERE ProductPackREC.JobNumber = " &
Session("SerialSearchJob") & ";"


Set PackRS = adoDataConn.Execute(PackQuery)

If PackRS.EOF then .................


__________________________________

Do I need to close the connection, then re-open ?

David

 

[David Gordon]

Ooops....sorry.
After trailing my code for half a day, I discovered the error..

In the SQL statement, if you do not leave a space before the end quotes
if you are working such as

QRY = "SELECT......."
QRY = QRY & "FROM......"

then it does not recognize the SQL text....DOH...a bloody space for
hours of debugging....thats programming for you

Appreciate you looking.

Thank

David.

 

[Mark Schupp]

any time you have problems with a SQL statement

Response.Write QRY
Response.End

 

[caulker]

and if these sql statements were stored procedures, you'd never had this
problem to begin with ;-)

 

[Bob Barrows [MVP]]

Hey! That's my line!

and if these sql statements were stored procedures, you'd never had
this problem to begin with ;-)