By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,268 Members | 1,371 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,268 IT Pros & Developers. It's quick & easy.

Beginning to Dislike MS Access and ASP together

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. Error Type:
  2. Microsoft JET Database Engine (0x80004005)
  3. Operation must use an updateable query.
  4. /test2.asp, line 13
  5.  
Now the code:
Expand|Select|Wrap|Line Numbers
  1. Dim objConn, strConn
  2. 'CREATE STRING CONNECTION TO DATABASE
  3. Set objConn = Server.CreateObject("ADODB.Connection")
  4. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\thecircle.mdb")
  5.  
  6. 'now connect to database - conn
  7. objConn.Open(strConn)
  8.  
  9. mySql = "Update Staff SET Locked = 1 WHERE ID = 1"
  10.  
  11. objConn.Execute mySql
  12.  
  13. objConn.Close
  14. 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.
Expand|Select|Wrap|Line Numbers
  1. <!-- #INCLUDE File="strConn.asp" -->
  2. <%
  3.     'Dim all objects/variables used on page
  4.     Dim BadLogin, NumAttempts, Warning, MustEnter, Locked, objUser, Username, Password, strQuery, UsernameDB, PasswordDB
  5.  
  6.     'Check to see if form has been submitted or not
  7.     If Request.Form("Login") = "loginme" Then
  8.         'Here we begin the form processing code, otherwise move to the else
  9.  
  10.         'Set Username and Password variables
  11.         Username = Request.Form("Username")
  12.         Password = Request.Form("Password")
  13.  
  14.         'Check for a bad submission.
  15.         If Username = "" OR Password = "" Then
  16.             'Ok, bad form submission. Cannot process blank fields.
  17.             'So now we set the MustEnter and redirect the end user. Since
  18.             'in reality we could NEVER log a user in with a blank password
  19.             'or username, we will not increment the NumAttempts variable.
  20.             Response.Redirect "test.asp?Redirect=1&NumAttempts=0&MustEnter=1"
  21.         Else
  22.             'Ok, so both Username and Password have values. This means we need
  23.             'to attempt to log in an actual account. Also, time to get the number
  24.             'of attempts this user has tried to log in.
  25.             NumAttempts = Cint(Request.Form("NumAttempts"))
  26.  
  27.             'create the objUser and populate it from the database. This is the
  28.             'object needed to compare the submission to the database to either
  29.             'allow the user or not.
  30.             strQuery = "SELECT * from Staff where Username='"& Username &"'"
  31.             Set objUser = Server.CreateObject("ADODB.Recordset")
  32.             objUser.Open strQuery, objConn
  33.  
  34.             If NOT objUser.EOF Then
  35.                 'Let's check the account to see if it is a locked account. Locked
  36.                 'accounts should only be allowed on after review AND discussion with
  37.                 'the web administrator. Also, confirm the IP address that locked the account
  38.                 If objUser("Locked") = 1 Then
  39.                     'Ok, so this account is locked. Let's redirect the user to the page and
  40.                     'let them know what is up. They won't be able to get into the system until
  41.                     'they've spoken with the web administrator. However, do not show them the
  42.                     'IP address that locked the account. It's how to determine if it was them
  43.                     'accidently screwing up or someone else trying to break in
  44.                     Response.Redirect "test.asp?NumAttempts=0&Redirect=1&Locked=1"
  45.                 Else
  46.                     PasswordDB = objUser("Password")
  47.                     NumAttempts = Cint(Request.Form("NumAttempts")) + 1
  48.  
  49.                     'Ok, so let's check the passwords and see if the user submitted the correct
  50.                     'one with their username.
  51.                     If Password <> PasswordDB Then
  52.                         'If a bad password was submitted, count the attempts. If less than 3
  53.                         'then return with increment. If 3 or more, lock account
  54.                         If NumAttempts = 3 Then
  55.                             'Ok, this user has tried too many times to log in. Time to lock the
  56.                             'account for satefy sake.
  57.                             strQuery = "UPDATE Staff SET Staff.Locked = 1 WHERE Staff.Username="& Username &";"
  58.                             objConn.Execute(strQuery)
  59.  
  60.                             Response.Redirect "test.asp?Redirect=1&Locked=1"
  61.                         Elseif NumAttemps = 2 Then
  62.                             Response.Redirect "test.asp?Redirect=1&NumAttempts="& NumAttempts &"&BadLogin=1&Warning=1"
  63.                         Else
  64.                             Response.Redirect "test.asp?Redirect=1&NumAttempts="& NumAttempts &"&BadLogin=1"
  65.                         End If
  66.                     Else
  67.                         'Ok, good user and good password. Set system, then redirect to homepage
  68.                         'Set Session Variable for this user
  69.                         Session("Name") = objUser("Username")
  70.                         If objUser("Rank") = 1 Then
  71.                             Session("Menu") = "Full"
  72.                         Else
  73.                             Session("Menu") = "Advanced"
  74.                         End If
  75.                         GoodLogin = "Success"
  76.                     End If
  77.                 End If
  78.             Else
  79.                 'Ok, so no user could be found with that username. Let them know this.
  80.                 'Also, this again, does not increment NumAttempts since no account has
  81.                 'actually been accessed. This only causes a bad login to occur
  82.                 Response.Redirect "test.asp?NumAttempts=0&Redirect=1&BadLogin=1"
  83.             End If
  84.         End If
  85.     Else
  86.         'Now, since the form was not submitted, we need to check to see
  87.         'if a redirection has occurred. If so, then operate using those
  88.         'variables, otherwise the form should look new.
  89.         If Request.Querystring("Redirect")  = "1" Then
  90.             'Ok, so the form was submitted and something was wrong. Let's
  91.             'begin to process what it was by defining our variables
  92.             BadLogin = Request.Querystring("BadLogin")
  93.             NumAttempts = Request.Querystring("NumAttempts")
  94.             Warning = Request.Querystring("Warning")
  95.             MustEnter = Request.Querystring("MustEnter")
  96.             Locked = Request.Querystring("Locked")
  97.         Else
  98.             'Ok, so the form wasn't submitted and the user has not been here
  99.             'before. This means a blank, new form should be loaded with the
  100.             'correct values for the correct variables.
  101.             NumAttempts = 0
  102.         End If
  103.     End If
  104.  
This code above also generates the same error about must use an updateable query..etc. Really frustrated at this point.

-Dave
May 18 '07 #1
Share this Question
Share on Google+
1 Reply


shweta123
Expert 100+
P: 692
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.
May 20 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.