DotNet Programmer DotNet Programmer - 1 year ago 156
Vb.net Question

OleDB database to SQLite database

I am trying to take the data from a database that is using OleDb connection through VB.net and taking the dataset and trying to put it straight into a sqlite database. The issue that I am having is I get the data out of the first database but after I do the Update command and I look at the SQLite database it is empty with zero records. They both are pointing two different databases in the same directory. I get no execptions but nothing gets populated into the new SQLite database. Any suggestions to what could cause that?

Dim ds As New DataSet
Dim SQL As String = "SELECT * FROM SOMETABLE"
Using conn As New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;Data Source=Data.mdb;Jet OLEDB:Engine Type=5"
conn.Open()
Using com As New OleDbCommand(SQL, conn)
Using da As OleDBDataAdapter = New OleDBDataAdapter(conn)
da.Fill(ds, "Employees")
da.Dispose()
End Using
com.Dispose()
End Using
conn.Close()
End Using

Using conn As New SqliteConnection("Data Source=Data.db;Version=3;"
conn.Open()
Using com As New SqliteCommand(SQL, conn)
Using da As SqliteDataAdapter = New SqliteDataAdapter(conn)
Using cmdBuilder As New SQLiteCommandBuilder(da)
da.TableMappings.Add("SOMETABLE", "SOMETABLE")
da.InsertCommand = cmdBuilder.GetInsertCommand()
da.Update(ds, "SOMETABLE")
End Using
da.Dispose()
End Using
com.Dispose()
End Using
conn.Close()
End Using

Answer Source

Keep in mind that each row in the DataTable includes a property to track its state - whether it is new, changed or deleted. When you first read in the data, that state will be unchanged unless you tell it differently. It can also be done with a bit less code:

Dim SQL As String = "SELECT Id, Name, ... ItemDate, Active FROM SampleY"

Dim dt As New DataTable
Using da As New OleDbDataAdapter(SQL, ACEConnStr)
    ' tell it not to set rows to Unchanged
    da.AcceptChangesDuringFill = False
    da.Fill(dt)
End Using

' for demo purposes
Console.WriteLine("Rows: {0}", dt.Rows.Count)
Console.WriteLine("State: {0}", dt.Rows(0).RowState)

Using da As New SQLiteDataAdapter(SQL, LiteConnStr)
    Dim cb As New SQLiteCommandBuilder(da)
    da.InsertCommand = cb.GetInsertCommand

    Dim rows = da.Update(dt)
End Using

da.AcceptChangesDuringFill = False tells the adapter not to set the RowState on the rows when loading. The debug line will report the states as "Added" which means they are eligible for inserting.

You also do not need all those Close and Dispose calls; that is the point of the Using statement - it does that for you. Using a DbDataAdapter you can skip creating a Connection and Command objects and let it create its own from the SQL and ConnectionString passed to it in the constructor.

I also skipped the DataSet since it is just one table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download