Help with VB + SQL code

[cptcanada]

I am trying to useSQL code inside VB code and I keep getting an Expected Case error on my first select statement, can any one help me with this??

Thanks

ConnectionStrin g = "Provider=SQLOL EDB.1;server=ws us;Integrated Security=SSPI;P ersist Security Info=False;Init ial Catalog=inv_tra cking"
set cn = CreateObject("A DODB.Connection ")
cn.Open ConnectionStrin g

mStringSQL ="INSERT INTO driver_destroye d_log ( emp_id, date_recieved, violation, form_manner, log_status, status_date, date_destroyed, comments, log_id )"

SELECT Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, Driver_daily_lo gs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, Driver_daily_lo gs.status_date, Driver_daily_lo gs.date_destroy ed, Driver_daily_lo gs.comments, Driver_daily_lo gs.log_id
FROM Driver_daily_lo gs
WHERE DATE()>date_des troyed;
CN.Execute (mStringSQL)

[CyberSoftHari]

Can you pointout errors in detail?

[cptcanada]

Well the first error I get is a case error on the select stements. Then when I put the select statement in brackets it gives me an Expected Statement error.

[cptcanada]

Since I am developing in InfoPath there is no error number that is displayed when I get the two errors

[CyberSoftHari]

You should point where the error is (Keep break point)?
I assume the problem is in where clause

WHERE DATE()>date_destroyed;

[cptcanada]

Ok so with the line of code I have I moved the quotations so that the where clause is appart of the sting, but when i did that it tells me the string is unterminated.

[jeffstl]

mStringSQL ="INSERT INTO driver_destroyed_log ( emp_id, date_recieved, violation, form_manner, log_status, status_date, date_destroyed, comments, log_id )
SELECT Driver_daily_logs.emp_id, Driver_daily_logs.date_recieved, Driver_daily_logs.violation, Driver_daily_logs.form_manner, Driver_daily_logs.log_status, Driver_daily_logs.status_date, Driver_daily_logs.date_destroyed, Driver_daily_logs.comments, Driver_daily_logs.log_id
FROM Driver_daily_logs
WHERE DATE() > date_destroyed"

Is this what you have right now?

[cptcanada]

That is correct, but there is a ; ending the SQL statement

[jeffstl]

That is correct, but there is a ; ending the SQL statement

Ok well in that case I am not sure how your code layout is but assuming you are using VB make sure all your SQL is on 1 line.

If its not you need to be using the & _ to connect each line otherwise it is indeed an unterminated string.

[cptcanada]

so right after the inerst statement line, select put an & in?

[cptcanada]

So this is my code now and I am still getting the unterminated error

ConnectionStrin g = "Provider=SQLOL EDB.1;server=ws us;Integrated Security=SSPI;P ersist Security Info=False;Init ial Catalog=inv_tra cking"
set cn = CreateObject("A DODB.Connection ")
cn.Open ConnectionStrin g

mStringSQL ="INSERT INTO driver_destroye d_log ( emp_id, date_recieved, violation, form_manner, log_status, status_date, date_destroyed, comments, log_id)&_
SELECT Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, Driver_daily_lo gs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, Driver_daily_lo gs.status_date, Driver_daily_lo gs.date_destroy ed, Driver_daily_lo gs.comments, Driver_daily_lo gs.log_id&_
FROM Driver_daily_lo gs&_
WHERE DATE()>date_des troyed;"
CN.Execute (mStringSQL)

cn.Close

[cptcanada]

unterminated string error in Vb and SQL

here is the VB code, and i am using the &_ to connect all my lines into one sting, why am i still getting the error?

Thanks for the help

So this is my code now and I am still getting the unterminated error

ConnectionStrin g = "Provider=SQLOL EDB.1;server=ws us;Integrated Security=SSPI;P ersist Security Info=False;Init ial Catalog=inv_tra cking"
set cn = CreateObject("A DODB.Connection ")
cn.Open ConnectionStrin g

mStringSQL ="INSERT INTO driver_destroye d_log ( emp_id, date_recieved, violation, form_manner, log_status, status_date, date_destroyed, comments, log_id)&_
SELECT Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, Driver_daily_lo gs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, Driver_daily_lo gs.status_date, Driver_daily_lo gs.date_destroy ed, Driver_daily_lo gs.comments, Driver_daily_lo gs.log_id&_
FROM Driver_daily_lo gs&_
WHERE DATE()>date_des troyed;"
CN.Execute (mStringSQL)

cn.Close

[debasisdas]

semicolon is not required to execute the SQL from VB ?

[QVeen72]

Hi,

I think there is a problem with your Line continuation :

Try this:

[code=vb]
mStringSQL ="INSERT INTO driver_destroye d_log ( emp_id, date_recieved, " _
& " violation, form_manner, log_status, status_date, " _
& " date_destroyed, comments, log_id) " _
& " SELECT Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, " _
& " Driver_daily_lo gs.violation, Driver_daily_lo gs.form_manner, " _
& " Driver_daily_lo gs.log_status, Driver_daily_lo gs.status_date, " _
& " Driver_daily_lo gs.date_destroy ed, Driver_daily_lo gs.comments, " _
& " Driver_daily_lo gs.log_id FROM Driver_daily_lo gs " _
& " WHERE DATE()>date_des troyed"
[/code]

Just copy and paste it in vb..

Regards
Veena

[cptcanada]

Thanks that helped out alot