fill datagridview based on combobox selected item which depends on another selected combobox from database

Question

I am creating an add student windows form and it contains two comboboxes. the first one contains semester of the student and the second one contains the course code based on the value of semester. the problem I'm facing is that i also have a datagridview and i want to populate it with data of students contained in table of course code and as soon as i click semester combobox value of course code combobox changes, as soon as it changes, I want to load data of datagridview but it shows the error which basically is cannot convert datarows to string but when I use ComboBox1.ValueMember and load datagridview on clicking button it works fine. but when i load datagridview on combobox change it shows error.

this code works fine

Imports System.Data.OleDb
Public Class AddStudent
    Dim con As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Students.accdb")
    Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Users.accdb")
    Private Sub AddStudent_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        con.Open()
        Dim cmd As New OleDbCommand("Select stud_roll From " + ComboBox1.SelectedValue + " where stud_roll = @roll1", con)
        cmd.Parameters.AddWithValue("roll1", TextBox2.Text)
        Dim myreader As OleDbDataReader = cmd.ExecuteReader
        If myreader.Read() Then
            con.Close()
            MessageBox.Show("Student Inserted before")
        Else
            con.Close()
            Dim cmd1 As New OleDbCommand("Insert into " + ComboBox1.SelectedValue + "(stud_roll,stud_name,date_of_birth,course,gender,mobile_no,semester) Values(@roll,@name,@dob,@course,@gender,@mobile,@semester)", con)
            cmd1.Parameters.AddWithValue("roll", Convert.ToInt32(TextBox2.Text))
            cmd1.Parameters.AddWithValue("name", TextBox1.Text)
            cmd1.Parameters.AddWithValue("dob", DateTimePicker1.Value.Date)
            cmd1.Parameters.AddWithValue("course", ComboBox1.SelectedValue)
            cmd1.Parameters.AddWithValue("gender", ComboBox2.SelectedItem)
            cmd1.Parameters.AddWithValue("mobile_no", MaskedTextBox1.Text)
            cmd1.Parameters.AddWithValue("semester", Convert.ToInt32(ComboBox3.SelectedItem))
            con.Open()
            cmd1.ExecuteNonQuery()
            con.Close()
            MessageBox.Show("Records inserted successfully")
        End If
        con.Open()
        Dim cmd3 As New OleDbCommand("Select stud_roll, stud_name, date_of_birth, course, gender,mobile_no, semester From " + ComboBox1.SelectedValue + "", con)
        Dim da As New OleDbDataAdapter
        da.SelectCommand = cmd3
        Dim dt As New DataTable
        dt.Clear()
        da.Fill(dt)
        DataGridView1.DataSource = dt
        con.Close()
    End Sub

    Private Sub Label8_Click(sender As Object, e As EventArgs) Handles Label8.Click
        AddTeacher.Show()
    End Sub

    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
        con1.Open()
        Dim cmd As New OleDbCommand("Select course_code From courses Where semester= " + ComboBox3.SelectedItem + "", con1)
        Dim da As New OleDbDataAdapter
        da.SelectCommand = cmd
        Dim dt As New DataTable
        dt.Clear()
        da.Fill(dt)
        ComboBox1.DataSource = dt
        ComboBox1.DisplayMember = "course_code"
        ComboBox1.ValueMember = "course_code"
        con1.Close()
    End Sub

    Private Sub Label14_Click(sender As Object, e As EventArgs) Handles Label14.Click
        Courses.Show()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    End Sub
End Class

when i load after combobox change like this, it shows error :- System.InvalidCastException: 'Operator '+' is not defined for string "Select stud_name, stud_roll From" and type 'DataRowView'.' which is because of datarowview type conversion

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    con.Open()
    Dim cmd3 As New OleDbCommand("Select stud_roll, stud_name, date_of_birth, course, gender,mobile_no, semester From " + ComboBox1.SelectedValue + "", con)
    Dim da As New OleDbDataAdapter
    da.SelectCommand = cmd3
    Dim dt As New DataTable
    dt.Clear()
    da.Fill(dt)
    DataGridView1.DataSource = dt
    con.Close()
End Sub 

please help me with this.

Answer 1

1. Make sure to give the controls meaningful names: it will make your life easier. "ComboBox1" is not a meaningful name ;)

2. Don't create a single instance of a database connection that is re-used: you are meant to create the connection object, use it, and then dispose of it. There are mechanisms behind the scenes to make this efficient (Connection Pooling). There is an example of how to do it in a previous answer of mine.

3. You don't need to open and close the connection for da.Fill(dt): it does it automatically.

4. Because ComboBox1.SelectedValue comes from a datatable, you will need to extract the column of that datarowview, something like:

Dim tableName = DirectCast(ComboBox1.SelectedItem, DataRowView).Row.Field(Of String)("course_code")
Dim cmd3 As New OleDbCommand("Select stud_roll, stud_name, date_of_birth, course, gender,mobile_no, semester From [" & tableName & "]", con)