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

INSERT query in VB6

P: 5
hello community,
I am using VB6 and Access in my RDBMS project.
I have one query ' flt_availabale ' . i want to insert records in another table named booking.
i have made one ado with flt_availabale and one with booking
in the adoflt_availabale properties recordsource is adcmdunknown and SQL is SELECT * FROM FLT_AVAILABALE.
the parameters for the select query are in text box in the form formbooking.
Code used is as under:-

Private Sub Cmdtrial_Click()

Dim str3 As String
str3 = " insert into booking ( flight, orgin, destination ) select * from flt_availabale where fltno = ' " & Formbooking.Text6.Text & " ' and fromcode = > ' " & Formbooking.Text11.Text & "' and tocode =< '" & Formbooking.Text12.Text & " ' "
MsgBox str3
Adobooking.RecordSource = str3
Adobooking.Refresh

End Sub

the above code returns error : [microsoft][ODBC microsoft access driver] syntax error in from clause

thereafetr it informs adorefresh failed

what is going wrong ?? please help me. this is my first ever VB6 project

regards
prateek
Oct 30 '06 #1
Share this Question
Share on Google+
9 Replies


100+
P: 1,646
str3 = " insert into booking ( flight, orgin, destination ) select * from flt_availabale where fltno = ' " & Formbooking.Text6.Text & " ' and fromcode = > ' " & Formbooking.Text11.Text & "' and tocode =< '" & Formbooking.Text12.Text & " ' "

Hi
The INSERT clause also needs VALUES (value1, value2, value3...) FROM (...)

It is good practise to use uppercase with sql keywords to aid readability and to put each section on a new line. You will find it very much easier to debug :)

Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO booking ( flight, orgin, destination) " _
  2. & "VALUES (fltno, fromcode, dest) " _
  3. & "FROM (SELECT fltno, fromcode, dest " _
  4. & "FROM flt_availabale " _
  5. & "WHERE fltno = ' " & Formbooking.Text6.Text & "' " _
  6. & "AND fromcode = > ' " & Formbooking.Text11.Text & "' " _
  7. & "AND tocode =< '" & Formbooking.Text12.Text & " ') "
  8.  
Oct 30 '06 #2

100+
P: 1,646
I also see that you are using comparison operators against text values. If your data is stored as numeric values you should cast the textbox values to the apporpriate data type...

Expand|Select|Wrap|Line Numbers
  1. & "AND tocode <= " & CInt(Formbooking.Text12.Text) & ") "
This also means that you do not need a text delimiter " ' " in your code.
You might also find it useful to name your textbox something easy to work with in your code...

Expand|Select|Wrap|Line Numbers
  1. & "AND tocode <= " & CInt(Formbooking.txtToCode.Text) & ") "
Oct 30 '06 #3

P: 5
Thanks a lot. i used the code. it was giving syntax error . i have removed the FROM before SELECT and it is giving no errors now. but when i try to refresh the ado it says it can not do.

i.e

adobooking.recordsource = str3
adobooking.refresh

the records are not getting appended . the same query is working fine in access
please help

regards
Oct 30 '06 #4

P: 5
thanks a lot .
i have rectified the mistake of comparing a text value . used CInt and removed delimiters.
the code is still raising a syntax error
ado refersh is also a failure.
could you help me please
Oct 30 '06 #5

100+
P: 1,646
thanks a lot .
i have rectified the mistake of comparing a text value . used CInt and removed delimiters.
the code is still raising a syntax error
ado refersh is also a failure.
could you help me please
An update or insert is run from an ado command as execute. It can also be run from the connection object.

cnCon.Execute strSQL

Please post your code as it is now to allow us to find any errors
Oct 30 '06 #6

P: 5
the code is as under
[CODE}..
Dim str3 As String
str3 = "INSERT INTO booking ( flight, origin, destination) " _
& "VALUES (fltno, fromcity, tocity) " _
& " FROM (SELECT fltno, fromcity, tocity " _
& "FROM flt_availabale " _
& "WHERE fltno = " & CInt(Formbooking.Text6.Text) & " " _
& "AND fromcode >= " & CInt(Formbooking.Text11.Text) & " " _
& "AND tocode <= " & CInt(Formbooking.Text12.Text) & " ) "
MsgBox str3
Adobooking.RecordSource = str3
Adobooking.Refresh
..[/code]
Oct 30 '06 #7

P: 5
i abandoned the idea of INSERT query. here is how i worked it out.
I returned the filtered result in text boxes.
in a loop i copied theses results to another text boxes connected with ado wher i wanted to save them (booking table) and saved them.

Expand|Select|Wrap|Line Numbers
  1. ..
  2. Private Sub Cmdsave_Click()
  3.  
  4. Dim str1 As String
  5. str1 = "select * from flt_availabale where fltno= '" & Text6 & "'AND fromcode >= " & CInt(Text11) & " And tocode <= " & CInt(Text12) & ""
  6. Adofltavail.RecordSource = str1
  7. Adofltavail.Refresh
  8. Adofltavail.Recordset.MoveFirst
  9. Do Until Adofltavail.Recordset.EOF
  10.     With Adobooking
  11.       .Recordset.Fields(0) = Text14.Text
  12.       .Recordset.Fields(1) = Text15.Text
  13.       .Recordset.Fields(2) = Text16.Text
  14.       .Recordset.Fields(3) = Text10.Text
  15.       .Recordset.Fields(4) = CInt(Text2.Text)
  16.       .Recordset.Fields(5) = Text9.Text
  17.       .Recordset.Save
  18.       .Recordset.Update
  19.    End With
  20.    Adofltavail.Recordset.MoveNext
  21.    Adobooking.Recordset.AddNew
  22. Loop
  23. Adopassenger.Recordset.Save
  24. Adofltavail.Recordset.Close
  25. Formfltavailable.Show
  26. Formbooking.Visible = False
  27. End Sub
  28.  
  29. ..
Oct 30 '06 #8

100+
P: 1,646
This should work for you
Expand|Select|Wrap|Line Numbers
  1.     Dim cnCon As ADODB.Connection
  2.     Dim strSQL As String
  3.     Dim strCon As String
  4.  
  5.     strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
  6.         & "Data Source=MyAccessDB.mdb;" _
  7.         & "Persist security Info=False"
  8.  
  9.     Set cnCon = New ADODB.Connection
  10.  
  11.     cnCon.Open strCon
  12.  
  13.     strSQL = "INSERT INTO booking ( flight, origin, destination) " _
  14.                & "VALUES (fltno, fromcity, tocity) " _
  15.                & " SELECT fltno, fromcity, tocity " _
  16.                & "FROM flt_availabale " _
  17.                & "WHERE fltno =  " & CInt(Formbooking.Text6.Text) & " " _
  18.                & "AND fromcode >=  " & CInt(Formbooking.Text11.Text) & " " _
  19.                & "AND tocode <= " & CInt(Formbooking.Text12.Text) & " ) "
  20.  
  21.     cnCon.Execute strSQL
  22.  
  23.     cnCon.Close
  24.     Set cnCon = Nothing
  25.  
Oct 30 '06 #9

100+
P: 1,646
i abandoned the idea of INSERT query. here is how i worked it out.
I returned the filtered result in text boxes.
in a loop i copied theses results to another text boxes connected with ado wher i wanted to save them (booking table) and saved them.

Expand|Select|Wrap|Line Numbers
  1. ..
  2. Private Sub Cmdsave_Click()
  3.  
  4. Dim str1 As String
  5. str1 = "select * from flt_availabale where fltno= '" & Text6 & "'AND fromcode >= " & CInt(Text11) & " And tocode <= " & CInt(Text12) & ""
  6. Adofltavail.RecordSource = str1
  7. Adofltavail.Refresh
  8. Adofltavail.Recordset.MoveFirst
  9. Do Until Adofltavail.Recordset.EOF
  10.     With Adobooking
  11.       .Recordset.Fields(0) = Text14.Text
  12.       .Recordset.Fields(1) = Text15.Text
  13.       .Recordset.Fields(2) = Text16.Text
  14.       .Recordset.Fields(3) = Text10.Text
  15.       .Recordset.Fields(4) = CInt(Text2.Text)
  16.       .Recordset.Fields(5) = Text9.Text
  17.       .Recordset.Save
  18.       .Recordset.Update
  19.    End With
  20.    Adofltavail.Recordset.MoveNext
  21.    Adobooking.Recordset.AddNew
  22. Loop
  23. Adopassenger.Recordset.Save
  24. Adofltavail.Recordset.Close
  25. Formfltavailable.Show
  26. Formbooking.Visible = False
  27. End Sub
  28.  
  29. ..
There are many problems with this code. First of all you will overwrite the first record in booking with the values in the textboxes. Secondly you will continually add the same new record to booking depending on the number of rows returned from flt_available. Thirdly, and this might surprise you, the two recordsets appear to be global variables to which you are reassigning different data. This is definitely taboo in programming.

I tested the code from my previous posting. This works
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO booking (flight, origin, destination) " _
  2.           & "SELECT fltno, fromcity, tocity " _
  3.           & "FROM flt_availabale " _
  4.           & "WHERE fltno =  " & CInt(Formbooking.Text6.Text) & " " _
  5.           & "AND fromcode >=  " & CInt(Formbooking.Text11.Text) & " " _
  6.           & "AND tocode <= " & CInt(Formbooking.Text12.Text)
  7.  
.
Oct 30 '06 #10

Post your reply

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