Link to home
Start Free TrialLog in
Avatar of colepc
colepc

asked on

help adding new records from a dataset to a database...

Hi All,

I'm reading in rows from a csv file to a datatable ("Records") within a dataset ("dsAppData") when a certain event happns.  This datatable was empty when my app loads.  After filling it, it contains 500 new rows.  I want to add these rows to the underlying database.  I'm not able to update the database using the OleDbCommandBuilder even though other datatables within the same dataset are updating their recordsources using this method.  My code is pasted in below.    

Question:  If there's enough info below, why is my database accepting additions to and modifications of one datatable in a dataset ("Files"), but not another ("CurrentBatch")?

Thanks,
Terry

'ConsumeBatchData() is triggered later which calls on UpdateRecords() at the end.  
'The dataset dsAppData.Tables("CurrentBatch") still has newly added rows when it hits UpdateRecords()

    Public dsAppData as DataSet

    Public Sub daCompany()          'COMMENT:   daCompany() is called on Form_Load for the app.    
        Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & OLEDBDatabase & ";User ID=;Password="
        Dim oOLEDBCommand As OleDbCommand

        dsAppData = New DataSet("AppData")
        Dim strSQL As String
        Dim oOLEDBConnection As OleDbConnection = New OleDbConnection(sConnString)
        oOLEDBConnection.Open()

        strSQL = "Select watchfile as File, watchfileprefix as Prefix, watchfiledescription as Description from tblWatchFiles order by watchfile"
        myDA_WatchFiles = New OleDbDataAdapter(strSQL, oOLEDBConnection)
        myDA_WatchFiles.Fill(dsAppData, "Files")

        strSQL = "select * from tblDatRecords"
        myDA_Records = New OleDbDataAdapter(strSQL, oOLEDBConnection)
        myDA_Records.Fill(dsAppData, "CurrentBatch")

        oOLEDBConnection.Close()
  End Sub

    Sub ConsumeBatchData(ByVal myfile As WatchFile)          'COMMENT: this consumes a csv file and populates a dataset table
        Dim batchConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myCo.WatchPath & ";Extended Properties=Text;"
        Dim batchConn As New OleDbConnection(batchConnectionString)
        batchConn.Open()
        Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & myWF.WatchFile, batchConn)
        Dim objAdapter1 As New OleDbDataAdapter
        objAdapter1.SelectCommand = objCmdSelect
        objAdapter1.Fill(dsAppData, "CurrentBatch")
        batchConn.Close()
        dgCurrent.DataSource = dsAppData.Tables("CurrentBatch")
        UpdateRecords()
    End Sub

     Public Sub UpdateWatchFiles()       'COMMENT:  UpdateWatchFileS() is here for reference.  It updates the datasource as expected.
        Dim db As OleDbCommandBuilder = New OleDbCommandBuilder(myDA_WatchFiles)
        myDA_WatchFiles.Update(dsAppData, "Files")
    End Sub

    Public Sub UpdateRecords()
        Dim db As OleDbCommandBuilder = New OleDbCommandBuilder(myDA_Records)
        myDA_Records.Update(dsAppData, "CurrentBatch")
    End Sub

Avatar of ptakja
ptakja
Flag of United States of America image

The problem is that your ojbAdapter1 has a SELECT command but no Update, Insert or Delete commands. These cmds are only built automatically when you build your data adapter visually through the IDE. WHen you do it all in code, you have to define each one. Fortunately, the Framework has a CommandBuilder object which will do it for you based on your SELECT statement.

Dim cb as New OleDbCommandBuilder(objAdapter)
With objAdapter
    .InsertCommand = cb.GetInsertCommand
    .DeleteCommand = cb.GetDeleteCommand
    .UpdateCommand = cb.GetUpdatecommand
End With

Now when you run your Update routine using the objAdapter.Update() it should work.
Avatar of colepc
colepc

ASKER

Thanks ptkja.  At the bottom of my code example, the CommandBuilder is invoked.  This is the same code that is invoked for other dataadapters.  However, the "UpdateRecords()" is the only one that is not actually updating the database.

   Public Sub UpdateRecords()
        Dim db As OleDbCommandBuilder = New OleDbCommandBuilder(myDA_Records)
        myDA_Records.Update(dsAppData, "CurrentBatch")
    End Sub

Have I overlooked something in your comments, or am I mistakenly interpreting them?  If not, I think I'm actually doing what you suggested already.  ???
You are sorta doing what I suggested, but you are only 1/4 of the way there. In your code, you are creating a command builder object. but you never actually get the commands to put into the Adapter object.  You need to execute the db.GetInsertCommand, db.GetUpdateCommand and db.GetDeleteCommand as I showed in my previous post. Then you should be good to go.

Avatar of colepc

ASKER

I think the thing I'm missing is why I would need to do something different (i.e., manually adding the individual commands) for one dataadapter when it's not necessary for an identical one (only the name and the underlying database table are different).  Is there a reason (from what scant code I've posted) that you can tell as to why "UpdateWatchFiles()" commandbuilder produces the updatecommnds, but "UpdateRecords()" doesn't.    Please forgive my hard head...thanks for your patience!
Avatar of colepc

ASKER

I get it...sorry...don't bother answering that last one!!!!
Avatar of colepc

ASKER

(actually...I retract my eureka moment.  I implemented the code per your suggestion and updated my "UpdateRecords()" routine to look like:

     Public Sub UpdateRecords()
        Dim db As OleDbCommandBuilder = New OleDbCommandBuilder(myDA_Records)
        myDA_Records.InsertCommand = db.GetInsertCommand
        myDA_Records.Update(dsAppData, "CurrentBatch")
    End Sub

I had 2 statements in there for .UpdateCommand and .DeleteCommand but got exceptions based on no primary key information being available in my select query.  I omitted them as they  were not relevant to my process which is an Insert process.  

Nevertheless, the routine ran through without an exception after limiting the code to what you see above (in this post), but also did not update the database.

Please consider my post above at "09:40PM CST" and post your thoughts on that one.  In my UpdateWatchFiles() routine, I can add records in my UI (a datagrid) and they are inserted into the database with just the code you see there.  UpdateRecords() seems that it should do likewise.
ASKER CERTIFIED SOLUTION
Avatar of ptakja
ptakja
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of colepc

ASKER

Here's what I've learned.  It's wierd.  I can populate a dataset.table with rows from a text (csv) file (see "ReadCSV()" below).  Calling "UpdateRecords()" after filling the datatable does not succeed in updating the database.  If I immediately MANUALLY add a row to the same dataset.table (increasing the rows.count by 1), and then call "UpdateRecords()" again, the manually added row DOES get added to the database.  

This is absolutely baffling.  I've pasted in my code below (actual running code).  I'll be happy to forward anyone who want's to see it my .sln project zipped up.  It's only about 43KB including the reference database and csv file.  I'll willing to send a solution provider a paypal contribution to the person who solves this!


   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dsAppData = New DataSet("dataset")
        Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=collector.mdb;User ID=;Password="
        Dim oOLEDBCommand As OleDbCommand
        Dim strSQL As String
        Dim oOLEDBConnection As OleDbConnection = New OleDbConnection(sConnString)
        oOLEDBConnection.Open()
        strSQL = "select * from tblDatRecords"
        myda_Records = New OleDbDataAdapter(strSQL, oOLEDBConnection)
        myda_Records.Fill(dsAppData, "CurrentBatch")
        oOLEDBConnection.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ReadCSV()
        UpdateRecords()

        dsAppData.Tables("CurrentBatch").Rows.Add(New Object() {CStr(Now()), "My Address 1", "My Address 2", "My City"})
        UpdateRecords()
    End Sub

    Sub ReadCSV()
            Dim batchConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
            Dim batchConn As New OleDbConnection(batchConnectionString)
            batchConn.Open()
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM ezpay.dat", batchConn)
            Dim objAdapter1 As New OleDbDataAdapter(objCmdSelect)
            objAdapter1.Fill(dsCSV, "myCSV")
            batchConn.Close()
            Dim myRow As DataRow
    End Sub

    Public Sub UpdateRecords()
            Dim db As OleDbCommandBuilder = New OleDbCommandBuilder(myda_Records)
            myda_Records.Update(dsAppData, "CurrentBatch")
    End Sub
Avatar of colepc

ASKER

In fact, the zipped solution can be grabbed here...  http://www.colepc.com/test_DA_UpdateRecords problem.zip
Avatar of colepc

ASKER

I've narrowed it down to this code snippet:

  objAdapter1.Fill(dsAppData, "CurrentBatch")      'COMMENT: this line added 546 rows to the object dsAppData.Tables("CurrentBatch")
  ret = dsAppData.HasChanges                           'COMMENT: yet this line returns False (no changes).
 
Under what conditions would a dataset that just had rows added to it return a value of False for "HasChanges"?

Avatar of colepc

ASKER

ITS SOLVED!!!!!   See   https://www.experts-exchange.com/questions/21244494/Adding-rows-to-a-dataset-table-does-not-cause-HasChanges-to-become-True.html


In short, the dataadapter.fill(mydataset)method was setting "AcceptChanges" on each row as it was being added to the dataset.  The UpdateRecords sub thought there was nothing to be done to the dataset so nothing got added.

Thanks for your help!!