insert syntax error

  • Thread starter Thread starter tim
  • Start date Start date
T

tim

why am I getting a syntax error on the insert statement

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

If rs.RecordCount > 0 Then
i = 1
rs.MoveFirst
Do While i <= 12
Do Until rs.EOF

con.Execute "insert into prereport_abend (month,
system, feq, box, count_of, total_jobs, job_name,
row_order ) " _
& "values( i , rs!system , rs!feq, rs!box,
0 , rs!total_jobs, 'Ratio' , 9 ) ;"

rs.MoveNext
Loop
i = i + 1
Loop
rs.MoveFirst
 
you have to insert variable "values" into SQL:

& "values( " & i & ", " & rs!system & ", " & rs!feq & ", " & rs!box & ", 0
," & rs!total_jobs & ", 'Ratio' , 9 ) ;"

if var returns string value - also add ' around it
 
getting closer but i'm still missing something.
if I paste the statement in a query it works

insert into prereport_abend (month, system, feq, box,
count_of, total_jobs, job_name, row_order ) values (
1, 'Caesar', 'Bi monthly', 'Mainframe', 0 ,5, 'Ratio' ,9 )

the table is number, text, text, text , number ,number
text , number

"insert into prereport_abend (month, system, feq, box,
count_of, total_jobs, job_name, row_order ) " _
& "values ( " & i & ", '" & rs!system
& "', '" & rs!feq & "', '" & rs!box & "', " & 0 & " ," &
rs!total_jobs & ", 'Ratio' ," & 9 & " ) ;"

produces

"insert into prereport_abend (month, system, feq, box,
count_of, total_jobs, job_name, row_order ) values (
1, 'Caesar', 'Bi monthly', 'Mainframe',
0 ,5, 'Ratio' ,9 ) ;"
 
Back
Top