|
 |
asp_databases thread: queries in ASP
Message #1 by IT@g... on Mon, 21 Oct 2002 15:42:57
|
|
Can anyone give me the correct syntax to use 2 select SQL queries in an
asp page?
I can only get 1 of my queries to work.
Thanks
Dennis
Message #2 by Mark Eckeard <meckeard2000@y...> on Mon, 21 Oct 2002 08:02:19 -0700 (PDT)
|
|
Dennis,
Post your code so we can see what you are doing/trying
to accomplish.
Mark
--- IT@g... wrote:
> Can anyone give me the correct syntax to use 2
> select SQL queries in an
> asp page?
>
> I can only get 1 of my queries to work.
>
>
> Thanks
>
>
>
> Dennis
__________________________________________________
Do you Yahoo!?
Y! Web Hosting - Let the expert host your web site
http://webhosting.yahoo.com/
Message #3 by "Drew, Ron" <RDrew@B...> on Mon, 21 Oct 2002 11:05:53 -0400
|
|
Joining 2 via something like a UNION or 2 Recordsets??
-----Original Message-----
From: IT@g... [mailto:IT@g...]
Sent: Monday, October 21, 2002 11:43 AM
To: ASP Databases
Subject: [asp_databases] queries in ASP
Can anyone give me the correct syntax to use 2 select SQL queries in an
asp page?
I can only get 1 of my queries to work.
Thanks
Dennis
Message #4 by IT@g... on Tue, 22 Oct 2002 11:45:58
|
|
Please help. I am completely stuck.
I would like to count the results of my varSQL query:
<%@ Language=VBScript %>
<%Option Explicit%>
<% Dim strConnect%>
<!--#include file="datastore.asp" -->
<!--#include file="adovbs.inc" -->
<HTML>
<HEAD>
<link rel="stylesheet" type="text/css" href="text.css">
</HEAD>
<BODY>
<div align="center">
<TABLE BORDER=1 width="602">
<TR>
<TD width="353" align="left"><B>Company Name</B></TD><TD width="200"
align="left"><B>Green Dragon Level</B></TD><TD width="238"
align="left"><B>County Borough</B></TD>
</TR>
<% Dim intGDLevel
intGDLevel = Request.Form ("GreenDragonLevel")
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 varCount
varCount = " SELECT count(CompanyName) 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 ""
ELSE
While Not objRS.EOF
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
Response.Write ("varCount")
Wend
END IF
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>
</TABLE>
</center>
</div>
</BODY>
</HTML>
Message #5 by Paul Bucknell <paul@d...> on Tue, 22 Oct 2002 20:48:19 +0100
|
|
noOfRecords=objRs.RecordCount
But it needs to be opened with something like....
objRS.Open varSQL, objConn, adOpenKeySet, adLockOptimistic, adCmdText
instead of.....
objRS.Open varSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
for it to work
Regards
Paul
At 11:45 22/10/02 +0000, you wrote:
>Please help. I am completely stuck.
>
>I would like to count the results of my varSQL query:
>
>
>
>
><%@ Language=VBScript %>
><%Option Explicit%>
><% Dim strConnect%>
>
><!--#include file="datastore.asp" -->
><!--#include file="adovbs.inc" -->
>
>Company NameGreen Dragon LevelCounty Borough
><% Dim intGDLevel intGDLevel = Request.Form ("GreenDragonLevel") 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 varCount varCount = " SELECT
>count(CompanyName) 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 "" ELSE While Not objRS.EOF
>Response.Write "
>
Message #6 by "Ken Schaefer" <ken@a...> on Wed, 23 Oct 2002 10:45:17 +1000
|
|
www.adopenstatic.com/faq/recordcountalternatives.asp explains better ways to
do what you want.
Get your data into an array, then evaluate the UBound() of the array to ind
out how many records you have.
If you want to do it the way you are currently doing it, you need to change
a few things.
varSQL is just a string. You send it to the database to be evaluated.
Database sends back results.
varCount is just another string. If you want to return a count, you need to
send it to the database to be evaluated. You can't query varSQL for a count.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <IT@g...>
Subject: [asp_databases] Re: queries in ASP
: Please help. I am completely stuck.
:
: I would like to count the results of my varSQL query:
:
: <%@ Language=VBScript %>
: <%Option Explicit%>
: <% Dim strConnect%>
:
: <!--#include file="datastore.asp" -->
: <!--#include file="adovbs.inc" -->
:
: <HTML>
: <HEAD>
: <link rel="stylesheet" type="text/css" href="text.css">
: </HEAD>
:
: <BODY>
:
:
: <div align="center">
:
:
: <TABLE BORDER=1 width="602">
: <TR>
: <TD width="353" align="left"><B>Company Name</B></TD><TD width="200"
: align="left"><B>Green Dragon Level</B></TD><TD width="238"
: align="left"><B>County Borough</B></TD>
:
: </TR>
:
:
:
:
:
: <% Dim intGDLevel
:
: intGDLevel = Request.Form ("GreenDragonLevel")
:
: 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 varCount
:
: varCount = " SELECT count(CompanyName) 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 ""
:
:
: ELSE
:
: While Not objRS.EOF
:
:
: 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
:
:
: Response.Write ("varCount")
:
:
:
: Wend
:
: END IF
:
:
: objRS.Close
: objConn.Close
: Set objRS = Nothing
: Set objConn = Nothing
:
: %>
:
: </TABLE>
:
: </center>
: </div>
:
:
: </BODY>
: </HTML>
:
|
|
|
View Cart
