SQL UPDATE statement for ASP & Access DB

jguy · Nov 21, 2000

Hello, I've built an ASP series that allows users to view info in a table (MS ACCESS) and they also have an "edit" link listed by the row in the record. When the "edit" is clicked the user is directed to another ASP page that contains a form. The information inside of the form text boxes is the information from the record. The user then can make manual changes to the data and click a submit button. The data is then supposed to be updated by a SQL statement. I keep getting some errors from this step complaining about the SQL statement. I have troubleshot this thing to death. I'm new to SQL update statements and the ever so thorn in the side of quotes and doublequotes. The data in the tables consists of a record id(autonumber, which isn't editable on the form), a userid(text),a password(text by Access Definition, but does contain numbers, ex. "12ms32&quot

, and an email(text). I will include the ASP code that "does the work" and the error message. Please advise on corrections. Thanks in advance!

ERROR MSG:
Database Errors Occured
UPDATE tblUsers SET UserID='rsmith';Password='rwssmy';Email='rsmith@smithsmachine.com';WHERE ID=24;

Error #-2147217900

Error desc. -> Characters found after end of SQL statement.

ACTUAL CODE:
<%
on error resume next
form_userid=request.form("userID&quot

form_password=request.form("password&quot

form_email=request.form("email&quot

form_ID=request.form("ID&quot

set conn=server.createobject("ADODB.connection&quot

conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/db/pics/mbvault.mdb"

form_userid=Replace(form_userid,"'","''&quot

if instr(lcase(form_userid),"<img&quot

>0 then
form_userid=""
end if

SQLstmt = "UPDATE tblUsers SET "
SQLstmt = SQLstmt & "UserID='" & form_userid & "';"
SQLstmt = SQLstmt & "Password='" & form_password & "';"
SQLstmt = SQLstmt & "Email='" & form_email & "';"
SQLstmt = SQLstmt & "WHERE ID=" & form_id & ";"

set rs = conn.Execute(SQLStmt)

If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
end if

if conn.Errors.Count>0 then
response.write "Database Errors Occured" & ""
response.write SQLstmt & ""
for counter= 0 to conn.Errors.Count
response.write "Error #" & conn.Errors(counter).Number & ""
response.write "Error desc. -> " & conn.Errors(counter).Description & ""
next
else
response.write "Everything Went Fine! Record is now UPDATED!"
response.write " " & SQLstmt
end if

rs.close
set rs = nothing
conn.Close
set conn = nothing
%>

Thank you guys for the help!!!

caf · Nov 21, 2000

Hi

THe problem is SQL interprets the semi-colon an "end-of-line" character and therefore you should append to the very end of your sql statement. Even that is optional.
Use the comma to separate your fields & if you wish append the semi colon ONLY once at the end of your statement

Eg

SQLstmt = "UPDATE tblUsers SET "
SQLstmt = SQLstmt & "UserID='" & form_userid & "',"
SQLstmt = SQLstmt & "Password='" & form_password & "',"
SQLstmt = SQLstmt & "Email='" & form_email & "',"
SQLstmt = SQLstmt & "WHERE ID=" & form_id
'Optional
SQLstmt = SQLstmt & ";"

Hope this helps
caf

jguy · Nov 21, 2000

caf, I still generate a SQL error. Here it is:

ERROR

atabase Errors Occured
UPDATE tblUsers SET UserID='jguy',Password='letmeiny',Email='jguy@smithsmachine.com',WHERE ID=23

Error #-2147217900

Error desc. -> Syntax error in UPDATE statement.

Any more suggestions? I think that we may be on the right track now, however....Again, thanks for the input!

Joe W. Guy
Network Admin
MIS Director

jguy · Nov 21, 2000

I get both the same errors with the ";" and without the ";". Sorryt that I didn't include that in the last response. Joe W. Guy
Network Admin
MIS Director

caf · Nov 21, 2000

Hi

Leave the last "," out...the one just before 'WHERE'

UPDATE tblUsers SET UserID='jguy',Password='letmeiny',Email='jguy@smithsmachine.com',WHERE ID=23

It should read

UPDATE tblUsers SET UserID='jguy',Password='letmeindammit',Email='jguy@smithsmac
hine.com' WHERE ID=23

No comma should be before the WHERE clause
When you've defined your last field you don't add a comma after it.

Hope this help
caf

jguy · Nov 21, 2000

AGGGGGGGGGGGGGGGGGhhhhhhh!!!!!!! Still no go! I've been hung on this statement for weeks now! Here's my statement and then yet, again the error msg (ps: caf, thank you for helping me

)
Statement:
SQLstmt = "UPDATE tblUsers SET "
SQLstmt = SQLstmt & "UserID='" & form_userid & "',"
SQLstmt = SQLstmt & "Password='" & form_password & "',"
SQLstmt = SQLstmt & "Email='" & form_email & "'"
SQLstmt = SQLstmt & "WHERE ID=" & form_id

Database Errors Occured
UPDATE tblUsers SET UserID='jguy',Password='letmeinplease',Email='jguy@smithsmachine.com'WHERE ID=23

Error #-2147217900

Error desc. -> Syntax error in UPDATE statement.

Joe W. Guy
Network Admin
MIS Director

khorjak · Nov 21, 2000

jguy,

leave a space between each variable-value pair, like so:

Code:

SQLstmt = &quot;UPDATE tblUsers SET &quot;
SQLstmt = SQLstmt & &quot;UserID='&quot; & form_userid & &quot;', &quot;
SQLstmt = SQLstmt & &quot;Password='&quot; & form_password & &quot;', &quot;
SQLstmt = SQLstmt & &quot;Email='&quot; & form_email & &quot;' &quot;
SQLstmt = SQLstmt & &quot;WHERE ID=&quot; & form_id

Choo Khor
choo.khor@intelebill.com

jguy · Nov 21, 2000

Still yet no success.... Here's the error msg

atabase Errors Occured
UPDATE tblUsers SET UserID='jguy', Password='letmeinagain', Email='jguy@smithsmachine.com' WHERE ID=23

Error #-2147217900

Error desc. -> Syntax error in UPDATE statement.

Here's the statement:

SQLstmt = "UPDATE tblUsers SET "
SQLstmt = SQLstmt & "UserID='" & form_userid & "', "
SQLstmt = SQLstmt & "Password='" & form_password & "', "
SQLstmt = SQLstmt & "Email='" & form_email & "' "
SQLstmt = SQLstmt & "WHERE ID=" & form_id

Again, thanks for the suggestions....this is really bugging me!

This should of been an easy project, but now it's turning green! AGggggh! HELP!
Joe W. Guy
Network Admin
MIS Director

khorjak · Nov 21, 2000

Try it this way and see what happens:

Code:

set oRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
oRS.Open &quot;tblUsers&quot;, conn, 1, 3, &H0002
oRS.Filter &quot;ID = &quot; & form_ID
if not oRS.EOF then
  oRS(&quot;UserID&quot;) = form_userid
  oRS(&quot;Password&quot;) = form_password
  oRS(&quot;Email&quot;) = form_email
  oRS.Update
end if

Choo Khor
choo.khor@intelebill.com

palbano · Nov 21, 2000

Take the SQL statement and run it in your Database environment. Get the SQL syntax correct within your Database IDE before trying to piece it together in ASP code.

"But, that's just my opinion... I could be wrong".
-pete

caf · Nov 21, 2000

Hi

You should look at your field names in tblUsers and see if they're spelt the same as in your sql statement.

The best way to check this is to start MS Access
Copy & paste the SQL in a new query (you'll have to change to SQL view to paste it obviously)
Run it and if no error message pops up then an input box would popup requesting a value for a variable.
Check the spelling of that variable to see if you actually meant a field name.
Also check the data types of your fields compared to what you're placing in them.
Also check the table name if it's spelt correctly.

(PS

ete, the original post contained a syntax error that was easily noticable just by viewing it & at that time it was obvious to replace the semi colons with commas...you did not need to run it in Access to find that out...the error message is exactly the same "Characters found after SQL string" resulting in an extra thread & maybe an extra p#$$ed off jguy.)

Good Luck!
caf

jguy · Nov 22, 2000

I've double checked every field name, table name, and value for this project against the code. All names are exactly the same. I tried to execute the SQL in the Access DB, but there was just one problem, I used an HTML form with txt boxes to import the data from. The SQL wouldn't work right in Access without the HTML form. Every field in the db is a text field, all except for the Primary Key, which isn't going to be edited anyway. I'm stuck now, and am really considering revamping this editing page! Aggh! It's been a long week........ I don't know what to do next, besides plant some C-4 under this box and give it a charge...hehehehe

Joe Joe W. Guy
Network Admin
MIS Director

jguy · Nov 22, 2000

YES!!!!!!! Looks like Khorjak wins this battle! Thanks Khorjak! Your code bypassed the SQL statement and worked with my db. I just commented out all of the extra code and placed in Khorjak's and the db updated! Yehaw!! (that's Southern Alabamian Dialect!

) Now I can spuce up the design and add some color to the pages. Thank you guys so much for all of the good comments and answers to this "green" programmers knowledgebase! Great thanks!

Joe W. Guy
Network Admin
MIS Director

jguy · Nov 22, 2000

Nevermind! Khorjak's code does update records...BUT! it only changes the data on the first ID! If I pick ID 45 and edit it, and then submit it, it changes ID 1 ! No matter which ID I choose, it always just changes ID 1! Agggggggggggggggggggggggghhhhhhhhh! I thought that we were out of this one!

So, I guess that I'm re-opening this thread! HELP!
Here's the code:
set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open "tblUsers",conn,1,3,&H0002
rs.Filter "ID = " & form_ID
if not rs.EOF then
rs("UserID&quot

= form_userid
rs("Password&quot

= form_password
rs("Email&quot

= form_email
rs.Update
Joe W. Guy
Network Admin
MIS Director

khorjak · Nov 22, 2000

Hi Joe,

Before actually doing an update, try displaying the variable 'form_id' to make sure you're not calling the same ID number all the time.

Choo Khor
choo.khor@intelebill.com

jguy · Nov 22, 2000

Yes, every time I choose a record from the db, the ID changes values on the form. In other words, if I choose record ID # 43, then on the html form, the number 43 is listed in the textbox along with all of the other data that is in the record. After editing the data from the record (listed inside of text boxes), I submit them and they all change the 1st record, or in other words, they duplicate the UserID's name,password, and email. Everything is a duplicate except for the ID, which is a PK (primary key)in the access db. Hope this helps.
PS:Coo Khor, a special thanks for your code ;-)

Joe Joe W. Guy
Network Admin
MIS Director

khorjak · Nov 22, 2000

Joe,

Just for the sake of testing, please do this:

Code:

set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
    rs.Open &quot;tblUsers&quot;,conn,1,3,&H0002
    rs.Filter &quot;ID = &quot; & form_ID 
        if not rs.EOF then
          response.write &quot;form: &quot; & form_id & &quot;<br>&quot;
          response.write &quot;dbpk: &quot; & rs(&quot;ID&quot;)
       ' rs(&quot;UserID&quot;) = form_userid
       ' rs(&quot;Password&quot;) = form_password
       ' rs(&quot;Email&quot;) = form_email 
       ' rs.Update

See what the results show.

Choo Khor
choo.khor@intelebill.com

swilliams · Nov 26, 2000

jguy,

If you send me your database, I will see if I can get to the bottom of it. My e mail address is swilliams@paragon.bm

Simon

Microbe · Nov 28, 2000

Also, try trouble shhooting the SQL statement. Start with the barest minimum, see if it works. Then add bit by bit.

Also try hard coding the values and see if that works and then one by one replacing the value with a variable.

At least that way you will see where it falls over.

Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

Guest_imported · Jan 10, 2001

Same problem guys...
sExeS = "UPDATE Security SET User='a', Password='aa', Access=90 WHERE ID=16;"
same error message as jguy... and I've to use a string, and not the workaround of khorjak

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL UPDATE statement for ASP & Access DB

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

IS-IT--Management

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

New member

Similar threads

Log in

Part and Inventory Search

Sponsor