Beginning to Dislike MS Access and ASP together

[dixcyn04]

Ok, now I've run into another little hiccup in my application. The ability to update records already in existance. What is bugging me about this, is the code I will submit was what I found on forums and suggestions here and there on the Internet and very similiar to what is suggested on this forum too.

I've begun toi regret not having SQL Server for this little exercise I am coding since much of the code I have written in the past worked just fine with SQL Server and I still have application code from that app I could reuse. Anyways, enough about my regrets. Here is the error/problem

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/test2.asp, line 13
 

Now the code:

Dim objConn, strConn
'CREATE STRING CONNECTION TO DATABASE
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\thecircle.mdb")
 
'now connect to database - conn
objConn.Open(strConn)
 
mySql = "Update Staff SET Locked = 1 WHERE ID = 1"
 
objConn.Execute mySql
 
objConn.Close
Set objConn

Ok, so.. why the heck am I getting an error. The query works JUST fine in MS Access' little Query Wizard and such.

What I hate is trying to figure this out now... with people expecting this site done... but not having the tools I am familiar with.

BTW, here is the alternative code.. that is actually on the page itself.

<!-- #INCLUDE File="strConn.asp" -->
<%
    'Dim all objects/variables used on page
    Dim BadLogin, NumAttempts, Warning, MustEnter, Locked, objUser, Username, Password, strQuery, UsernameDB, PasswordDB
 
    'Check to see if form has been submitted or not
    If Request.Form("Login") = "loginme" Then
        'Here we begin the form processing code, otherwise move to the else
 
        'Set Username and Password variables
        Username = Request.Form("Username")
        Password = Request.Form("Password")
 
        'Check for a bad submission.
        If Username = "" OR Password = "" Then
            'Ok, bad form submission. Cannot process blank fields.
            'So now we set the MustEnter and redirect the end user. Since
            'in reality we could NEVER log a user in with a blank password
            'or username, we will not increment the NumAttempts variable.
            Response.Redirect "test.asp?Redirect=1&NumAttempts=0&MustEnter=1"
        Else
            'Ok, so both Username and Password have values. This means we need
            'to attempt to log in an actual account. Also, time to get the number
            'of attempts this user has tried to log in.
            NumAttempts = Cint(Request.Form("NumAttempts"))
 
            'create the objUser and populate it from the database. This is the
            'object needed to compare the submission to the database to either
            'allow the user or not.
            strQuery = "SELECT * from Staff where Username='"& Username &"'"
            Set objUser = Server.CreateObject("ADODB.Recordset")
            objUser.Open strQuery, objConn
 
            If NOT objUser.EOF Then
                'Let's check the account to see if it is a locked account. Locked
                'accounts should only be allowed on after review AND discussion with
                'the web administrator. Also, confirm the IP address that locked the account
                If objUser("Locked") = 1 Then
                    'Ok, so this account is locked. Let's redirect the user to the page and
                    'let them know what is up. They won't be able to get into the system until
                    'they've spoken with the web administrator. However, do not show them the
                    'IP address that locked the account. It's how to determine if it was them
                    'accidently screwing up or someone else trying to break in
                    Response.Redirect "test.asp?NumAttempts=0&Redirect=1&Locked=1"
                Else
                    PasswordDB = objUser("Password")
                    NumAttempts = Cint(Request.Form("NumAttempts")) + 1
 
                    'Ok, so let's check the passwords and see if the user submitted the correct
                    'one with their username.
                    If Password <> PasswordDB Then
                        'If a bad password was submitted, count the attempts. If less than 3
                        'then return with increment. If 3 or more, lock account
                        If NumAttempts = 3 Then
                            'Ok, this user has tried too many times to log in. Time to lock the
                            'account for satefy sake.
                            strQuery = "UPDATE Staff SET Staff.Locked = 1 WHERE Staff.Username="& Username &";"
                            objConn.Execute(strQuery)
 
                            Response.Redirect "test.asp?Redirect=1&Locked=1"
                        Elseif NumAttemps = 2 Then
                            Response.Redirect "test.asp?Redirect=1&NumAttempts="& NumAttempts &"&BadLogin=1&Warning=1"
                        Else
                            Response.Redirect "test.asp?Redirect=1&NumAttempts="& NumAttempts &"&BadLogin=1"
                        End If
                    Else
                        'Ok, good user and good password. Set system, then redirect to homepage
                        'Set Session Variable for this user
                        Session("Name") = objUser("Username")
                        If objUser("Rank") = 1 Then
                            Session("Menu") = "Full"
                        Else
                            Session("Menu") = "Advanced"
                        End If
                        GoodLogin = "Success"
                    End If
                End If
            Else
                'Ok, so no user could be found with that username. Let them know this.
                'Also, this again, does not increment NumAttempts since no account has
                'actually been accessed. This only causes a bad login to occur
                Response.Redirect "test.asp?NumAttempts=0&Redirect=1&BadLogin=1"
            End If
        End If
    Else
        'Now, since the form was not submitted, we need to check to see
        'if a redirection has occurred. If so, then operate using those
        'variables, otherwise the form should look new.
        If Request.Querystring("Redirect")  = "1" Then
            'Ok, so the form was submitted and something was wrong. Let's
            'begin to process what it was by defining our variables
            BadLogin = Request.Querystring("BadLogin")
            NumAttempts = Request.Querystring("NumAttempts")
            Warning = Request.Querystring("Warning")
            MustEnter = Request.Querystring("MustEnter")
            Locked = Request.Querystring("Locked")
        Else
            'Ok, so the form wasn't submitted and the user has not been here
            'before. This means a blank, new form should be loaded with the
            'correct values for the correct variables.
            NumAttempts = 0
        End If
    End If
 

This code above also generates the same error about must use an updateable query..etc. Really frustrated at this point.

-Dave

[shweta123]

Hi,

Try this code

Dim objConn, strConn
'CREATE STRING CONNECTION TO DATABASE
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\thecircle.mdb")

'now connect to database - conn
objConn.Open(strConn)
objConn.Mode = 3 '3 = adModeReadWrite
mySql = "Update Staff SET Locked = 1 WHERE ID = 1"
objConn.Execute(mySql)

Also ,check for write permissions on IUSR_MACHINE account.