ComboBox and DataGridView

[Ruchi224]

Hello..

I have a form and i have used a combobox along with datagridview and textbox..

I have various values in combobox namely Mechanical, Electrical, Etectronics etc..
When I select the option Mechanics, I wish only few fields of the table be updated in the gridview, with the type mechanics. The Code i have used is :

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

        Try
            'MsgBox("Open")
            cn = New OleDbConnection("Provider=microsoft.jet.oledb.4.0;Data Source=E:\Project-Hemtech\HemDatabase1.mdb;")
            cn.Open()
            cmd = New OleDbCommand("select partname,partdesc,partnum,partqty from partno where type='" & ComboBox2.Text & "'", cn)
            cmd.ExecuteNonQuery()

            HemDatabase1DataSet2.Reset()
            Me.PartnoTableAdapter2.Fill(Me.HemDatabase1DataSet2.partno)
            dataGridView1.DataSource = Me.HemDatabase1DataSet2.partno

            MsgBox("Your Account Created Successfully ")

            'MsgBox("Done !!")

        Catch myException As Exception

            MsgBox("No Record Inserted" + myException.ToString())

        Finally
            'MsgBox("Closing Connection")

            cn.Close()
        End Try

    End Sub

Now, the problems I am facing are :

I am getting all the fields, instead of the specified ones in the query, in the grid view.
Also, the where clause, is not being executed ie. It gives the entire database as it is.
The database in the gridview does not get updated. It keeps giving me the old databse, even after updation, unless I Bind it again, with the data source.

I am completely new to the concept of Datagridview and combobox as making the project for first time in vb.net.. Please Help me !!

Thank You.

[Mitja Bonca]

Hi,
try using "ComboBox2.SelectedItem.ToString" instead.

[Ruchi224]

Tried that before.. No effect.. :(

[Mitja Bonca]

Hmm, it should select only rows where type is selectedItem from comboBox.

Would you mind showing me an image of database? I means the data from it (from partno table)?

[Ruchi224]

partid type role partname partdesc partnum partqty loc
1 Computer Part kgirheoiythj 3 2 1
2 Electronics Instrument MyPart asdfjkl 3 2 1
3 Mechanical Instrument efgw[]vylv grvrthy 123434gg 44 3 2 1
4 Miscellaneous Part regdrtse 5 4 6
5 Others Instrument hfgsiurgawgraw 332 7 4 1

It is something like this.. F u cn show me how to insert an iage here, i cn show u the screenshot !! o mention ur id.. i vl mail..

[Mitja Bonca]

I have a better idea. Why dont you get all the data from the database at ones. And then you will not need to access to it any longer (to the database I mean). You will do all the work on the datatable, using filtering. ComboBox selected item will be serving as a filter to get vanted data (rows). This means using SELECT method od datatable.

Here, i did an example how to use it.

NOTE:
- table is a variable, which stores all your data from the database.
- tempTable is filtered data.

Private table As DataTable
Private tempTable As DataTable
Public Sub New()
	InitializeComponent()
	'creating new instance of dataTable (and creating columns)
	'you create and fill table your way, this is only part of example:
		'HERE YOU GET ALL THE DATA FROM DB using SQL QUERY:
		' ->> "select partname,partdesc,partnum,partqty from partno"
		'NO WHERE CLAUSE
	table = New DataTable()
End Sub

Private Sub comboBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
	Dim item As String = comboBox1.SelectedItem.ToString()
	Dim rows As DataRow() = table.[Select]("Name = '" + item + "'")
	tempTable = New DataTable()
	tempTable = table.Clone()
	For Each row As DataRow In rows
		tempTable.ImportRow(row)
	Next
	dataGridView1.DataSource = New BindingSource(tempTable, Nothing)
End Sub

[Mitja Bonca]

If you are maybe wondering how to make it work, you can see into my full example code I just did (I dont fill datatable from database, but I create columns and fill it manually).

The example fully works, if you will have problems, you can try it out. On form only put a datagridview and a comboBox, for the rest the code will take care.

Code:

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms

Namespace Okt01_2
	Public Partial Class Form1
		Inherits Form
		Private table As DataTable
		Private tempTable As DataTable
		Public Sub New()
			InitializeComponent()
			'creating new instance of dataTable (and creating columns)
			'you create and fill table your way, this is only part of example:
			table = New DataTable()
			table.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String))})

			table.Rows.Add(1, "A")
			table.Rows.Add(2, "B")
			table.Rows.Add(3, "A")
			table.Rows.Add(4, "B")
			table.Rows.Add(5, "A")
			table.Rows.Add(6, "C")
			table.Rows.Add(7, "C")
			table.Rows.Add(8, "A")
			'binding table to DGV:
			dataGridView1.DataSource = New BindingSource(table, Nothing)

			comboBox1.Items.AddRange(New String() {"A", "B", "C"})
			comboBox1.SelectedIndexChanged += New EventHandler(comboBox1_SelectedIndexChanged)
		End Sub

		Private Sub comboBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
			Dim item As String = comboBox1.SelectedItem.ToString()
			Dim rows As DataRow() = table.[Select]("Name = '" + item + "'")
			tempTable = New DataTable()
			tempTable = table.Clone()
			For Each row As DataRow In rows
				tempTable.ImportRow(row)
			Next
			dataGridView1.DataSource = New BindingSource(tempTable, Nothing)
		End Sub
	End Class
End Namespace