Using Select Statement from 1 database to insert into another database using VB .NET

Question

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

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

Answer 1

ExecuteNonQuery cannot be used to SELECT stuff from a database. You should use ExecuteReader and use the result in a loop to set the parameters of the INSERT statement and then run ExecuteNonQuery in that loop. From the code you've written, how you'd expect the values should be populated in the INSERT statement?

Answer 2

1. You're using SELECT to try to return rows, but then calling ExecuteNonQuery(), which returns nothing. You'll want to use ExecuteReader() instead.
2. You'll probably get another error later because you're INSERTing into "Table1" but trying to reference fields in "Sample".

Also unrelated to the error, but you aren't doing anything with the data in the SELECT statement to use it in the INSERT statement.

Answer 3

Here is a sugestion,

If the columns you are retriving have the same type as the columns you are inserting (Basicaly you are not making any conversion and/or transformations) do a single query that does this.

INSERT INTO TestTable2
SELECT * FROM TestTable1