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

Why are you insert an extra space in the filter condition " year like '%{0}%' ". before and after.

i removed it but it still has the same error

In the line 79 use ComboBox1.SelectedText in lue of ComboBox1.SelectedItem

i've tried it but it has the same error in line 79

Cannot find column [year]

Does the column year exists it the queried tables? Have you tried to perform the same queries directly in a MySQL client?

it exist i've already solved it... it is just the position where you will call the values in mcs. year...it is after when you called the values of mcs.student and insert it into datagrid

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.