tattoo

I'm trying to copy data from one dataset to another with the same field layout.

I'm using Visual Studio 2005 Professional edition and I got the following code from the online help. I looked up Dataset > Copy Data


Dim germanyCustomers As DataSet = customerDataSet.Clone()

Dim copyRows() As DataRow = _
 customerDataSet.Tables("Customers").Select("CountryRegion = 'Germany'")

Dim customerTable As DataTable = germanyCustomers.Tables("Customers")
Dim copyRow As DataRow

For Each copyRow In copyRows
 customerTable.ImportRow(copyRow)
Next

I entered the following code in my app


Dim ImportRecs As DataSet = ImportDS.Clone()
Dim copyRows() As DataRow = ImportBUDS.Tables("ARCUS_Import_BU").Select("*")
Dim ImportBUTable As DataTable = ImportRecs.Tables("ARCUS_Import")
Dim copyrow As DataRow
Dim reccount As Integer = 1
For Each copyrow In copyRows
ImportBUTable.ImportRow(copyrow)
Label3.Text = reccount &
" Records copied....."

Next


I receive an error at the line that defines copyRows() saying that

"Object reference not set to an instance of an object.".

anyone have any idea why

thanks

tattoo



Re: Visual Basic General trying to copy a dataset to another dataset

Rahul Saxena

Dim copyRows() As DataRow = ImportBUDS.Tables("ARCUS_Import_BU").Select("*")

Probably ImportBuds is not initialized or there is no table "ARCUS_Import_BU" in the dataset ImportBuds.

One more thing, please make sure about the filter select("*"). Usually this is for filtering the rows in the Datatable example Select("CountryRegion = 'Germany'"). I this there is the problem with your filter. I assume you want to retieve all the rows in the datatable. Probably you can use :

ImportBUDS.Tables("ARCUS_Import_BU").Rows

-Rahul





Re: Visual Basic General trying to copy a dataset to another dataset

tattoo

Rahul, thanks for your response, however, the above did not work.

I changed tactics and tried using the following.


For i = 0 To 100
For j = 0 To 157
NRow = To_DS.Tables(
"ARCUS_Import_BU").NewRow
NRow.Item(j) = From_DS.Tables(
"ARCUS_Import").Rows(i).Item(j)
To_DS.Tables(
"ARCUS_Import_BU").Rows.Add(NRow)

Next

ToAdapter.InsertCommand = INScomm
ToAdapter.InsertCommand.Connection = AccessConnection

ToAdapter.Update(To_DS,
"ARCUS_Import_BU")


The problem with this is that, when it gets to the update statement it informs me that I need a valid INSERT statement.

So I create one. Basically what I have and what I'm trying to do is; I have two Access table Import and Import_BU (the BU being a backup version).

I want to backup the data from Import to Import_BU

both files have the exact same layout. they each have 159 fields.. Now I have run into two other problems. In the Import table, if a field contains no value the program bombs and tells me that a Null value cannot be mapped to a String, then I made sure I had something in each field so that I could test the rest of the program. Now when it gets to the update it tells me my Insert statement isn't valid.

The layout of my insert statement is partiall as follows. (I didn't want to list 159 fields)



INScomm = New OleDbCommand("INSERT INTO ARCUS_Import_BU SET" & _
" (IDCUST,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,TEXTSNAM,IDGRP, " & _
"IDNATACCT,SWACTV,DATEINAC,DATELASTMN,SWHOLD,DATESTART," & _
"IDPPNT,CODEDAB,CODEDABRTG,DATEDAB, NAMECUST,TEXTSTRE1,"

"CCTYPE) VALUES( , , , , , , , , , , , , , , , , , , , " & _
", , , , , , , , , , , , , , , , , , , , )")

INScomm.Parameters.Add("IDCUST", OleDbType.Char, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(0))
INScomm.Parameters.Add(
"AUDTDATE", OleDbType.Date, 8, From_DS.Tables("ARCUS_Import").Rows(i).Item(1))
INScomm.Parameters.Add(
"AUDTTIME", OleDbType.DBTime, 12.0, From_DS.Tables("ARCUS_Import").Rows(i).Item(2))
INScomm.Parameters.Add(
"AUDTUSER", OleDbType.Char, 8, From_DS.Tables("ARCUS_Import").Rows(i).Item(3))
INScomm.Parameters.Add(
"AUDTORG", OleDbType.Char, 6, From_DS.Tables("ARCUS_Import").Rows(i).Item(4))
INScomm.Parameters.Add(
"TEXTSNAM", OleDbType.Char, 10, From_DS.Tables("ARCUS_Import").Rows(i).Item(5))
INScomm.Parameters.Add(
"IDGRP", OleDbType.Char, 6, From_DS.Tables("ARCUS_Import").Rows(i).Item(6))
INScomm.Parameters.Add(
"IDNATACCT", OleDbType.Char, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(7))
INScomm.Parameters.Add(
"SWACTV", OleDbType.Integer, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(8))
INScomm.Parameters.Add(
"DATEINAC", OleDbType.Date, 10.0, From_DS.Tables("ARCUS_Import").Rows(i).Item(9))
INScomm.Parameters.Add(
"DATELASTMN", OleDbType.Date, 8, From_DS.Tables("ARCUS_Import").Rows(i).Item(10))
INScomm.Parameters.Add(
"SWHOLD", OleDbType.Integer, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(11))
INScomm.Parameters.Add(
"DATESTART", OleDbType.Date, 8, From_DS.Tables("ARCUS_Import").Rows(i).Item(12))
INScomm.Parameters.Add(
"IDPPNT", OleDbType.Char, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(13))
INScomm.Parameters.Add(
"CODEDAB", OleDbType.Char, 9, From_DS.Tables("ARCUS_Import").Rows(i).Item(14))
INScomm.Parameters.Add(
"CODEDABRTG", OleDbType.Char, 5, From_DS.Tables("ARCUS_Import").Rows(i).Item(15))
INScomm.Parameters.Add(
"DATEDAB", OleDbType.Date, 8, From_DS.Tables("ARCUS_Import").Rows(i).Item(16))
INScomm.Parameters.Add(
"NAMECUST", OleDbType.Char, 60, From_DS.Tables("ARCUS_Import").Rows(i).Item(17))
INScomm.Parameters.Add(
"TEXTSTRE1", OleDbType.Char, 60, From_DS.Tables("ARCUS_Import").Rows(i).Item(18))

INScomm.Parameters.Add("CCTYPE", OleDbType.Char, 12, From_DS.Tables("ARCUS_Import").Rows(i).Item(19))


If anyone has any ideas as to how to fix these problems or an easier way to do this, I would greatly appreciate any feedback.

Thanks





Re: Visual Basic General trying to copy a dataset to another dataset

tattoo

for anyone that's interested, I psted this problem on the vbCity forum and received the following suggestion from

djjeavons.

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Temp\VIHINV.mdb")

'Create the sql statement to copy data from ARCUS_Import to ARCUS_Import_BU
Dim transferDataSQL As String = "INSERT INTO ARCUS_Import_BU SELECT * FROM ARCUS_Import"
Dim command As New OleDbCommand(transferDataSQL, connection)

'Open the connection and execute the command
connection.Open()
command.ExecuteNonQuery()
connection.Close()





Re: Visual Basic General trying to copy a dataset to another dataset

cybertaz69

Are you trying to copy the entire table to anthoer table

Code Snippet
me.northwindDS1.customers.merge(me.northwindDS.customers)

Just to ask but why copy the same table back into the same DB for backup

If the DB gets crupted both tables are going to be crupted..... User Error Same problem with this design...






Re: Visual Basic General trying to copy a dataset to another dataset

tattoo

That's a good point although the only reason I'm copying the table at this time is so that I can clear out

the original table and reload it from our ACCPAC database where all our customer records are entered and maintained.

thanks for your response though..

tattoo