ASP - Multiple Queries on same asp page..

Author

Multiple Queries on same asp page..

David

2004-10-25, 3:55 pm

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

2004-10-25, 3:55 pm

On 25 Oct 2004 08:27:58 -0700, david@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

2004-10-25, 3:55 pm

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

2004-10-25, 3:55 pm

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

2004-10-25, 3:55 pm

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" <david@scene-double.co.uk> wrote in message
news:%232WCG6quEHA.3808@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

2004-10-25, 8:55 pm

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

"David Gordon" <david@scene-double.co.uk> wrote in message
news:%232WCG6quEHA.3808@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]

2004-10-25, 8:55 pm

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"

Sponsored Links