Update a record using Prepared Statement

Dev Shed Forums Sponsor:

April 20th, 2012, 10:01 AM

jmss66

Registered User

Join Date: May 2007

Posts: 6

Time spent in forums: 59 m 51 sec
Reputation Power: 0

Update a record using Prepared Statement

I am trying to include Prepare Statement in my program and so far I have learned how to do a select statement with parameter. Next I am trying to update a record but keep getting the error messge below:

Microsoft JET Database Engineerror '80004005'

Operation must use an updateable query.
/development/CheckLogin.asp, line 102

Here is my code where I am updating using Prepared Statement. I don't even know if how I am using prepared statement is correct.

Line 102 is objCMD.Execute

Code:






   Set objCmd = Server.CreateObject("ADODB.command")

   set rsUsers = Server.CreateObject("ADODB.Recordset")

   objCmd.ActiveConnection = objConn

   objCmd.Prepared = True

   objCmd.CommandType = adCmdText

   objCmd.CommandText = "UPDATE Member SET LastLogin = ?"

   objCmd.Parameters.Append(objCmd.CreateParameter("@LastLogin", adDate, adParamInput, Len(Now()), Now()))

    rsUsers.CursorType = adOpenKeyset

    rsUsers.LockType = adLockOptimistic

    objCmd.Execute()

Below is my code for the Select Statement which is doing fine. This one executes before the code above which I am having a problem with..

Code:


Dim rsUsers

Set objCmd = Server.CreateObject("ADODB.command")

set rsUsers = Server.CreateObject("ADODB.Recordset")

objCmd.ActiveConnection = objConn

objCmd.Prepared = True

objCmd.CommandType = adCmdText

objCmd.CommandText = "SELECT * FROM Member WHERE SSN = ?"

objCmd.Parameters.Append(objCmd.CreateParameter("@SSN", adChar, adParamInput, Len(strSSN), strSSN))

rsUsers.CursorType = adOpenKeyset

rsUsers.LockType = adLockOptimistic

rsUsers.Open = objCmd.Execute()

Please I am new to using Prepared Statement and asking for any assistance.

Thanks.

April 20th, 2012, 12:43 PM

Ronster

Contributing User

Join Date: Feb 2007

Location: Charlotte

Posts: 412

Time spent in forums: 5 Days 2 h 6 m 13 sec
Reputation Power: 143

The first thing I would try is to add a WHERE clause to your update statement. As it is now, it is going to change the LastLogin value for every use in the table.

So making it look like:

"UPDATE Member SET LastLogin = ? WHERE SSN = ?" would be a start.

Lastly.... not sure what that Now() is doing.

Honestly... while I did a lot of Classic ASP a few years ago... we didn't do it like this.

We just made the SQL statement a string.
Your SELECT statement would look like this:

"SELECT * FROM Member WHERE SSN = " + strSSN

and go from there. I never did variable substitution in the object quite like that.

As a test... maybe try to update a value in the Member table that is NOT a datetime value. Like the last name for example. Just a test to make sure that your UPDATE statement is working. But I suspect your problem might be involved with that DateTime field.

April 20th, 2012, 06:57 PM

Doug G

Grumpier Old Moderator

Dev Shed God 19th Plane (14000 - 14499 posts)

Join Date: Jun 2003

Posts: 14,235

Time spent in forums: 1 Month 4 Weeks 14 h 27 m 16 sec
Reputation Power: 4445

Quote:

Operation must use an updateable query.

Your .mdb file or it's folder probably doesn't allow writing by the web server user. Also check the %TEMP% directory permissions.

__________________
======
Doug G
======
It is a truism of American politics that no man who can win an election deserves to. --Trevanian, from the novel Shibumi

Update a record using Prepared Statement

Developer Shed Advertisers and Affiliates