Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Multiple Queries on same asp page..

Reply
Thread Tools

Multiple Queries on same asp page..

 
 
David
Guest
Posts: n/a
 
      10-25-2004
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.
 
Reply With Quote
 
 
 
 
Turkbear
Guest
Posts: n/a
 
      10-25-2004
On 25 Oct 2004 08:27:58 -0700, http://www.velocityreviews.com/forums/(E-Mail Removed) (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?


 
Reply With Quote
 
 
 
 
David Gordon
Guest
Posts: n/a
 
      10-25-2004

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!
 
Reply With Quote
 
David Gordon
Guest
Posts: n/a
 
      10-25-2004

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!
 
Reply With Quote
 
Mark Schupp
Guest
Posts: n/a
 
      10-25-2004
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> 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!



 
Reply With Quote
 
caulker
Guest
Posts: n/a
 
      10-25-2004
and if these sql statements were stored procedures, you'd never had this
problem to begin with


"David Gordon" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> 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!



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-25-2004
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using ajax call to fetch multiple results from multiple queries and showing them 1 by 1 as the results comes. Biranchi Narayan Panda ASP .Net 0 02-21-2010 03:46 PM
xslt queries in xml to SQL queries Ian Roddis Python 3 02-26-2006 06:49 PM
bind multiple data tables from sql queries to multiple datacontainers? Daves ASP .Net 1 01-06-2006 04:17 PM
so many queries within queries I'm confused Abby Lee ASP General 11 08-06-2004 07:56 PM
DBI queries with same results (mysql) Rodrigo Perl 1 07-31-2003 02:38 PM



Advertisments