Syntax error in INSERT INTO statement

Stephan · July 12th, 2004, 10:59 AM

Hi,

I am currently working on chapter 16. I have created the database and the relevant asp pages. However, when i submit my name, address and telephone number in the AddOrderDetails form i get the following error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/test_database/AddOrderDetails.asp, line 41

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)

Page:
POST 94 bytes to /test_database/AddOrderDetails.asp

POST Data:
txtStockId=&txtCustomerName=test&txtCustomerAddres s=test&txtTelNumber=23232&submit1=Send+Order

Time:
12 July 2004, 16:54:22

I have double checked the spelling of each field name and they are all correct. Any Ideas?

bmains · July 12th, 2004, 03:04 PM

Hello,
[
That's a SQL error, so I will have to see the statement to make a suggestion. In addition, if you do a:

Response.Write(strSQL)
Response.End

before you execute it, you can see what the error may be in the SQL.

Brian

terry_s · July 13th, 2004, 03:14 AM

Hi stephan here is some code that i have been working on works fine for me.
hope it helps !javascript:insertsmilie(';)')
Wink ;)

<%@LANGUAGE="JAVASCRIPT"%>
<%
// getting values from previous form
var sn,fn,surn,a1,a2,a3,ext,mobile,email,dob;
sn = Request.Form("staffNumber");
fn = Request.Form("firstname");
surn = Request.Form("surname");
a1 = Request.Form("address1");
a2 = Request.Form("address2");
a3 = Request.Form("address3");
ext = Request.Form("extn");
mobile = Request.Form("mobile");
email = Request.Form("email");
dob = Request.Form("day1") +"/"+Request.Form("month1")+"/"+Request.Form("year1");

adoConnection = Server.CreateObject("ADODB.Connection");
adoConnection.Open("DSN=input");

var SQL = "INSERT INTO tblStaff (staffID,firstname,surname,DateOfBirth,address1,ad dress2,address3,extn,mobile,email)";
SQL = SQL + "VALUES("+sn+",'"+fn+"','"+surn+"','"+dob+"','"+a1 +"','"+a2+"','"+a3+"',"+ext+","+mobile+",'"+email+ "')";

adoConnection.Execute(SQL);
adoConnection.Close();
adoConnection = null;
Response.Redirect("part1done.asp");

%>;););)

qazi_nomi · July 13th, 2004, 04:54 AM

Dear Stephan can you post the code here. And one thing that you should post your problem on right topic

Numan
--------------------------------------------------
Love is the most beautiful thing of this world. So do this !

Stephan · July 13th, 2004, 07:39 AM

Here is the code

<%@ language = JavaScript%>
<html>
<head>
</head>
<body>
<%
    var month = new Array("Jan", "Feb", "Mar", "Apr", "May", "Jun",
        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
    var nowDate = new Date();
    var nowDate = nowDate.getDate() + " " + month[nowDate.getMonth()] + " " + nowDate.getFullYear();
    var orderNo;

    var adoConnection = Server.CreateObject("ADODB.Connection");
    adoConnection.Open("DSN=ComBitsCoDB");
    var adoRecordset;

    var mySQL = "INSERT INTO CustomerOrder " + "(CustomerName, CustomerAddress, TelNumber, OrderDate)";
    mySQL = mySQL + " VALUES ('" + Request.Form("txtCustomerName") + "','";
    mySQL = mySQL + Request.Form("txtCustomerAddress") + "','";
    mySQL = mySQL + Request.Form("txtTelNumber") + "','";
    mySQL = mySQL + nowDate + "')";

    adoConnection.Execute(mySQL);

    mySQL = "SELECT Max(OrderNo) AS MaxOrderNo FROM CustomerOrder WHERE ";
    mySQL = mySQL + " OrderDate = #" + nowDate + "# AND ";
    mySQL = mySQL + " CustomerName = '" + Request.Form("txtCustomerName") + "'";

    adoRecordset = adoConnection.Execute(mySQL);

    orderNo = adoRecordset("MaxOrderNo").Value;

    adoRecordset.Close();
    adoRecordset = null;

    var mySQL = "INSERT INTO OrderItem (OrderNo, StockId, QtyOrdered)";
    mySQL = mySQL + " VALUES (" + orderNo + ",";
    mySQL = mySQL + Request.Form("txtStockId") + ",";
    mySQL = mySQL + "1)";

    adoConnection.Execute(mySQL);

    adoConnection.Close();
    adoConnection = null;

    Response.Write("<h2><center>Your order was completed successfully" + "</center></h2>");
    %>
</body>
</html>

om_prakash · July 13th, 2004, 09:17 AM

I think the problem is with date in the following statement:

mySQL = mySQL + " VALUES ('" + Request.Form("txtCustomerName") + "','";
    mySQL = mySQL + Request.Form("txtCustomerAddress") + "','";
    mySQL = mySQL + Request.Form("txtTelNumber") + "','";
    mySQL = mySQL + nowDate + "')";

Om Prakash

Stephan · July 13th, 2004, 09:24 AM

I beleive the error is in the following section, well i think that's where the error message is pointing to:

r mySQL = "INSERT INTO OrderItem (OrderNo, StockId, QtyOrdered)";
    mySQL = mySQL + " VALUES (" + orderNo + ",";
    mySQL = mySQL + Request.Form("txtStockId") + ",";
    mySQL = mySQL + "1)";

    adoConnection.Execute(mySQL);

Stephan · July 13th, 2004, 09:25 AM

But i have no idea what it could be

Snib · July 13th, 2004, 09:56 AM

Try printing out what mySQL is, and comment out the line that executes it.

Also, could it be that you didn't quote the '1' at the end of the statement?

HTH,

Snib

<><

Stephan · July 13th, 2004, 10:41 AM

The qoutes didn't change anything. Here is the print out:

INSERT INTO OrderItem (OrderNo, StockId, QtyOrdered) VALUES (18,,1)