Do Until Loop problem

25 Feb 2005 7:25 PM

.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.

25 Feb 2005 7:38 PM

Morris

Show quote

".Net Sports" <ballz2w***@cox.net> wrote in message
news:1109359510.047057.102170@z14g2000cwz.googlegroups.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  

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

25 Feb 2005 9:12 PM

Bob Barrows [MVP]

..Net Sports wrote:
Show quote

> 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.

25 Feb 2005 10:14 PM

Morris

Show quote

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eB0k363GFHA.580@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

25 Feb 2005 10:41 PM

Bob Barrows [MVP]

Morris wrote:
Show quote

>>
>> 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.