how to populate my unbound datagridview after update of a combo

[annarene]

Hello: I have an unbound datagridview and a seperate dropdown outside of the grid. When the user makes a selection from this dropdown, I would like to filter on their selection in order to repopulate my grid. I searched through all the postings in the forum but only found one post similar...however it had to do with a bound datagrid.

All my cells in the datagrid are of DataGridViewComboBoxColumn type.

OnChange of my drop-down ...I've started this code:


'onChange on my drop-down:

Private Sub cboProfiles_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProfiles.SelectionChangeCommitted
If Not IsNothing(cboProfiles.SelectedValue) Then
Dim sql As String = ""
sql = "SELECT tblProfileName.ProfileId, tblProfileName.ProfileName, tblStates.StateId,tblStates.StateName, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1,tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2,tblStates.Flash2, tblStates.Zone3, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4,tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblProfileName,tblStates where tblProfileName.ProfileId = tblStates.ProfileId and tblStates.ProfileId" & cboProfiles.SelectedValue.ToString()
Dim myDB As New OleDb.OleDbConnection(sConnectionString)
Dim myDA As New OleDb.OleDbDataAdapter("SELECT tblProfileName.ProfileId, tblProfileName.ProfileName, tblStates.StateId,tblStates.StateName, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1,tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2,tblStates.Flash2, tblStates.Zone3, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4,tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblProfileName,tblStates where tblProfileName.ProfileId = tblStates.ProfileId and tblStates.ProfileId=" & cboProfiles.SelectedValue.ToString(), myDB)
Dim myDS As New Data.DataSet
Dim myDR As Data.DataRow

Try
myDA.Fill(myDS, "profiles")
Dim profiles As DataTable
profiles = myDS.Tables("profiles")

If profiles.Rows.Count = 1 Then
'first, populate other textboxes that are not in the grid

For Each myDR In profiles.Rows

txtZone1.Text = myDR("Zone1")
txtZone2.Text = myDR("Zone2")
txtZone3.Text = myDR("Zone3")
txtZone4.Text = myDR("Zone4")
txtState1.Text = myDR("Statename")

'now, populate the grid...for now, just trying to just get one of my cells (DataGridViewComboBoxColumn) to set it's selected index to value from filtered ds:
With Me.DataGridView1.Rows
Dim cbn As DataGridViewComboBoxColumn = DataGridView1.Columns(0)

With cbn
.ValueMember = myDR("Color1")
End With
End With
Next
End If
myDB.Close()
Catch
MsgBox("An error occurred")
End Try
Else
End If
End Sub



Can anyone point me in the right dirrection? I'm just not sure how to set the value for my DataGridViewComboBoxColumn
controls in the grid.

Thank you for your help,
annarene

 

[Qik3Coder]

Your datagridview HAS rows
Your Rows HAVE items
Those Items HAVE a value, which should be able to be cast to ComboBoxes
Those comboBoxes HAVE a Selected(Index)/(Value)/(Item)/(Text)

You will wind up with something like

CType(dgvMyGrid.Rows(intCounter).Items("MyColumn).value, GetType(ComboBox)).SelectedItem = myTmpValue

-Sometimes the answer to your question is the hack that works

 

[annarene]

Qik3Coder: thanks for your help.... I was able to make it work using the following code:

Private Sub cboProfiles_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProfiles.SelectionChangeCommitted
If Not IsNothing(cboProfiles.SelectedValue) Then
Dim sql As String = ""
sql = "SELECT tblStates.StateId, tblStates.StateName, tblStates.ProfileId, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1, tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2, tblStates.Flash2, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3,tblStates.Zone3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4, tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblStates where profileid=" & cboProfiles.SelectedValue.ToString()
Dim myDB As New OleDb.OleDbConnection(sConnectionString)
Dim myDA As New OleDb.OleDbDataAdapter("SELECT tblStates.StateId, tblStates.StateName, tblStates.ProfileId, tblStates.Zone1, tblStates.Color1, tblStates.Brightness1, tblStates.Flash1, tblStates.Zone2, tblStates.Color2, tblStates.Brightness2, tblStates.Flash2, tblStates.Color3, tblStates.Brightness3, tblStates.Flash3, tblStates.Zone3, tblStates.Zone4, tblStates.Color4, tblStates.Brightness4, tblStates.Flash4, tblStates.SpeedId, tblStates.Wheel FROM tblStates where profileid=" & cboProfiles.SelectedValue.ToString(), myDB)
Dim myDS As New Data.DataSet
Dim myDR As Data.DataRow

Try
myDA.Fill(myDS, "profiles")
Dim profiles As DataTable
profiles = myDS.Tables("profiles")

If profiles.Rows.Count > 0 Then
Dim i As Integer = 0
For Each myDR In profiles.Rows
Select Case i
Case 0
txtProfileId.Text = myDR("ProfileId")
txtZone1.Text = myDR("Zone1")
txtZone2.Text = myDR("Zone2")
txtZone3.Text = myDR("Zone3")
txtZone4.Text = myDR("Zone4")
txtState1.Text = myDR("Statename")
Case 1
txtState2.Text = myDR("Statename")
Case 2
txtState3.Text = myDR("Statename")
Case 3
txtState4.Text = myDR("Statename")
Case 4
txtState5.Text = myDR("Statename")
Case 5
txtState6.Text = myDR("Statename")
Case 6
txtState7.Text = myDR("Statename")
Case 7
txtState8.Text = myDR("Statename")
End Select

DataGridView1.Item("C1", i).Value = myDR("Color1")
DataGridView1.Item("B1", i).Value = myDR("Brightness1")
DataGridView1.Item("F1", i).Value = myDR("Flash1")

DataGridView1.Item("C2", i).Value = myDR("Color2")
DataGridView1.Item("B2", i).Value = myDR("Brightness2")
DataGridView1.Item("F2", i).Value = myDR("Flash2")

DataGridView1.Item("C3", i).Value = myDR("Color3")
DataGridView1.Item("B3", i).Value = myDR("Brightness3")
DataGridView1.Item("F3", i).Value = myDR("Flash3")

DataGridView1.Item("C4", i).Value = myDR("Color4")
DataGridView1.Item("B4", i).Value = myDR("Brightness4")
DataGridView1.Item("F4", i).Value = myDR("Flash4")
i = i + 1
Next
End If
myDB.Close()
Catch
MsgBox("An error occurred")
End Try
Else
End If
End Sub


The main line being:

DataGridView1.Item("C1", i).Value = myDR("Color1")

I tried casting it as suggested, but it didn't like GetType and wasn't sure what to do..I also played a bit with trying to set it as selected, but it didn't like that either. Finally, I tried just doing the above line and it's works great.

Do you think the way I did it is ok?

thanks again for helping me...I really appreciate it.
annarene

 

[Qik3Coder]

For Each myDR In profiles.Rows
                        Select Case i
                            Case 0
                                txtProfileId.Text = myDR("ProfileId")
                                txtZone1.Text = myDR("Zone1")
                                txtZone2.Text = myDR("Zone2")
                                txtZone3.Text = myDR("Zone3")
                                txtZone4.Text = myDR("Zone4")
                                txtState1.Text = myDR("Statename")
                            Case 1
                                txtState2.Text = myDR("Statename")
                            Case 2
                                txtState3.Text = myDR("Statename")
                            Case 3
                                txtState4.Text = myDR("Statename")
                            Case 4
                                txtState5.Text = myDR("Statename")
                            Case 5
                                txtState6.Text = myDR("Statename")
                            Case 6
                                txtState7.Text = myDR("Statename")
                            Case 7
                                txtState8.Text = myDR("Statename")
                        End Select

This probably isn't going to do what you want. It will leave the last select row data in the text box. You want to make sure to clear out the old values, so that you don't overlap datavalues from the last one.

Looks good, but you might want subs and functions to break out your logic.

Good job.

-Sometimes the answer to your question is the hack that works

 

[annarene]

Qik3Coder: you are absolutely right...when I started testing, I noticed that the last row of my grid wasn't clearing...so created a ClearForm function to call first. However, it's still not clearing out my grid.

Here's my code:

Private Sub ClearForm()
txtState1.Text = ""
txtState2.Text = ""
txtState3.Text = ""
txtState4.Text = ""
txtState5.Text = ""
txtState6.Text = ""
txtState7.Text = ""
txtState8.Text = ""
txtZone1.Text = ""
txtZone2.Text = ""
txtZone3.Text = ""
txtZone4.Text = ""

'Tried this first:
'DataGridView1.ClearSelection()

'Tried this next:

For j As Integer = 0 To (Me.DataGridView1.RowCount - 1) - 1
DataGridView1.Item("C1", j).Value = ""
DataGridView1.Item("B1", j).Value = ""
DataGridView1.Item("F1", j).Value = ""

DataGridView1.Item("C2", j).Value = ""
DataGridView1.Item("B2", j).Value = ""
DataGridView1.Item("F2", j).Value = ""

DataGridView1.Item("C3", j).Value = ""
DataGridView1.Item("B3", j).Value = ""
DataGridView1.Item("F3", j).Value = ""

DataGridView1.Item("C4", j).Value = ""
DataGridView1.Item("B4", j).Value = ""
DataGridView1.Item("F4", j).Value = ""
Next

End Sub

How come my dg won't clear out? Does it work differently for unbound grids?

thanks again for your help,
annarene

 

[Qik3Coder]

You can put your code in a block by using the hard brackets
with the word CODE and end it with /CODE

 For Each myDR In profiles.Rows
                        Select Case i
...


                        DataGridView1.Item("C1", i).Value = myDR("Color1")
                        DataGridView1.Item("B1", i).Value = myDR("Brightness1")
                        DataGridView1.Item("F1", i).Value = myDR("Flash1")

                        DataGridView1.Item("C2", i).Value = myDR("Color2")
                        DataGridView1.Item("B2", i).Value = myDR("Brightness2")
                        DataGridView1.Item("F2", i).Value = myDR("Flash2")

Not sure whats going on here:
You have to construct a new datarow and add that datarow to your datagridview

 For Each oRow As DataGridViewRow In dgv.Rows
            If Not blnVisibleRowsOnly Or (blnVisibleRowsOnly And oRow.Visible = True) Then
                dt.Rows.Add()
                Dim intCounter As Integer = 0
                For Each oCell As DataGridViewCell In oRow.Cells
                    dt.Rows(dt.Rows.Count - 1).Item(intCounter) = oCell.Value
                    intCounter += 1
                Next
            End If

-Sometimes the answer to your question is the hack that works

 

[annarene]

Thanks once again for your help.

here's my code to clear it out:

        DataGridView1.Rows.Clear()

        Private Sub LoadDataGrid_New()
        Dim row0 As String() = {""}
        Dim row1 As String() = {""}
        Dim row2 As String() = {""}
        Dim row3 As String() = {""}
        Dim row4 As String() = {""}
        Dim row5 As String() = {""}
        Dim row6 As String() = {""}
        Dim row7 As String() = {""}

        With Me.DataGridView1.Rows
            .Add(row0)
            .Add(row1)
            .Add(row2)
            .Add(row3)
            .Add(row4)
            .Add(row5)
            .Add(row6)
            .Add(row7)
        End With
    End Sub