Selected Value in ComboBox Populates DataGridView

[Neogortex]

'I need assistance on how to Load the GridView By selecting a value in the ComboBox
1. ComboBox is loaded with Satellite SCC #s' From SQL Server 2005 Express

2. The DatGridView is loaded with signals that correspond to the Transponders on the Satellite From SQL Server Express 2005. The GridView also contains the Satellite SCC #s'.

3. I need the GridView to only load the Signal that Matches the Satellite SCC #s' that are Selected from the ComboBox.

4. So when you click the SCC # in the ComboBox, it will search the Signal Database Table for all the matching
Satellite SCC #s' and then load the signal information into the GridView.

DataBase: SQL Server 2005 express
Language: VB.NET 2005
FrameWork: .NET 2.0

Imports System.Data
Imports System.Data.SqlClient

PublicClass frmCboBoxTest

'Declare Object and Database Connection String
Dim objConnection AsNew SqlConnection _
("server=OFFSITE-223181S\SQLEXPRESS;database=TemDb;Trusted_Connection=yes;")


'SELECT statement for the ComboBox
Dim objDataAdapter2 AsNew SqlDataAdapter( _
"SELECT Satellite.SatelliteSccNum4 From Satellite", objConnection)


'SELECT statement for DataGridView And
'JOIN WHERE Signal Table Transponder ID is Equal to Transponder Table Transponder ID
Dim objDataAdapter AsNew SqlDataAdapter( _
"SELECT * FROM Signal, objConnection)

'ComboBox1 connection Information
Dim objDataSet2 As DataSet
Dim objDataView2 As DataView

'DataGrid connection information
Dim objDataSet As DataSet
Dim objDataView As DataView

PrivateSub FillDataSetAndView()

'ComboBox1
objDataSet2 = New DataSet
objDataView2 = New DataView(objDataSet2.Tables("Satellite"))

'DataGridView
objDataSet = New DataSet
objDataView = New DataView(objDataSet.Tables("Signal"))


'Fill the ComboBox with data
objDataAdapter2.Fill(objDataSet2, "Satellite")

'Fill the DataGrid with data
objDataAdapter.Fill(objDataSet, "Signal")

EndSub

PrivateSub BindFields()

'Clear any previous bindings
ComboBox1.DataBindings.Clear()
grdCboTest.DataBindings.Clear()

'Set the ComboBox properties to bind it to the data and
'Add Satellite SCC #s' from the Satellite DB
ComboBox1.DataSource = objDataSet2.Tables("Satellite")
ComboBox1.DisplayMember = "SatelliteSccNum4"
ComboBox1.ValueMember = "SatelliteSccNum4"

EndSub

PrivateSub ComboBox1_SelectedIndexChanged( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

If ComboBox1.SelectedIndex Then

'Open the database connection
objConnection.Open()

'Set the DataGridView properties to bind it to the data
grdCboTest.AutoGenerateColumns = True
grdCboTest.DataSource = objDataSet
grdCboTest.DataMember = "Signal"

'Close the Database connection
objConnection.Close()

'Fill the dataset and bind the fields
FillDataSetAndView()
BindFields()

EndIf

EndSub

PrivateSub frmCboBoxTest_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load

'Fill ComboBox and DataGridView with data from DB at Load
FillDataSetAndView()
BindFields()

'Set ComboBox Selected index to the 1st position or 0 index at Load
ComboBox1.SelectedIndex = 0

EndSub

PrivateSub btnExit_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click

'Close application when btnExit is Clicked
Me.Close()

EndSub

EndClass

 

[jebenson]

Don't grab the data every time the user clicks on the combobox. Instead, just grab all of the data once and use a DataView to populate the grid. Then, when the users click on the combobox set the DataView's RowFilter property to display only the matching records. You are almost there...you have DataViews defined, but don't seem to be actually using them. Here's an example:

PrivateSub FillDataSetAndView()

'ComboBox1
objDataSet2 = New DataSet
objDataView2 = New DataView(objDataSet2.Tables("Satellite"))

'DataGridView
objDataSet = New DataSet


'Fill the ComboBox with data
objDataAdapter2.Fill(objDataSet2, "Satellite")

'Fill the DataGrid with data
objDataAdapter.Fill(objDataSet, "Signal")

objDataView = New DataView(objDataSet.Tables("Signal"))

'set the RowFilter to some value that is NOT in the data,
'to show no records until a selection is made
[red]objDataView.RowFilter = "SatelliteSccNum4=-9999999"[/red]

EndSub

PrivateSub BindFields()

'Clear any previous bindings
ComboBox1.DataBindings.Clear()
grdCboTest.DataBindings.Clear()

'Set the ComboBox properties to bind it to the data and
'Add Satellite SCC #s' from the Satellite DB
ComboBox1.DataSource = objDataSet2.Tables("Satellite")
ComboBox1.DisplayMember = "SatelliteSccNum4"
ComboBox1.ValueMember = "SatelliteSccNum4"

'assign the dataview to the datagrid
[red]grdCboTest.DataBindings.DataSource = objDataView[/red]

EndSub

PrivateSub ComboBox1_SelectedIndexChanged( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

If ComboBox1.SelectedIndex Then
'Set the dataview's RowFilter to the currently selected value
objDataView.RowFilter = "SatelliteSccNum4=" & ComboBox1.SelectedValue
EndIf

EndSub

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Neogortex]

jebenson, I am having an issue applying your code. What Framework are you using?

'I can not apply this suggestion
grdCboTest.DataBindings.DataSource = objDataView

'I can go as far as:
grdCboTest.DataBindings = objDataView

'After grdCboTest.DataBindings Visual Studio does not give me the option to place .DataSource after the
grdCboTest.DataBindings

'Any other suggestions would be greatly appreciated

Thanks In Advance, Mike

 

[jebenson]

Sorry, I was typing the code in the form not in visual studio.

It should be:

grdCboTest.DataSource = objDataView

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira