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]

On 25 Oct 2004 08:27:58 -0700, da***@scene-double.co.uk (David) wrote:

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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Schupp]

any time you have problems with a SQL statement

Response.Write QRY
Response.End

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"David Gordon" <da***@scene-double.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

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.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[caulker]

and if these sql statements were stored procedures, you'd never had this
problem to begin with ;-)
"David Gordon" <da***@scene-double.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

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.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Hey! That's my line! :-)

Bob
caulker wrote:

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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"