Hey rockovo,
Please refer below sample.
Namespaces
C#
using System.Data.SqlClient;
VB.Net
Imports System.Data.SqlClient
Code
C#
private void Form1_Load(object sender, EventArgs e)
{
this.PopulateComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country");
this.PopulateComboBox(cbCities, "SELECT DISTINCT City FROM Employees", "City", "City");
this.PopulateDataGridView();
}
private void PopulateComboBox(ComboBox comboBox, string query, string displayMember, string valueMember)
{
string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
DataRow row = dt.NewRow();
dt.Rows.InsertAt(row, 0);
comboBox.DataSource = dt;
comboBox.DisplayMember = displayMember;
comboBox.ValueMember = valueMember;
}
}
}
}
private void PopulateDataGridView()
{
string query = "SELECT EmployeeId, Country, City FROM Employees";
query += " WHERE Country = @Country AND City = @City";
query += " OR ISNULL(@Country, '') = ''";
query += " OR ISNULL(@City, '') = ''";
string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@Country", cbCountries.SelectedValue);
cmd.Parameters.AddWithValue("@City", cbCities.SelectedValue);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
}
private void Search(object sender, EventArgs e)
{
this.PopulateDataGridView();
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Me.PopulateComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country")
Me.PopulateComboBox(cbCities, "SELECT DISTINCT City FROM Employees", "City", "City")
Me.PopulateDataGridView()
End Sub
Private Sub PopulateComboBox(ByVal comboBox As ComboBox, ByVal query As String, ByVal displayMember As String, ByVal valueMember As String)
Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Dim row As DataRow = dt.NewRow()
dt.Rows.InsertAt(row, 0)
comboBox.DataSource = dt
comboBox.DisplayMember = displayMember
comboBox.ValueMember = valueMember
End Using
End Using
End Using
End Sub
Private Sub PopulateDataGridView()
Dim query As String = "SELECT EmployeeId, Country, City FROM Employees"
query += " WHERE Country = @Country AND City = @City"
query += " OR ISNULL(@Country, '') = ''"
query += " OR ISNULL(@City, '') = ''"
Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@Country", cbCountries.SelectedValue)
cmd.Parameters.AddWithValue("@City", cbCities.SelectedValue)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
dataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Private Sub Search(ByVal sender As Object, ByVal e As EventArgs)
Me.PopulateDataGridView()
End Sub
Screenshot