Question Simple Straightforward INSERT problem...I thought

[themaj]

Having a brain fart on what I believe is a no brainer.


I'm trying to read the contents of a DBF and write it to an ACCDB table; this is to be a daily event and the DBF has between 500-1200 records each day.

I have successfully connected to and read the contents of a DBF table and filled my dataset [ da.Fill(ds, "Data") ] along with a DataGridView. All is good so far.


Now I want to write the entire table to my ACCDB Table and both structures are the same. I've done this before with no problems but this time it's a little different because I have no arguments or parameters. I just want to add all fields of every record. I see a ton of samples & examples WITH parameters, but no cmd.command INSERT examples to insert/copy everything.


Basically I want to: INSERT INTO [MyDBTable] SELECT * FROM ds.tables("data") .... or something like that.


Everything related to the connection string, data source, etc is fine; I've been using it for other procedures with no problems.


What I can't seem to get is:
- the INSERT statement
- whether I loop through the datatable row by row, or can copy the whole thing in one blast


Appreciate someone's guidance and patience

 

[jmcilhinney]

The problem is that you're trying to do it without parameters. You can't tell a database to select records from a DataTable that it has no idea exists. You need to insert the rows into the Access database in EXACTLY the same way that you would if you'd populated the the DataTable from that same Access database in the first place, i.e. a data adapter with an InsertCommand WITH APPROPRIATE PARAMETERS. Here's a simple example of using a single data adapter with two different connections to insert records into one database form another. You just change the names and expand the number of parameters as required:

Using sourceConnection As New OleDbConnection("connection string here"),
      destinationConnection As New OleDbConnection("connection string here"),
      insertCommand As New OleDbCommand("INSERT INTO Person (GivenNames, FamilyName) VALUES (@GivenNames, @FamilyName)", destinationConnection),
      adapter As New OleDbDataAdapter("SELECT * FROM Person", sourceConnection) With {.InsertCommand = insertCommand}
    With insertCommand.Parameters
        .Add("@GivenNames", OleDbType.VarChar, 50, "GivenNames")
        .Add("@FamilyName", OleDbType.VarChar, 50, "FamilyName")
    End With

    'This is required to ensure that the rows are in a state to be inserted.
    adapter.AcceptChangesDuringFill = False

    Dim table As New DataTable

    adapter.Fill(table)
    adapter.Update(table)
End Using

 

[IanRyder]

Excellent post jmcilhinney, there are some real subtle and neat tricks to be learnt from that post.

Cheers,

Ian

 

[themaj]

[h=5]@jmcilhinney[/h]Glad you see you responding to my plight. Appreciate your quick response and certainly willing to listen & learn here; it's been driving me crazy.

So you're saying that there's not a simple way to just say 'grab * in each row of datatable and stick it in this db table'. Sort of a SELECT * FROM datatable and INSERT in AccessTable.

So even though I can do a SELECT * in one query and toss that into a dataset/datatable, I can't do a INSERT * from the dataset/datatable? Just trying to wrap my head around this and I'm not sure I'm explaining it correctly.

 

[IanRyder]

Hi,

Here is a more long winded example of what you can do but jmcilhinney beat me to it earlier. Read of the comments carefully to see what each step is doing:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  'Setup the sources
  Using sourceConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Using destinationConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
 
      'Setup the Dataset
      Using DS As New DataSet
        DS.Tables.Add("Source")
        DS.Tables.Add("Destination")
 
        'Set up the Data Adapter to read the data from your source table into your project
        Using daSourceAdapter As New SqlDataAdapter("Select * From Employees", sourceConn)
 
          'Set up the Data Adapter to read the destination table but do not get any records
          Using daDestinationAdapter As New SqlDataAdapter("Select * From tblEmployeeClone Where EmployeeID = -1", destinationConn)
 
            'Create the action queries for the Destination Data Adapter
            Using destinationCB As New SqlCommandBuilder(daDestinationAdapter)
 
              'Fill both Data Tables using both Data Adapters - the Destination Data Adapter is basically setting up the correct Schema for the Table
              daSourceAdapter.Fill(DS.Tables(0))
              daDestinationAdapter.Fill(DS.Tables(1))
 
              'Iterate the source table and add the record to the destination table
              For Each currentRow As DataRow In DS.Tables(0).Rows
                DS.Tables(1).Rows.Add(currentRow.ItemArray)
              Next
 
             'Add all the records at once to the external Database with one call to the Update Method of the Data Adapter
              Dim rowsUpdated As Integer = daDestinationAdapter.Update(DS.Tables(1))
 
              'Done
              MsgBox(String.Format("Number of Rows Added is {0}", rowsUpdated))
            End Using
          End Using
        End Using
      End Using
    End Using
  End Using
End Sub

I have just used SQL in this example, but you just need to change this to use your own source and destination databases.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

So even though I can do a SELECT * in one query and toss that into a dataset/datatable, I can't do a INSERT * from the dataset/datatable? Just trying to wrap my head around this and I'm not sure I'm explaining it correctly.

That's correct. It's the database that executes the SQL code and the database doesn't know anything about your DataTable. It only knows about the SQL code you send it and the parameter values you send with that SQL.

So you're saying that there's not a simple way to just say 'grab * in each row of datatable and stick it in this db table'. Sort of a SELECT * FROM datatable and INSERT in AccessTable.

You have to use a DbCommand object, although the details may vary. If the data is in a DataTable then you should use a data adapter to save the whole lot in a batch. That data adapter needs an InsertCommand to insert data. You can either create that yourself the way I've shown or use a command builder as IanRyder has shown. If you use a command builder then you must use two data adapters, because you need a SELECT statement for the INSERT statement to be created from. I would not recommend two DataTables though because the second one serves no purpose.

 

[themaj]

@ Ian & jmcilhinney,

OK guys, I think I'm getting it here and I'm following what both of you did although you took slightly different roads. Please bear with me.

If I've read this right and we were all on the same Source DB Table with the limited fields described by jmcilhinney, you've both copied the contents of one DB Table to another.

The data was added to the dataset tables with the adapter.fill and then sent to the database with the adapter.update (from the dataset table)

Ian copied across the adapters BUT left no capability to select specific fields, which could be just fine for my short term interests. This would work whether the SourceDB had 3 or 33 fields...right?

Whereas jmcilhinney did it with a more specific detail to what fields were being addressed. Am I correct so far?

Another difference in these examples was Ian used a second dataset table which was the reason for the For Next Loop. Regardless of it's importance or necessity.....am I still following you guys correctly?

So even though my SourceDB is DBF and my DestinationDB is ACCDB for which I have the connections, it becomes moot once I've filled the dataset table(s) via the adapters?

 

[IanRyder]

Hi,

OK guys, I think I'm getting it here and I'm following what both of you did although you took slightly different roads. Please bear with me.

Yeah, We did, didn't we.

If I've read this right and we were all on the same Source DB Table with the limited fields described by jmcilhinney, you've both copied the contents of one DB Table to another.

Yes, since jmcilhinney said that you needed to expand the parameters to incorporate all of your fields.

The data was added to the dataset tables with the adapter.fill and then sent to the database with the adapter.update (from the dataset table)

Yes.

Ian copied across the adapters BUT left no capability to select specific fields, which could be just fine for my short term interests. This would work whether the SourceDB had 3 or 33 fields...right?

Correct. My solution assumes that both the source and destination tables have exactly the same table schema, whereas jmcilhinney's solution does demonstrate how to save selected fields from a source table to a destination table.

Whereas jmcilhinney did it with a more specific detail to what fields were being addressed. Am I correct so far?

Yes.

Another difference in these examples was Ian used a second dataset table which was the reason for the For Next Loop. Regardless of it's importance or necessity.....am I still following you guys correctly?

Yes. The important point to note here is that jcmilhinney set the property adapter.AcceptChangesDuringFill = False which basically told the adapter that any records added to a DataTable during a Fill routine are to be treated as new records in that DataTable so that when the Update Method was called it recognised these as new inserted records in the DataTable to be added to the Destination Database. Since I manually added each record to the Destination Table these were automatically recognised as new records for insertion to the Destination table when the Update Method was called.

So even though my SourceDB is DBF and my DestinationDB is ACCDB for which I have the connections, it becomes moot once I've filled the dataset table(s) via the adapters?

Yes. So long as your connections are valid connections then everything should be added from your source table to your destination table as you expect.

Hope that helps.

Cheers,

Ian

 

[themaj]

Ian,

Tried your idea here and have a problem with the iteration of the source table to destination table.

"Invalid cast from 'DateTime' to 'Double'.Couldn't store <1/1/2013 12:00:00 AM> in CHECK Column. Expected type is Double."

From what I can see it being caused because the two tables have 2 different schemas. The destination has an extra field; the internal index. So it's trying to write the Date of Business data to a numeric field. I tried removing the first index field and everything copied over just fine. I loaded both tables into datagridviews and they were perfect. But that apparently screwed up the command builder; does that make sense?

Any ideas....thanks

 

[IanRyder]

Hi,

You have already really identified your own problem on the bases that you know that the two table schemas are not the same. Let's say that the Internal Index column you mention is an identity column in the first column of the destination table then what will happen, using my example, is that all the columns will be displaced to the Left by one column in the .Rows.Add Method in the For Loop. This will then cause all sorts of type errors to occur which is what you have seen.

So, you have two options. You can use the example shown by jmcilhinney to specify the parameters to be used within the Insert statement or you can use a bit of a "hack" with my example and add an extra column to compensate for that additional column in the destination table. i.e:-

For Each currentRow As DataRow In DS.Tables(0).Rows
  Dim myListOfFields As List(Of Object) = currentRow.ItemArray.ToList
  myListOfFields.Insert(0, -1)
  DS.Tables(1).Rows.Add(myListOfFields.ToArray)
Next

If I had to make a choice here, I would probably specify the parameters as demonstrated by jmcilhinney but the hack I have shown does work too.

Hope that helps.

Cheers,

Ian