VB.NET - Re:Display a datagridview based on selected value in a combo box

Asked By kibika on 26-May-12 12:45 PM

I have a combo box on a form and a datagridview. I want to display related records in the datagridview based on the selected value from the combo box. I have been doing this using listbox and combo box. But the datagridview appears to be giving a headache. Please assist.

And my code I am using is as:

Imports System
Imports System.Data.OleDb
Public Class Form1
  Public accConnection As OleDbConnection
  Dim sCommand As OleDbCommand
  Dim sAdapter As OleDbDataAdapter
  Dim sBuilder As OleDbCommandBuilder
  Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Practice.mdb"
  Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Practice.mdb")
  Dim sql As String = "SELECT ADMNO,FirstName, LastName, RollCall FROM myClassList"
  Dim sDs As DataSet
  Dim sTable As DataTable
  Private CourseID() As Double
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    Dim connection As New OleDbConnection(connectionString)
    connection.Open()
    sCommand = New OleDbCommand(sql, connection)
    sAdapter = New OleDbDataAdapter(sCommand)
    sBuilder = New OleDbCommandBuilder(sAdapter)
    Dim sds = New DataSet()
 
    sAdapter.Fill(sds, "myClassList")
    sTable = sds.tables("myClassList")
    connection.Close()
    StdcboForm()
    DataGridView1.DataSource = sds.tables("myClassList")
    'DataGridView1.ReadOnly = True
    DataGridView1.GridColor = Color.Red
    DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
    DataGridView1.BackgroundColor = Color.LightGray
 
    DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
    DataGridView1.DefaultCellStyle.SelectionForeColor = Color.Yellow
 
    DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True
 
    DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
    DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
    DataGridView1.AllowUserToAddRows = False
 
    DataGridView1.RowHeadersBorderStyle = DataGridViewHeaderBorderStyle.None
    DataGridView1.EnableHeadersVisualStyles = False
    DataGridView1.Columns(0).SortMode = False
    DataGridView1.Columns(1).SortMode = False
    DataGridView1.Columns(2).SortMode = False
  End Sub
  Public Sub StdcboForm()
    'Filling cboForm combo box with forms
    Dim cmd As New OleDbCommand("SELECT DISTINCT sForm FROM myClassList", cn)
 
    cmd.Connection.Open()
    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataSet
    Dim dt As New DataTable
 
    da.Fill(ds, "myClassList")
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    While dr.Read()
      cboForm.Items.Add(dr("sForm"))
    End While
  End Sub
  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    'Save the tables's changes back to the database.
    Dim changes As Integer
    'Open a connection to the database for updating.
    'Update the database with changes from the data table.
    changes = sAdapter.Update(sTable)
 
    'Display the number of changes made.
    If changes > 0 Then
      MsgBox(changes & " changed rows were stored in the database.")
    Else
      MsgBox("No changes made.")
    End If
    sAdapter.Dispose()
  End Sub
 
  Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
    Dim bm As New Bitmap(Me.DataGridView1.Width, Me.DataGridView1.Height)
    DataGridView1.DrawToBitmap(bm, New Rectangle(0, 0, Me.DataGridView1.Width, Me.DataGridView1.Height))
    e.Graphics.DrawImage(bm, 0, 0)
  End Sub
  Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
    PrintDocument1.Print()
  End Sub
  Private Sub cboStream_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboStream.SelectedIndexChanged
    Dim ds As DataSet = New DataSet()
    Dim conn As OleDbConnection = New OleDbConnection
    Dim dbcmd As OleDbCommand = New OleDbCommand
    Dim da As OleDbDataAdapter = New OleDbDataAdapter()
    conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Practice.mdb")
    dbcmd.Connection = conn
    dbcmd.CommandText = "SELECT ADMNO,Stream, FirstName, LastName, RollCall FROM myClassList WHERE Stream = '" & cboStream.SelectedItem.ToString & "' AND sForm = '" & cboForm.SelectedItem.ToString & " ' "
    da.SelectCommand = dbcmd
    conn.Open()
    da.Fill(ds, "myClassList")
    conn.Close()
    DataGridView1.DataSource = ds
    DataGridView1.Refresh()
  End Sub
  Private Sub cboForm_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboForm.SelectedIndexChanged
    'Filling cboStream combo box with streams
    Dim cmd As New OleDbCommand("SELECT DISTINCT Stream FROM myClassList WHERE sForm = '" & cboForm.SelectedItem.ToString() & "'", cn)
 
    Dim da As New OleDbDataAdapter(cmd)
    Dim dr As New DataSet
    Dim dt As New DataTable
 
    da.Fill(dr, "myClassList")
    Dim dm As OleDbDataReader = cmd.ExecuteReader
    cboStream.Items.Clear()
    While dm.Read()
      cboStream.Items.Add(dm("Stream"))
    End While
    dm.Close()
    cmd.Cancel()
    cmd.Dispose()
  End Sub
End Class

Unfortunately, after selecting the values in the combo box, the datagridview remains blank. Assist me solve this problem. I am stuck for two days on this problem.

dipa ahuja replied to kibika on 26-May-12 01:50 PM
private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
  if (dataGridView1.CurrentCell.ColumnIndex == 0)
  {
    // Check box column
    ComboBox comboBox = e.Control as ComboBox;
    comboBox.SelectedIndexChanged += new EventHandler(comboBox_SelectedIndexChanged);
  }
}
 
void comboBox_SelectedIndexChanged(object sender, EventArgs e)
{
  int selectedIndex = ((ComboBox)sender).SelectedIndex;
  MessageBox.Show("Selected Index = " + selectedIndex);
}
 
 
Vikram Singh Saini replied to kibika on 26-May-12 07:57 PM
Hi,

Tested your whole code with dummy database. And found single line mistake in Function cboStream_SelectedIndexChanged

You have to change line:

DataGridView1.DataSource = ds

to

DataGridView1.DataSource = ds.Tables("myClassList")
kibika replied to Vikram Singh Saini on 26-May-12 10:37 PM
EXCELLENT my dear!

Thank you very much Vikram Singh Saini. I want to assure you that even if , I had read all the books on VB.Net, I wouldn't have discovered that. Thanks again.

Now a small problem has gropped up again. I can't save the changes I make to the DATAGRIDVIEW that I had displayed after selection. In the same code, you worked, previously it was just easy saving the changes. You can take a look at the code under btnSave_Clicked event. How to do I fix this please.

Thanks in advance

Kibika
Vikram Singh Saini replied to kibika on 27-May-12 05:02 AM
Let us try to understand the issue why the save function was not working?

It was because of you were using two dataset , one during form load to store data and to set it to datagridview. Second was used by creating a new dataset in click event of combobox.

So here happens two things:

(1) When you ran the application, try to modify values and then save them; it would work as usual because at that moment  you are trying to update the table (the one that is getting it values from first dataset).

(2) When you are firing combobox selected index change event, you are creating a new second dataset, updating it with select command values and binding same to datagridview. And then after when you try to save the values, they would not get save. As you know the reason, save function would try to save values modified in tables from first dataset.

Solution:

What we have done as solution that we are also updating the table used in form load with new table values from dataset in combobox selected index change event. So now when we try to update the same table it is found different from previous one, and hence the updates are saved in database.

Try the given code (as it is) because I have made some modifications in between:

Imports System
Imports System.Data.OleDb
Public Class Form1
 
  Public accConnection As OleDbConnection
  Dim sCommand As OleDbCommand
  Dim sAdapter As OleDbDataAdapter
  Dim sBuilder As OleDbCommandBuilder
  Dim da As OleDbDataAdapter
  Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\VB\DataGrid\Practice.mdb"
  Dim cn As New OleDbConnection(connectionString)
  Dim sql As String = "SELECT ADMNO,FirstName, LastName, RollCall FROM myClassList"
  Dim sDs As DataSet
  Dim sTable As DataTable
  Private CourseID() As Double
  Private Sub cboForm_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboForm.SelectedIndexChanged
    'Filling cboStream combo box with streams
    Dim cmd As New OleDbCommand("SELECT DISTINCT Stream FROM myClassList WHERE sForm = '" & cboForm.SelectedItem.ToString() & "'", cn)
 
    Dim da As New OleDbDataAdapter(cmd)
    'Dim dr As New DataSet
    'Dim dt As New DataTable
 
    sDs = New DataSet()
    da.Fill(sDs, "myClassList")
    Dim dm As OleDbDataReader = cmd.ExecuteReader
    cboStream.Items.Clear()
    While dm.Read()
      cboStream.Items.Add(dm("Stream"))
    End While
    'cboStream.SelectedIndex = 0
    dm.Close()
    cmd.Cancel()
    cmd.Dispose()
  End Sub
 
  Private Sub cboStream_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboStream.SelectedIndexChanged
    'Dim ds As DataSet = New DataSet()
    Dim conn As OleDbConnection = New OleDbConnection(connectionString)
    Dim dbcmd As OleDbCommand = New OleDbCommand
 
    dbcmd.Connection = conn
    dbcmd.CommandText = "SELECT ADMNO,Stream, FirstName, LastName, RollCall FROM myClassList WHERE Stream = '" & cboStream.SelectedItem.ToString & "' AND sForm = '" & cboForm.SelectedItem.ToString & "'"
    sAdapter.SelectCommand = dbcmd
    conn.Open()
    sDs.Clear()
    sAdapter.Fill(sDs, "myClassList")
    sTable = sDs.Tables(0)
 
    conn.Close()
    DataGridView1.DataSource = sDs.Tables(0)
    DataGridView1.Refresh()
  End Sub
  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    'Save the tables's changes back to the database.
    Dim changes As Integer
    'Open a connection to the database for updating.
    'Update the database with changes from the data table.
    changes = sAdapter.Update(sTable)
 
    'Display the number of changes made.
    If changes > 0 Then
      MsgBox(changes & " changed rows were stored in the database.")
    Else
      MsgBox("No changes made.")
    End If
    sAdapter.Dispose()
  End Sub
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connection As New OleDbConnection(connectionString)
    connection.Open()
    sCommand = New OleDbCommand(Sql, connection)
    sAdapter = New OleDbDataAdapter(sCommand)
    sBuilder = New OleDbCommandBuilder(sAdapter)
    sDs = New DataSet()
 
    sAdapter.Fill(sDs, "myClassList")
    sTable = sDs.Tables("myClassList")
    connection.Close()
    StdcboForm()
    DataGridView1.DataSource = sDs.Tables("myClassList")
    'DataGridView1.ReadOnly = True
    DataGridView1.GridColor = Color.Red
    DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
    DataGridView1.BackgroundColor = Color.LightGray
 
    DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
    DataGridView1.DefaultCellStyle.SelectionForeColor = Color.Yellow
 
    DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True
 
    DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
    DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
    DataGridView1.AllowUserToAddRows = False
 
    DataGridView1.RowHeadersBorderStyle = DataGridViewHeaderBorderStyle.None
    DataGridView1.EnableHeadersVisualStyles = False
    DataGridView1.Columns(0).SortMode = False
    DataGridView1.Columns(1).SortMode = False
    DataGridView1.Columns(2).SortMode = False
  End Sub
 
  Public Sub StdcboForm()
    'Filling cboForm combo box with forms
    Dim cmd As New OleDbCommand("SELECT DISTINCT sForm FROM myClassList", cn)
 
    cmd.Connection.Open()
    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataSet
    Dim dt As New DataTable
 
    da.Fill(ds, "myClassList")
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    While dr.Read()
      cboForm.Items.Add(dr("sForm"))
    End While
    'cboForm.SelectedIndex = 0
  End Sub
End Class

Hope this helps you to achieve your objective.