My question is this:

Can you have two separate SQL Statements in one ASP page?

I have two separate queries that work great on their own but when I try to 
combine them into one query it will not work.

My idea is that I would run a query to display information that comes from 
a number of tables in my Access 2000 database, and then once that query 
has run I would run a count query on the number of company names displayed.

If my method is correct, does anyone know the syntax for using two SQL 
statements in one ASP page?


Cheers

Show us the 2 queries..maybe we can help

-----Original Message-----
From: IT@g... [mailto:IT@g...]
Sent: Thursday, October 17, 2002 11:11 AM
To: ASP Databases
Subject: [asp_databases] Multiple SQL statements in one ASP page


My question is this:

Can you have two separate SQL Statements in one ASP page?

I have two separate queries that work great on their own but when I try
to
combine them into one query it will not work.

My idea is that I would run a query to display information that comes
from
a number of tables in my Access 2000 database, and then once that query
has run I would run a count query on the number of company names
displayed.

If my method is correct, does anyone know the syntax for using two SQL
statements in one ASP page?


Cheers

You have to execute them seperately, one after the other.

You can do like this, where cmd is your command object and sql_query_one/two
are your sql statements:

cmd.commandtext = sql_query_one
set rs1 = cmd.execute
cmd.commandtext = sql_query_two
set rs2 = cmd.execute

-Kim

> -----Original Message-----
> From: IT@g... [mailto:IT@g...]
> Sent: 17. oktober 2002 15:11
> To: ASP Databases
> Subject: [asp_databases] Multiple SQL statements in one ASP page
>
>
> My question is this:
>
> Can you have two separate SQL Statements in one ASP page?
>
> I have two separate queries that work great on their own but when
> I try to
> combine them into one query it will not work.
>
> My idea is that I would run a query to display information that
> comes from
> a number of tables in my Access 2000 database, and then once that query
> has run I would run a count query on the number of company names
> displayed.
>
> If my method is correct, does anyone know the syntax for using two SQL
> statements in one ASP page?
>
>
> Cheers
>

The Dim intCount bit is where I think I'm going seriously wrong.

I want the count query to work from the varSQL query

The code below works but it show the word RecordNumber instead of the 
number that I want.


Now what I had in mind is something like this in my ASP page:




Dim varSQL

varSQL = " SELECT tblCompanyDetails.CompanyDetailsID, 
tblCompanyDetails.CompanyName, tblBorough.BoroughName, 
tblCertificate.CertificateID, tblGreenDragonLevel.Int_GreenDragonLevel" & _
" FROM tblBorough INNER JOIN (tblGreenDragonLevel INNER JOIN 
(tblCertificate INNER JOIN tblCompanyDetails ON 
tblCertificate.CertificateID = tblCompanyDetails.FKCertificateID) ON 
tblGreenDragonLevel.GreenDragonLevelID = 
tblCertificate.FKGreenDragonLevelID) ON tblBorough.BoroughID = 
tblCompanyDetails.FKBoroughID" & _
" WHERE Int_GreenDragonLevel = " & intGDLevel & _
" ORDER BY CompanyName "';"



Dim intCount

intCount = " SELECT select count(*) AS RecordNumber" &_
"FROM varSQL;"



Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open strConnect

objRS.Open varSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText


IF objRS.BOF AND objRS.EOF=TRUE Then

Response.Write "<HR>"

Response.Write "<B><font color=#FF0000><p align=center>"

Response.Write "Sorry, but curently there are no companies that have 
reached this level.<BR>"
Response.Write "Please click the back button and select a different level 
or try again soon"



ELSE

While Not objRS.EOF

Response.Write ("RecordNumber")

Response.Write "<TR><TD><A HREF = LiveExample3.asp?CompanyID=" & objRS
("CompanyDetailsID") &">" & objRS("CompanyName") & "</A></TD><TD>" &  objRS
("Int_GreenDragonLevel") & "</TD><TD>" &  objRS("BoroughName") 
& "</TD></TR>"
objRS.MoveNext

Wend

END IF


objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing

While Not objRS.EOF

Response.Write (objRS("RecordNumber"))


> -----Original Message-----
> From: IT@g... [mailto:IT@g...]
> Sent: Thursday, October 17, 2002 5:00 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Multiple SQL statements in one ASP page
> 
> 
> The Dim intCount bit is where I think I'm going seriously wrong.
> 
> I want the count query to work from the varSQL query
> 
> The code below works but it show the word RecordNumber instead of the 
> number that I want.
> 
> 
> Now what I had in mind is something like this in my ASP page:
> 
> 
> 
> 
> Dim varSQL
> 
> varSQL = " SELECT tblCompanyDetails.CompanyDetailsID, 
> tblCompanyDetails.CompanyName, tblBorough.BoroughName, 
> tblCertificate.CertificateID, 
> tblGreenDragonLevel.Int_GreenDragonLevel" & _
> " FROM tblBorough INNER JOIN (tblGreenDragonLevel INNER JOIN 
> (tblCertificate INNER JOIN tblCompanyDetails ON 
> tblCertificate.CertificateID = tblCompanyDetails.FKCertificateID) ON 
> tblGreenDragonLevel.GreenDragonLevelID = 
> tblCertificate.FKGreenDragonLevelID) ON tblBorough.BoroughID = 
> tblCompanyDetails.FKBoroughID" & _
> " WHERE Int_GreenDragonLevel = " & intGDLevel & _
> " ORDER BY CompanyName "';"
> 
> 
> 
> Dim intCount
> 
> intCount = " SELECT select count(*) AS RecordNumber" &_
> "FROM varSQL;"
> 
> 
> 
> Dim objConn, objRS
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject("ADODB.Recordset")
> 
> objConn.Open strConnect
> 
> objRS.Open varSQL, objConn, adOpenForwardOnly, 
> adLockReadOnly, adCmdText
> 
> 
> IF objRS.BOF AND objRS.EOF=TRUE Then
> 
> Response.Write "<HR>"
> 
> Response.Write "<B><font color=#FF0000><p align=center>"
> 
> Response.Write "Sorry, but curently there are no companies that have 
> reached this level.<BR>"
> Response.Write "Please click the back button and select a 
> different level 
> or try again soon"
> 
> 
> 
> ELSE
> 
> While Not objRS.EOF
> 
> Response.Write ("RecordNumber")
> 
> Response.Write "<TR><TD><A HREF = LiveExample3.asp?CompanyID=" & objRS
> ("CompanyDetailsID") &">" & objRS("CompanyName") & 
> "</A></TD><TD>" &  objRS
> ("Int_GreenDragonLevel") & "</TD><TD>" &  objRS("BoroughName") 
> & "</TD></TR>"
> objRS.MoveNext
> 
> Wend
> 
> END IF
> 
> 
> objRS.Close
> objConn.Close
> Set objRS = Nothing
> Set objConn = Nothing
>