i have a datagridview and a combobox. the combobox is auto populated by values from a database. i need to filter the datagridview using combobox. this is the code that i've done. it shows a error : Cannot find column [year]. can you help me???
Imports MySql.Data.MySqlClient
Public Class ReportGradeLevel
Dim MySqlConnection As MySqlConnection
Dim dbDataSet As New DataTable
Private Sub ReportGradeLevel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
MySqlConnection = New MySqlConnection
MySqlConnection.ConnectionString = "server = localhost; port=3307; user id = root; password = 1234; database = mcs;"
Dim SDA As New MySqlDataAdapter
Dim bSource As New BindingSource
Try
MySqlConnection.Open()
Dim query As String
query = "SELECT DISTINCT year FROM mcs.year "
Dim da As New MySqlDataAdapter(query, MySqlConnection)
Dim ds As New DataSet
da.Fill(ds, "mcs.year")
With ComboBox1
.DataSource = ds.Tables("mcs.year")
.DisplayMember = "year"
.ValueMember = "year"
End With
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
MySqlConnection.Dispose()
End Try
Try
MySqlConnection.Open()
Dim query As String
query = "select student_no, last_name, first_name, middle_name, grade, year from mcs.student "
Dim Command As New MySqlCommand(query, MySqlConnection)
SDA.SelectCommand = Command
SDA.Fill(dbDataSet)
bSource.DataSource = dbDataSet
DataGridView1.DataSource = bSource
SDA.Update(dbDataSet)
With DataGridView1
.RowHeadersVisible = False
.Columns(0).HeaderCell.Value = "Student No."
.Columns(1).HeaderCell.Value = "Last Name"
.Columns(2).HeaderCell.Value = "First Name"
.Columns(3).HeaderCell.Value = "Middle Name"
.Columns(4).HeaderCell.Value = "Grade Level"
.Columns(5).HeaderCell.Value = "School Year"
End With
MySqlConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
MySqlConnection.Dispose()
End Try
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim DV As New DataView(dbDataSet)
DV.RowFilter = String.Format(" year like '%{0}%' ", ComboBox1.SelectedItem)
DataGridView1.DataSource = DV
End Sub
