[RESOLVED] Adding a row to a datatable

[MechEng]

I have two questions regarding adding rows to datatables:

First Question: When adding a new row to a datatable using the following code:

TblMatchDataBindingSource.CancelEdit()
TblMatchDataBindingSource.AddNew()

‘This next line is where I get the error (these lines were commented out for the second test described below)
Dim Row As sqlScoreBoardDBDataSet.tbl_MatchDataRow = SqlScoreBoardDBDataSet.tbl_MatchData(TblMatchDataBindingSource.Position)
Row.GameIndex = int_GameIndex
Call LoadScoreBoard()

The results are as follows: The row is added (I can see that my forms binding navigator incremented), but when trying to address the new row position to write data to the table, I get an error indicating that a row does not exist at position (what ever the next position is). Oddly enough, if after adding the row, I use the binding navigator and move down one position and back up, I will not get that error when trying to write data to the new row. I am sure that it is something simple, but I just can not figure it out.

Question 2: My work around for the above issue is to use the following code which works fine.

Dim myRow As DataRow = Me.SqlScoreBoardDBDataSet.tbl_MatchData.NewRow()
Me.SqlScoreBoardDBDataSet.tbl_MatchData.Rows.Add(myRow)
TblMatchDataBindingSource.MoveLast()

‘No error here… all is well
Dim Row As sqlScoreBoardDBDataSet.tbl_MatchDataRow = SqlScoreBoardDBDataSet.tbl_MatchData(TblMatchDataBindingSource.Position)
Row.GameIndex = int_GameIndex
Call LoadScoreBoard()

My question here is: Is this a bad way to add data rows and edit data? Is there a better more correct way to add rows to a datatable?

Thanks for your help.

[jmcilhinney]

When you call the AddNew method of the BindingSource it will create a new DataRow object by calling the bound DataTable's NewRow method. Just like if you had called NewRow yourself, its RowState is initially Detached because it is NOT part of the DataTable. When you navigate away from that new row the BindingSource's EndEdit method is called, which will then call the DataTable's Rows.Add method. The DataRow is added to the DataTable and its RowState becomes Added.

You can call the EndEdit explicitly yourself to force the row to be added to the table, but you should be aware that there are several reasons that it is done this way. Firstly, when the row is created it fields will all be empty unless they have default values. If any of those empty fields are not allowed to have null values then adding the row to the table at that point will throw an exception. Secondly, it also allows you to call the BindingSource's CancelEdit method to discard the new row without ever adding it to the table. Pressing the Escape key in a bound DataGridView, for instance, will implicitly call CancelEdit.

When you are using a BindingSource you are NOT supposed to access the DataTable directly anyway. The AddNew method returns the new item that was created. If the BindingSource is bound to a DataTable that item will be a DataRowView for the new DataRow. You can set all the fields via that DataRowView, or you can get its Row property and set them via the DataRow itself. Just note that AddNew returns an Object reference, because the BindingSource can be bound to lists other than DataTable's, so you need to cast it, e.g.

Dim row As DataRowView = DirectCast(myBindingSource.AddNew(), DataRowView)
 
row("FirstName") = "John"
row("LastName") = "Smith"
myBindingSource.EndEdit()

[MechEng]

That is an excellent answer to my question. Thanks jmcilhinney.

Now that I have that little nugget of understanding, I am questioning the way I have been approaching datatables altogether. I have consistently been addressing datatables directly even though they have a bindingsource attached. This seams to work fine in that there are no errors and every thing seams to update correctly. Below is an example of how I have been addressing the datatables using the bindingsource.position.

New question: Based upon your previous comments I now wonder if I should go back and revise the code to use the bindingsource methods to read/write data to the datatable. I am about 2500 lines into what will be a 20K line project… should I recode? Is there a compelling reason to do so?

Thanks very much. You are very good at explaining.

    Private Sub Button23_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button23.Click
        'UMC   Unsporstman like conduct- Team Penalty Second offence

        Dim DRow As sqlScoreBoardDBDataSet.tbl_DefaultDataRow = SqlScoreBoardDBDataSet.tbl_DefaultData(0)
        Dim Row As sqlScoreBoardDBDataSet.tbl_MatchDataRow = SqlScoreBoardDBDataSet.tbl_MatchData(TblMatchDataBindingSource.Position)
        Dim GameRow As sqlScoreBoardDBDataSet.tbl_GameDataRow = SqlScoreBoardDBDataSet.tbl_GameData(int_GameRow)

        Dim str_TempColumn As String = "DefaultTeamMC2"
        Dim int_TempDefaultScore As Integer = CInt(DRow.Item(str_TempColumn))
        Dim strScoredesc As String = "UCT" & Trim(Str(int_TempDefaultScore))

        Call UpdateScoreSummaryRed(strScoredesc)

        If IsDBNull(GameRow.Item("redTeamPenaltySummary")) Or GameRow.Item("RedTeamPenaltySummary") Is "" Then
            GameRow.RedTeamPenaltySummary = strScoredesc
        Else
            GameRow.RedTeamPenaltySummary = GameRow.RedTeamPenaltySummary & " " & strScoredesc
        End If

        If IsDBNull(GameRow.Item("RedTeamPenaltyPoints")) Then
            GameRow.RedTeamPenaltyPoints = int_TempDefaultScore
        Else
            GameRow.RedTeamPenaltyPoints += int_TempDefaultScore
        End If

        Me.TableLayoutPanelRedPenalty.Visible = False
        Me.TableLayoutPanelRedTeamPenalty.Visible = False

        Call UpdateTeamScores()
    End Sub

[jmcilhinney]

The BindingSource was created to be the one-stop shop for accessing bound data. In future you should always access the data via the BindingSource, rather than the Datatable or the bound control(s), unless you have a compelling reason to do otherwise. If you are not sorting or filtering your data then there's no need to change your existing code. It's critical to note, though, that if you do sort or filter the data then the index of a row in the BindingSource is likely to be different to the index of the corresponding row in the DataTable. If you use the BindingSource.Position property to index the DataTable then you'll be accessing the wrong row most of the time in that case.

[MechEng]

Thanks for the quick reply. I will be indexing and sorting data (later code), so I guess I will go back and revise my code. This will be a painful lesson. But better to do it now… before I get too deep. Thanks again.