Test Your Knowledge!

**jesus4u** · Mar 15th, 2001, 07:53 AM

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

**jesus4u** · Mar 15th, 2001, 07:59 AM

!!

**jesus4u** · Mar 15th, 2001, 08:28 AM

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

**jbart** · Mar 15th, 2001, 08:34 AM

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

**jesus4u** · Mar 15th, 2001, 08:39 AM

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 +++++

**jesus4u** · Mar 15th, 2001, 08:42 AM

!!!

**jesus4u** · Mar 15th, 2001, 08:46 AM

Anyone have good syntax here?

**Litehouse** · Mar 15th, 2001, 08:53 AM

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 " & _
-------------------------------------------------------^

**Frans C** · Mar 15th, 2001, 08:54 AM

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'

**jesus4u** · Mar 15th, 2001, 08:56 AM

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

ugh!

**Litehouse** · Mar 15th, 2001, 09:17 AM

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...

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'

**jesus4u** · Mar 15th, 2001, 09:25 AM

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&""

**Litehouse** · Mar 15th, 2001, 09:39 AM

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...

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&""

**jesus4u** · Mar 15th, 2001, 09:45 AM

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!

**Frans C** · Mar 15th, 2001, 10:29 AM

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.

Thread: Test Your Knowledge!

Thread Tools

Display

Posting Permissions