#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    6
    Rep 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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2007
    Location
    Charlotte
    Posts
    412
    Rep Power
    144
    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.
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,441
    Rep Power
    4539
    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
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo