error in code

bukky · March 5th, 2004, 04:05 PM

I am having the following error, but most of the solutions I got relates to the error when a stored procedure is used and 'am not using a stored procedure, so how do I correct it.
here's the error:
ADODB.Recordset error '800a0e78' The operation requested by the application is not allowed if the object is closed.

here's the code:
<% Response.Buffer = true %>

<html>
<head>
<Title>Passoword change website</title>
</head>
<body>
<h2>Password change response<h2>
<%

'Declare variable needed
Dim strSql
Dim adCmdText

adCmdText = 1

'Retrieve the values
varStudentID = Request.Form("StudentId")
varPassword = Request.Form("Password")
varNewpassword = Request.Form("NewPassword")

'start building sql statement
strSql = "Update Try Set"
strSql = strSql & " Password ='" & varNewpassword & "'"
strSql = strSql & " WHERE StudentID =" & varStudentID
strSql = strSql & " AND Password='" & varPassword & "'"

'Create and Open connection to database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.OPen "DSN=new1"

'Create command object
Set objCmd = Server.CreateObject("ADODB.Command")

'Set Command Properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSql
objCmd.CommandType = adCmdText

'Execute Command
set objRS = objCmd.Execute

'Check if the user entered a valid username and password.

    If objRS.EOF or objRS.BOF Then
            Response.Write "Incorrect Username or password please try again"
        Response.Clear
        Response.Redirect "New112.asp"
    Else
        Response.Write "You have successfuly changed your password"
    End If

'Response.Write " Your passwords has been changed."
'Response.write " The number of record that has changed is: " & _
' intnumofrecord & " records<BR><BR>"

Set objRS = Nothing
objRS.close
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

%>

</body>
</html>

DaveGerard · March 5th, 2004, 04:13 PM

I have found that closing recordsets for an update query often give me that error unless my update is within an if/else or an if/end statement as follows...

rs.open sql, cn
if not rs.eof then
re("Field1") = Field1
re("Field2") = Field2
rs.update
end if
rs.close
set rs = nothing

If I didn't have an if/else or if/end statement I simply would not close the recordset. If that helps you out at all.

Imar · March 5th, 2004, 07:10 PM

Hi there,

An UPDATE query returns a closed recordset if you don't specify explicitly you don't want a recordset (adExecuteNoRecords), so you need to open the Recordset before you can use it in this scenario. However, AFAIK, it won't contain anything useful with an UPDATE statement.

You can take a look at RecordsAffected instead. This will return the number of records affected by the previous query. If it's 0, the UPDATE statement didn't update anything. If it returns 1, your query ran fine. If it returned more than 1, you have a data integrity issue ;)

This page will give you all the nitty gritty details.

Cheers,

Imar

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