ADODB.Recordset error '800a0e78' 2

[jbcamel]

I get the above error message when I run my asp script. The error points to the line "If Not usertable.EOF Then". I am trying to update an MS Access database. The update works but I get the error anyway. I'm not using Recordsets so I'm puzzled. I'm also new at this game so I'm sure I've made some basic mistake. My code is below. Can someone help? Thanks


<%
Set ObjPBase=Server.CreateObject(&quot;ADODB.Connection&quot
objPBase.Open &quot;authentix&quot;


SQL=&quot;UPDATE [authentix] SET [password] = '&quot; & Request(&quot;newpassword&quot & &quot;'&quot;
SQL=SQL & &quot;WHERE [logname] = '&quot; & Request(&quot;logname&quot & &quot;'&quot;
SQL=SQL & &quot; AND &quot;
SQL=SQL & &quot;[password] = '&quot; & Request(&quot;oldpassword&quot & &quot;';&quot;

Set usertable = objPBase.Execute (SQL)

If Not usertable.EOF Then
Response.Write &quot;<br>&quot;
Response.Write &quot;Your Password Has Been Changed&quot;
count = count +1
End If

If count = 0 Then
Response.Write &quot;<br><br><br><center><strong>&quot;
Response.Write &quot;Unable to Match Your Username and Old Password<br>&quot;
Response.Write &quot;</strong></center>&quot;
End If

objPBase.Close

%>

 

[giahan]

Hi,

Do u use a form to get the logname and password??? If yes, then your SQL should be:


SQL=&quot;UPDATE authentix SET password = '&quot; & Request.form(&quot;newpassword&quot & &quot;'&quot;
SQL=SQL & &quot;WHERE logname = '&quot; & Request.form(&quot;logname&quot & &quot;'&quot;
SQL=SQL & &quot; AND &quot;
SQL=SQL & &quot;password = '&quot; & Request.form(&quot;oldpassword&quot & &quot;';&quot;


If u not using a form to get logname and password, then your SQL should be:
SQL=&quot;UPDATE authentix SET password = '&quot; & newpassword & &quot;'&quot;
SQL=SQL & &quot;WHERE logname= '&quot; & logname & &quot;'&quot;
SQL=SQL & &quot; AND &quot;
SQL=SQL & &quot;password = '&quot; & oldpassword & &quot;';&quot;

Let me know if u still get any error






GH

 

[kor]

When you access a variable from the Request object without specifing which Collection it's in, the script will automatically search through the Collections until it finds the variable with that name. The order it searches is QueryString, Form, Cookies, ClientCertificate, and ServerVariables. The code as you wrote it is not wrong, but the operation would be far less expensive if you specified which collection you are accessing as giahan recomends.

That's not your error.

The error code does indicate that the...

Set usertable = objPBase.Execute (SQL)
If Not usertable.EOF Then

...area is where your error is occuring. Your premise is that the Connection.Execute operation returns an empty RecordSet object. This is correct. The catch is that it returns a closed recordset, so you can't access the EOF property.

I assume the only reason you are trying to use the return value is that you want to try to check for unspecified database errors. There are better ways to do that. Try using a VBScript &quot;On Error&quot; statement for that purpose.

 

[jbcamel]

GH - yes I am using a form. I tried substituting your syntax but received the same error.

kor - On Error doesn't work because the error code is 0. I am assuming that VBScript does not see the return of 0 records as an error. Any other suggestions?

What confuses me is I have another script that does a query instead of an update. It uses almost exactly the same syntax and it works. Am I missing something obvious? That code that works is below.

Jane

<%
Set ObjPBase=Server.CreateObject(&quot;ADODB.Connection&quot
objPBase.Open &quot;authentix&quot;


SQL=&quot;SELECT * FROM [authentix] WHERE&quot;
SQL=SQL & &quot;[logname] = '&quot; & Request(&quot;logname&quot & &quot;'&quot;
SQL=SQL & &quot; AND &quot;
SQL=SQL & &quot;[password] = '&quot; & Request(&quot;password&quot & &quot;'&quot;

Set usertable = objPBase.Execute (SQL)

If Not usertable.EOF Then
count = count +1
Response.Redirect(&quot;../disclaimer.htm&quot
End If

If count = 0 Then
Response.Write &quot;<br><br><br><center><strong>&quot;
Response.Write &quot;Invalid Logname or Password<br>&quot;
Response.Write &quot;</strong></center>&quot;
End If

%>

 

[kor]

Maybe your question is about SQL? An update querry is not supposed to return any records.

 

[jonscott8]

Hi jbcamel,

Your answer is as kor pointed out. Update/Insert/Delete queries return closed recordsets (non-row-returning recordsets), hence the EOF property is not applicable. It is applicable on row-returning recordsets, as in your last example with the SELECT query.

To achieve the result you seek, use the RecordsAffected parameter of the Execute method. For example:

objPBase.Execute SQL, lRecordsAffected
IF lRecordsAffected > 0
Response.Write &quot;Your Password Has Been Changed&quot;
ENDIF Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[jbcamel]

Jon,

Thank you so much. Worked like a charm!

Jane