Question Update dataSet...

[lidds]

I have a storedProcedure that populates a dataset, this dataset is then used for a dataSource for a third party grid control. This all works fine, however I am having real trouble in updating the dataSet with the most recent data from my database.

Below is the code that I am using to populate the dataSet:

Dim myDA As New OleDb.OleDbDataAdapter("spQryParentComments", myDB.myConn)
            myDA.SelectCommand.CommandType = CommandType.StoredProcedure
            myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = "Ticodi"
            myDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@userID", OleDb.OleDbType.VarChar)).Value = "2"
            Dim dataParentTable As DataTable = New DataTable("Parent")

            myDA.Fill(dataParentTable)

            Dim myChildDA As New OleDb.OleDbDataAdapter("spQryChildComments", myDB.myConn)
            myChildDA.SelectCommand.CommandType = CommandType.StoredProcedure
            myChildDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@projName", OleDb.OleDbType.VarChar)).Value = "Ticodi"
            myChildDA.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@userID", OleDb.OleDbType.VarChar)).Value = "2"

            Dim dataChildTable As DataTable = New DataTable("Child")

            myChildDA.Fill(dataChildTable)

            Me.ReviewInSightDataSet.Tables.Add(dataParentTable)
            Me.ReviewInSightDataSet.Tables.Add(dataChildTable)

            Dim keyColumn As DataColumn = Me.ReviewInSightDataSet.Tables("Parent").Columns("commID")
            Dim foreignKey As DataColumn = Me.ReviewInSightDataSet.Tables("Child").Columns("commID")
            Me.ReviewInSightDataSet.Relations.Add("Comment Revision", keyColumn, foreignKey)

            Me.lstComments.DataSource = Me.ReviewInSightDataSet.Tables("Parent")

I am not sure how to update the dataSet with new data so that my grid control displays the most recent data. Could someone show me the code to do this?

Thanks in advance

Simon

 

[jmcilhinney]

If you want your DataSet to contain the latest data from the database then you have to Fill it with the latest data. That means executing a query, exactly as you are there. Now, you can either discard all the current data and get everything, which is easier but less efficient, or else you can get just the data that has changed since the last time you retrieved it. The second option requires that your table(s) has a timestamp column that gets updated each time a row is changed. That way you can just retrieve the data where that column is greater than the greatest value you already have.

 

[cjard]

Do bear in mind that you cannot Fill a parent table while child rows exist, because a fill initiates a clear() which would leave orphans

Curious as to why you dont set up this dataset and relationship permanently in a strongly typed dataset..