Do Until Loop problem

[.Net Sports]

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]

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

 

[Bob Barrows [MVP]]

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

 

[Morris]

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

 

[Bob Barrows [MVP]]

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