Help

HPryce · October 30th, 2003, 10:00 AM

I have another, nut this time it is with the registration form, I get the error: Microsoft OLE DB Provider for ODBC Drivers error '80040e29'

Rowset does not support scrolling backward.

/registration.asp, line 83
But I put the line in objRs.Cursor = adOpenStatic, this page is also for editing your profile after you have registered and people "with powers" can change your details. Line 83 is marked in the code

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New account registration</title>
<%

Dim strUsername 'There user name
Dim strPassword 'Password in box 1
Dim strPassword2 'Password in box 2
Dim numPasswordencoded 'Password after encryption
Dim strEmail 'Email in box 1
Dim strEmail2 'Email in box 2
Dim numAge 'Their age
Dim strFirstName 'First Name
Dim strSurname 'Surname
Dim blnReciveEmails 'Whether they wont to recieve occasional emails
Dim blnRecieveNewsletter 'Whether they want the newsletter
Dim blnViewerHasPowers 'Whether the person viewing the page has powers
Dim blnHasPowers 'If the person has powers
Dim numHousePoints 'How many house points they have
Dim numBertieBotts 'How many bertie botts they have
Dim objConn 'Database Connection
Dim objRS 'Database recordset
Dim objComm 'For updating the database
Dim blnAutoLogin 'Whether auto login is true or false
Dim blnNewRegistration 'Whether it is a new connection
Dim strViewerName 'Person viewing the page username
Dim strAdminSearchCriteria 'The thing to search for to see if they have powers
Dim strUserEdit 'Name of the user whos details are being edited
Dim strUserEditSearchCriteria 'The user whos details need to be looked up
Dim blnBanned 'Whether the user is banned
Dim strConnectionDetails 'Holds the connection details
Dim strSQLQuery 'Holds the SQL Query

'Set default boolean values
blnRecieveEmails = False
blmRecieveNewsletter = False
blnViewerHasPowers = False
blnHasPowers = False
blnNewRegistration = True
blnBanned = False

'Set viewer and registration details
strViewerName = Session("Username")
If strViewerName = "" Then
blnNewRegistration = True
Else blnNewRegistration = False
End If

'Set Constants
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'Open Database Connection
Set objConn = Server.CreateObject("ADODB.Connection")
Set objComm = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.Recordset")

strConnectionDetails = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("mainuser.mdb")

'Make command
objComm.ActiveConnection = strConnectionDetails
objComm.CommandText = "Select Username From UserDetails"

'Set Recordset
objRS.CursorType = adOpenStatic
objRS.LockType = adLockPessimistic
Set objRS = objComm.Execute

'See if viewing user has admin powers
strAdminSearchCriteria = "Username='" & strViewerName & "'"
objRS.Find strAdminSearchCriteria '<<<<<<Line 83
blnViewerHasPowers = objRS("HasPowers")
objRS.Close
Set objComm = Nothing
Set objRS = Nothing

'Open another recordset
objRS.Open strSQLQuery, objConn, adOpenKeyset

'Set existing settings
If blnNewRegistration = False Then
strUserEdit = Request.Form("User")
strUserEditSearchCriteria = "Username=" & strUserEdit
objRS.Find strUserEditSearch
strUsername = objRS("Username")
strEmail = objRS("Email")
strEmail2 = objRS("Email")
numAge = objRS("Age")
strFirstName = objRS("FirstName")
strSurname = objRs("Surname")
blnRecieveEmails = objRS("AllowEmails")
blnRecieveNewsletter = objRS("EmailNewsletter")
blnHasPowers = objRS("HasPowers")
blnAutoLogin = objRS("AutoLogin")
blnBanned = objRS("Banned")
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Else
blnNewRegistration = True
End If
%>
</head>

<body>
Registration Form<br>
This form is for registering for the website, it is not to register for the forum, to do that visit the forums.<br>
* = Required information<br><br>
<%
If strUserEdit = "" Then
    Response.Write "*Username: <form name='details' action='regbot.asp' method="put"> <input type='text' name='username'> <br>"
    Response.Write "*Password: <input type='password' name='password1'> <br>"
    Response.Write "*Retype password: <imput type='password' name='password2'><br>"
    Response.Write "Email: <input type='text' name='email1'><br>"
    Response.Write "Retype Email: <input type='text' name='email2'><br>"
    Response.Write "First name: <input type='text' name='firstname'><br>"
    Response.Write "Surname: <input type='text' name='surname'><br>"
    Response.Write "Age: <input type='text' name='age'><br>"
    Else
    Response.Write "*Username: strUsername <br>"
    Response.Write "*Password: <form name='details' action='regbot.asp' method="put"> <input type='password' name='password1'> <br>"
    Response.Write "*Retype password: <imput type='password' name='password2'><br>"
    Response.Write "Email: <input type='text' name='email1' value='" & strEmail &"'><br>"
    Response.Write "Retype Email: <input type='text' name='email2' value='" & strEmail &"'><br>"
    Response.Write "First name: <input type='text' name='firstname' value='" & StrFirstName & "'><br>"
    Response.Write "Surname: <input type='text' name='surname' value='" & strSurname & "'><br>"
    Response.Write "Age: <input type='text' name='age' value='" & numAge &"'><br>"
    End If
%>
Recieve Occansonal Emails: <input type='radio' name='RecieveOccansonal' value='Yes' <% If blnRecieveEmails = True Then Response.Write "Checked" Else Response.Write "" End If %>%>>Yes <input type='radio' name='RecieveOccansonal' value='No' <% If blnRecieveEmails = False Then Response.Write "Checked" Else Response.Write "" End If %>>No<br>
Recieve the newsletter: <input type='radio' name='RecieveNewsletter' Value='Yes' <% If blnRecieveNewsletter = True Then Response.Write "Checked" Else Response.Write "" End If %>>Yes <input type='radio' name='RecieveNewsletter' Checked <% If blnRecieveNewsletter = False Then Response.Write "Checked" Else Response.Write "" End If %>>No<br>
Auto Login: <input type='radio' name='Login' Value='Yes' Checked <% If blnAutoLogin = True Then Response.Write "Checked" Else Response.Write "" End If %>>Yes <input type='radio' name='Login' value='no' <% If AutoLogin = False Then Response.Write "Checked" Else Response.Write "" End If %>>No
<br>
<br>
<input type="submit" name="submit" value="Submit" ><br><br>

<%
If strViewerHasPowers = True Then
Response.Write "Viewer has powers: <input type="radio" name="powers" value="yes"> Yes <input type="radio" name="powers" value="No" Checked> No
%>
</form>

</body>

</html>

Imar · October 30th, 2003, 10:27 AM

Hi there,

The error is caused by the fact that the Execute method has reset the CursorType for the Recordset back to adOpenForwardOnly. You can check that out with this code:

Response.Write("CursorType is " & objRS.CursorType)

You'll see it has a value of 0 again.

Ways to work around this:

1. Don't use the Command Object, but use the Open method of the Recordset. This allows you to preserver the CursorType value.

2. Don't use the Find method, but instead use Username in your WHERE clause. IMO, this makes more sense as you are not retrieving all records from the database, but just the one you need.

You may be able to change the Command object, so it doesn't return a forward-only, read-only recordset, but I am not sure about that. The documentation for the Connection's Execute method explicitly states it returns a firehose cursor, but for the Command object there is no mention of this.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

HPryce · October 30th, 2003, 10:55 AM

Thanks again, it works now