SQL Syntax Error (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

Kage -> SQL Syntax Error (12/22/2003 17:05:52)

I'm trying to use a form to update records in a database and so far I've gotten it all to work up until now and I've got no clue where I went wrong in the SQL. I get this message when it runs:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/ErrorTracking/update.asp, line 106


line 106 is just where the SQL executes so that does me little to no good.



Kage -> RE: SQL Syntax Error (12/22/2003 17:07:14)

The code for the SQL stuff is:



<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Update Error Tracking</title>
</head>

<html>
<body>

<br><br><br>
Updating Data...
<br><br><br>

<%
' ' ' ' declare all the variables
DIM SQL, objRS, objConn, strConn, strID
DIM strDate, strPickticket, strClaimNumber, strPartNumber, strReason, strPickedBy, strDatePicked, strWeighedBy, strDateWeighed, strFindings, strInHouseOrClaim_InHouse
DIM strInHouseOrClaim_Claim, strQtyCustomerGot, strActualQty, strPartNumIfReceivedWrong, strAmtOverShort, strLocation, strQtyShippedReceived, strComments
DIM strCustomer, strPartDescription, strScaleCounted_Yes, strValue, strOrderDropTime, strPickTime, strPackTime, strShipVia, strExportOrder_Yes, strExportOrder_No, strLinesOnPick

' ' ' ' pull all the data from the previous page
strDate = Request.Form("Date")
strPickticket = Replace(Request.Form("Pickticket"), "'", "''")
strClaimNumber = Replace(Request.Form("ClaimNumber"), "'", "''")
strPartNumber = Replace(Request.Form("PartNumber"), "'", "''")
strReason = Replace(Request.Form("Reason"), "'", "''")
strPickedBy = Replace(Request.Form("PickedBy"), "'", "''")
strDatePicked = Replace(Request.Form("DatePicked"), "'", "''")
strWeighedBy = Replace(Request.Form("WeighedBy"), "'", "''")
strDateWeighed = Replace(Request.Form("DateWeighed"), "'", "''")
strFindings = Replace(Request.Form("Findings"), "'", "''")
strInHouseOrClaim_InHouse = Replace(Request.Form("InHouseOrClaim_In-House"), "'", "''")
strInHouseOrClaim_Claim = Replace(Request.Form("InHouseOrClaim_Claim"), "'", "''")
strQtyCustomerGot = Replace(Request.Form("QtyCustomerGot"), "'", "''")
strActualQty = Replace(Request.Form("ActualQty"), "'", "''")
strPartNumIfReceivedWrong = Replace(Request.Form("PartNumIfReceivedWrong"), "'", "''")
strAmtOverShort = Replace(Request.Form("AmtOverShort"), "'", "''")
strLocation = Replace(Request.Form("Location"), "'", "''")
strQtyShippedReceived = Replace(Request.Form("QtyShippedReceived"), "'", "''")
strComments = Replace(Request.Form("Comments"), "'", "''")
strCustomer = Replace(Request.Form("Customer"), "'", "''")
strPartDescription = Replace(Request.Form("PartDescription"), "'", "''")
strScaleCounted_Yes = Replace(Request.Form("ScaleCounted_Yes"), "'", "''")
strValue = Replace(Request.Form("Value"), "'", "''")
strOrderDropTime = Replace(Request.Form("OrderDropTime"), "'", "''")
strPickTime = Replace(Request.Form("PickTime"), "'", "''")
strPackTime = Replace(Request.Form("PackTime"), "'", "''")
strShipVia = Replace(Request.Form("ShipVia"), "'", "''")
strExportOrder_Yes = Replace(Request.Form("ExportOrder_Yes"), "'", "''")
strExportOrder_No = Replace(Request.Form("ExportOrder_No"), "'", "''")
strLinesOnPick = Replace(Request.Form("LinesOnPick"), "'", "''")


' ' ' ' create sql statement
SQL = "UPDATE ""Results"" SET "
SQL = SQL & ", Date = #" & strDate & "#"
SQL = SQL & ", Pickticket = '" & strPickticket & "'"
SQL = SQL & ", ClaimNumber = #" & strClaimNumber & "#"
SQL = SQL & ", PartNumber = '" & strPartNumber & "'"
SQL = SQL & ", Reason = '" & strReason & "'"
SQL = SQL & ", PickedBy = '" & strPickedBy & "'"
SQL = SQL & ", DatePicked = #" & strDatePicked & "#"
SQL = SQL & ", WeighedBy = '" & strWeighedBy & "'"
SQL = SQL & ", DateWeighed = #" & strDateWeighed & "#"
SQL = SQL & ", Findings = '" & strFindings & "'"
SQL = SQL & ", InHouseOrClaim_In-House = '" & strInHouseOrClaim_In-House & "'"
SQL = SQL & ", InHouseOrClaim_Claim = '" & strInHouseOrClaim_Claim & "'"
SQL = SQL & ", QtyCustomerGot = '" & strQtyCustomerGot & "'"
SQL = SQL & ", PartNumIfReceivedWrong = '" & strPartNumIfReceivedWrong & "'"
SQL = SQL & ", AmtOverShort = '" & strAmtOverShort & "'"
SQL = SQL & ", Location = '" & strLocation & "'"
SQL = SQL & ", QtyShippedReceived = '" & strQtyShippedReceived & "'"
SQL = SQL & ", Comments = '" & strComments & "'"
SQL = SQL & ", Customer = '" & strCustomer & "'"
SQL = SQL & ", PartDescription = '" & strPartDescription & "'"
SQL = SQL & ", ScaleCounted_Yes = '" & strScaleCounted_Yes & "'"
SQL = SQL & ", Value = '" & strValue & "'"
SQL = SQL & ", OrderDropTime = '" & strOrderDropTime & "'"
SQL = SQL & ", PickTime = '" & strPickTime & "'"
SQL = SQL & ", PackTime = '" & strPackTime & "'"
SQL = SQL & ", ShipVia = '" & strShipVia & "'"
SQL = SQL & ", ExportOrder_Yes = '" & strExportOrder_Yes & "'"
SQL = SQL & ", ExportOrder_No = '" & strExportOrder_No & "'"
SQL = SQL & ", LinesOnPick = '" & strLinesOnPick & "'"
SQL = SQL & ", DayOfWeek = '" & strDayOfWeek & "'"


' ' ' ' finish the sql statement
SQL = SQL & " WHERE PickTicket = " & strPickTicket & ""

' ' ' ' creates a connection object
Set objConn = Server.CreateObject("ADODB.Connection")

' ' ' ' identifies the database and tells the computer where to store the data
strConn = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("/fpdb/ErrorTracking.mdb")
' ' ' ' opens the database
objConn.Open strConn

' ' ' ' creates the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")

' ' ' ' executes the sql statement
Set objRS = objConn.Execute (SQL)

' ' ' ' clears the memory buffer
Set objRS = Nothing
Set objConn = Nothing
%>

<!-- BEGIN FORM -->
<form METHOD="POST" ACTION="findID2.asp" name="FrontPage_Form1">
</form>
<!-- END FORM -->

<!-- Javascript code that automatically forces the form to submit -->
<script language="javascript">
document.UpdateErrorTracking.submit();
</script>

</body>
</html>
</body>

</html>





any ideas? [sm=help.gif]



Kage -> RE: SQL Syntax Error (12/22/2003 17:53:26)

The Syntax Error turned out to be the strID variable up top.

it gives me another error now but I think I know how to fix it.



BeTheBall -> RE: SQL Syntax Error (12/22/2003 19:40:51)

This line appears incorrect:

SQL = SQL & ", ClaimNumber = #" & strClaimNumber & "#"

I doubt ClaimNumber is a date field so do not surround it with ##. If the field is numeric in Access, then you don't surround it with anything. It should just be:

SQL = SQL & ", ClaimNumber = " & strClaimNumber & ""

You should look at your table in Design view and determine which field are text, which are numeric and which are dates. Dates are surrounded by ##, text are surrounded by single (') quotes and numeric fields are not surrounded by anything. Seems to me in your previous questions, we determined that PickTicket was a text field which would require changing your WHERE clause to:

SQL = SQL & " WHERE PickTicket = '" & strPickTicket & "'"



Spooky -> RE: SQL Syntax Error (12/23/2003 8:44:59)

Also, column name 'date' is a reserved word, it should really be called something else or written as [date]



Kage -> RE: SQL Syntax Error (12/23/2003 11:47:13)

That didn't seem to help, I let frontpage build the database so everything is text [:@]
I guess I'm going to have to start over and change the field types then work around it. This crap is tedious.



BeTheBall -> RE: SQL Syntax Error (12/23/2003 12:49:59)

Problem: You cannot have PickTicket in the Update SET and in the WHERE clause. I am guessing PickTicket does not change, right? If not, get it out of the Update SET.

If indeed all the fields in the db are text, then your SQL should look like this:

' ' ' ' create sql statement
SQL = "UPDATE ""Results"" SET "
SQL = SQL & ", [Date] = '" & strDate & "'"
SQL = SQL & ", ClaimNumber = '" & strClaimNumber & "'"
SQL = SQL & ", PartNumber = '" & strPartNumber & "'"
SQL = SQL & ", Reason = '" & strReason & "'"
SQL = SQL & ", PickedBy = '" & strPickedBy & "'"
SQL = SQL & ", DatePicked = '" & strDatePicked & "'"
SQL = SQL & ", WeighedBy = '" & strWeighedBy & "'"
SQL = SQL & ", DateWeighed = '" & strDateWeighed & "'"
SQL = SQL & ", Findings = '" & strFindings & "'"
SQL = SQL & ", InHouseOrClaim_In-House = '" & strInHouseOrClaim_In-House & "'"
SQL = SQL & ", InHouseOrClaim_Claim = '" & strInHouseOrClaim_Claim & "'"
SQL = SQL & ", QtyCustomerGot = '" & strQtyCustomerGot & "'"
SQL = SQL & ", PartNumIfReceivedWrong = '" & strPartNumIfReceivedWrong & "'"
SQL = SQL & ", AmtOverShort = '" & strAmtOverShort & "'"
SQL = SQL & ", Location = '" & strLocation & "'"
SQL = SQL & ", QtyShippedReceived = '" & strQtyShippedReceived & "'"
SQL = SQL & ", Comments = '" & strComments & "'"
SQL = SQL & ", Customer = '" & strCustomer & "'"
SQL = SQL & ", PartDescription = '" & strPartDescription & "'"
SQL = SQL & ", ScaleCounted_Yes = '" & strScaleCounted_Yes & "'"
SQL = SQL & ", Value = '" & strValue & "'"
SQL = SQL & ", OrderDropTime = '" & strOrderDropTime & "'"
SQL = SQL & ", PickTime = '" & strPickTime & "'"
SQL = SQL & ", PackTime = '" & strPackTime & "'"
SQL = SQL & ", ShipVia = '" & strShipVia & "'"
SQL = SQL & ", ExportOrder_Yes = '" & strExportOrder_Yes & "'"
SQL = SQL & ", ExportOrder_No = '" & strExportOrder_No & "'"
SQL = SQL & ", LinesOnPick = '" & strLinesOnPick & "'"
SQL = SQL & ", DayOfWeek = '" & strDayOfWeek & "'"

And this line:

' ' ' ' finish the sql statement
SQL = SQL & " WHERE PickTicket = '" & strPickTicket & "'"



Spooky -> RE: SQL Syntax Error (12/23/2003 14:10:04)

If in doubt, can you post the actual error?



Kage -> RE: SQL Syntax Error (12/23/2003 16:18:37)

Thanks for the Date info spooky, i've changed that and I still seem to get the syntax error. I'm not sure why.



Kage -> RE: SQL Syntax Error (12/23/2003 16:35:38)

Let me try the Pickticket situation you suggested Duane. I'll get back with results in a second or two.



Spooky -> RE: SQL Syntax Error (12/23/2003 20:31:27)

Do this to trouble shoot the SQL string : 

SQL = SQL & " WHERE PickTicket = " & strPickTicket & "" 

response.write SQL 
response.end

' ' ' ' creates a connection object 
Set objConn = Server.CreateObject("ADODB.Connection")


Or paste the actual error :-)



Kage -> RE: SQL Syntax Error (12/23/2003 20:48:03)

LOL sp00ky I'll post it when I get back to work, I got caught up helping my floor crew with a 38,000 dollar part that had to ship out. Thanks for the help, I will see what I can do. Duane has been helping me outside of here. All of it helps. You are both much appreciated.

Merry christmas to the both of you.

Kage



BeTheBall -> RE: SQL Syntax Error (12/23/2003 20:48:42)

I looked at the latest version of your code (sent by email). Your SQL included two variables, strID and strDayofWeek that had not been defined. Also, there was a comma in the first line of your SQL string that should not be there. Here is a copy of the corrections I made that allowed the update to occur successfully on my site and, BTW, I was only able to find the errors after I followed the troubleshooting technique that Spooky mentioned in the above post.

strID = Request.Form("ID")
strDate = Request.Form("Date1")
strPickticket = Replace(Request.Form("Pickticket"), "'", "''")
strClaimNumber = Replace(Request.Form("ClaimNumber"), "'", "''")
strPartNumber = Replace(Request.Form("PartNumber"), "'", "''")
strReason = Replace(Request.Form("Reason"), "'", "''")
strPickedBy = Replace(Request.Form("PickedBy"), "'", "''")
strDatePicked = Replace(Request.Form("DatePicked"), "'", "''")
strWeighedBy = Replace(Request.Form("WeighedBy"), "'", "''")
strDateWeighed = Replace(Request.Form("DateWeighed"), "'", "''")
strFindings = Replace(Request.Form("Findings"), "'", "''")
strInHouseOrClaim_InHouse = Replace(Request.Form("InHouseOrClaim_In-House"), "'", "''")
strInHouseOrClaim_Claim = Replace(Request.Form("InHouseOrClaim_Claim"), "'", "''")
strQtyCustomerGot = Replace(Request.Form("QtyCustomerGot"), "'", "''")
strActualQty = Replace(Request.Form("ActualQty"), "'", "''")
strPartNumIfReceivedWrong = Replace(Request.Form("PartNumIfReceivedWrong"), "'", "''")
strAmtOverShort = Replace(Request.Form("AmtOverShort"), "'", "''")
strLocation = Replace(Request.Form("Location"), "'", "''")
strQtyShippedReceived = Replace(Request.Form("QtyShippedReceived"), "'", "''")
strComments = Replace(Request.Form("Comments"), "'", "''")
strCustomer = Replace(Request.Form("Customer"), "'", "''")
strPartDescription = Replace(Request.Form("PartDescription"), "'", "''")
strScaleCounted_Yes = Replace(Request.Form("ScaleCounted_Yes"), "'", "''")
strValue = Replace(Request.Form("Value"), "'", "''")
strOrderDropTime = Replace(Request.Form("OrderDropTime"), "'", "''")
strPickTime = Replace(Request.Form("PickTime"), "'", "''")
strPackTime = Replace(Request.Form("PackTime"), "'", "''")
strShipVia = Replace(Request.Form("ShipVia"), "'", "''")
strExportOrder_Yes = Replace(Request.Form("ExportOrder_Yes"), "'", "''")
strExportOrder_No = Replace(Request.Form("ExportOrder_No"), "'", "''")
strLinesOnPick = Replace(Request.Form("LinesOnPick"), "'", "''")
strDayofWeek = Replace(Request.Form("DayofWeek"), "'", "''")

' ' ' ' create sql statement
SQL = "UPDATE Results SET "
SQL = SQL & " Date1 = #" & strDate & "#"
SQL = SQL & ", Pickticket = '" & strPickticket & "'"
SQL = SQL & ", ClaimNumber = '" & strClaimNumber & "'"
SQL = SQL & ", PartNumber = '" & strPartNumber & "'"
SQL = SQL & ", Reason = '" & strReason & "'"
SQL = SQL & ", PickedBy = '" & strPickedBy & "'"
SQL = SQL & ", DatePicked = #" & strDatePicked & "#"
SQL = SQL & ", WeighedBy = '" & strWeighedBy & "'"
SQL = SQL & ", DateWeighed = #" & strDateWeighed & "#"
SQL = SQL & ", Findings = '" & strFindings & "'"
SQL = SQL & ", InHouseOrClaim_In-House = '" & strInHouseOrClaim_InHouse & "'"
SQL = SQL & ", InHouseOrClaim_Claim = '" & strInHouseOrClaim_Claim & "'"
SQL = SQL & ", QtyCustomerGot = '" & strQtyCustomerGot & "'"
SQL = SQL & ", PartNumIfReceivedWrong = '" & strPartNumIfReceivedWrong & "'"
SQL = SQL & ", AmtOverShort = '" & strAmtOverShort & "'"
SQL = SQL & ", Location = '" & strLocation & "'"
SQL = SQL & ", QtyShippedReceived = '" & strQtyShippedReceived & "'"
SQL = SQL & ", Comments = '" & strComments & "'"
SQL = SQL & ", Customer = '" & strCustomer & "'"
SQL = SQL & ", PartDescription = '" & strPartDescription & "'"
SQL = SQL & ", ScaleCounted_Yes = '" & strScaleCounted_Yes & "'"
SQL = SQL & ", Value = '" & strValue & "'"
SQL = SQL & ", OrderDropTime = '" & strOrderDropTime & "'"
SQL = SQL & ", PickTime = '" & strPickTime & "'"
SQL = SQL & ", PackTime = '" & strPackTime & "'"
SQL = SQL & ", ShipVia = '" & strShipVia & "'"
SQL = SQL & ", ExportOrder_Yes = '" & strExportOrder_Yes & "'"
SQL = SQL & ", ExportOrder_No = '" & strExportOrder_No & "'"
SQL = SQL & ", LinesOnPick = '" & strLinesOnPick & "'"
SQL = SQL & ", DayOfWeek = '" & strDayOfWeek & "'"


' ' ' ' finish the sql statement
SQL = SQL & " WHERE ID = " & strID & ""



Spooky -> RE: SQL Syntax Error (12/23/2003 22:38:15)

Its christmas, you arent supposed to be working ;-)



Kage -> RE: SQL Syntax Error (12/24/2003 13:38:19)

LOL
I know I'm not supposed to be in fact I'm out of here at 2 today.

I never got a chance to try spooky's advice due to things that came up last night which happened as I was sending it to you. It's the end of the year rush around here and our customer is pushing production up so things have been a little nuts.

Thanks for all your help guys, and have a Great Christmas.



Kage -> RE: SQL Syntax Error (12/24/2003 13:44:40)

I used the update.asp you sent me duane and it came up with:

Updating Data...



Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/ErrorTracking/update.asp, line 108



BeTheBall -> RE: SQL Syntax Error (12/24/2003 13:55:03)

Larry, I am at a bit of a disadvantage because I do not have MS Access at home, so I can't see what the data types are for the various fields. If you have time, please open your table in design view and then post a list of every field and its data type, i.e., text, date or numeric.



Kage -> RE: SQL Syntax Error (12/24/2003 14:02:14)

Everything is text save for the 3 date fields and they are Date/Time.

Findings and Comments are memo fields but shouldn't they just be text?

The db i sent last night is exactly what this is pointed to here if that's what you used to get it to work.



Kage -> RE: SQL Syntax Error (12/24/2003 14:04:24)

ID is autonumber btw.



Spooky -> RE: SQL Syntax Error (12/24/2003 14:15:15)

value is also a reserved word - so modify that [value]
Place all your memo feilds as the last to be updated.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q209187&ID=KB;EN-US;Q209187



BeTheBall -> RE: SQL Syntax Error (12/24/2003 14:26:01)

Ahhh. Forgot about the memo field rule. Spooky, it's Christmas! Go away until tomorrow! Of course, it will be yesterday here. [&:]



Kage -> RE: SQL Syntax Error (12/24/2003 14:31:35)

They celebrate christmas in middle earth?



Kage -> RE: SQL Syntax Error (12/24/2003 14:33:57)

I've changed value:

SQL = SQL & ", [Value] = '" & strValue & "'"

and I've moved my memo fields to the bottom of the line. Let me give this a shot.



Kage -> RE: SQL Syntax Error (12/24/2003 14:38:46)

Updating Data...



Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/ErrorTracking/update.asp, line 106



The difference in line # is i've taken out some extra spaces. Line 106 is still:

Set objRS = objConn.Execute (SQL)



Spooky -> RE: SQL Syntax Error (12/24/2003 14:40:16)

What is the full SQL statement when written with :

response.write SQL
response.end

??



Spooky -> RE: SQL Syntax Error (12/24/2003 14:41:14)

[InHouseOrClaim_In-House]



Kage -> RE: SQL Syntax Error (12/24/2003 14:44:01)

' ' ' ' finish the sql statement
SQL = SQL & " WHERE ID = " & strID & ""

' ' ' ' creates a connection object
Set objConn = Server.CreateObject("ADODB.Connection")

' ' ' ' identifies the database and tells the computer where to store the data
strConn = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("/fpdb/ErrorTracking.mdb")
' ' ' ' opens the database
objConn.Open strConn

' ' ' ' creates the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")

' ' ' ' executes the sql statement
Set objRS = objConn.Execute (SQL)

' ' ' ' clears the memory buffer
Set objRS = Nothing
Set objConn = Nothing
%>



Spooky -> RE: SQL Syntax Error (12/24/2003 14:47:40)

If the last suggestion doesnt work (dont use a minus sign!)
then what I mean, is modify the asp code like so : 

' ' ' ' executes the sql statement 

response.write SQL 
response.end

Set objRS = objConn.Execute (SQL)


Submit a record as normal, but youll only see the outputted SQL written to the page.
It helps with trouble shooting



Kage -> RE: SQL Syntax Error (12/24/2003 14:49:52)

ooooh..sweet. thanks for that man, that'll help.

I think it was the [ ] around those two variables that straightened it out. Now the results tell me that that anything left blank cannot be a zero length string. Do i need to write If statements to sort that out or what?



Spooky -> RE: SQL Syntax Error (12/24/2003 15:03:57)

No - thats done in the database design.
Ensure all fields can be zero length or null



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1088867