SQL UPDATE statement for ASP & Access DB

[jguy]

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(&quot;userID&quot
form_password=request.form(&quot;password&quot
form_email=request.form(&quot;email&quot
form_ID=request.form(&quot;ID&quot

set conn=server.createobject(&quot;ADODB.connection&quot
conn.provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;
conn.Open &quot;c:/db/pics/mbvault.mdb&quot;

form_userid=Replace(form_userid,&quot;'&quot;,&quot;''&quot
if instr(lcase(form_userid),&quot;<img&quot>0 then
form_userid=&quot;&quot;
end if

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 & &quot;;&quot;

set rs = conn.Execute(SQLStmt)

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

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

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


Thank you guys for the help!!!

 

[sacview]

I don't know if this will help you any soulds like it might of already been tried. This is the exact code that I used to perform the same function. I went with a basic select statement. It works
<%
dim RS
dim Con
dim strSQL
SET Con = Server.CreateObject(&quot;ADODB.Connection&quot
Con.open=&quot;Driver={SQL Server}; Server=000000000; Database=training; UID=ssssss; PWD=ssssss&quot;

set RS = Server.CreateObject(&quot;ADODB.Recordset&quot

strSQL = &quot;SELECT * FROM Patient WHERE Patient_ID =&quot; &amp; frmid &amp; &quot;&quot;
RS.Execute(strSQL)
if RS.EOF then

else

'**********************************************************
'* Update crital perameters
'**********************************************************

RS( &quot;Patient_CP_Pressure_Min&quot; ) = Patient_CP_Pressure_Min
RS( &quot;Patient_CP_Pressure_Max&quot; ) = Patient_CP_Pressure_Max
RS( &quot;Patient_CP_Temp_Min&quot; ) = Patient_CP_Temp_Min
RS( &quot;Patient_CP_Temp_Max&quot; ) = Patient_CP_Temp_Max
RS( &quot;Patient_CP_Rate_Min&quot; ) = Patient_CP_Rate_Min
RS( &quot;Patient_CP_Rate_Max&quot; ) = Patient_CP_Rate_Max
RS.Update
end if
RS.Close
Con.Close
%>

 

[sacview]

here is a connection for Access

Connection for oledb DSN Less
<%
DIM Con
DIM RS
Set Con = Server.CreateObject(&quot;ADODB.Connection&quot
Con.provider=&quot;microsoft.jet.oledb.4.0&quot;
Con.open=server.mappath(&quot;\&quot&amp;&quot;\hospital\hospital.mdb&quot;
Set RS = Server.CreateObject( &quot;ADODB.Recordset&quot; )
RS.ActiveConnection = Con
RS.CursorType = adOpenStatic
RS.LockType = adLockOptimistic
%>

Connection for ODBC DSN-LESS

<%
DIM Con
DIM RS
Set Con = Server.CreateObject(&quot;ADODB.Connection&quot
Con.Open(&quot;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=d:\Inetpub\

http://wwwroot\hospital\hospital.mdb&quot;)

Set RS = Server.CreateObject( &quot;ADODB.Recordset&quot; )
RS.ActiveConnection = Con
RS.CursorType = adOpenStatic
RS.LockType = adLockOptimistic
%>