ASP + MySQL 5.0 : Rowset position cannot be restarted. [Archive]

View Full Version : ASP + MySQL 5.0 : Rowset position cannot be restarted.

Yarrokon

04-08-2009, 08:11 PM

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

<%
' 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

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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> </td>
<td> </td>
<td align="right">Total:</td>
<td><%= Response.Write(numTotal) %></td>
<td> </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

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">

</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> </td>
<td align="right" bgcolor="#EFEFEF">Total:</td>
<td bgcolor="#666666"><span class="style1"><%=Response.Write(numTotal)%></span></td>
<td> </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!

Old Pedant

04-08-2009, 09:37 PM

Just for starters, I don't PRETEND to understand how your first code BEGINS to work:

<%
' 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:

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

Old Pedant

04-08-2009, 09:48 PM

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

RS_GetPollF.MoveFirst

And that makes sense. Because when you use

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

Set RS_GetPollF = RS_GetPollF_cmd.Execute

to these two lines:

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.

Yarrokon

04-08-2009, 11:50 PM

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.

:thumbsup:

Yarrokon

04-09-2009, 12:03 AM

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.

'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!

Old Pedant

04-09-2009, 03:08 AM

Yarrokon

04-09-2009, 07:31 AM

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.

<%
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... :o

Yarrokon

04-09-2009, 11:51 AM

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.

<%
' 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
%>

Old Pedant

04-10-2009, 07:05 AM

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.