-
recordset open
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
%>
-
Re: recordset open
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
-
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.
-
I was out of town for a few days, so couldn't get ack earlier. THanks a lot for the help, it worked perfectly.
