Populate combo box

[dheeraj.mangrah]

Hi, Could anyone solve this for me.

I'm using VB 2010 and Access Database file. The database consists of a table called 'usertbl' with 'username', 'type' and 'level' fields. The records are as follows:

Record 1 - Username: admin Type: admin Level:1
Record 2 - Username: user1 Type: user Level:2
Record 1 - Username: user2 Type: user Level:1

Suppose I select 'User' from the cboname combobox then the second combobox list all the users (admin, user1, user2); where it was supposed to list only user1 and user 2. What I want it's only when I select admin from cboName then the cboType list only admin and when User is selected from cboName them cboType list only user1, user2... It should filter according its type as my database records.

So what i really what:

when cboName = admin
then cboType = admin

when cboName = user
then cbotype = user1 & user2

Here is my code...

Imports System.Data.OleDb

Public Class Form1

Sub fillcombo()

mysql = "Select distinct * From Usertbl"

Dim cboCmd As New OleDb.OleDbCommand
cboCmd.CommandText = mysql
cboCmd.Connection = dataconn
dataread = cboCmd.ExecuteReader
While (dataread.Read())
cboName.Items.Add(dataread("username"))

End While
cboCmd.Dispose()
dataread.Close()

End Sub
Sub fillcbotype()
mysql = "Select distinct [Type] From Usertbl"

Dim cboCmd As New OleDb.OleDbCommand
cboCmd.CommandText = mysql
cboCmd.Connection = dataconn
dataread = cboCmd.ExecuteReader
While (dataread.Read())
cbotype.Items.Add(dataread("Type"))

End While
cboCmd.Dispose()
dataread.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Module1.Connect()
Me.fillcombo()
Me.fillcbotype()
Me.myselection()
End Sub
Private Sub datagridshow()
Dim ds As New DataSet
Dim dt As New DataTable

ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter

da = New OleDbDataAdapter("Select * From Usertbl", dataconn)
da.Fill(dt)

DataGridView1.DataSource = dt.DefaultView
dataconn.Close()

End Sub

Private Sub cboName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboName.SelectedIndexChanged
cbochange()

End Sub
Private Sub cbochange()
Dim ds As New DataSet
Dim dt As New DataTable

ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter

da = New OleDbDataAdapter("Select Distinct * From Usertbl where username= '" & cboName.Text & "' and type='" & cbotype.Text & "'", dataconn)
da.Fill(dt)

DataGridView1.DataSource = dt.DefaultView
dataconn.Close()
End Sub

Private Sub cbotype_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbotype.SelectedIndexChanged

myselection()

End Sub
Private Sub myselection()
Dim mycon As New OleDb.OleDbConnection
mycon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 'C:\Users\Genius\Desktop\VBCode\Usertable.mdb'"
mycon.Open()

'dataconn.Open()
mysql = "Select * From Usertbl where username = '" & cbotype.Text & "' and type= '" & cbotype.Text & "'"
Dim mycmd As New OleDbCommand
mycmd.CommandText = mysql
mycmd.Connection = mycon
dataread = mycmd.ExecuteReader
If (dataread.Read() = True) Then
cboName.Text = (dataread("Username"))
cbotype.Text = (dataread("type"))

End If
mycmd.Dispose()
dataread.Close()
mycon.Dispose()

End Sub
End Class

[Begginnerdev]

Can you do us a HUGE favor and indent your code for easier reading?

You can do this by pressing Tab to indent the code. You will see the text turn green when it is done correctly.

[Begginnerdev]

For the select, you can place the following code in your procedure:

    Try
        Dim da As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand("SELECT * FROM usertbl WHERE type='" & cboType.Text & "'", dataconn))
        Dim ds As New DataSet

        da.Fill(ds, "Type")

        If IsNothing(ds.Tables("Type")) = False And ds.Tables("Type").Rows.Count > 0 Then
            cboName.Items.Clear()
            For i = 0 To ds.Tables("Type").Rows.Count - 1
                cboName.Items.Add(ds.Tables("Type").Rows(i)("Username"))
            Next
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

[dheeraj.mangrah]

Thanks Begginnerdev, bingo that works and in the future I'll follow ur advise like using indentation when posting for easy reading. Thanks once again