Test Your Knowledge!

What is wrong here?

How do you update a DB when users are trying to update their personal info?

Below is the code.

Code:

---

'-- Declare your variables
                Dim DataConnection, cmdDC, RecordSet, SQL, strError
                Dim strLastName, strFirstName, strPassword, strEmail, strUserID
                Dim strAddress, strState, strZipCode, strPhone

'-- Get data from the form fields
                strLastName = Request.Form("name")
                strFirstName = Request.Form("name1")
                strAddress = Request.Form("address")
                strState = Request.Form("state")
                strZipCode = Request.Form("zipcode")
                strPhone = Request.Form("phone")
                strPassword = Request.Form("password")
                strEmail = Request.Form("email")
                strUserID = Request.Form("userID")
               

'-- Create object and open database
                Set DataConnection = Server.CreateObject("ADODB.Connection")
                DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                                    "DBQ=" & DatabasePath & ";"
                                                               

                Set cmdDC = Server.CreateObject("ADODB.Command")
                cmdDC.ActiveConnection = DataConnection
               
        End If

'-- default SQL
                SQL = "SELECT * FROM UserRegistration"

                If Request.Form("name") <> "" Then       
                        SQL = "SELECT Register.userID, Register.password, Register.email FROM Register WHERE " & _
                              "Register.userID='" & strUserID & "' OR " & _
                              "Register.password ='" & strPassword & _
                              "' OR Register.email ='" & strEmail & "'"
                End If

                cmdDC.CommandText = SQL
                Set RecordSet = Server.CreateObject("ADODB.Recordset")

'-- Cursor Type, Lock Type

'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4


                RecordSet.Open cmdDC, , 3, 3

'-- Add new record to the database

                        Dim Dconn, sSQL
                  Set Dconn = Server.CreateObject("ADODB.Connection")
                  Dconn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
                                  DatabasePath & ";"

                        sSQL = "INSERT INTO Register(FirstName, LastName, address, state, zipcode, phonenumber, email, userID, " & _
                              "password, userLevel) VALUES ('" & strFirstName & _
                              "','" & strLastName & "','" & strAddress & "','" & strState & _
                              "','" & strZipCode & "','" & strPhone & "','" & strEmail & _
                              "','" & strUserID & "','" & strPassword & "',1)"
                        Dconn.Execute sSQL
                        Dconn.Close
                        Set Dconn = Nothing

---

!!

OK here is more code. Why does this error occur?

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/blah.org/User_Profile/update.asp, line 71, column 56
"','" & strUserID & "','" & strPassword & "',1) WHERE ("Register.FirstName='" & strFirstName & "' AND " & _
-------------------------------------------------------^

Code:

---

'-- Update the database

                        Dim Dconn, sSQL
                  Set Dconn = Server.CreateObject("ADODB.Connection")
                  Dconn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
                                  DatabasePath & ";"

                        sSQL = "UPDATE Register SET (FirstName, LastName, address, state, zipcode, phonenumber, email, userID, " & _
                              "password, userLevel) = ('" & strFirstName & _
                              "','" & strLastName & "','" & strAddress & "','" & strState & _
                              "','" & strZipCode & "','" & strPhone & "','" & strEmail & _
                              "','" & strUserID & "','" & strPassword & "',1) WHERE ("Register.FirstName='" & strFirstName & "' AND " & _
                              "Register.LastName='" & strLastName & "' AND " & _
                              "Register.Address='" & strAddress & "' AND " & _
                              "Register.State='" & strState & "' AND " & _
                              "Register.ZipCode='" & strZipCode & "' AND " & _
                                        "Register.PhoneNumber='" & strPhone & "' AND " & _
                                        "Register.LastName='" & strLastName & "' AND " & _
                                        "Register.Email='" & strEmail & "' AND " & _
                                        "Register.userID='" & strUserID & "' AND " & _
                                        "Register.Password='" & strPassword & "')"

                        Dconn.Execute sSQL
                        Dconn.Close
                        Set Dconn = Nothing

---

Looks like you might have an unmatched double quote just before the WHERE on that line.

That was good but now it gives me this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/blah.org/User_Profile/update.asp, line 82

Code:

---

'-- Update the database

                        Dim Dconn, sSQL
                  Set Dconn = Server.CreateObject("ADODB.Connection")
                  Dconn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
                                  DatabasePath & ";"

                        sSQL = "UPDATE Register SET (FirstName, LastName, address, state, zipcode, phonenumber, email, userID, " & _
                              "password, userLevel) = ('" & strFirstName & _
                              "','" & strLastName & "','" & strAddress & "','" & strState & _
                              "','" & strZipCode & "','" & strPhone & "','" & strEmail & _
                              "','" & strUserID & "','" & strPassword & "',1) WHERE (Register.FirstName='" & strFirstName & "' AND " & _
                              "Register.LastName='" & strLastName & "' AND " & _
                              "Register.Address='" & strAddress & "' AND " & _
                              "Register.State='" & strState & "' AND " & _
                              "Register.ZipCode='" & strZipCode & "' AND " & _
                                        "Register.PhoneNumber='" & strPhone & "' AND " & _
                                        "Register.LastName='" & strLastName & "' AND " & _
                                        "Register.Email='" & strEmail & "' AND " & _
                                        "Register.userID='" & strUserID & "' AND " & _
                                        "Register.Password='" & strPassword & "')"

                  +++++          Dconn.Execute sSQL
                        Dconn.Close
                        Set Dconn = Nothing

---

line 82 marked with +++++

!!!

Anyone have good syntax here?

Hey, I think I found the problem. Looks like you just messed up the string a little, no big deal. Right after your WHERE ( you have a ", remove it and it should work. Well, at least it will fix THAT problem! :)

Good Luck!
Litehouse...

[QUOTE]Originally posted by jesus4u
[B]OK here is more code. Why does this error occur?

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/blah.org/User_Profile/update.asp, line 71, column 56
"','" & strUserID & "','" & strPassword & "',1) WHERE ("Register.FirstName='" & strFirstName & "' AND " & _
-------------------------------------------------------^

There are three things to say about your update statement:
1) The syntax is not correct. It must be in this form:
Update Table Set field1 = 'Value1', Field2 = 'Value2', .... Where Fieldx = 'Valuex' And Fieldy = 'Valuey'

Assuming all fields are strings

2) You have referenced the lastname twice in the where part.

3) The update statement won't update a thing, if you include fields you update in the where part with the same values as you want to update them to.

eg: this sql statement does nothing.

Update Table1 set Field1 = 'Hello' Where Field1 = 'Hello'

I appreciate your answer and that is true but look above at my previous response. I now get a syntax error.

ugh!

Frans C,
Actually, while that is one way (syntax) it does not HAVE to be in that format. It can also be in the following format:
Update Table Set (Field1, Field2) = (Value1, Value2)
The above format was the one that jesus4u was attempting to use...

Quote:

---

Originally posted by Frans C
There are three things to say about your update statement:
1) The syntax is not correct. It must be in this form:
Update Table Set field1 = 'Value1', Field2 = 'Value2', .... Where Fieldx = 'Valuex' And Fieldy = 'Valuey'

Assuming all fields are strings

2) You have referenced the lastname twice in the where part.

3) The update statement won't update a thing, if you include fields you update in the where part with the same values as you want to update them to.

eg: this sql statement does nothing.

Update Table1 set Field1 = 'Hello' Where Field1 = 'Hello'

---

Litehouse thanks for the info. I changed my code but I still get an error like this:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/ReclaimAmerica.org/User_Profile/update.asp, line 71, column 114
sSQL = "UPDATE Register SET FirstName='"&strFirstName&"', LastName='"&strLastName&"', Address='"&strAddress&"' & _
-----------------------------------------------------------------------------------------------------------------^

Code:

---

                        sSQL = "UPDATE Register SET FirstName='"&strFirstName&"', LastName='"&strLastName&"', Address='"&strAddress&"' & _
                                        State='"&strState&"', ZipCode='"&strZipCode&"', PhoneNumber='"&strPhone&"', Email='"&strEmail&"' & _
                                        userID='"&strUserID&"', Password='"&strPassword&"' WHERE Email="&strEmail&""

---

Here's another easy one... :)
I believe the reason you are getting that particular error, is because you don't have spaces in between your &'s and your variable names. This is rather important, 'cause it tells the compiler to do something other than what you are intending here.

Litehouse...

Quote:

---

Originally posted by jesus4u
Litehouse thanks for the info. I changed my code but I still get an error like this:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/ReclaimAmerica.org/User_Profile/update.asp, line 71, column 114
sSQL = "UPDATE Register SET FirstName='"&strFirstName&"', LastName='"&strLastName&"', Address='"&strAddress&"' & _
-----------------------------------------------------------------------------------------------------------------^

Code:

---

                        sSQL = "UPDATE Register SET FirstName='"&strFirstName&"', LastName='"&strLastName&"', Address='"&strAddress&"' & _
                                        State='"&strState&"', ZipCode='"&strZipCode&"', PhoneNumber='"&strPhone&"', Email='"&strEmail&"' & _
                                        userID='"&strUserID&"', Password='"&strPassword&"' WHERE Email="&strEmail&""

---

---

Well to finally bring this thread to a close the code that works now looks like this

Code:

---


sSQL = "UPDATE Register SET FirstName='" & strFirstName & "', LastName='" & strLastName & "', Address='" & strAddress & _
                                        "', State='" & strState & "', ZipCode='" & strZipCode & "', PhoneNumber='" & strPhone & "', Email='" & strEmail & _
                                        "', userID='" & strUserID & "', Password='" & strPassword & "' WHERE Email='" & strEmail & "'"

---

Thank you for all your help!

This will only work, if the email is not changed. Find a field that is unique, and unchangeable to identify the record and include this field in the where part.