how to solve this error ADODB.Recordset (0x800A0E78)

hi,

how to solve this error

ADODB.Recordset (0x800A0E78) Operation is not allowed when the object is closed.
/bank.asp, line 43

please help me!!!!

The exact code like below:

<%
Dim conn
Dim rs
Dim MYSQL,MYSQL2

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.ConnectionString = "DSN=prmm;UID=administrator;pwd=sa"
conn.Open

startdate=request("tarikh1")
enddate=request("tarikh2")


MYSQL = "set nocount on;SELECT * FROM table_x where paymendate between '" & tarikh1 & "' and '" & tarikh2 & "'"
rs.open MYSQL,conn

if not rs.BOF then
rs.Movefirst

do until rs.EOF

if ([table_x.id] = [table_y.id]) then
MYSQL2 = "set nocount on;update table_y" & " set paymentdate='" & rs("paymentdate") & "'," & "no_resit='" & rs("no_resit") & "'," & "status='" & rs("status") & "'"
Set rs = conn.Execute(MYSQL2)
else
response.write "No transaction"
response.write "<p>" & "<A HREF=javascript:history.go(-1)>" & "GoBack" & "</a>"
end if

rs.movenext <<<----------error in here (line 43)
Loop

response.write "<p>" & "<center>" & "Transaction have been done" & "</center>" & "</p>"
response.write "<p>" & "<A HREF=javascript:history.go(-1)>" & "GoBack" & "</a>"
else
response.Write("No data")
response.write "<p>" & "<A HREF=javascript:history.go(-1)>" & "GoBack" & "</a>"
end if

Set rs = Nothing
Set conn = Nothing

%>

anyone pls help me to solve this error!

thanks

what is that line?

why do you have Do Until rs.EOF but inside that loop you set rs?

i cannot follow your code at all. for future posts please put your code in {code} {/code} tags (replacing {} with [])

Ok where/how to start hummm...

Ok first thing, *ALWAYS* wait till the *LAST* minute before you create a connection and/or a recordset object.

Now this is NOT a big deal but its good practice. Also, close and set to nothing the objects the sooner the better, you'll notice(maybe not with 5 users connected but with 1000 users you'll see) a performance increase.

So :

<%
Dim conn
Dim rs
Dim MYSQL
Dim MYSQL2 <-- Try to be more specific in naming your variables.

'Notice you haven't declared,unless you haven't shown the/your entire code, where you declare these two variables:
startdate and enddate

So let's start over:

<%
Dim conn
Dim rs
Dim MYSQL
Dim MYSQL2 <-- Try to be more specific in naming your variables.
Dim startdate
Dim enddate


'Retrieve the values for the dates:
startdate = Request("tarikh1")
enddate = Request("tarikh2")


'ok what if either startdate and enddate holds nothing? Do you make a validation?

'Validation
If NOT IsDate(startdate) Then
Response.Write "The start date is invalid..."
Response.End 'or Response.Redirect
ElseIf NOT IsDate(enddate) Then
Response.Write "The end date is invalid..."
Response.End 'or Response.Redirect
End If


'Create the SQL Query(Notice I haven't created a connection object nor a recordset object yet!)

MYSQL = "SELECT * " & _
"FROM table_x " & _
"WHERE paymendate BETWEEN '" & startdate & "' " & _
"AND enddate "
'FOR DEBUG ONLY
'Response.Write strSql & "<hr>"
'Response.End

'NOTE: First thing, why are you asking for * ?
'Why not ask for the specific fields you need ?
'Another thing, in the example you gave us, your between
'statement was like this:
'between '" & tarikh1 & "' and '" & tarikh2 & "'"

'Is this an error/typo on your part? or are these *other* variables that we are not aware of ?



'Ok now we can start creating the connection object and the recordset
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=prmm;UID=administrator;pwd=sa"
conn.Open


'Notice I'm NOT creating a recordset, in fact it is the Connection object that will create *IMPLICITLY* my recordset object.

NOTE: Creating an IMPLICIT recordset has its pros and cons be sure to read about them.

so:


Set rs = conn.execute MYSQL


'Now, no need to use the rs.MoveFirst cause you haven't even played with the recordset yet so its obviously at the first record.
'What you need to check though is if it has reached EOF so:


If NOT rs.EOF Then

While NOT rs.EOF

'What is this ?
'if ([table_x.id] = [table_y.id]) then
'Are they variables ? what? are they field from your *rs* recordset?
'If so where is the *rs* in front of table_x.id ?
'Shouldn't be:

If rs("table_x.id") = rs("table_y.id") Then
blah...blah...blah
End If

no? shouldn't be that?

As for the rest, I can't help you because there are/is either too many mistakes and/or typos and I don't have time to ask questions. Your the one asking, shouldn't be me

But the reason you're getting the error is because you are Seting another variable rs to become a recordset BUT YOU ALREADY HAVE A VARIABLE NAMED *RS* THAT *IS* A RECORDSET do you see it?
Whe are *INSIDE* the loop


rs.MoveNext
Wend
End If

'Close the Recordset and Connection object
rs.Close
Set rs = nothing

conn.Close
Set conn = nothing
%>


Hope this helped a bit!
Sincerely

Vlince

Wow Vlince, I'm impressed. I'd looked at the code but didn't want to re-write the whole thing to fix the problem. You definitely put some effort into this one, and made things clearer for me at least. So even though its not my question or anything, thanks!

LOL thanks karsh44

I remember when I first started ASP things where ruff i.e. like what the h*ll is a recordset and a connection object ?

Why so many loop and which one to chose grrrrr...

So if I can give back a little to the ASP community its, I guess, by helping people/others the way I would've liked being helped.
Hope that make sense

Although I'll admit, I do get mad sometimes at poster that don't take a minute to either search google.com OR simply buy an ASP book(speaking of which, I have a Learn ASP for Dummies book to sell...any one?)

Thanks again karsh44

Sincerely

Vlince

hi all,

sorry if i'm make u all too confuse about my coding actually i want to transfer data from table_x with the field paymentdate, status and no_resit to table_y based on id. If field id in table_x same with field id in table_y all data from table_x (paymentdate, no_resit, status) must be fill in table_y with same id. That why i use command update table_y.

By the way thank you a lot to vlince because show me step by step i try to fix it is. I'm new in ASP programming and try to understand also learn more about ASP.

thanks again vlince, nopoint and karsh44 because answer my message.

sincerely,
zalia