ASP Problem

[Ryan]

I don't know what is wrong here, but I could use some insight. I have a dsn setup called order_info but I havent figured out how to use it, this is the code i have so far, I keep getting the same error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.



Private Function Conn(bytType, strPath_V, strFile)

Dim strPath_L
strPath_L = Server.MapPath(strPath_V)

Select Case bytType
Case 1
Conn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strPath_L & "\" & strFile & ";" _
& "Mode=Read|Write;Persist Security Info = False"

End Select

End Function

'# connect to the db and add the record
strInsert = "INSERT INTO tbl_orders (First,Last,Company,Phone,Email,Address,City,State,Country,Zip,CardType,CardNumber,CardExp,SerialNum ,ViewedNum) VALUES (" & Chr(34) & strFirst & Chr(34) & ", " & Chr(34) & strLast & Chr(34) & ", " & Chr(34) & strCompany & Chr(34) & ", " & Chr(34) & strPhone & Chr(34) & ", " & Chr(34) & strEmail & Chr(34) & ", " & Chr(34) & strAddress & Chr(34) & ", " & Chr(34) & strCity & Chr(34) & ", " & Chr(34) & strState & Chr(34) & ", " & Chr(34) & strCountry & Chr(34) & ", " & Chr(34) & strZip & Chr(34) & ", " & Chr(34) & strCCType & Chr(34) & ", " & Chr(34) & strCCNum & Chr(34) & ", " & Chr(34) & strCCExp & Chr(34) & ", " & Chr(34) & "ssa" & Chr(34) & ", " & Chr(34) & "asd" & Chr(34) &")"
'strInsert = "INSERT INTO tbl_orders (First, Last, Company, Phone, Email, Address, City, State, Country, Zip, CardType, CardNumber, CardExp, SerialNum, ViewedNum) VALUES ('"strFirst & "' , '" & strLast & "' , '" & strCompany & "' , '" & Chr(34) & strPhone & Chr(34) & ", " & Chr(34) & strEmail & Chr(34) & ", " & Chr(34) & strAddress & Chr(34) & ", " & Chr(34) & strCity & Chr(34) & ", " & Chr(34) & strState & Chr(34) & ", " & Chr(34) & strCountry & Chr(34) & ", " & Chr(34) & strZip & Chr(34) & ", " & Chr(34) & strCCType & Chr(34) & ", " & Chr(34) & strCCNum & Chr(34) & ", " & Chr(34) & strCCExp & Chr(34) & ", " & Chr(34) & "ssa" & Chr(34) & ", " & Chr(34) & "asd" & Chr(34) &");"

strConnect = Conn(1, "\db\", "orders.mdb")
Set conn1 = Server.CreateObject ("ADODB.Connection")
conn1.Open strConnect

conn1.Execute (strInsert)

conn1.Close
Set conn1 = Nothing


Any help would be great.

try something like this(fyi, lose chr(34) in the sql string, that's probably part of what's screwing up the code):

strConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/db/orders.mdb")
set conData = Server.CreateObject("ADODB.Connection")
conData.ConnectionString = strConnectionString
conData.Open

strInsert = "INSERT INTO tbl_orders " & _
"(First,Last,Company,Phone,Email,Address,City,State, " & _
"Country,Zip,CardType,CardNumber,CardExp,SerialNum ,ViewedNum) VALUES " & _
"('" & strFirst & ", '" & strLast & "', '" & strCompany & "', " & _
"'" & strPhone & "', '" & strEmail & "', '" & strAddress & "', " & _
"'" & strCity & "', '" & strState & "', '" & strCountry & "', " & _
"'" & strZip & "', '" & strCCType & "', '" & strCCNum & "', " & )
"'" & strCCExp & "', 'ssa', 'asd')

conData.Execute strInsert
conData.Close
Set conData = Nothing

'does not check variables for "SQL Statement breakers" like extra single quotes
'before executing maybe place "Response.Write strInsert" in there and comment
' out the conData.execute statement so you see what you're trying to execute.
'I did this in a text editor so i may have missed a single quote somewhere, hopefully
'you get the general idea