|
 |
asp_databases thread: 2 sql queries in an ASP page
Message #1 by IT@g... on Wed, 11 Dec 2002 16:51:21
|
|
The information listed below is for 2 SQL queries in one ASP page.
The first query will display the information and the second query will
count how many records have been displayed.
See it working at http://www.greendragonwales.com/searchlevel.asp
I hope some of you will find this useful.
I wish someone had put this information up for me in the past.
Here's the code
<%@ Language=VBScript %>
<%Option Explicit%>
' Declare the connection string
<% Dim strConnect%>
' Server side includes
' Pathkeep points to the filename and path of the database
<!--#include file="pathkeep.asp" -->
<!--#include file="adovbs.inc" -->
' Open the ASP code
<%
' I collect the information from a form on a previous page
Dim intGDLevel
intGDLevel = Request.Form ("GreenDragonLevel")
' Declare your first SQL statement here
Dim varSQL
varSQL = " SELECT CompanyDetailsID, CompanyName, BoroughName,
CertificateID, 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 "';"
' Declare your objects, connection method and your first record set
Dim objConn, objRS1
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS1 = Server.CreateObject("ADODB.Recordset")
' Open the connection string
objConn.Open strConnect
' Open the first record set, execute the SQL, set the direction, read
only, the
command is text
objRS1.Open varSQL, objConn, adOpenDynamic, adLockReadOnly, adCmdText
' If the first record set is empty, write an error message to the screen
IF objRS1.BOF AND objRS1.EOF=TRUE Then
Response.Write "<HR>"
Response.Write "<B><font color=#FF0000><p align=center>"
Response.Write "Sorry, but currently 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"
Response.Write "<HR>"
Response.Write "<p><br>"
' Else, while the recordset is not empty continue the loop
ELSE
While Not objRS1.EOF
Response.Write "<TR><TD><A HREF = LiveExample3.asp?CompanyID=" & objRS1
("CompanyDetailsID") &">" & objRS1("CompanyName") & "</A></TD><TD>" &
objRS1("Int_GreenDragonLevel") & "</TD><TD>" & objRS1("BoroughName")
& "</TD></TR>"
' Move to the next record set
objRS1.MoveNext
' End the While statement
Wend
' End the If statement
END IF
' Close the first recordset and set it to nothing
objRS1.Close
Set objRS1 = Nothing
''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''
' Declare your second record set
Dim objRS2
Set objRS2 = Server.CreateObject ("ADODB.Recordset")
' Declare your second SQL statement. This is a count query that will
count the number of records that the first query has displayed.
Dim varSQL2
varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS RecordNumber" & _
" FROM tblGreenDragonLevel INNER JOIN (tblCertificate INNER JOIN
tblCompanyDetails ON tblCertificate.CertificateID =
tblCompanyDetails.FKCertificateID) ON
tblGreenDragonLevel.GreenDragonLevelID =
tblCertificate.FKGreenDragonLevelID" & _
" WHERE Int_GreenDragonLevel = " & intGDLevel &";"
' Open the second record set, execute the SQL, set the direction, read
only,
the command is text
objRS2.Open varSQL2, objConn, adOpenDynamic, adLockReadOnly, adCmdText
' If the second record set is empty, write nothing to the screen
IF objRS2.BOF AND objRS2.EOF=TRUE Then
Response.Write " "
' Else, while the recordset is not empty continue the loop
ELSE
While Not objRS2.EOF
Response.Write "<hr>"
Response.Write "<p align=center>"
Response.Write ("<font color=#FF0000><font face=Arial size=5>")
Response.Write objRS2("RecordNumber")
Response.Write ("</font color=#FF0000></font face>")
Response.Write (" companies in total have achieved Green Dragon Level ")
Response.Write ("<font color=#FF0000><font face=Arial size=5>")
Response.Write intGDLevel
Response.Write ("</font color=#FF0000></font face>")
Response.Write "<br>"
Response.Write "<hr>"
Response.Write "<br>"
Response.Write "<br>"
Response.Write "<br>"
' Move to the next record set
objRS2.Move(2)
' End the While statement
Wend
' End the If statement
END IF
' Close the second recordset and set it to nothing
objRS2.Close
set objRS2 = Nothing
' Close the database connection and set it to nothing
objConn.Close
Set objConn = Nothing
' Close the ASP code
%>
Message #2 by Mark Eckeard <meckeard2000@y...> on Wed, 11 Dec 2002 08:42:03 -0800 (PST)
|
|
Just out of curiosity, why do you need to run another
query to get the number of records displayed? Can't
you just use the recordset.recordcount?
Mark.
--- IT@g... wrote:
> The information listed below is for 2 SQL queries in
> one ASP page.
>
> The first query will display the information and the
> second query will
> count how many records have been displayed.
>
> See it working at
> http://www.greendragonwales.com/searchlevel.asp
>
>
>
> I hope some of you will find this useful.
> I wish someone had put this information up for me in
> the past.
>
>
>
> Here's the code
>
>
>
> <%@ Language=VBScript %>
> <%Option Explicit%>
>
> ' Declare the connection string
> <% Dim strConnect%>
>
>
> ' Server side includes
> ' Pathkeep points to the filename and path of the
> database
> <!--#include file="pathkeep.asp" -->
> <!--#include file="adovbs.inc" -->
>
>
> ' Open the ASP code
> <%
>
> ' I collect the information from a form on a
> previous page
> Dim intGDLevel
>
> intGDLevel = Request.Form ("GreenDragonLevel")
>
>
> ' Declare your first SQL statement here
> Dim varSQL
>
> varSQL = " SELECT CompanyDetailsID, CompanyName,
> BoroughName,
> CertificateID, 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 "';"
>
>
>
> ' Declare your objects, connection method and your
> first record set
> Dim objConn, objRS1
> Set objConn
> Server.CreateObject("ADODB.Connection")
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
>
>
> ' Open the connection string
> objConn.Open strConnect
>
>
>
> ' Open the first record set, execute the SQL, set
> the direction, read
> only, the
> command is text
> objRS1.Open varSQL, objConn, adOpenDynamic,
> adLockReadOnly, adCmdText
>
>
>
> ' If the first record set is empty, write an error
> message to the screen
> IF objRS1.BOF AND objRS1.EOF=TRUE Then
>
> Response.Write "<HR>"
>
> Response.Write "<B><font color=#FF0000><p
> align=center>"
>
> Response.Write "Sorry, but currently 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"
>
>
>
> Response.Write "<HR>"
>
> Response.Write "<p><br>"
>
>
>
> ' Else, while the recordset is not empty continue
> the loop
> ELSE
>
> While Not objRS1.EOF
>
>
> Response.Write "<TR><TD><A HREF
> LiveExample3.asp?CompanyID=" & objRS1
> ("CompanyDetailsID") &">" & objRS1("CompanyName") &
> "</A></TD><TD>" &
> objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> objRS1("BoroughName")
> & "</TD></TR>"
>
>
> ' Move to the next record set
> objRS1.MoveNext
>
>
> ' End the While statement
> Wend
>
>
> ' End the If statement
> END IF
>
>
> ' Close the first recordset and set it to nothing
> objRS1.Close
> Set objRS1 = Nothing
>
>
>
>
>
> ''''''''''''''''''''''''''''''
> ''''''''''''''''''''''''''''''
>
>
>
>
> ' Declare your second record set
> Dim objRS2
> Set objRS2 = Server.CreateObject ("ADODB.Recordset")
>
>
> ' Declare your second SQL statement. This is a
> count query that will
> count the number of records that the first query has
> displayed.
> Dim varSQL2
> varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS
> RecordNumber" & _
> " FROM tblGreenDragonLevel INNER JOIN
> (tblCertificate INNER JOIN
> tblCompanyDetails ON tblCertificate.CertificateID =
> tblCompanyDetails.FKCertificateID) ON
> tblGreenDragonLevel.GreenDragonLevelID =
> tblCertificate.FKGreenDragonLevelID" & _
> " WHERE Int_GreenDragonLevel = " & intGDLevel &";"
>
>
> ' Open the second record set, execute the SQL, set
> the direction, read
> only,
> the command is text
> objRS2.Open varSQL2, objConn, adOpenDynamic,
> adLockReadOnly, adCmdText
>
>
> ' If the second record set is empty, write nothing
> to the screen
> IF objRS2.BOF AND objRS2.EOF=TRUE Then
>
> Response.Write " "
>
>
> ' Else, while the recordset is not empty continue
> the loop
> ELSE
>
>
> While Not objRS2.EOF
>
> Response.Write "<hr>"
>
> Response.Write "<p align=center>"
> Response.Write ("<font color=#FF0000><font
> face=Arial
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 11 Dec 2002 18:16:13 +0100
|
|
Or use getrows() and measure the size of the second dimension of the
returned array for better performance.
-Kim
-----Original Message-----
From: Mark Eckeard [mailto:meckeard2000@y...]
Sent: 11. december 2002 17:42
To: ASP Databases
Subject: [asp_databases] Re: 2 sql queries in an ASP page
Just out of curiosity, why do you need to run another
query to get the number of records displayed? Can't
you just use the recordset.recordcount?
Mark.
--- IT@g... wrote:
> The information listed below is for 2 SQL queries in
> one ASP page.
>
> The first query will display the information and the
> second query will
> count how many records have been displayed.
>
> See it working at
> http://www.greendragonwales.com/searchlevel.asp
>
>
>
> I hope some of you will find this useful.
> I wish someone had put this information up for me in
> the past.
>
>
>
> Here's the code
>
>
>
> <%@ Language=VBScript %>
> <%Option Explicit%>
>
> ' Declare the connection string
> <% Dim strConnect%>
>
>
> ' Server side includes
> ' Pathkeep points to the filename and path of the
> database
> <!--#include file="pathkeep.asp" -->
> <!--#include file="adovbs.inc" -->
>
>
> ' Open the ASP code
> <%
>
> ' I collect the information from a form on a
> previous page
> Dim intGDLevel
>
> intGDLevel = Request.Form ("GreenDragonLevel")
>
>
> ' Declare your first SQL statement here
> Dim varSQL
>
> varSQL = " SELECT CompanyDetailsID, CompanyName,
> BoroughName,
> CertificateID, 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 "';"
>
>
>
> ' Declare your objects, connection method and your
> first record set
> Dim objConn, objRS1
> Set objConn
> Server.CreateObject("ADODB.Connection")
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
>
>
> ' Open the connection string
> objConn.Open strConnect
>
>
>
> ' Open the first record set, execute the SQL, set
> the direction, read
> only, the
> command is text
> objRS1.Open varSQL, objConn, adOpenDynamic,
> adLockReadOnly, adCmdText
>
>
>
> ' If the first record set is empty, write an error
> message to the screen
> IF objRS1.BOF AND objRS1.EOF=TRUE Then
>
> Response.Write "<HR>"
>
> Response.Write "<B><font color=#FF0000><p
> align=center>"
>
> Response.Write "Sorry, but currently 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"
>
>
>
> Response.Write "<HR>"
>
> Response.Write "<p><br>"
>
>
>
> ' Else, while the recordset is not empty continue
> the loop
> ELSE
>
> While Not objRS1.EOF
>
>
> Response.Write "<TR><TD><A HREF
> LiveExample3.asp?CompanyID=" & objRS1
> ("CompanyDetailsID") &">" & objRS1("CompanyName") &
> "</A></TD><TD>" &
> objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> objRS1("BoroughName")
> & "</TD></TR>"
>
>
> ' Move to the next record set
> objRS1.MoveNext
>
>
> ' End the While statement
> Wend
>
>
> ' End the If statement
> END IF
>
>
> ' Close the first recordset and set it to nothing
> objRS1.Close
> Set objRS1 = Nothing
>
>
>
>
>
> ''''''''''''''''''''''''''''''
> ''''''''''''''''''''''''''''''
>
>
>
>
> ' Declare your second record set
> Dim objRS2
> Set objRS2 = Server.CreateObject ("ADODB.Recordset")
>
>
> ' Declare your second SQL statement. This is a
> count query that will
> count the number of records that the first query has
> displayed.
> Dim varSQL2
> varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS
> RecordNumber" & _
> " FROM tblGreenDragonLevel INNER JOIN
> (tblCertificate INNER JOIN
> tblCompanyDetails ON tblCertificate.CertificateID
> tblCompanyDetails.FKCertificateID) ON
> tblGreenDragonLevel.GreenDragonLevelID
> tblCertificate.FKGreenDragonLevelID" & _
> " WHERE Int_GreenDragonLevel = " & intGDLevel &";"
>
>
> ' Open the second record set, execute the SQL, set
> the direction, read
> only,
> the command is text
> objRS2.Open varSQL2, objConn, adOpenDynamic,
> adLockReadOnly, adCmdText
>
>
> ' If the second record set is empty, write nothing
> to the screen
> IF objRS2.BOF AND objRS2.EOF=TRUE Then
>
> Response.Write " "
>
>
> ' Else, while the recordset is not empty continue
> the loop
> ELSE
>
>
> While Not objRS2.EOF
>
> Response.Write "<hr>"
>
> Response.Write "<p align=center>"
> Response.Write ("<font color=#FF0000><font
> face=Arial
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #4 by Mark Eckeard <meckeard2000@y...> on Wed, 11 Dec 2002 10:03:07 -0800 (PST)
|
|
Or just increment a counter as you loop thru the
array/recordset and display it at the end.
It would save an extra hit to the database.
Mark
--- Kim Iwan Hansen <kimiwan@k...> wrote:
> Or use getrows() and measure the size of the second
> dimension of the
> returned array for better performance.
>
> -Kim
>
> -----Original Message-----
> From: Mark Eckeard [mailto:meckeard2000@y...]
> Sent: 11. december 2002 17:42
> To: ASP Databases
> Subject: [asp_databases] Re: 2 sql queries in an ASP
> page
>
>
> Just out of curiosity, why do you need to run
> another
> query to get the number of records displayed? Can't
> you just use the recordset.recordcount?
>
> Mark.
> --- IT@g... wrote:
> > The information listed below is for 2 SQL queries
> in
> > one ASP page.
> >
> > The first query will display the information and
> the
> > second query will
> > count how many records have been displayed.
> >
> > See it working at
> > http://www.greendragonwales.com/searchlevel.asp
> >
> >
> >
> > I hope some of you will find this useful.
> > I wish someone had put this information up for me
> in
> > the past.
> >
> >
> >
> > Here's the code
> >
> >
> >
> > <%@ Language=VBScript %>
> > <%Option Explicit%>
> >
> > ' Declare the connection string
> > <% Dim strConnect%>
> >
> >
> > ' Server side includes
> > ' Pathkeep points to the filename and path of the
> > database
> > <!--#include file="pathkeep.asp" -->
> > <!--#include file="adovbs.inc" -->
> >
> >
> > ' Open the ASP code
> > <%
> >
> > ' I collect the information from a form on a
> > previous page
> > Dim intGDLevel
> >
> > intGDLevel = Request.Form ("GreenDragonLevel")
> >
> >
> > ' Declare your first SQL statement here
> > Dim varSQL
> >
> > varSQL = " SELECT CompanyDetailsID, CompanyName,
> > BoroughName,
> > CertificateID, 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 "';"
> >
> >
> >
> > ' Declare your objects, connection method and your
> > first record set
> > Dim objConn, objRS1
> > Set objConn
> > Server.CreateObject("ADODB.Connection")
> > Set objRS1
> Server.CreateObject("ADODB.Recordset")
> >
> >
> > ' Open the connection string
> > objConn.Open strConnect
> >
> >
> >
> > ' Open the first record set, execute the SQL, set
> > the direction, read
> > only, the
> > command is text
> > objRS1.Open varSQL, objConn, adOpenDynamic,
> > adLockReadOnly, adCmdText
> >
> >
> >
> > ' If the first record set is empty, write an error
> > message to the screen
> > IF objRS1.BOF AND objRS1.EOF=TRUE Then
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<B><font color=#FF0000><p
> > align=center>"
> >
> > Response.Write "Sorry, but currently 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"
> >
> >
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<p><br>"
> >
> >
> >
> > ' Else, while the recordset is not empty continue
> > the loop
> > ELSE
> >
> > While Not objRS1.EOF
> >
> >
> > Response.Write "<TR><TD><A HREF
> > LiveExample3.asp?CompanyID=" & objRS1
> > ("CompanyDetailsID") &">" & objRS1("CompanyName")
> &
> > "</A></TD><TD>" &
> > objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> > objRS1("BoroughName")
> > & "</TD></TR>"
> >
> >
> > ' Move to the next record set
> > objRS1.MoveNext
> >
> >
> > ' End the While statement
> > Wend
> >
> >
> > ' End the If statement
> > END IF
> >
> >
> > ' Close the first recordset and set it to nothing
> > objRS1.Close
> > Set objRS1 = Nothing
> >
> >
> >
> >
> >
> > ''''''''''''''''''''''''''''''
> > ''''''''''''''''''''''''''''''
> >
> >
> >
> >
> > ' Declare your second record set
> > Dim objRS2
> > Set objRS2 = Server.CreateObject
> ("ADODB.Recordset")
> >
> >
> > ' Declare your second SQL statement. This is a
> > count query that will
> > count the number of records that the first query
> has
> > displayed.
> > Dim varSQL2
> > varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS
> > RecordNumber" & _
> > " FROM tblGreenDragonLevel INNER JOIN
> > (tblCertificate INNER JOIN
> > tblCompanyDetails ON tblCertificate.CertificateID
>
> > tblCompanyDetails.FKCertificateID) ON
> > tblGreenDragonLevel.GreenDragonLevelID
> > tblCertificate.FKGreenDragonLevelID" & _
>
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #5 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 11 Dec 2002 20:19:23 +0100
|
|
It's still considerably faster to iterate through the array returned by
getrows() than stepping through the recordset using the movenext() method.
-Kim
-----Original Message-----
From: Mark Eckeard [mailto:meckeard2000@y...]
Sent: 11. december 2002 19:03
To: ASP Databases
Subject: [asp_databases] Re: 2 sql queries in an ASP page
Or just increment a counter as you loop thru the
array/recordset and display it at the end.
It would save an extra hit to the database.
Mark
--- Kim Iwan Hansen <kimiwan@k...> wrote:
> Or use getrows() and measure the size of the second
> dimension of the
> returned array for better performance.
>
> -Kim
>
> -----Original Message-----
> From: Mark Eckeard [mailto:meckeard2000@y...]
> Sent: 11. december 2002 17:42
> To: ASP Databases
> Subject: [asp_databases] Re: 2 sql queries in an ASP
> page
>
>
> Just out of curiosity, why do you need to run
> another
> query to get the number of records displayed? Can't
> you just use the recordset.recordcount?
>
> Mark.
> --- IT@g... wrote:
> > The information listed below is for 2 SQL queries
> in
> > one ASP page.
> >
> > The first query will display the information and
> the
> > second query will
> > count how many records have been displayed.
> >
> > See it working at
> > http://www.greendragonwales.com/searchlevel.asp
> >
> >
> >
> > I hope some of you will find this useful.
> > I wish someone had put this information up for me
> in
> > the past.
> >
> >
> >
> > Here's the code
> >
> >
> >
> > <%@ Language=VBScript %>
> > <%Option Explicit%>
> >
> > ' Declare the connection string
> > <% Dim strConnect%>
> >
> >
> > ' Server side includes
> > ' Pathkeep points to the filename and path of the
> > database
> > <!--#include file="pathkeep.asp" -->
> > <!--#include file="adovbs.inc" -->
> >
> >
> > ' Open the ASP code
> > <%
> >
> > ' I collect the information from a form on a
> > previous page
> > Dim intGDLevel
> >
> > intGDLevel = Request.Form ("GreenDragonLevel")
> >
> >
> > ' Declare your first SQL statement here
> > Dim varSQL
> >
> > varSQL = " SELECT CompanyDetailsID, CompanyName,
> > BoroughName,
> > CertificateID, 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 "';"
> >
> >
> >
> > ' Declare your objects, connection method and your
> > first record set
> > Dim objConn, objRS1
> > Set objConn
> > Server.CreateObject("ADODB.Connection")
> > Set objRS1
> Server.CreateObject("ADODB.Recordset")
> >
> >
> > ' Open the connection string
> > objConn.Open strConnect
> >
> >
> >
> > ' Open the first record set, execute the SQL, set
> > the direction, read
> > only, the
> > command is text
> > objRS1.Open varSQL, objConn, adOpenDynamic,
> > adLockReadOnly, adCmdText
> >
> >
> >
> > ' If the first record set is empty, write an error
> > message to the screen
> > IF objRS1.BOF AND objRS1.EOF=TRUE Then
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<B><font color=#FF0000><p
> > align=center>"
> >
> > Response.Write "Sorry, but currently 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"
> >
> >
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<p><br>"
> >
> >
> >
> > ' Else, while the recordset is not empty continue
> > the loop
> > ELSE
> >
> > While Not objRS1.EOF
> >
> >
> > Response.Write "<TR><TD><A HREF
> > LiveExample3.asp?CompanyID=" & objRS1
> > ("CompanyDetailsID") &">" & objRS1("CompanyName")
> &
> > "</A></TD><TD>" &
> > objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> > objRS1("BoroughName")
> > & "</TD></TR>"
> >
> >
> > ' Move to the next record set
> > objRS1.MoveNext
> >
> >
> > ' End the While statement
> > Wend
> >
> >
> > ' End the If statement
> > END IF
> >
> >
> > ' Close the first recordset and set it to nothing
> > objRS1.Close
> > Set objRS1 = Nothing
> >
> >
> >
> >
> >
> > ''''''''''''''''''''''''''''''
> > ''''''''''''''''''''''''''''''
> >
> >
> >
> >
> > ' Declare your second record set
> > Dim objRS2
> > Set objRS2 = Server.CreateObject
> ("ADODB.Recordset")
> >
> >
> > ' Declare your second SQL statement. This is a
> > count query that will
> > count the number of records that the first query
> has
> > displayed.
> > Dim varSQL2
> > varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS
> > RecordNumber" & _
> > " FROM tblGreenDragonLevel INNER JOIN
> > (tblCertificate INNER JOIN
> > tblCompanyDetails ON tblCertificate.CertificateID
>
> > tblCompanyDetails.FKCertificateID) ON
> > tblGreenDragonLevel.GreenDragonLevelID
> > tblCertificate.FKGreenDragonLevelID" & _
>
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #6 by Mark Eckeard <meckeard2000@y...> on Wed, 11 Dec 2002 12:25:56 -0800 (PST)
|
|
True. I was just offering an alternative.
Mark.
--- Kim Iwan Hansen <kimiwan@k...> wrote:
> It's still considerably faster to iterate through
> the array returned by
> getrows() than stepping through the recordset using
> the movenext() method.
>
> -Kim
>
> -----Original Message-----
> From: Mark Eckeard [mailto:meckeard2000@y...]
> Sent: 11. december 2002 19:03
> To: ASP Databases
> Subject: [asp_databases] Re: 2 sql queries in an ASP
> page
>
>
> Or just increment a counter as you loop thru the
> array/recordset and display it at the end.
>
> It would save an extra hit to the database.
>
> Mark
> --- Kim Iwan Hansen <kimiwan@k...> wrote:
> > Or use getrows() and measure the size of the
> second
> > dimension of the
> > returned array for better performance.
> >
> > -Kim
> >
> > -----Original Message-----
> > From: Mark Eckeard [mailto:meckeard2000@y...]
> > Sent: 11. december 2002 17:42
> > To: ASP Databases
> > Subject: [asp_databases] Re: 2 sql queries in an
> ASP
> > page
> >
> >
> > Just out of curiosity, why do you need to run
> > another
> > query to get the number of records displayed?
> Can't
> > you just use the recordset.recordcount?
> >
> > Mark.
> > --- IT@g... wrote:
> > > The information listed below is for 2 SQL
> queries
> > in
> > > one ASP page.
> > >
> > > The first query will display the information and
> > the
> > > second query will
> > > count how many records have been displayed.
> > >
> > > See it working at
> > > http://www.greendragonwales.com/searchlevel.asp
> > >
> > >
> > >
> > > I hope some of you will find this useful.
> > > I wish someone had put this information up for
> me
> > in
> > > the past.
> > >
> > >
> > >
> > > Here's the code
> > >
> > >
> > >
> > > <%@ Language=VBScript %>
> > > <%Option Explicit%>
> > >
> > > ' Declare the connection string
> > > <% Dim strConnect%>
> > >
> > >
> > > ' Server side includes
> > > ' Pathkeep points to the filename and path of
> the
> > > database
> > > <!--#include file="pathkeep.asp" -->
> > > <!--#include file="adovbs.inc" -->
> > >
> > >
> > > ' Open the ASP code
> > > <%
> > >
> > > ' I collect the information from a form on a
> > > previous page
> > > Dim intGDLevel
> > >
> > > intGDLevel = Request.Form ("GreenDragonLevel")
> > >
> > >
> > > ' Declare your first SQL statement here
> > > Dim varSQL
> > >
> > > varSQL = " SELECT CompanyDetailsID, CompanyName,
> > > BoroughName,
> > > CertificateID, 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 "';"
> > >
> > >
> > >
> > > ' Declare your objects, connection method and
> your
> > > first record set
> > > Dim objConn, objRS1
> > > Set objConn
> > > Server.CreateObject("ADODB.Connection")
> > > Set objRS1
> > Server.CreateObject("ADODB.Recordset")
> > >
> > >
> > > ' Open the connection string
> > > objConn.Open strConnect
> > >
> > >
> > >
> > > ' Open the first record set, execute the SQL,
> set
> > > the direction, read
> > > only, the
> > > command is text
> > > objRS1.Open varSQL, objConn, adOpenDynamic,
> > > adLockReadOnly, adCmdText
> > >
> > >
> > >
> > > ' If the first record set is empty, write an
> error
> > > message to the screen
> > > IF objRS1.BOF AND objRS1.EOF=TRUE Then
> > >
> > > Response.Write "<HR>"
> > >
> > > Response.Write "<B><font color=#FF0000><p
> > > align=center>"
> > >
> > > Response.Write "Sorry, but currently 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"
> > >
> > >
> > >
> > > Response.Write "<HR>"
> > >
> > > Response.Write "<p><br>"
> > >
> > >
> > >
> > > ' Else, while the recordset is not empty
> continue
> > > the loop
> > > ELSE
> > >
> > > While Not objRS1.EOF
> > >
> > >
> > > Response.Write "<TR><TD><A HREF
> > > LiveExample3.asp?CompanyID=" & objRS1
> > > ("CompanyDetailsID") &">" &
> objRS1("CompanyName")
> > &
> > > "</A></TD><TD>" &
> > > objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> > > objRS1("BoroughName")
> > > & "</TD></TR>"
> > >
> > >
> > > ' Move to the next record set
> > > objRS1.MoveNext
> > >
> > >
> > > ' End the While statement
> > > Wend
> > >
> > >
> > > ' End the If statement
> > > END IF
> > >
>
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #7 by "Nirav Pradhan" <niravpradhan@y...> on Wed, 11 Dec 2002 20:42:35
|
|
I was wondering if it is possible to count distinct using getrows()in SQL
statement like "SELECT KeyWords, COUNT(KeyWords) AS Total FROM tblKeyWord
GROUP BY KeyWords"
What would the code looks like?
> It's still considerably faster to iterate through the array returned by
getrows() than stepping through the recordset using the movenext() method.
-Kim
-----Original Message-----
From: Mark Eckeard [mailto:meckeard2000@y...]
Sent: 11. december 2002 19:03
To: ASP Databases
Subject: [asp_databases] Re: 2 sql queries in an ASP page
Or just increment a counter as you loop thru the
array/recordset and display it at the end.
It would save an extra hit to the database.
Mark
--- Kim Iwan Hansen <kimiwan@k...> wrote:
> Or use getrows() and measure the size of the second
> dimension of the
> returned array for better performance.
>
> -Kim
>
> -----Original Message-----
> From: Mark Eckeard [mailto:meckeard2000@y...]
> Sent: 11. december 2002 17:42
> To: ASP Databases
> Subject: [asp_databases] Re: 2 sql queries in an ASP
> page
>
>
> Just out of curiosity, why do you need to run
> another
> query to get the number of records displayed? Can't
> you just use the recordset.recordcount?
>
> Mark.
> --- IT@g... wrote:
> > The information listed below is for 2 SQL queries
> in
> > one ASP page.
> >
> > The first query will display the information and
> the
> > second query will
> > count how many records have been displayed.
> >
> > See it working at
> > http://www.greendragonwales.com/searchlevel.asp
> >
> >
> >
> > I hope some of you will find this useful.
> > I wish someone had put this information up for me
> in
> > the past.
> >
> >
> >
> > Here's the code
> >
> >
> >
> > <%@ Language=VBScript %>
> > <%Option Explicit%>
> >
> > ' Declare the connection string
> > <% Dim strConnect%>
> >
> >
> > ' Server side includes
> > ' Pathkeep points to the filename and path of the
> > database
> > <!--#include file="pathkeep.asp" -->
> > <!--#include file="adovbs.inc" -->
> >
> >
> > ' Open the ASP code
> > <%
> >
> > ' I collect the information from a form on a
> > previous page
> > Dim intGDLevel
> >
> > intGDLevel = Request.Form ("GreenDragonLevel")
> >
> >
> > ' Declare your first SQL statement here
> > Dim varSQL
> >
> > varSQL = " SELECT CompanyDetailsID, CompanyName,
> > BoroughName,
> > CertificateID, 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 "';"
> >
> >
> >
> > ' Declare your objects, connection method and your
> > first record set
> > Dim objConn, objRS1
> > Set objConn
> > Server.CreateObject("ADODB.Connection")
> > Set objRS1
> Server.CreateObject("ADODB.Recordset")
> >
> >
> > ' Open the connection string
> > objConn.Open strConnect
> >
> >
> >
> > ' Open the first record set, execute the SQL, set
> > the direction, read
> > only, the
> > command is text
> > objRS1.Open varSQL, objConn, adOpenDynamic,
> > adLockReadOnly, adCmdText
> >
> >
> >
> > ' If the first record set is empty, write an error
> > message to the screen
> > IF objRS1.BOF AND objRS1.EOF=TRUE Then
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<B><font color=#FF0000><p
> > align=center>"
> >
> > Response.Write "Sorry, but currently 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"
> >
> >
> >
> > Response.Write "<HR>"
> >
> > Response.Write "<p><br>"
> >
> >
> >
> > ' Else, while the recordset is not empty continue
> > the loop
> > ELSE
> >
> > While Not objRS1.EOF
> >
> >
> > Response.Write "<TR><TD><A HREF
> > LiveExample3.asp?CompanyID=" & objRS1
> > ("CompanyDetailsID") &">" & objRS1("CompanyName")
> &
> > "</A></TD><TD>" &
> > objRS1("Int_GreenDragonLevel") & "</TD><TD>" &
> > objRS1("BoroughName")
> > & "</TD></TR>"
> >
> >
> > ' Move to the next record set
> > objRS1.MoveNext
> >
> >
> > ' End the While statement
> > Wend
> >
> >
> > ' End the If statement
> > END IF
> >
> >
> > ' Close the first recordset and set it to nothing
> > objRS1.Close
> > Set objRS1 = Nothing
> >
> >
> >
> >
> >
> > ''''''''''''''''''''''''''''''
> > ''''''''''''''''''''''''''''''
> >
> >
> >
> >
> > ' Declare your second record set
> > Dim objRS2
> > Set objRS2 = Server.CreateObject
> ("ADODB.Recordset")
> >
> >
> > ' Declare your second SQL statement. This is a
> > count query that will
> > count the number of records that the first query
> has
> > displayed.
> > Dim varSQL2
> > varSQL2 = " SELECT Count(Int_GreenDragonLevel) AS
> > RecordNumber" & _
> > " FROM tblGreenDragonLevel INNER JOIN
> > (tblCertificate INNER JOIN
> > tblCompanyDetails ON tblCertificate.CertificateID
>
> > tblCompanyDetails.FKCertificateID) ON
> > tblGreenDragonLevel.GreenDragonLevelID
> > tblCertificate.FKGreenDragonLevelID" & _
>
=== message truncated ===
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Message #8 by pgtips@m... on Thu, 12 Dec 2002 14:24:45
|
|
Oh dear, Ken will not be impressed with this one :-)
You publish on the web your url, your database structure and the fact that
you don't validate input from the client. SQL injection attack anyone?
Take a look here to see what you could be letting yourself in for:
http://downloads.securityfocus.com/library/advanced_sql_injection.pdf
At least you named your include file pathkeep.asp not .inc.
;-)
Phil
>-----------------------------------------------
> The information listed below is for 2 SQL queries in one ASP page.
> The first query will display the information and the second query will
c> ount how many records have been displayed.
> See it working at
http://www.greendragonwales.com/searchlevel.asp
...clipped
|
|
|
View Cart
