Create a Select statement in 1 db and insert results in another db file using VB .NET

mveliz

New member
Joined
Feb 18, 2005
Messages
2
Programming Experience
1-3
Hi,

I am currently writing a VB .NET application where I am trying to open 1 database, create a select statement and then post the results into another database file using Microsoft Access database 2002.


The code seems to stop executing at the statement "cmdJetDB.ExecuteNonQuery()"

I am using the following code:

Dim conn1 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\Sample.mdb")

Dim conn2 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\db2.mdb")
conn1.Open()
conn2.Open()
Dim mySelectQuery As String
mySelectQuery = "SELECT Sample.LANE_ADDR, Sample.LANE_DT, Sample.LANE_TM, Sample.LANE_SPEED FROM (Sample) WHERE ((Sample.LANE_ADDR) = '164.909' OR (Sample.LANE_ADDR) = '164.909' AND Sample.LANE_DT BETWEEN #4/4/2003# AND #4/5/2003#)"
Dim cmdJetDB As New OleDbCommand(mySelectQuery, conn1)
cmdJetDB.ExecuteNonQuery()

Dim cmdInsert As String
cmdInsert = "Insert INTO Table1 (Sample.LANE_ADDR, Sample.LANE_TM,Sample.LANE_SPEED) VALUES ('164.909', '00:12:30' , '30' )"
Dim cmdJetDB2 As New OleDbCommand(cmdInsert, conn2)
cmdJetDB2.ExecuteNonQuery()

conn2.Close()
conn1.Close()

Question: What is it that I am not doing. I opened both connections, stated my two SQL statements, yet it is still not working. I really need to get this application working. I have spent the past 2 days working on this problem. Please Help.........
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Select IS a query, thus you can't use ExecuteNonQuery. Use an oledbdataadapter and Fill a dataset. Also to insert the records you will either have to loop through each of the records inserting each one Or write them to a file and use BULK INSERT.

TPM
 

mveliz

New member
Joined
Feb 18, 2005
Messages
2
Programming Experience
1-3
Hi,

Thank you for the prompt reply, I had read a little on oledbdataadapters and datasets, I need to look into that more.
I had even tried using an "Insert Into Table1 ....Select" statement, and executing it as a NonQuery, but that didn't work. I guess I need to find some example on oledbdataadapters, since I haven't worked with them before.

Thanks,
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
INSERT INTO ... will only allow you to insert 1 row at a time. If the DB you inserting into is local, I'd recoment using the BULK INSERT and a .csv file.
 
Top Bottom