recordset open

**spandex44** · Jul 14th, 2001, 09:35 PM

I have a new question for all of you ASP gurus out there:

If I am connected to a database and I create a recordset like this:

Code:

Set User_Data = Server.CreateObject("ADODB.Recordset")

Then, how can I perform several queries within the same ASP script? Currently, I use this code:
User_Data.Open Query, Connect, adOpenDynamic, adLockOptimistic
However, if I change the Query variable and then run the same line of code again, I get an error message saying 'Operation is not allowed when the object is open.'

What's the deal here? How can I change the query without trying to open the recordset again?

Code:

<html>
<head>
<title>Post New</title>
</head>
<body>

<form method="POST" action="postnew.asp">
<b>Username: </b>
<input type="text" width="50" name="txtUsername">
<input type="password" width="50" name="txtPassword">
<br>
<b>Title: </b>
<input type="text" width="50" name="txtTitle">
<br>
<b>Message: </b>
<br>
<textarea name="txtText" rows="6" cols="50"></textarea>
<br>
<input type="hidden" value="go" name="status">
<input type="submit" value="Sign Up">
</form>

<%
If Request("status") = "go" then
 Const adOpenStatic = 3
 Const adLockOptimistic = 3
 Dim Connect, User_Data, Query
 Set Connect = Server.CreateObject("ADODB.Connection")
 Connect.Open "forum_database"
 Set User_Data = Server.CreateObject("ADODB.Recordset")
 Query = "SELECT Password FROM users WHERE Username='" & Request("txtUsername") & "'"
 User_Data.Open Query, Connect, adOpenDynamic, adLockOptimistic
  If User_Data("Password") = Request("txtPassword") Then 
   Query = "SELECT * FROM messages"
   User_Data.Open Query, Connect, adOpenDynamic, adLockOptimistic
   User_Data.AddNew
   User_Data("Poster") = Request("txtUsername")
   User_Data("Title") = Request("txtTitle")
   User_Data("Message") = Request("txtText")
   User_Data.Update
  Else
   response.write("Invalid Username/Password")
  End If
End If
%>

**rikshawdriver** · Jul 14th, 2001, 11:22 PM

Code:

  If User_Data("Password") = Request("txtPassword") Then 
   Query = "SELECT * FROM messages"
'--------------------------------------------------
user_data.close
'--------------------------------------------------
   User_Data.Open Query, Connect, adOpenDynamic, adLockOptimistic
   User_Data.AddNew
   User_Data("Poster") = Request("txtUsername")
   User_Data("Title") = Request("txtTitle")
   User_Data("Message") = Request("txtText")
   User_Data.Update
  Else
   response.write("Invalid Username/Password")
  End If
End If

**monte96** · Jul 16th, 2001, 12:50 AM

You can't. You have to close the recordset and reopen it with the new query. You don't have to set the reference to Nothing and reset it to a recordset object, just use the close method before you try to reopen it with the new query.

**spandex44** · Jul 20th, 2001, 05:20 PM

I was out of town for a few days, so couldn't get ack earlier. THanks a lot for the help, it worked perfectly.

Thread: recordset open

Thread Tools

Display

recordset open

Re: recordset open

Posting Permissions