Problem copying datatable-long post

  • Thread starter Thread starter james
  • Start date Start date
J

james

Sorry for posting a long post, (the code) but, I am having a problem copying one data table to another new table in another
Access database that has the same table schema. You will notice that I do not list exact column names etc. because this routine
needs to be generic so I can use it to copy different tables
into new tables and each one has different Column Names, Types and Field sizes. There is only a few constants between all
tables and those I have a special routine that looks for those (by looking at the Column Names) and sets the data type for those
special fields to correspond to the types in the new table. The original data table(s) come from CSV files and do not contain
any schema info as CSV does not pass that info to the file for import. Access treats everything in a CSV file as a String and
sets the default field size to 255. Therefore, I am doing the conversion on the fly.
Here's the code:

Private Sub btnAddData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddData.Click

'table name from database loaded

Dim mytbl As String

With OpenFileDialog1

..Filter = "Access Database(*.mdb)|*.mdb"

End With

OpenFileDialog1.ShowDialog()

'this opens the new EMPTY database with a new "pre-built" datatable

Dim myconnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" +
OpenFileDialog1.FileName + ";Mode=ReadWrite"

Dim myconn As New OleDb.OleDbConnection(myconnString)

myconn.Open()

'gets tablename from database table schema

Dim schematable As DataTable = myconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dr As DataRow

' cheezy but, I needed to get the table name so just slightly modified another routine

For Each dr In schematable.Rows

mytbl = dr("TABLE_NAME").ToString()

TextBox2.Text = mytbl

Next

Dim objcommand As New OleDb.OleDbCommand("SELECT * FROM [" & mytbl & "]", myconn)

Dim objda As New OleDb.OleDbDataAdapter

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myfield) VALUES(myvalue)", myconn)

objda.SelectCommand = objcommand

Dim objds As New DataSet



objda.Fill(objds, mytbl)

DataGrid2.DataSource = objds.Tables(mytbl).DefaultView

' not using binding manger--- yet

bmb = Me.BindingContext(Me.DataGrid2.DataSource, Me.DataGrid2.DataMember)

myconn.Close()

'below is routine to copy data and insert it into the new database's new table

Dim x As Integer

Dim i As Integer

Dim j As Integer

Dim col As Integer

Dim myfield As String

Dim myvalue As Object

Dim rc As DataRowCollection

Dim myrow As DataRow

roc = objDataset1.Tables(0).Rows.Count - 1

For x = 0 To roc

'For i = 0 To objDataset1.Tables(0).Rows.Count - 1

For j = 0 To objDataset1.Tables(0).Columns.Count - 1

'myvalue = ""

'myfield = ""

myrow = objds.Tables(mytbl).NewRow()



myfield = objDataset1.Tables(0).Columns(j).ColumnName

Dim cellvalue As Object = Me.DataGrid1(x, j)

myvalue = cellvalue

'this routine makes sure the each data's type is correct, Integer,string etc.

Select Case myfield

Case myfield = "RECNUM"

myvalue = CType(myvalue, Integer)

Case myfield = "RECORDID"

myvalue = CType(myvalue, Integer)

Case myfield = "REFID"

myvalue = CType(myvalue, Integer)

Case myfield = "CLIENTID"

myvalue = CType(myvalue, Integer)

Case myfield <> ""

myvalue = CType(myvalue, String)

Case Else

myvalue = myvalue

End Select

myrow(myfield) = myvalue



Next

' counts records being proccessed........

Label8.Text = "Proccessing: " & x + 1.ToString

' adds each new row to the new table

objds.Tables(mytbl).Rows.Add(myrow)

'Have tried both these insert commands,,,,,,,,don't work will not add new rows

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myfield) VALUES(myvalue)", myconn)

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myrow)", myconn)

' Dim objcommandbuilder As New OleDbCommandBuilder(objda)

Application.DoEvents() ' stops the openfile dialog from freezing until proccessing is finished

Try

myconn.Open()

objds.AcceptChanges() 'tells dataset to accept changes- NOT

objda.Fill(objds, mytbl) 'fills the table with the new data-NOT

Dim objcommandbuilder As New OleDbCommandBuilder(objda)

objda.Update(objds, mytbl)

myconn.Close()

DataGrid2.Refresh()

Catch oledbe As OleDbException

MsgBox("ERROR" & oledbe.ToString)

End Try

Next

Dim ct As Integer

ct = objds.Tables(mytbl).Rows.Count ' ends up being empty (Null) in each row

MessageBox.Show("RECORDS ADDED: " & ct)

Label8.Text = "TOTAL RECORDS ADDED: " & ct



End Sub



Several things I have tried are commented out. But, left in the code just in case I was on the right track and had just missed a
couple of things. As can be seen, (hopefully) I have tried building INSERT commands to insert the data into the new table and
that does not work. (not for me) And I have tried to Copy the entire table from the original to the new table, and that does not
work.

Which might be because the original table contains all strings with field sizes of 255.

Anyway, if you have read this far and haven't decided I am nuts, I am open to suggestions on how to make this work.

james
 
In cases like this, I am not overly fond of using the Update() method of the
DataAdapter, as it is difficult to figure out what is going on. I will remark
that

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myfield)
VALUES(myvalue)", myconn)

is never going to work. The correct format is to leave ? in the statement
like VALUES (?) and append parameters to the command. In this instance, it is
better to make a command object and append to the InsertCommand than it is to
attempt to add params to the Insert command. You can more easily debug in
this manner.

A better option is adding a query to your Access DB and using it, as it will
have param names, but the ? will work as long as you are always in Access. If
you move to SQL Server, you will find some refactoring work to your query, as
SQL requires named parameters in a query.

Your other option is to take control of the INSERT and get away from the
DataAdapter.Update() methodology. It loops also, so there is not a lot of
overhead in setting up the INSERT on your loop that makes the "new" DataTable.

Hope this helps you get on track.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


james said:
Sorry for posting a long post, (the code) but, I am having a problem copying one data table to another new table in another
Access database that has the same table schema. You will notice that I do not list exact column names etc. because this routine
needs to be generic so I can use it to copy different tables
into new tables and each one has different Column Names, Types and Field sizes. There is only a few constants between all
tables and those I have a special routine that looks for those (by looking at the Column Names) and sets the data type for those
special fields to correspond to the types in the new table. The original data table(s) come from CSV files and do not contain
any schema info as CSV does not pass that info to the file for import. Access treats everything in a CSV file as a String and
sets the default field size to 255. Therefore, I am doing the conversion on the fly.
Here's the code:

Private Sub btnAddData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddData.Click

'table name from database loaded

Dim mytbl As String

With OpenFileDialog1

..Filter = "Access Database(*.mdb)|*.mdb"

End With

OpenFileDialog1.ShowDialog()

'this opens the new EMPTY database with a new "pre-built" datatable

Dim myconnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" +
OpenFileDialog1.FileName + ";Mode=ReadWrite"

Dim myconn As New OleDb.OleDbConnection(myconnString)

myconn.Open()

'gets tablename from database table schema

Dim schematable As DataTable = myconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dr As DataRow

' cheezy but, I needed to get the table name so just slightly modified another routine

For Each dr In schematable.Rows

mytbl = dr("TABLE_NAME").ToString()

TextBox2.Text = mytbl

Next

Dim objcommand As New OleDb.OleDbCommand("SELECT * FROM [" & mytbl & "]", myconn)

Dim objda As New OleDb.OleDbDataAdapter

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myfield) VALUES(myvalue)", myconn)

objda.SelectCommand = objcommand

Dim objds As New DataSet



objda.Fill(objds, mytbl)

DataGrid2.DataSource = objds.Tables(mytbl).DefaultView

' not using binding manger--- yet

bmb = Me.BindingContext(Me.DataGrid2.DataSource, Me.DataGrid2.DataMember)

myconn.Close()

'below is routine to copy data and insert it into the new database's new table

Dim x As Integer

Dim i As Integer

Dim j As Integer

Dim col As Integer

Dim myfield As String

Dim myvalue As Object

Dim rc As DataRowCollection

Dim myrow As DataRow

roc = objDataset1.Tables(0).Rows.Count - 1

For x = 0 To roc

'For i = 0 To objDataset1.Tables(0).Rows.Count - 1

For j = 0 To objDataset1.Tables(0).Columns.Count - 1

'myvalue = ""

'myfield = ""

myrow = objds.Tables(mytbl).NewRow()



myfield = objDataset1.Tables(0).Columns(j).ColumnName

Dim cellvalue As Object = Me.DataGrid1(x, j)

myvalue = cellvalue

'this routine makes sure the each data's type is correct, Integer,string etc.

Select Case myfield

Case myfield = "RECNUM"

myvalue = CType(myvalue, Integer)

Case myfield = "RECORDID"

myvalue = CType(myvalue, Integer)

Case myfield = "REFID"

myvalue = CType(myvalue, Integer)

Case myfield = "CLIENTID"

myvalue = CType(myvalue, Integer)

Case myfield <> ""

myvalue = CType(myvalue, String)

Case Else

myvalue = myvalue

End Select

myrow(myfield) = myvalue



Next

' counts records being proccessed........

Label8.Text = "Proccessing: " & x + 1.ToString

' adds each new row to the new table

objds.Tables(mytbl).Rows.Add(myrow)

'Have tried both these insert commands,,,,,,,,don't work will not add new rows

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myfield) VALUES(myvalue)", myconn)

'objda.InsertCommand = New OleDbCommand("INSERT INTO mytbl(myrow)", myconn)

' Dim objcommandbuilder As New OleDbCommandBuilder(objda)

Application.DoEvents() ' stops the openfile dialog from freezing until proccessing is finished

Try

myconn.Open()

objds.AcceptChanges() 'tells dataset to accept changes- NOT

objda.Fill(objds, mytbl) 'fills the table with the new data-NOT

Dim objcommandbuilder As New OleDbCommandBuilder(objda)

objda.Update(objds, mytbl)

myconn.Close()

DataGrid2.Refresh()

Catch oledbe As OleDbException

MsgBox("ERROR" & oledbe.ToString)

End Try

Next

Dim ct As Integer

ct = objds.Tables(mytbl).Rows.Count ' ends up being empty (Null) in each row

MessageBox.Show("RECORDS ADDED: " & ct)

Label8.Text = "TOTAL RECORDS ADDED: " & ct



End Sub



Several things I have tried are commented out. But, left in the code just in case I was on the right track and had just missed a
couple of things. As can be seen, (hopefully) I have tried building INSERT commands to insert the data into the new table and
that does not work. (not for me) And I have tried to Copy the entire table from the original to the new table, and that does not
work.

Which might be because the original table contains all strings with field sizes of 255.

Anyway, if you have read this far and haven't decided I am nuts, I am open to suggestions on how to make this work.

james
Click to expand...
 
Back
Top