Do Until Loop problem

Discussion in 'ASP General' started by .Net Sports, Feb 25, 2005.

  1. .Net Sports

    .Net Sports Guest

    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.
    .Net Sports, Feb 25, 2005
    #1
    1. Advertising

  2. .Net Sports

    Morris Guest

    ".Net Sports" <> wrote in message
    news:...
    >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
    Morris, Feb 25, 2005
    #2
    1. Advertising

  3. ..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.
    Bob Barrows [MVP], Feb 25, 2005
    #3
  4. .Net Sports

    Morris Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > .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
    Morris, Feb 25, 2005
    #4
  5. 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.
    Bob Barrows [MVP], Feb 25, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Remi Villatel

    Loop until condition is true

    Remi Villatel, Jun 18, 2005, in forum: Python
    Replies:
    40
    Views:
    1,110
    Mike Meyer
    Jun 23, 2005
  2. placid
    Replies:
    6
    Views:
    5,482
    placid
    Aug 11, 2006
  3. yogi_bear_79
    Replies:
    2
    Views:
    249
    yogi_bear_79
    Mar 11, 2008
  4. .Net Sports

    Do Until Loop problem

    .Net Sports, Feb 25, 2005, in forum: ASP General
    Replies:
    0
    Views:
    113
    .Net Sports
    Feb 25, 2005
  5. Isaac Won
    Replies:
    9
    Views:
    364
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page