0

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

Edited by happygeek: moved: don't ask tech questions in the community centre if you expect any help

3
Contributors
6
Replies
43
Views
3 Years
Discussion Span
Last Post by kaye.santos.92
0

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

0

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?

1

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.