By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Help with VB + SQL code

P: 19
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

ConnectionString = "Provider=SQLOLEDB.1;server=wsus;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=inv_tracking"
set cn = CreateObject("ADODB.Connection")
cn.Open ConnectionString

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;
CN.Execute (mStringSQL)
May 28 '08 #1
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 487
Can you pointout errors in detail?
May 28 '08 #2

P: 19
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.
May 28 '08 #3

P: 19
Since I am developing in InfoPath there is no error number that is displayed when I get the two errors
May 28 '08 #4

Expert 100+
P: 487
You should point where the error is (Keep break point)?
I assume the problem is in where clause
Expand|Select|Wrap|Line Numbers
  1. WHERE DATE()>date_destroyed;
May 28 '08 #5

P: 19
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.
May 28 '08 #6

jeffstl
Expert 100+
P: 432
Expand|Select|Wrap|Line Numbers
  1.  
  2. mStringSQL ="INSERT INTO driver_destroyed_log ( emp_id, date_recieved, violation, form_manner, log_status, status_date, date_destroyed, comments, log_id )
  3. 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
  4. FROM Driver_daily_logs
  5. WHERE DATE() > date_destroyed"
  6.  
  7.  
  8.  
Is this what you have right now?
May 28 '08 #7

P: 19
That is correct, but there is a ; ending the SQL statement
May 28 '08 #8

jeffstl
Expert 100+
P: 432
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.
May 28 '08 #9

P: 19
so right after the inerst statement line, select put an & in?
May 28 '08 #10

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

ConnectionString = "Provider=SQLOLEDB.1;server=wsus;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=inv_tracking"
set cn = CreateObject("ADODB.Connection")
cn.Open ConnectionString

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;"
CN.Execute (mStringSQL)

cn.Close
May 28 '08 #11

P: 19
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

ConnectionString = "Provider=SQLOLEDB.1;server=wsus;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=inv_tracking"
set cn = CreateObject("ADODB.Connection")
cn.Open ConnectionString

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;"
CN.Execute (mStringSQL)

cn.Close
May 28 '08 #12

debasisdas
Expert 5K+
P: 8,127
semicolon is not required to execute the SQL from VB ?
May 29 '08 #13

QVeen72
Expert 100+
P: 1,445
Hi,

I think there is a problem with your Line continuation :

Try this:

Expand|Select|Wrap|Line Numbers
  1. mStringSQL ="INSERT INTO driver_destroyed_log ( emp_id, date_recieved, " _
  2. & " violation, form_manner, log_status, status_date, " _
  3. & " date_destroyed, comments, log_id) " _
  4. & " SELECT Driver_daily_logs.emp_id, Driver_daily_logs.date_recieved, " _
  5. & " Driver_daily_logs.violation, Driver_daily_logs.form_manner, " _
  6. & " Driver_daily_logs.log_status, Driver_daily_logs.status_date, " _
  7. & " Driver_daily_logs.date_destroyed, Driver_daily_logs.comments, " _
  8. & " Driver_daily_logs.log_id FROM Driver_daily_logs " _
  9. & " WHERE DATE()>date_destroyed"
  10.  
Just copy and paste it in vb..

Regards
Veena
May 29 '08 #14

P: 19
Thanks that helped out alot
May 29 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.