>Hi, everyone.
>Would anyone out there be able to tell me why the query below causes a "Syntax error in INSERT INTO statement" when used in VB5 with ADO 2.1? The field types are as follows:
>
>quarter - long
>year - long
>sequence - long
>run_desc - text
>run_comments - memo
>run_date_time - date/time
>status - text
>
>Here's how the code looks:
>
>Dim sSQL As String
>Dim qtr As String, yr As String, desc As String, comments As String
>qtr = CStr(frmMain.cboQuarter.ListIndex + 1)
>yr = CStr(frmMain.cboYear.List(frmMain.cboYear.ListIndex))
>desc = frmMain.txtDesc.Text
>comments = frmMain.txtComments.Text
>sSQL = "INSERT INTO run (quarter,year,sequence,run_desc,run_comments,run_date_time,status) VALUES (" & qtr & "," & yr & ",0,'" & desc & "','" & comments & "','" & Date & "','New')"
>cn.Execute sSQL ' ERROR HAPPENS HERE!
>
>Here's what sSQL looks like at run-time:
>
>INSERT INTO status(quarter,year,sequence,run_desc,run_comments,run_date_time,status) VALUES (1,1952,0,'test','test','09/14/1999','New')
>
>I've tried putting # around the date/time value and also putting nothing around it and the same error still happens.
>
>Any help would be greatly appreciated. Thanks in advance.
>
>Andrew
Try creating your Quarter and Year variables as LONG and then use the conversion function "CLNG(value)". I've had problems inserting this way, and this solution worked for me.
Hugo
"My get up and go must've got up and went"
-Steve Tyler, Aerosmith