|
| |
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
 |
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
Posts: 129
Joined: 11/20/2003
Status: offline
|
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? 
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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 & "'"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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 & "'"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 129
Joined: 11/20/2003
Status: offline
|
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.
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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 & ""
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 129
Joined: 11/20/2003
Status: offline
|
RE: SQL Syntax Error - 12/24/2003 14:04:24
ID is autonumber btw.
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
RE: SQL Syntax Error - 12/24/2003 14:31:35
They celebrate christmas in middle earth?
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
Posts: 129
Joined: 11/20/2003
Status: offline
|
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)
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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
%>
| |
|
|
|
Kage
Posts: 129
Joined: 11/20/2003
Status: offline
|
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?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
ky 
