Web Analytics Made Easy -
StatCounter ASP + MySQL 5.0 : Rowset position cannot be restarted. - CodingForum

Announcement

Collapse
No announcement yet.

ASP + MySQL 5.0 : Rowset position cannot be restarted.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ASP + MySQL 5.0 : Rowset position cannot be restarted.

    Hello,

    This is an odd one that I've never seen before, and I have the same bit of code in two seperate pages with different DB calls, one works and the other tosses this error. I've done a little research via Google but am not fully understanding what I am doing wrong.

    In using Dreamweaver (Yes, I know smack me) I've even tried calling some of the var's that reset the record position and at that point the error is fixed, but nothing displays on the page. :-)

    Code in Question
    Code:
    <%
    ' Builds a total of all Records Count Field to create a Percentage Value.
    Dim numOne
    Dim numTwo
    Dim numTotal
    qCount = (RS_GetPollF.Fields.Item("qCount").Value)
    Do While Not RS_GetPollF.EOF
     numOne = qCount
     numTwo = numTwo + numOne
     RS_GetPollF.MoveNext()
    Loop
    numTotal = numTwo
    RS_GetPollF.MoveFirst()
    %>
    Failing Page Code
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!--#include virtual="BLAH" -->
    <%
    Dim RS_GetPollF__valFemale
    RS_GetPollF__valFemale = "2"
    If (Request.QueryString("qpollID") <> "") Then 
      RS_GetPollF__valFemale = Request.QueryString("qpollID")
    End If
    %>
    <%
    Dim RS_GetPollF
    Dim RS_GetPollF_cmd
    Dim RS_GetPollF_numRows
    Set RS_GetPollF_cmd = Server.CreateObject ("ADODB.Command")
    RS_GetPollF_cmd.ActiveConnection = MM_pollConn_STRING
    RS_GetPollF_cmd.CommandText = "SELECT pollNames.pollName, pollQuestions.qText, pollQuestions.qGender, pollQuestions.qCount FROM pollNames Inner Join pollQuestions ON pollQuestions.qpollID = pollNames.pollID WHERE pollQuestions.qGender =  'Female' AND qpollID = ?" 
    RS_GetPollF_cmd.Prepared = true
    RS_GetPollF_cmd.Parameters.Append RS_GetPollF_cmd.CreateParameter("param1", 5, 1, -1, RS_GetPollF__valFemale) ' adDouble
    Set RS_GetPollF = RS_GetPollF_cmd.Execute
    RS_GetPollF_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    Repeat1__numRows = -1
    Repeat1__index = 0
    RS_GetPollF_numRows = RS_GetPollF_numRows + Repeat1__numRows
    %>
    <%
    ' Builds a total of all Records Count Field to create a Percentage Value.
    Dim numOne
    Dim numTwo
    Dim numTotal
    qCount = (RS_GetPollF.Fields.Item("qCount").Value)
    Do While Not RS_GetPollF.EOF
     numOne = qCount
     numTwo = numTwo + numOne
     RS_GetPollF.MoveNext()
    Loop
    numTotal = numTwo
    RS_GetPollF.MoveFirst()
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd[/URL]">
    <html xmlns="[URL]http://www.w3.org/1999/xhtml[/URL]">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Filter By Female</title>
    <link href="/Testing/assets/css/poll.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
    <p><a href="/Testing">‹ Back</a></p>
    <h1>Filter Poll by Female</h1>
    <% If Not RS_GetPollF.EOF Or Not RS_GetPollF.BOF Then %>
      <table border="0" cellpadding="3" cellspacing="0">
        <tr>
          <td><h4>pollName</h4></td>
          <td><h4>qText</h4></td>
          <td><h4>qGender</h4></td>
          <td><h4>qCount</h4></td>
          <td><h4>%</h4></td>
        </tr>
        <% While ((Repeat1__numRows <> 0) AND (NOT RS_GetPollF.EOF)) %>
          <tr>
            <td><%=(RS_GetPollF.Fields.Item("pollName").Value)%></td>
            <td><%=(RS_GetPollF.Fields.Item("qText").Value)%></td>
            <td><%=(RS_GetPollF.Fields.Item("qGender").Value)%></td>
            <td><%=(qCount)%></td>
            <td><%=Response.Write(FormatPercent(qCount/numTotal,1))%></td>
          </tr>
    <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      RS_GetPollF.MoveNext()
    Wend
    %>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td align="right">Total:</td>
            <td><%= Response.Write(numTotal) %></td>
            <td>&nbsp;</td>
          </tr>
      </table>
      <% End If ' end Not RS_GetPollF.EOF Or NOT RS_GetPollF.BOF %>
    <% If RS_GetPollF.EOF And RS_GetPollF.BOF Then %>
      <p>No Data in your Request Tono...</p>
      <% End If ' end RS_GetPollF.EOF And RS_GetPollF.BOF %>
    </body>
    </html>
    <%
    RS_GetPollF.Close()
    Set RS_GetPollF = Nothing
    %>
    Working Page
    Code:
    <[EMAIL="%@LANGUAGE="VBSCRIPT"]%@LANGUAGE="VBSCRIPT[/EMAIL]" CODEPAGE="65001"%>
    <!--#include virtual="BLAH" -->
    <%
    Dim getIT
    Dim getIT_cmd
    Dim getIT_numRows
    Set getIT_cmd = Server.CreateObject ("ADODB.Command")
    getIT_cmd.ActiveConnection = MM_PhxNowDB_STRING
    getIT_cmd.CommandText = "SELECT * FROM phxnowdb.counting" 
    getIT_cmd.Prepared = true
    Set getIT = getIT_cmd.Execute
    getIT_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    Repeat1__numRows = -1
    Repeat1__index = 0
    getIT_numRows = getIT_numRows + Repeat1__numRows
    %>
    <%
    ' Builds a total of all Records Count Field to create a Percentage Value.
    dim numOne, numTwo, numTotal
    do while not getIT.EOF
     numOne = (getIT.Fields.Item("qCount").Value)
     numTwo = numTwo + numOne
     getIT.MoveNext()
    loop
    numTotal = numTwo
    getIt.MoveFirst()
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd[/URL]">
    <html xmlns="[URL]http://www.w3.org/1999/xhtml[/URL]">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style1 {
     color: #FFFFFF;
     font-weight: bold;
    }
    -->
    </style>
    </head>
    <body>
    <table width="250" border="0" cellpadding="3" cellspacing="1">
      <tr>
        <td bgcolor="#666666"><span class="style1">Player</span></td>
        <td bgcolor="#666666"><span class="style1">Name</span></td>
        <td bgcolor="#666666"><span class="style1">Score</span></td>
        <td bgcolor="#666666"><span class="style1">Percent</span></td>
      </tr>
    <% While ((Repeat1__numRows <> 0) AND (NOT getIT.EOF)) %>
        <tr>
          <td bgcolor="#EFEFEF"><%=(getIT.Fields.Item("qID").Value)%></td>
          <td bgcolor="#EFEFEF"><%=(getIT.Fields.Item("qName").Value)%></td>
          <td align="right" bgcolor="#EFEFEF"><%=(getIT.Fields.Item("qCount").Value)%></td>
          <td align="right" bgcolor="#EFEFEF"><%=Response.Write(FormatPercent(getIT.Fields.Item("qCount").Value/numTotal,1))%></td>
        </tr>
    <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      getIT.MoveNext()
    Wend
    %>
        <tr>
          <td>&nbsp;</td>
          <td align="right" bgcolor="#EFEFEF">Total:</td>
          <td bgcolor="#666666"><span class="style1"><%=Response.Write(numTotal)%></span></td>
          <td>&nbsp;</td>
        </tr>
    </table>
    </body>
    </html>
    <%
    getIT.Close()
    Set getIT = Nothing
    %>
    As normal, any information would be wonderful, if not an answer then maybe some tips on troubleshooting this type of issue.

    Thank You!
    Last edited by Yarrokon; Apr 8, 2009, 02:14 PM. Reason: Added working page to show the difference between the failing and working page.
    Regards,
    Yarrokon

  • #2
    Just for starters, I don't PRETEND to understand how your first code BEGINS to work:
    Code:
    <%
    ' Builds a total of all Records Count Field to create a Percentage Value.
    Dim numOne
    Dim numTwo
    Dim numTotal
    qCount = (RS_GetPollF.Fields.Item("qCount").Value)
    Do While Not RS_GetPollF.EOF
     numOne = qCount
     numTwo = numTwo + numOne
     RS_GetPollF.MoveNext()
    Loop
    numTotal = numTwo
    RS_GetPollF.MoveFirst()
    %>
    You loop through the recordset, but qCount NEVER CHANGES, so all you are really doing is multiplying the value of qCount *FROM THE FIRST RECORD* by the number of records in the recordset.

    ALMOST SURELY you have the line that gets the value for qCount in the wrong place!

    SURELY you wanted to do this:
    Code:
    <%
    ' Builds a total of all Records Count Field to create a Percentage Value.
    Dim numTotal : numTotal = 0 ' *ALWAYS* initialize values!!!
    
    Do While Not RS_GetPollF.EOF
        numTotal = numTotal +  RS_GetPollF("qCount")
        RS_GetPollF.MoveNext()
    Loop
    RS_GetPollF.MoveFirst()
    %>
    Now, that's actually a really bad way to do this. If at all possible, you should have used a separate SQL query to get the SUM(qCount) *or* you should have calculated that sum as you are running through the records for another reason *or* you maybe could have used GetRows.

    But never mind, at least now the DO loop makes sense and should get you the sum of all the qCount values.

    Now let me look at the other code and see what I can see.
    Be yourself. No one else is as qualified.

    Comment


    • #3
      I wish you had told us WHICH LINE you are getting that error on!

      As it is, I have to *GUESS*.

      Since the error refers to "cannot be restarted", I am going to *GUESS* that it occurs on the line
      Code:
      RS_GetPollF.MoveFirst
      And that makes sense. Because when you use
      Code:
      Set RS_GetPollF = RS_GetPollF_cmd.Execute
      you are getting a FORWARD ONLY cursor. Meaning that it can *NOT* be "rolled back" to the first record.

      I don't understand how/why it works on the page where you say it does work. But never mind.

      If my GUESS is right, then the answer is probably simple: Just make sure you use a STATIC cursor instead of a FORWARD ONLY one. So just change that line
      Code:
      Set RS_GetPollF = RS_GetPollF_cmd.Execute
      to these two lines:
      Code:
      Set RS_GetPollF = Server.CreateObject("ADODB.Recordset")
      RS_GetPollF.Open RS_GetPollF_cmd, , 3  ' 3 is adOpenStatic
      Untested. A guess.

      *Possibly* the reason the MoveFirst worked on the other page is because you were doing a "whole table fetch" and MoveFirst can figure out that it just needs to start the entire table all over again. Dunno. I do know that you aren't guaranteed to be able to do anything but MoveNext when a forward-only cursor is in use.
      Last edited by Old Pedant; Apr 8, 2009, 03:50 PM. Reason: typo
      Be yourself. No one else is as qualified.

      Comment


      • #4
        Old Pedant,

        Very sorry for any misunderstandings. Yes qCount is an extra variable for another part of the script in the first code example so it is in the wrong place.

        The script is looping over a column in the recordset that is filled with numbers and adding them together. This is not a record count, but simply adding various numbers together.

        What I found is that when this script completed, my other loops to build the data would fail because I was at the end of the recordset, so I used the standard MoveFirst(). Now this worked in my preliminary test pages without error, but when I moved forward in further testing the Move.Forward() produced the "Rowset position cannot be restarted" error.

        Your Second post clearly answers the question, your final statement is what I did not know and needed to understand "I do know that you aren't guaranteed to be able to do anything but MoveNext when a forward-only cursor is in use." I will be testing your final code example here in a moment and will update with what is found.

        Finally, I again really do appreciate you going over this. Please in the future if I am not providing enough information to answer the question so you don't have to poor over lines of code, tell me what I am doing wrong. A simple "How do you expect me to answer this if you do not provide the error or the line it happens on" is perfect for me. Communication is not my strong point when explaining code and am still learning on "what is important" when reporting issues or asking for help with ASP and MySQL.

        Regards,
        Yarrokon

        Comment


        • #5
          Your second post fixed the issue as you had expected. Commented out the first line and added the additional two, uploaded and the page poped right up.

          Code:
          'Set RS_GetPollF = RS_GetPollF_cmd.Execute
          Set RS_GetPollF = Server.CreateObject("ADODB.Recordset")
          RS_GetPollF.Open RS_GetPollF_cmd, , 3  ' 3 is adOpenStatic
          Thank you again!
          Regards,
          Yarrokon

          Comment


          • #6
            It was no big problem. Just remember, though, when you report a problem you need to show (preferably highlight) the line of code that caused the problem. Luckily, this time the error message was enough of a clue. Glad it worked!

            ***************

            With regards to this statement of yours:
            The script is looping over a column in the recordset that is filled with numbers and adding them together. This is not a record count, but simply adding various numbers together
            Yes, I know. And I say again that your code for that loop was BOGUS!!!

            If you had 3 records where qCount was, say,
            2
            12
            19
            YOUR code would have given you a result of 6 !!!!

            *MY* code will give you (properly) 33.

            GO READ IT AGAIN!
            Be yourself. No one else is as qualified.

            Comment


            • #7
              Originally posted by Old Pedant View Post
              It was no big problem. Just remember, though, when you report a problem you need to show (preferably highlight) the line of code that caused the problem. Luckily, this time the error message was enough of a clue. Glad it worked!

              ***************

              With regards to this statement of yours:

              Yes, I know. And I say again that your code for that loop was BOGUS!!!

              If you had 3 records where qCount was, say,
              2
              12
              19
              YOUR code would have given you a result of 6 !!!!

              *MY* code will give you (properly) 33.

              GO READ IT AGAIN!
              Old Pedant,

              Understood and thank you again. Yes that script was hosed and what was posted was hosed. The one I have in place works perfectly. However, the code you posted is much cleaner and shorter then what I was using (gawd I am not a programmer and they just keep shoving this stuff to me). Here is the code I was using with a different db for testing.

              Code:
              <%
              dim numOne, numTwo, numTotal
              do while not getIT.EOF
               numOne = (getIT.Fields.Item("qCount").Value)
               numTwo = numTwo + numOne
               getIT.MoveNext()
              loop
              numTotal = numTwo
              getIt.MoveFirst()
              %>
              Was not aware of "*ALWAYS* initialize values!!!" again thank you. I have a long way to go at this...
              Last edited by Yarrokon; Apr 9, 2009, 01:31 AM. Reason: spelling and more spelling
              Regards,
              Yarrokon

              Comment


              • #8
                You had made a statement earlier on the selection type of SELECT * when I had said one page works and the other does not. This was the case, the error does not persist if I am running a SELECT *...

                I also had to do a final check for the MoveFirst call. I had to check BOF and EOF.
                Code:
                <%
                ' Builds a total of all Records Count Field to create a Percentage Value.
                Dim numTotal1 : numTotal1 = 0 ' *ALWAYS* initialize values!!!
                Do While Not RS_getPollF.EOF
                    numTotal1 = numTotal1 +  RS_getPollF("qCount")
                    RS_getPollF.MoveNext()
                Loop
                If Not RS_getPollF.BOF AND RS_getPollF.EOF Then
                  RS_getPollF.MoveFirst()
                End If
                %>
                Regards,
                Yarrokon

                Comment


                • #9
                  Actually, the BOF test is utterly bogus and useless.

                  There are only *TWO* ways to get ADODB.Recordset.BOF

                  (1) If you create the recordset via a query and there are NO RECORDS AT ALL.
                  If so, then yes, BOF will be true. But then EOF will *ALSO* be true. So you can check EITHER ONE and get the same answer. So why mess with BOF in this case?

                  (2) If you have a STATIC or DYNAMIC cursor and you move *BACKWARDS* through the recordset using RS.MovePrevious or using RS.Move and a negative offset. If you manage to go backwards to BEFORE the first record in the recordset, then you will get BOF. I dunno about you, but I have never ever in 10 years gone backward through a recordset or done relative backwards movement. If you don't do this, either, then there is no reason to check for BOF.

                  In short, 99.99% of all the ASP code out there today--most of it created by DumbWeasel--that checks for BOF is just wasting code and time.
                  Be yourself. No one else is as qualified.

                  Comment

                  Working...
                  X