DataGridView repeating extra row???

[blakemckenna]

I have a datagridview that is being loaded from a dataset. This dataset is the result of a SQL Statement (which could be my problem). The MS Access Client table is the main table and contains 2 records, however, after loading the dataset, the row count is 3.

Below is the procedure where I load the dataset and then the DGV.

    Public Function LoadClientDataSet()
        Try
            strSQL = "SELECT A.clientID, A.name, B.phone, B.zipCode, B.stateCD " & _
                       "FROM (tblClients A LEFT JOIN tblClientAddr B ON A.addrType = B.addrID) " & _
                                          "LEFT JOIN tblAddressCodes C ON B.addrID = C.addrID " & _
                                  "WHERE A.clientID <> NULL"

            Dim clientADP As New OleDb.OleDbDataAdapter(strSQL, oConn)

            clientADP.Fill(rsClientGrid, "tblClients")

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function
    '
    '
    '
    Private Sub LoadClientGrid()
        Try
            'grdClientList.Rows.Clear()

            For Each row As DataRow In rsClientGrid.Tables("tblClients").Rows
                Dim grdRow As New DataGridViewRow
                Dim dgvCell As DataGridViewCell

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = row.Item(0)
                grdRow.Cells.Add(dgvCell)

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = row.Item(1)
                grdRow.Cells.Add(dgvCell)

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = row.Item(2)
                grdRow.Cells.Add(dgvCell)

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = row.Item(3)
                grdRow.Cells.Add(dgvCell)

                dgvCell = New DataGridViewTextBoxCell()
                dgvCell.Value = row.Item(4)
                grdRow.Cells.Add(dgvCell)

                grdClientList.Rows.Add(grdRow)
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

The Access table "tblAddressCodes" is a lookup table and the "tblClientAddr" contains only 2 records and only 1 of them should be returned in the SQL Statement.

Hope this makes sense.

Thanks,

[jmcilhinney]

I don't really understand why you would populate a DataTable and then add the data like that. Why not simply bind the data to the grid by assigning it to the DataSource?

As for record counts, what is the return value of your Fill call? What is the value of the Rows.Count property of your DataTable? What is the value of the RowCount property of your DGV?

[blakemckenna]

jmc,

I know your suggesting one way, but their seems to be so many ways of doing 1 thing. Obviously, I wish to use the easiest and most efficient method, which in this case, I'm just not sure what that method is.

As far as the counts go, after the fill, there are 3 rows in the DS.

[jmcilhinney]

I know your suggesting one way, but their seems to be so many ways of doing 1 thing. Obviously, I wish to use the easiest and most efficient method, which in this case, I'm just not sure what that method is.

The easiest method is data-binding. At it's simplest, as I think it would be in your case, it's one line of code: you assign the DataTable to the grid's DataSource and you're done. You can also configure data-binding to do much more if required.

As far as the counts go, after the fill, there are 3 rows in the DS.

I believe that I asked for three different values and you've only provided one. If I ask a question it's for a reason. If you'd like me to help further then please provide the information I've asked for.

[blakemckenna]

jmc,

I'm not using a DataTable. As far as the DGV rowcount, it's 4 and the last row is empty. Also, after looking at the DGV after it's been populated, here is what the columns equate to. This is just sample data.

              Col 1         Col 2         Col 3                Col 4         Col 5
 Row 1    00001         ABC, Inc.    (123) 456-7890          45459         OH
 Row 2    00001         ABC, Inc.    (222) 333-4444          90210         CA
 Row 3    00005         Test Co.       empty                     empty       empty
 Row 4    empty           empty        empty                     empty       empty

As far as using a BindingSource, how do I set the datasource when the SQL statement is using 3 different tables?

[jmcilhinney]

I'm not using a DataTable.

You're filling a DataSet, getting an item from its Tables collection of that DataSet, then looping through the DataRows in that item's Rows collection. If the Rows collection of that table contains DataRows, what do you suppose the Tables collection of the DataSet contains? I suggest that you read this.

As far as the DGV rowcount, it's 4 and the last row is empty. Also, after looking at the DGV after it's been populated, here is what the columns equate to. This is just sample data.

              Col 1         Col 2         Col 3                Col 4         Col 5
 Row 1    00001     ABC, Inc.    (123) 456-7890    45459         OH
 Row 2    00001     ABC, Inc.    (222) 333-4444    90210         CA
 Row 3    00005     Test Co.       empty               empty       empty
 Row 4    empty       empty        empty               empty       empty

Are you doing this on purpose? I've twice now asked for three values and you have still only provided two of them. If I ask someone for help and they ask me for more information, as a matter of course I either provide that information or an explanation of why I am unable to. I suggest that you adopt the same approach because otherwise you can really frustrate those who are trying to help. Making someone ask the same question three times is not the way to get a solution to your problem. Please tell me what the return value of your Fill call is.

As far as using a BindingSource, how do I set the datasource when the SQL statement is using 3 different tables?

BindingSources have nothing to do with SQL statements. A BindingSource is simply an object that facilitates binding a list to one or more controls. How many lists do you have? You have one list. The fact that the data in that list came from three different database tables is of no concern to the BindingSource. It doesn't care where the data came from.

[sapator]

Also.
Have you checked the SQL statement on SQL to see what it returns?
Check the Left Joins they are tricky sometimes on the values they provide.

[blakemckenna]

There are only 2 row counts because I'm NOT using a DataTable. If this is to much trouble, just don't reply again.

[jmcilhinney]

There are only 2 row counts because I'm NOT using a DataTable. If this is to much trouble, just don't reply again.

OK, you ARE using a DataTable. This:

For Each row As DataRow In rsClientGrid.Tables("tblClients").Rows

is a DataTable. It contains DataRows and its in the Tables collection of a DataSet; what else would it be?

Try reading what's posted:

As for record counts, what is the return value of your Fill call? What is the value of the Rows.Count property of your DataTable? What is the value of the RowCount property of your DGV?

You've answered the green question:

As far as the counts go, after the fill, there are 3 rows in the DS.

DataSets don't contain any rows. DataSets contains DataTables and DataRelations. The DataTables contain the DataRows. If there are 3 rows in the DataSet then those 3 rows are in your DataTable.

You've answered the blue question:

As far as the DGV rowcount, it's 4 and the last row is empty.

You are yet to answer the red question. The red question has nothing to do with DataTables so whether you're using one or not is immaterial. All I'm asking for is the return value of the Fill method.

Just so you know, that number will tell us exactly how many records are returned when the query is executed. Knowing the number of rows in the DataTable is all well and good but maybe two of those three rows were already there. If you tell me that Fill returns 3 then I can tell you that, if you expect your query to retrieve one row, it's your query that's faulty and you can stop looking elsewhere. If I ask for information I ask for it for a reason. If you want help solving your problem then why would you not provide it?

[blakemckenna]

I don't know how to get the "Fill" count? What do I need to do?

[jmcilhinney]

I don't know how to get the "Fill" count? What do I need to do?

There we go. If you don't know then you don't know but if you don't say so then I don't know that you don't know. It just seems like you're ignoring my question.

Fill is a function. You get its return value like you do any other function. This is from the MSDN documentation for the particular overload of OleDbDataAdapter.Fill that you're calling:

Dim instance As DbDataAdapter
Dim dataSet As DataSet
Dim srcTable As String
Dim returnValue As Integer

returnValue = instance.Fill(dataSet, srcTable)

[zuzu22]

Hmmm... even it's a quite old thread, I faced the same problem these hours.
The correct answer was setting the dgv.AllowUserToAddRows = False, otherwise, dgv will automatically add a blank * row at the end for inserting

Cheers,
Edouard Gora, YO3HCV

[Spybot]

That was a great answer, I was just passing through the same issue and I solved it with this post.