INSERT query in VB6

prateekprashar

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

willakawill

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 :)

Oct 30 '06 #2

willakawill

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...

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...

Oct 30 '06 #3

prateekprashar

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

prateekprashar

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

willakawill

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

prateekprashar

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

prateekprashar

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.

Oct 30 '06 #8

willakawill

This should work for you

Oct 30 '06 #9

willakawill

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
.

Oct 30 '06 #10