Error 0x80040E14, I googled it but did't find much help

[Spyder1000]

Please bare with me i'm sort of new at this. I've got this thing to display the records jsut fine but when I go to update them I get the following error:


Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/editrecord.asp, line 57


My code is:

<FONT face="veranda" color=black>

<Form method="post" action="editrecord.asp?Processed=1" 
      id="editrecord" name="editrecord">

<%

Dim dbPubs
Dim rsAuthors
Dim strConn
Dim sql
Dim strFile

Dim fPerformUpdate
Dim strUpdated

Set cnnPubs = Server.CreateObject("ADODB.Connection")
Set rsAuthors = Server.CreateObject("ADODB.Recordset")

strConn = "Provider=SQLOLEDB.1;Initial Catalog=pubs;" 
strConn = strConn & "User ID=sa;Password=;Data Source=c1082797-a"

strFile = "Database.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & "Data Source=" & server.mappath(strFile)

cnnPubs.Open strConn

fPerformUpdate = False
If Request.QueryString("Processed")=1 Then fPerformUpdate = True

If fPerformUpdate = True Then

  Dim intCount
  intCount = 0
  
  Dim AuthorID
  Dim Name, Score, Phone
  Dim Address, City, State

  For each item in Request.Form
    If Left(item,3)="ID_" then
		  
      AuthorID =  Mid(Item,4)
      intCount=intCount+1

	  Name=Request.Form("Name_" & AuthorID)
	  Score=Request.Form("Score_" & AuthorID)
	  
	  Score=Replace(Score,"'","''")
	  	  
      sql = "UPDATE Tbl_Maintable SET"
      sql = sql & " au_Name='" & Name & "', "
      sql = sql & " au_Score='" & Score & "', "
      sql = sql & " WHERE RecordID='" & AuthorID & "'"
 
      cnnPubs.Execute sql
    End If
  Next
  
End IF

sql = "SELECT * FROM tbl_Maintable"
rsAuthors.Open sql, cnnPubs


If rsAuthors.BOF And rsAuthors.EOF Then
%> 
    <FONT size=3 color=red><STRONG>
    No Records Found For Authors<BR>
    <BR></STRONG></FONT>
<%
ELSE
%>
<TABLE border=1 width="80%">

<TR>
  <TD colspan=3 align="left">
    <input id="Save" 
    name="Save" 
    type="submit" 
    value="Save Changes">
  </TD>
</TR>

<TR>
  <TD>
  <FONT size=2 color=navy><STRONG>
  Author ID
  </STRONG></FONT></TD>
	  
  <TD>
  <FONT size=2 color=navy><STRONG>
  First Name
  </STRONG></FONT> </TD>
	  
  <TD>
  <FONT size=2 color=navy><STRONG>
  Last Name
  </STRONG></FONT></TD>
	  
</TR>
<%
  Do Until rsAuthors.EOF
%>
<TR>
  <TD><input type="text"  size=12 
             value="<%=rsAuthors("RecordID")%>"
             name="ID_<%=rsAuthors("RecordID")%>"
    readonly=true style="FONT-SIZE: 10">
  </TD>
  <TD>
    <input type="text" 
           value="<%=rsAuthors("Name")%>" 
           name="Name_<%=rsAuthors("RecordID")%>" size="20" >
  </TD>
  <TD><input type="text"  size=12
    value="<%=rsAuthors("Score")%>" 
    name="Score_<%=rsAuthors("RecordID")%>"
    style="FONT-SIZE: 10">
  </TD>
 </TR>		
<% 
	rsAuthors.MoveNext
  Loop
End If

Set cnnPubs = nothing
Set rsAuthors = nothing

%>

</TABLE>
</Form>

</Font>

 

[damber]

Just guessing here... ...

but your UPDATE statement has a syntax error..

to check what is actually being run, do a response.write - this will let you see what has really been executed. This is probably because you use the querystring variables directly in the SQL - if the name contains a ' char, you will have problems with the SQL syntax.

It is always important to parse user inputs and get rid of anything that may do unexpected things in your SQL... do a google search for "SQL Injection" and you'll get an idea of how risky not doing this is.

A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

try this:

 sql = "UPDATE Tbl_Maintable SET"
      sql = sql & " au_Name='" & Name & "', "
      sql = sql & " au_Score=" & Score & ", "
      sql = sql & " WHERE RecordID=" & AuthorID

also do a response.write on your sql statement

-DNG

 

[damber]

well spotted DNG- I had assumed all the fields were text - but it makes sense that the RecordID is a number and therefore doesn't need the quotes.

A smile is worth a thousand kind words. So smile, it's easy!

 

[Spyder1000]

thanks

 

[DotNetGnat]

so did it work for you?

-DNG