Syntax Error on INSERT Statement (ASP Related)

[Thimo]

<%
Dim whereClause, rs, sql
Dim i, myarray, temparray, x
Dim ItemName, ItemCode, RQty, Price, StaffName, TotalPrice, TPrice, TPrice2
Dim TimeDate

For Each Item in Request.Form("add")
If whereClause <> "" then
whereClause = whereClause & "OR ItemID=" & item
Else
whereClause = "ItemID=" & item
End If
Next

strSQL = "SELECT * FROM Item WHERE " & whereClause
set rs = objConn.Execute(strSQL)

myarray = Request.Form("RQty")
temparray = Split(myarray,",")

Response.Write "<center><h2>Items you Ordered</h2><center>"

%>
<form>
<table border="1" align="center">
<tr>
<th height="25">Item Code</th>
<th>ItemName</th>
<th>Price</th>
<th>Request Quantity</th>
</tr>
<%TPrice = 0%>
<%Do While Not rs.EOF%>
<%For x = 0 to UBound(temparray)%>
<tr align=center>
<td><%=rs("ItemCode")%></td>
<td align=left><%=rs("ItemName")%></td>
<td>$<%=FormatNumber(rs("Price"))%></td>
<td><%Response.Write(temparray(x) & "<br>")%></td>
</tr>
<%
StaffName = Session("PersonName")
ItemCode = rs("ItemCode")
ItemName = rs("ItemName")
Price = rs("Price")
RQty = temparray(x)
TimeDate = Now
TPrice = (RQty * Price)
TPrice2 = TPrice2 + TPrice
%>
<%sql = " INSERT INTO Staff (ItemName, ItemCode, Quantity, Price, StaffName) VALUES ('" & ItemName & "' , '" & ItemCode & "' , '" & RQty & "' , '" & Price & "' , '" & StaffName & "' )" %>

<%objConn.Execute sql%>
<%
dim sql2
sql2 = "INSERT INTO Staff (TimeStamp) VALUES ('" & TimeDate & "')" %>
<%objConn.Execute sql2%> -------> Error on this line
<%rs.MoveNext%>
<%Next%>
<%loop%>
</table>
<%TotalPrice = TPrice2%>
<%=Order%>

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.

/asp/FinishCart.asp, line 68

hi, its stated that my insert statement has error, but in my other pages i am able to insert the date but not using insert is Recordset.Update

i am able to display the Date <%=Order%> but not able to insert into the Table named Staff...

please help and regards

[glenngv]

seems like you're using Access.
use # instead of ' in surrounding the date field

why don't you just combine the two? you're just inserting into the same table

<%sql = " INSERT INTO Staff (ItemName, ItemCode, Quantity, Price, StaffName) VALUES ('" & ItemName & "' , '" & ItemCode & "' , '" & RQty & "' , '" & Price & "' , '" & StaffName & "' )" %>

<%objConn.Execute sql%>
<%
dim sql2
sql2 = "INSERT INTO Staff (TimeStamp) VALUES ('" & TimeDate & "')" %>
<%objConn.Execute sql2%>


and you have a loop like this:

<%
Do While Not rs.EOF
For x = 0 to UBound(temparray)

'codes here

rs.MoveNext
Next
loop
%>


your rs.MoveNext is inside the For loop.
are you sure this is what you want to do?
and another advice, to make the code clearer, you can enclose blocks of code in a single pair of <%%> not on every line.

[Thimo]

i tried to change ' to # but still synatx error.....

i did remember for inserting Date you need the # ...but no sure how to type the exact code...

[rcreyes]

This error usually occurs when using reserved word, if I am not mistaken, TimeDate is a reserved ODBC word. Try using other variable name, and try it again.

Hope this helps.....

Thanks,
Ray

[Thimo]

i still canot get the thing to work........ the syntaz error is killing me......

for variables that i wish to insert the syntaz is '" & Price &"'

i did that for the variable that has the Time value..... is there anything wrong?

please advice

[glenngv]

response.write the sql statement before executing it then post the output here

[Thimo]

Items you Ordered
Item Code ItemName Price Request Quantity
A002 Adhesive, Post-It Pad, 3 $131.20 5

11/13/02 2:37:28 PM ----> TimeDate
Total Price: $656

i am able to output the statement....but cannto insert...

i put the format for that field in Access is Date/Time

[glenngv]

i mean post the SQL statement, that is the Insert statement

[Thimo]

<%
dim sql2
sql2 = "INSERT INTO Staff (TimeStamp) VALUES ('" & TimeDate & "')"
%>
<%objConn.Execute sql2%>

[glenngv]

you didn't get me, i mean execute this:

<%
dim sql2
sql2 = "INSERT INTO Staff (TimeStamp) VALUES ('" & TimeDate & "')"
response.write(sql2 & "<br>")
%>
<%objConn.Execute sql2%>

and then post the generated output here

[Thimo]

so sorry glenn, i went to play soccer juz now




INSERT INTO Staff (TimeStamp) VALUES ('11/13/2002 9:01:39 PM')


INSERT INTO Staff (TimeStamp) VALUES ('11/13/2002 9:01:39 PM')


INSERT INTO Staff (TimeStamp) VALUES ('11/13/2002 9:01:39 PM')


Item Code ItemName Price RequestedQuantity
aaab a1234567 $11.11 1
A100 Pen $100.11 2
A047 Tape, Scotch,Large (18mm x 45m) $100.00 4

11/13/2002 9:01:39 PM
this is the output, the time is insert each time for each item its already inside the Do while loop....

[rcreyes]

I basically created a TABLE called STAFF with one column name TimeStaff on a MS-SQL 7.0 database, and was able to do the INSERTed records with no problem. Here's the script I used to insert the record:
<%
Dim objSPCmd
Dim TimeDate
Dim Conn
Dim DSN
Dim i

DSN = "Driver={SQL Server};Server=NY_NYTEST;Database=TEST;UID=sa;PWD=sa"

Set Conn = CreateObject("ADODB.Connection")
Conn.Open DSN
Conn.CommandTimeout = 120

Set objSPCmd = CreateObject("ADODB.Command")
Set objSPCmd.ActiveConnection = Conn

'-- insert two records
' one by assigning the value to a variable

TimeDate = "11/13/2002 9:01:39 PM"
strCmd = "INSERT INTO Staff(TimeStamp) Values (" & Chr(39) & TimeDate & CHr(39) & ")"
objSPCmd.CommandText = strCmd
objSPCmd.Execute

' record two use hardcoded value
strCmd = "INSERT INTO Staff(TimeStamp) Values (" & Chr(39) & "11/13/2002 10:01:12 AM" & Chr(39) & ")"

objSPCmd.CommandText = strCmd
objSPCmd.Execute


sSQL = "Select * from Staff"

Set RS = Conn.Execute (sSQL)
i = 0
Do While Not RS.EOF
i = i +1
With Response

.Write "Record: " & i & "Value is: " & RS(0)
.Write "<BR>" & vbCrlf


End With
RS.MoveNext
Loop

Set RS = Nothing
Set sSQL = Nothing
Set objSPCmd = Nothing
Set Conn = Nothing

%>


The result is the following:

Record: 1Value is: 11/13/02 9:01:39 PM
Record: 2Value is: 11/13/02 10:01:12 AM