Case statement issue

Discussion in 'ASP General' started by Tom, Apr 6, 2004.

  1. Tom

    Tom Guest

    Hello
    I am trying to display records with ASP and page thru a defined number of
    records.
    The records are dynamic and reside in a SQL 2000 enterprise database,
    Windows enterprise 2003 OS with IIS version 6 on the server.
    These are all on the same box during development
    I have taken the sample of paging using a stored procedure from the website
    http://www.aspfaq.com/show.asp?id=2120
    I have modified the code and the stored procedures to work with data and my
    setup.
    I modified to include dynamic code that works on a non-paging page.
    (This page displays all of the records. It uses a stored procedure)
    There is more dynamic code that reads a variable (mvar)and then goes thru a
    case statement to determine
    what type of element to build. Then a stored procedure call that will
    assign a description
    to all of the checkboxes or radio buttons as required based on variable
    (mvar2).
    This case statement is where the problem happens when.

    This is the error message:
    ADODB.Field error '80020009'
    Either BOF or EOF is True, or the current record has been deleted.
    Requested operation requires a current record

    The data is ok if it falls thru the case statement. When the data matches a
    case statement, then the error message.

    The for loop also seems to be a problem. The loop will not work in this for
    loop. The code works fine in another page setup:

    This is the error message for the second stored procedure.
    ADODB.Recordset error '800a0bcd'
    Either BOF or EOF is True

    I hope this is enough good information but any help or comments are
    appreciated!
    Tom

    CODE:


    <!--#include file=inc.asp-->
    <!--#include file=topSP1.asp-->

    set rs = rs.NextRecordset()

    if not rs.eof then

    Dim mvar
    Dim gr
    Dim rs2

    gr = rs.GetRows()
    rstop = PerPage - 1
    if rstop > ubound(gr, 2) then rstop = ubound(gr, 2)
    response.write "<table border=0 cellpadding=5>"

    for i = 0 to rstop

    artist = gr(0, i)
    title = gr(1, i)
    description = gr(2, i)
    question = gr(3, i)
    [id] = gr(4, i)
    [type] = gr(5, i)

    if artist <> prevArtist then
    prevArtist = artist
    response.write "<tr><td class=n>"
    response.write artist & "</td>"
    response.write "<td class=n>"
    else
    response.write "<tr><td>&nbsp;</td><td>"
    end if
    Response.Write "<tr><td> &nbsp; </td><td>"
    response.write title & "</td><td>"
    Response.Write description & "</td><td>"
    Response.Write question & "</td><td>"
    Response.Write [id] & "</td><td>"
    Response.Write [Type] & "</td><td>"
    Response.Write mvar & "</td><i>"

    mvar = gr(5, i)
    mvar2 = gr(4, i)

    Response.Write mvar2 & "</td><td><b>"
    Response.Write mvar & "</b></td></i></tr>"

    Select Case mvar

    Case 4
    response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    response.write "<TR>"
    response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    response.write "<TBODY>"
    response.write "<TR> "
    response.write "<TD vAlign=top align=center nowrap=true colspan=2><B> "
    Response.Write("description")
    response.Write "</B></TD> "
    response.write "</TR> "
    response.write "<TD align=left vAlign=top></TD> "
    response.write "</TR> "
    response.write "<TR> "
    response.write "<TD align=left vAlign=top></TD> "
    response.write "</TR> "

    response.write "<TR>"
    response.write "<TD class=quest width=715 align=center colSpan=2> "
    Response.write("Question")
    response.write "</TD> "
    response.write "<TD> "
    response.write "</TR> "
    response.write "<TR align=left> "
    response.write "<TD width=35></TD> "
    response.write "<TD width=715> "
    response.write "<TABLE cellSpacing=0 cellPadding=0 border=0> "
    response.write "<TBODY> "


    Set cnnStoredProc2 = Server.CreateObject("ADODB.Connection")
    cnnStoredProc2.Open "DSN=test1;"
    Set paramId2 = cmdStoredProc.CreateParameter("@testNumb", adInteger,
    adParamInput, , mvar2)
    set rs2 = server.createobject("adodb.recordset")
    cnnStoredProc2.GetQuestionsMultiChoice paramID2, rs2
    rs2.MoveFirst


    Do While Not rs2.eof
    response.write "<TR> "
    response.write "<TD vAlign=top><INPUT type=radio value= "
    Response.write("description")
    response.write "name=survey2></TD> "
    response.write "<TD class=subquest> "
    Response.write("description")
    Response.Write "</TD> "
    response.write "</TR> "
    rs2.MoveNext
    loop

    Response.Write "<tr><td>no more records</tr></td>"

    response.write "</TBODY>"
    response.write "</TABLE>"
    response.write "</TD>"
    response.write "</TR><!-- end of radio button -->"
    response.write "</TBODY>"
    response.write "</TABLE>"
    response.write "</TR>"
    response.write "<TR align=center>"
    response.write "<TD width=715>"
    response.write "</TD>"
    response.write "</TR>"
    response.write "</TABLE>"
    response.write "<TABLE BORDER=0 WIDTH=700;>"
    response.write "<TR>"
    response.write "<TD align=left vAlign=top WIDTH=66% colSpan=2>Extra
    Comments</TD> "
    response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2>Extra Comment
    2</TD> "
    response.write " </TR> "
    response.write "<TR> "
    response.write "<TD align=center vAlign=top WIDTH=66% colSpan=2> "
    response.write "<INPUT type=text value=1 size=30 name=survey2></TD> "

    response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2> "
    response.write "<INPUT TYPE=RADIO NAME=survey33 VALUE=IncludeMonthEnd
    /></TD> "
    response.write "</TR> "
    response.write "</TABLE> "
    response.write "<P> "
    response.write "<P> "
    response.write "<TABLE BORDER=0 WIDTH=700;> "

    response.write "<TR> "
    response.write "<TD ALIGN=LEFT VALIGN=top colSpan=3 WIDTH=33 "
    response.write "<SPAN ID=Browse CLASS=button "
    response.write "align=left "
    response.write "STYLE=position:left; "
    response.write "onclick=document.frmRecordAdd.submit()> "
    response.write "<IMG SRC=images/tealarrow.gif WIDTH=12 HEIGHT=16 ALIGN=left>
    "
    response.write "Next Record "
    response.write "</SPAN> "

    case else
    Response.write "<tr><td> Exit </td><td><b>"
    Response.Write [id] & "</b></td></tr>"

    end select

    next
    Response.Write "next"

    else
    response.write "No rows found."
    response.end
    end if

    %>
    <!--#include file=foot.asp-->
     
    Tom, Apr 6, 2004
    #1
    1. Advertising

  2. You need to find the offending line of code. Most likely you have already
    cursed through the RS and you are attempting to do one last thing afterward.
    That would cause a bomb, except with a cursor other than the default
    (forward-only) cursor. You can set the cursor type and then use
    RS.MoveFirst. This will bomb with a default cursor.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************************************************
    Think outside the box!
    ***************************************************************
    "Tom" <> wrote in message
    news:...
    > Hello
    > I am trying to display records with ASP and page thru a defined number of
    > records.
    > The records are dynamic and reside in a SQL 2000 enterprise database,
    > Windows enterprise 2003 OS with IIS version 6 on the server.
    > These are all on the same box during development
    > I have taken the sample of paging using a stored procedure from the

    website
    > http://www.aspfaq.com/show.asp?id=2120
    > I have modified the code and the stored procedures to work with data and

    my
    > setup.
    > I modified to include dynamic code that works on a non-paging page.
    > (This page displays all of the records. It uses a stored procedure)
    > There is more dynamic code that reads a variable (mvar)and then goes thru

    a
    > case statement to determine
    > what type of element to build. Then a stored procedure call that will
    > assign a description
    > to all of the checkboxes or radio buttons as required based on variable
    > (mvar2).
    > This case statement is where the problem happens when.
    >
    > This is the error message:
    > ADODB.Field error '80020009'
    > Either BOF or EOF is True, or the current record has been deleted.
    > Requested operation requires a current record
    >
    > The data is ok if it falls thru the case statement. When the data matches

    a
    > case statement, then the error message.
    >
    > The for loop also seems to be a problem. The loop will not work in this

    for
    > loop. The code works fine in another page setup:
    >
    > This is the error message for the second stored procedure.
    > ADODB.Recordset error '800a0bcd'
    > Either BOF or EOF is True
    >
    > I hope this is enough good information but any help or comments are
    > appreciated!
    > Tom
    >
    > CODE:
    >
    >
    > <!--#include file=inc.asp-->
    > <!--#include file=topSP1.asp-->
    >
    > set rs = rs.NextRecordset()
    >
    > if not rs.eof then
    >
    > Dim mvar
    > Dim gr
    > Dim rs2
    >
    > gr = rs.GetRows()
    > rstop = PerPage - 1
    > if rstop > ubound(gr, 2) then rstop = ubound(gr, 2)
    > response.write "<table border=0 cellpadding=5>"
    >
    > for i = 0 to rstop
    >
    > artist = gr(0, i)
    > title = gr(1, i)
    > description = gr(2, i)
    > question = gr(3, i)
    > [id] = gr(4, i)
    > [type] = gr(5, i)
    >
    > if artist <> prevArtist then
    > prevArtist = artist
    > response.write "<tr><td class=n>"
    > response.write artist & "</td>"
    > response.write "<td class=n>"
    > else
    > response.write "<tr><td>&nbsp;</td><td>"
    > end if
    > Response.Write "<tr><td> &nbsp; </td><td>"
    > response.write title & "</td><td>"
    > Response.Write description & "</td><td>"
    > Response.Write question & "</td><td>"
    > Response.Write [id] & "</td><td>"
    > Response.Write [Type] & "</td><td>"
    > Response.Write mvar & "</td><i>"
    >
    > mvar = gr(5, i)
    > mvar2 = gr(4, i)
    >
    > Response.Write mvar2 & "</td><td><b>"
    > Response.Write mvar & "</b></td></i></tr>"
    >
    > Select Case mvar
    >
    > Case 4
    > response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    > response.write "<TR>"
    > response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    > response.write "<TBODY>"
    > response.write "<TR> "
    > response.write "<TD vAlign=top align=center nowrap=true colspan=2><B> "
    > Response.Write("description")
    > response.Write "</B></TD> "
    > response.write "</TR> "
    > response.write "<TD align=left vAlign=top></TD> "
    > response.write "</TR> "
    > response.write "<TR> "
    > response.write "<TD align=left vAlign=top></TD> "
    > response.write "</TR> "
    >
    > response.write "<TR>"
    > response.write "<TD class=quest width=715 align=center colSpan=2> "
    > Response.write("Question")
    > response.write "</TD> "
    > response.write "<TD> "
    > response.write "</TR> "
    > response.write "<TR align=left> "
    > response.write "<TD width=35></TD> "
    > response.write "<TD width=715> "
    > response.write "<TABLE cellSpacing=0 cellPadding=0 border=0> "
    > response.write "<TBODY> "
    >
    >
    > Set cnnStoredProc2 = Server.CreateObject("ADODB.Connection")
    > cnnStoredProc2.Open "DSN=test1;"
    > Set paramId2 = cmdStoredProc.CreateParameter("@testNumb", adInteger,
    > adParamInput, , mvar2)
    > set rs2 = server.createobject("adodb.recordset")
    > cnnStoredProc2.GetQuestionsMultiChoice paramID2, rs2
    > rs2.MoveFirst
    >
    >
    > Do While Not rs2.eof
    > response.write "<TR> "
    > response.write "<TD vAlign=top><INPUT type=radio value= "
    > Response.write("description")
    > response.write "name=survey2></TD> "
    > response.write "<TD class=subquest> "
    > Response.write("description")
    > Response.Write "</TD> "
    > response.write "</TR> "
    > rs2.MoveNext
    > loop
    >
    > Response.Write "<tr><td>no more records</tr></td>"
    >
    > response.write "</TBODY>"
    > response.write "</TABLE>"
    > response.write "</TD>"
    > response.write "</TR><!-- end of radio button -->"
    > response.write "</TBODY>"
    > response.write "</TABLE>"
    > response.write "</TR>"
    > response.write "<TR align=center>"
    > response.write "<TD width=715>"
    > response.write "</TD>"
    > response.write "</TR>"
    > response.write "</TABLE>"
    > response.write "<TABLE BORDER=0 WIDTH=700;>"
    > response.write "<TR>"
    > response.write "<TD align=left vAlign=top WIDTH=66% colSpan=2>Extra
    > Comments</TD> "
    > response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2>Extra

    Comment
    > 2</TD> "
    > response.write " </TR> "
    > response.write "<TR> "
    > response.write "<TD align=center vAlign=top WIDTH=66% colSpan=2> "
    > response.write "<INPUT type=text value=1 size=30 name=survey2></TD> "
    >
    > response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2> "
    > response.write "<INPUT TYPE=RADIO NAME=survey33 VALUE=IncludeMonthEnd
    > /></TD> "
    > response.write "</TR> "
    > response.write "</TABLE> "
    > response.write "<P> "
    > response.write "<P> "
    > response.write "<TABLE BORDER=0 WIDTH=700;> "
    >
    > response.write "<TR> "
    > response.write "<TD ALIGN=LEFT VALIGN=top colSpan=3 WIDTH=33 "
    > response.write "<SPAN ID=Browse CLASS=button "
    > response.write "align=left "
    > response.write "STYLE=position:left; "
    > response.write "onclick=document.frmRecordAdd.submit()> "
    > response.write "<IMG SRC=images/tealarrow.gif WIDTH=12 HEIGHT=16

    ALIGN=left>
    > "
    > response.write "Next Record "
    > response.write "</SPAN> "
    >
    > case else
    > Response.write "<tr><td> Exit </td><td><b>"
    > Response.Write [id] & "</b></td></tr>"
    >
    > end select
    >
    > next
    > Response.Write "next"
    >
    > else
    > response.write "No rows found."
    > response.end
    > end if
    >
    > %>
    > <!--#include file=foot.asp-->
    >
    >
     
    Cowboy \(Gregory A. Beamer\) [MVP], Apr 7, 2004
    #2
    1. Advertising

  3. Tom

    Tom Guest

    Thanks Greg
    That was one of the issues and when changed it allowed me to find another
    problem.
    Thanks for taking the time!
    Tom


    "Cowboy (Gregory A. Beamer) [MVP]" <> wrote
    in message news:#...
    You need to find the offending line of code. Most likely you have already
    cursed through the RS and you are attempting to do one last thing afterward.
    That would cause a bomb, except with a cursor other than the default
    (forward-only) cursor. You can set the cursor type and then use
    RS.MoveFirst. This will bomb with a default cursor.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    ***************************************************************
    Think outside the box!
    ***************************************************************
    "Tom" <> wrote in message
    news:...
    > Hello
    > I am trying to display records with ASP and page thru a defined number of
    > records.
    > The records are dynamic and reside in a SQL 2000 enterprise database,
    > Windows enterprise 2003 OS with IIS version 6 on the server.
    > These are all on the same box during development
    > I have taken the sample of paging using a stored procedure from the

    website
    > http://www.aspfaq.com/show.asp?id=2120
    > I have modified the code and the stored procedures to work with data and

    my
    > setup.
    > I modified to include dynamic code that works on a non-paging page.
    > (This page displays all of the records. It uses a stored procedure)
    > There is more dynamic code that reads a variable (mvar)and then goes thru

    a
    > case statement to determine
    > what type of element to build. Then a stored procedure call that will
    > assign a description
    > to all of the checkboxes or radio buttons as required based on variable
    > (mvar2).
    > This case statement is where the problem happens when.
    >
    > This is the error message:
    > ADODB.Field error '80020009'
    > Either BOF or EOF is True, or the current record has been deleted.
    > Requested operation requires a current record
    >
    > The data is ok if it falls thru the case statement. When the data matches

    a
    > case statement, then the error message.
    >
    > The for loop also seems to be a problem. The loop will not work in this

    for
    > loop. The code works fine in another page setup:
    >
    > This is the error message for the second stored procedure.
    > ADODB.Recordset error '800a0bcd'
    > Either BOF or EOF is True
    >
    > I hope this is enough good information but any help or comments are
    > appreciated!
    > Tom
    >
    > CODE:
    >
    >
    > <!--#include file=inc.asp-->
    > <!--#include file=topSP1.asp-->
    >
    > set rs = rs.NextRecordset()
    >
    > if not rs.eof then
    >
    > Dim mvar
    > Dim gr
    > Dim rs2
    >
    > gr = rs.GetRows()
    > rstop = PerPage - 1
    > if rstop > ubound(gr, 2) then rstop = ubound(gr, 2)
    > response.write "<table border=0 cellpadding=5>"
    >
    > for i = 0 to rstop
    >
    > artist = gr(0, i)
    > title = gr(1, i)
    > description = gr(2, i)
    > question = gr(3, i)
    > [id] = gr(4, i)
    > [type] = gr(5, i)
    >
    > if artist <> prevArtist then
    > prevArtist = artist
    > response.write "<tr><td class=n>"
    > response.write artist & "</td>"
    > response.write "<td class=n>"
    > else
    > response.write "<tr><td>&nbsp;</td><td>"
    > end if
    > Response.Write "<tr><td> &nbsp; </td><td>"
    > response.write title & "</td><td>"
    > Response.Write description & "</td><td>"
    > Response.Write question & "</td><td>"
    > Response.Write [id] & "</td><td>"
    > Response.Write [Type] & "</td><td>"
    > Response.Write mvar & "</td><i>"
    >
    > mvar = gr(5, i)
    > mvar2 = gr(4, i)
    >
    > Response.Write mvar2 & "</td><td><b>"
    > Response.Write mvar & "</b></td></i></tr>"
    >
    > Select Case mvar
    >
    > Case 4
    > response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    > response.write "<TR>"
    > response.write "<TABLE cellSpacing=0 cellPadding=0 width=750 border=0>"
    > response.write "<TBODY>"
    > response.write "<TR> "
    > response.write "<TD vAlign=top align=center nowrap=true colspan=2><B> "
    > Response.Write("description")
    > response.Write "</B></TD> "
    > response.write "</TR> "
    > response.write "<TD align=left vAlign=top></TD> "
    > response.write "</TR> "
    > response.write "<TR> "
    > response.write "<TD align=left vAlign=top></TD> "
    > response.write "</TR> "
    >
    > response.write "<TR>"
    > response.write "<TD class=quest width=715 align=center colSpan=2> "
    > Response.write("Question")
    > response.write "</TD> "
    > response.write "<TD> "
    > response.write "</TR> "
    > response.write "<TR align=left> "
    > response.write "<TD width=35></TD> "
    > response.write "<TD width=715> "
    > response.write "<TABLE cellSpacing=0 cellPadding=0 border=0> "
    > response.write "<TBODY> "
    >
    >
    > Set cnnStoredProc2 = Server.CreateObject("ADODB.Connection")
    > cnnStoredProc2.Open "DSN=test1;"
    > Set paramId2 = cmdStoredProc.CreateParameter("@testNumb", adInteger,
    > adParamInput, , mvar2)
    > set rs2 = server.createobject("adodb.recordset")
    > cnnStoredProc2.GetQuestionsMultiChoice paramID2, rs2
    > rs2.MoveFirst
    >
    >
    > Do While Not rs2.eof
    > response.write "<TR> "
    > response.write "<TD vAlign=top><INPUT type=radio value= "
    > Response.write("description")
    > response.write "name=survey2></TD> "
    > response.write "<TD class=subquest> "
    > Response.write("description")
    > Response.Write "</TD> "
    > response.write "</TR> "
    > rs2.MoveNext
    > loop
    >
    > Response.Write "<tr><td>no more records</tr></td>"
    >
    > response.write "</TBODY>"
    > response.write "</TABLE>"
    > response.write "</TD>"
    > response.write "</TR><!-- end of radio button -->"
    > response.write "</TBODY>"
    > response.write "</TABLE>"
    > response.write "</TR>"
    > response.write "<TR align=center>"
    > response.write "<TD width=715>"
    > response.write "</TD>"
    > response.write "</TR>"
    > response.write "</TABLE>"
    > response.write "<TABLE BORDER=0 WIDTH=700;>"
    > response.write "<TR>"
    > response.write "<TD align=left vAlign=top WIDTH=66% colSpan=2>Extra
    > Comments</TD> "
    > response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2>Extra

    Comment
    > 2</TD> "
    > response.write " </TR> "
    > response.write "<TR> "
    > response.write "<TD align=center vAlign=top WIDTH=66% colSpan=2> "
    > response.write "<INPUT type=text value=1 size=30 name=survey2></TD> "
    >
    > response.write "<TD align=left vAlign=top WIDTH=33% colSpan=2> "
    > response.write "<INPUT TYPE=RADIO NAME=survey33 VALUE=IncludeMonthEnd
    > /></TD> "
    > response.write "</TR> "
    > response.write "</TABLE> "
    > response.write "<P> "
    > response.write "<P> "
    > response.write "<TABLE BORDER=0 WIDTH=700;> "
    >
    > response.write "<TR> "
    > response.write "<TD ALIGN=LEFT VALIGN=top colSpan=3 WIDTH=33 "
    > response.write "<SPAN ID=Browse CLASS=button "
    > response.write "align=left "
    > response.write "STYLE=position:left; "
    > response.write "onclick=document.frmRecordAdd.submit()> "
    > response.write "<IMG SRC=images/tealarrow.gif WIDTH=12 HEIGHT=16

    ALIGN=left>
    > "
    > response.write "Next Record "
    > response.write "</SPAN> "
    >
    > case else
    > Response.write "<tr><td> Exit </td><td><b>"
    > Response.Write [id] & "</b></td></tr>"
    >
    > end select
    >
    > next
    > Response.Write "next"
    >
    > else
    > response.write "No rows found."
    > response.end
    > end if
    >
    > %>
    > <!--#include file=foot.asp-->
    >
    >
     
    Tom, Apr 8, 2004
    #3
    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. Steve Franks
    Replies:
    2
    Views:
    1,282
    Steve Franks
    Jun 10, 2004
  2. Tee
    Replies:
    3
    Views:
    7,904
    Herfried K. Wagner [MVP]
    Jun 23, 2004
  3. Guy Noir
    Replies:
    2
    Views:
    344
    Guy Noir
    Nov 2, 2006
  4. Janice

    lower case to upper case

    Janice, Dec 10, 2004, in forum: C Programming
    Replies:
    17
    Views:
    1,221
    Richard Bos
    Dec 14, 2004
  5. Replies:
    1
    Views:
    2,546
    Mark P
    Apr 6, 2007
Loading...

Share This Page