PDA

View Full Version : SQL Syntax Error

shocka
04-11-2005, 09:04 AM
Hi I am having problems with my Sql in trying to add to an access Database

I have a horrible feeling it may be in the "" But I have looked and looked and cannot seem to find the issue ( Not that this mesns a lot I am new ) I was wondering if somebody could cast an eye over it and see if they can see the problem.

Thanks In advance

Shocka
<code>

<html>
<head>
</head>

<body>

<%
' Declaring variables
Dim Branch_Number, State, Requested_By, Description, Supplier_Name, List_Price, Invoice_Discount, Price_List_Date, UOM, Supplier_Part_Number, Multiple_Order_Quantity, Minimum_Order_Quantity, Reason, Projected_Sales, Alternative_Code, JDE_Code, Department, Group, Sub_Group, Brand, Style, Colour, Exclusive, Price_Break_QTY, Status, State_Availability, Date, data_source, con, sql_insert

' A Function to check if some field entered by user is empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


' Receiving values from Form
Branch_Number = ChkString(Request.Form("Branch_Number"))
State = ChkString(Request.Form("State"))
Requested_By = ChkString(Request.Form("Requested_By"))
Description = ChkString(Request.Form("Description"))
Supplier_Name = ChkString(Request.Form("Supplier_Name"))
List_Price = ChkString(Request.Form("List_Price"))
Invoice_Discount = ChkString(Request.Form("Invoice_Discount"))
Price_List_Date = ChkString(Request.Form("Price_List_Date"))
UOM = ChkString(Request.Form("UOM"))
Supplier_Part_Number = ChkString(Request.Form("Supplier_Part_Number"))
Multiple_Order_Quantity = ChkString(Request.Form("Multiple_Order_Quantity"))
Minimum_Order_Quantity = ChkString(Request.Form("Minimum_Order_Quantity"))
Reason = ChkString(Request.Form("Reason"))
Projected_Sales = ChkString(Request.Form("Projected_Sales"))
Alternative_Code = ChkString(Request.Form("Alternative_Code"))
JDE_Code = ChkString(Request.Form("JDE_Code"))
Department = ChkString(Request.Form("Department"))
Group = ChkString(Request.Form("Group"))
Sub_Group = ChkString(Request.Form("Sub_Group"))
Brand = ChkString(Request.Form("Brand"))
Style = ChkString(Request.Form("Style"))
Colour = ChkString(Request.Form("Colour"))
Exclusive = ChkString(Request.Form("Exclusive"))
Price_Break_QTY = ChkString(Request.Form("Price_Break_QTY"))
Status = ChkString(Request.Form("Status"))
State_Availability = ChkString(Request.Form("State_Availability"))
Date = ChkString(Request.Form("Date"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("NCRform.mdb")
sql_insert = "insert INTO Results (Branch_Number, State, Requested_By, Description, Supplier_Name, List_Price, Invoice_Discount, Price_List_Date, UOM, Supplier_Part_Number, Multiple_Order_Quantity, Minimum_Order_Quantity, Reason, Projected_Sales, Alternative_Code, JDE_Code, Department, Group, Sub_Group, Brand, Style, Colour, Exclusive, Price_Break_QTY, Status, State_Availability, Date) values ('" & _
Branch_Number & "', '" & State & "', '" & Requested_By & "', '" & Description & "', '" & Supplier_Name & "', '" & List_Price & "', '" & Invoice_Discount & "', '" & Price_List_Date & "', '" & UOM & "', '" & Supplier_Part_Number & "', '" & Multiple_Order_Quantity & "', '" & Minimum_Order_Quantity & "', '" & Reason & "', '" & Projected_Sales & "', '" & Alternative_Code & "', '" & JDE_Code & "', '" & Department & "', '" & Group & "', '" & Sub_Group & "', '" & Brand & "', '" & Style & "', '" & Colour & "', '" & Exclusive & "', '" & Price_Break_QTY & "', '" & Status & "', '" & State_Availability & "', '" & Date & "'')"
' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
Response.Write "All records were successfully entered into the database."
%>

</body>
</html>

</code>
Roelf
04-11-2005, 10:13 AM
sql_insert = "insert INTO Results (Branch_Number, State, Requested_By, Description, Supplier_Name, List_Price, Invoice_Discount, Price_List_Date, UOM, Supplier_Part_Number, Multiple_Order_Quantity, Minimum_Order_Quantity, Reason, Projected_Sales, Alternative_Code, JDE_Code, Department, Group, Sub_Group, Brand, Style, Colour, Exclusive, Price_Break_QTY, Status, State_Availability, Date) values ('" & _
Branch_Number & "', '" & State & "', '" & Requested_By & "', '" & Description & "', '" & Supplier_Name & "', '" & List_Price & "', '" & Invoice_Discount & "', '" & Price_List_Date & "', '" & UOM & "', '" & Supplier_Part_Number & "', '" & Multiple_Order_Quantity & "', '" & Minimum_Order_Quantity & "', '" & Reason & "', '" & Projected_Sales & "', '" & Alternative_Code & "', '" & JDE_Code & "', '" & Department & "', '" & Group & "', '" & Sub_Group & "', '" & Brand & "', '" & Style & "', '" & Colour & "', '" & Exclusive & "', '" & Price_Break_QTY & "', '" & Status & "', '" & State_Availability & "', '" & Date & "'')"

your sql has one single quote too many after the date parameter (colored red in above code)
shocka
04-11-2005, 12:45 PM
Hi Roelf and thank you for responding to my "Crisis" :)
I took your advice and removed the offending ' but unfortunately it still shows the following error message

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/form1_ac.asp, line 55
Here is the updated code

<html>
<head>
</head>

<body>

<%
' Declaring variables
Dim Branch_Number, State, Requested_By, Description, Supplier_Name, List_Price, Invoice_Discount, Price_List_Date, UOM, Supplier_Part_Number, Multiple_Order_Quantity, Minimum_Order_Quantity, Reason, Projected_Sales, Alternative_Code, JDE_Code, Department, Group, Sub_Group, Brand, Style, Colour, Exclusive, Price_Break_QTY, Status, State_Availability, Date, data_source, con, sql_insert

' A Function to check if some field entered by user is empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''")
End Function


' Receiving values from Form
Branch_Number = ChkString(Request.Form("Branch_Number"))
State = ChkString(Request.Form("State"))
Requested_By = ChkString(Request.Form("Requested_By"))
Description = ChkString(Request.Form("Description"))
Supplier_Name = ChkString(Request.Form("Supplier_Name"))
List_Price = ChkString(Request.Form("List_Price"))
Invoice_Discount = ChkString(Request.Form("Invoice_Discount"))
Price_List_Date = ChkString(Request.Form("Price_List_Date"))
UOM = ChkString(Request.Form("UOM"))
Supplier_Part_Number = ChkString(Request.Form("Supplier_Part_Number"))
Multiple_Order_Quantity = ChkString(Request.Form("Multiple_Order_Quantity"))
Minimum_Order_Quantity = ChkString(Request.Form("Minimum_Order_Quantity"))
Reason = ChkString(Request.Form("Reason"))
Projected_Sales = ChkString(Request.Form("Projected_Sales"))
Alternative_Code = ChkString(Request.Form("Alternative_Code"))
JDE_Code = ChkString(Request.Form("JDE_Code"))
Department = ChkString(Request.Form("Department"))
Group = ChkString(Request.Form("Group"))
Sub_Group = ChkString(Request.Form("Sub_Group"))
Brand = ChkString(Request.Form("Brand"))
Style = ChkString(Request.Form("Style"))
Colour = ChkString(Request.Form("Colour"))
Exclusive = ChkString(Request.Form("Exclusive"))
Price_Break_QTY = ChkString(Request.Form("Price_Break_QTY"))
Status = ChkString(Request.Form("Status"))
State_Availability = ChkString(Request.Form("State_Availability"))
Date = ChkString(Request.Form("Date"))

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("NCRform.mdb")
sql_insert = "INSERT INTO Results (Branch_Number, State, Requested_By, Description, Supplier_Name, List_Price, Invoice_Discount, Price_List_Date, UOM, Supplier_Part_Number, Multiple_Order_Quantity, Minimum_Order_Quantity, Reason, Projected_Sales, Alternative_Code, JDE_Code, Department, Group, Sub_Group, Brand, Style, Colour, Exclusive, Price_Break_QTY, Status, State_Availability, Date) values ('" & Branch_Number & "', '" & State & "', '" & Requested_By & "', '" & Description & "', '" & Supplier_Name & "', '" & List_Price & "', '" & Invoice_Discount & "', '" & Price_List_Date & "', '" & UOM & "', '" & Supplier_Part_Number & "', '" & Multiple_Order_Quantity & "', '" & Minimum_Order_Quantity & "', '" & Reason & "', '" & Projected_Sales & "', '" & Alternative_Code & "', '" & JDE_Code & "', '" & Department & "', '" & Group & "', '" & Sub_Group & "', '" & Brand & "', '" & Style & "', '" & Colour & "', '" & Exclusive & "', '" & Price_Break_QTY & "', '" & Status & "', '" & State_Availability & "', '" & Date & " ')"
' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
Response.Write "All records were successfully entered into the database."
%>



</body>
</html>

Any more thoughts ???

Cheers Shocka
fractalvibes
04-11-2005, 03:11 PM
response.write sql_insert
response.end

immediately after your sql_insert = .... statement.

Format that mess into something readable and see what you have. Probably a missing quote, quoted numerica data or unquoted char data or missing values...

fv
shocka
04-12-2005, 04:41 AM
Just to update where I am at.

It seemed that I was using a couple of reserved words as my variables in the "Group" and "Date" I changed these and whola !!! It worked :) :)

:thumbsup: To all that helped you guys set me on the right path.


Thanks again

Shocka