By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Do Until Loop problem

P: n/a
I am trying to display records from a recordset after sql statement:

<% sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '" &
request.querystring("trackname") & "' and racedate = '" &
request.querystring("racedate");"
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open sqlstr,pConn,3

do until rs1.eof

response.write left(rs1("horsename"),14)

rno = rno + 1
rtg = rtg + 1

rs1.Close
set rs1 = nothing
loop %>

...but when I test the querystring link going into the page with the asp
scripting, I do not see my horsename(s) display; it just echoes a blank
webpage

??
E.M.

Jul 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

".Net Sports" <ba********@cox.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I am trying to display records from a recordset after sql statement:

<% sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '" &
request.querystring("trackname") & "' and racedate = '" &
request.querystring("racedate");"
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open sqlstr,pConn,3

do until rs1.eof

response.write left(rs1("horsename"),14)

rno = rno + 1
rtg = rtg + 1

rs1.Close
set rs1 = nothing
loop %>

..but when I test the querystring link going into the page with the asp
scripting, I do not see my horsename(s) display; it just echoes a blank
webpage

??
E.M.


You are destroying the recordset rs1 before calling loop. You are also
missing the movenext method. I'm also assuming that you want a line break
between each horse name, so I've included one in the response.write. If you
just want a space, substitute <br> for &nbsp;

Try this:

<%
sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '" &
request.querystring("trackname") & "' and racedate = '" &
request.querystring("racedate");"

Set rs1 = pConn.execute(sqlstr)

do until rs1.eof
response.write left(rs1("horsename"),14) & "<br>"
rs1.movenext
loop

rs1.close : set rs1 = nothing
%>

Morris
Jul 22 '05 #2

P: n/a
..Net Sports wrote:
I am trying to display records from a recordset after sql statement:

<% sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '"
& request.querystring("trackname") & "' and racedate = '" &
request.querystring("racedate");"
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open sqlstr,pConn,3

do until rs1.eof

response.write left(rs1("horsename"),14)

rno = rno + 1
rtg = rtg + 1

rs1.Close
set rs1 = nothing
loop %>

..but when I test the querystring link going into the page with the
asp scripting, I do not see my horsename(s) display; it just echoes a
blank webpage

??
E.M.


Morris corrected your code. Here's a security and performance tip:

dim pConn, rs, cmd, arData, arParms,sqlstr, i
arParms=Array(request.querystring("trackname"), _
request.querystring("racedate"))

sqlstr ="SELECT horsename FROM tblhorseentry " & _
WHERE trackname = ? and racedate = ?"

'initialize and open pconn, then

Set cmd=createobject("adodb.command")
cmd.commandtype=1
cmd.commandtext=sqlstr
Set cmd.activeconnection=pConn
Set rs = cmd.Execute(,arParms)

if not rs.EOF then arData=rs.GetRows
rs.Close: Set rs=nothing
pConn.close: set pConn=nothing

if isArray(arData) then
for i = 0 to ubound(arData,2)
response.write left(arData(0,i),14)
rno = rno + 1
rtg = rtg + 1
next
else
response.Write "No records returned"
end if
HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #3

P: n/a

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eB*************@TK2MSFTNGP15.phx.gbl...
.Net Sports wrote:
I am trying to display records from a recordset after sql statement:

<% sqlstr ="SELECT horsename FROM tblhorseentry WHERE trackname = '"
& request.querystring("trackname") & "' and racedate = '" &
request.querystring("racedate");"
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open sqlstr,pConn,3

do until rs1.eof

response.write left(rs1("horsename"),14)

rno = rno + 1
rtg = rtg + 1

rs1.Close
set rs1 = nothing
loop %>

..but when I test the querystring link going into the page with the
asp scripting, I do not see my horsename(s) display; it just echoes a
blank webpage

??
E.M.


Morris corrected your code. Here's a security and performance tip:

dim pConn, rs, cmd, arData, arParms,sqlstr, i
arParms=Array(request.querystring("trackname"), _
request.querystring("racedate"))

sqlstr ="SELECT horsename FROM tblhorseentry " & _
WHERE trackname = ? and racedate = ?"

'initialize and open pconn, then

Set cmd=createobject("adodb.command")
cmd.commandtype=1
cmd.commandtext=sqlstr
Set cmd.activeconnection=pConn
Set rs = cmd.Execute(,arParms)

if not rs.EOF then arData=rs.GetRows
rs.Close: Set rs=nothing
pConn.close: set pConn=nothing

if isArray(arData) then
for i = 0 to ubound(arData,2)
response.write left(arData(0,i),14)
rno = rno + 1
rtg = rtg + 1
next
else
response.Write "No records returned"
end if


I must start using parameter queries. In the meantime, 2 questions:

1. Why do you test to see if arData is an array? Won't it be an array if rs
is not EOF? and
2. Where's the End If to close off if not rs.EOF...?

TIA

Morris
Jul 22 '05 #4

P: n/a
Morris wrote:

Morris corrected your code. Here's a security and performance tip:

dim pConn, rs, cmd, arData, arParms,sqlstr, i
arParms=Array(request.querystring("trackname"), _
request.querystring("racedate"))

sqlstr ="SELECT horsename FROM tblhorseentry " & _
WHERE trackname = ? and racedate = ?"

'initialize and open pconn, then

Set cmd=createobject("adodb.command")
cmd.commandtype=1
cmd.commandtext=sqlstr
Set cmd.activeconnection=pConn
Set rs = cmd.Execute(,arParms)

if not rs.EOF then arData=rs.GetRows
rs.Close: Set rs=nothing
pConn.close: set pConn=nothing

if isArray(arData) then
for i = 0 to ubound(arData,2)
response.write left(arData(0,i),14)
rno = rno + 1
rtg = rtg + 1
next
else
response.Write "No records returned"
end if
I must start using parameter queries.


Yes, you must. ;-)
In the meantime, 2 questions:

1. Why do you test to see if arData is an array? Won't it be an array
if rs is not EOF? and
Yes. But it will not be an array if rs IS EOF.
It only gets defined as an array in the If statement: i.e., when rs.EOF is
false.

See? It gets dimmed as a variant:
Dim arData
So it is not yet an array.
It gets "converted to" an array in this statement:
if not rs.EOF then arData=rs.GetRows

So, if rs.EOF is true, it remains a variant.

My goal is to close and destroy the ADO objects as soon as I possibly can,
as well as reduce the lines of code I write, which is why I do not do this:

if not rs.eof then
arData=rs.getrows
rs.close:set rs=nothing
pConn.close: set pConn=nothing
for i = 0 to ...
else
rs.close:set rs=nothing
pConn.close: set pConn=nothing
response.Write "No records returned"
end if
2. Where's the End If to close off if not rs.EOF...?


In vbscript/VB/VBA, there are two versions of If...Then:

1. A single-line version which I used in my example. It should consist
solely of
If <expression> Then <action if true>

However, a bug in vbscript allows this to be used:
If <expression> Then <action if true> Else <action if false> End if
In VB/VBA, this would raise an error.

2. And the multi-line version which you are used to:
If <expression> then
<action if true>
{ElseIf <expression> Then
<action if true>}
Else
<action if false>
End if

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.