Populate Listbox on clicking a value in combobox

[Komalj]

Hi everyone....
I am populating my combo box with data from access database...
what i want to do is...upon clicking a value in the combo box it should display data related to the value selected in a listbox ...how do i do it....

here is the code i am using....
Private Sub Access_to_combo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' Open a database connection.

Dim strConnection As String = "Data Source=""D:\INSFORM\Library\Library.mdb"";Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin"

Dim cn As OleDbConnection = New OleDbConnection(strConnection)

cn.Open()

' Set up a data adapter object.

Dim strSql As String = "Select Distinct Publication from Book order by Publication"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, cn)

' Load a data set.

Dim ds As DataSet = New DataSet

da.Fill(ds, "Book")



cn.Close()



ComboBox1.DataSource = ds.Tables(0)

ComboBox1.DisplayMember = "Publication"

End Sub
________________________________________________________________
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim strConnection As String = "Data Source=""D:\INSFORM\Library\Library.mdb"";Provider=""Microsoft.Jet.OLEDB.4.0"";User ID=Admin"

Dim cn As OleDbConnection = New OleDbConnection(strConnection)

Dim selected As String

cn.Open()

' Set up a data adapter object.

Dim strSql As String = "Select Name from Book where Publication = ' " & ComboBox1.Text & " '"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, cn)

' Load a data set.

Dim ds As DataSet = New DataSet

da.Fill(ds, "Book")

cn.Close()

ListBox1.DataSource = ds.Tables(0)

ListBox1.DisplayMember = "Name"

End Sub

Will appreciate any feedback...

 

[jpetter7]

There are different ways to populate the list box, and I am not sure if yours is correct. try this

sqldatadapter.fill(dataset1, "Books")
'Bind listbox
with lbName
.datasource = dataset1
.displaymember = "books.id"
.valuemember = "Books.id"
end with

'you also have to filter the sql statement based on the combo box:
cmd.rowfilter = "the testing condition(book??) = " & cbo1.selecteditem(0)

'Maybe add this to the code not sure
lbName.datasource = cmd

I am no expert, but I hope this helps!!

 

[Komalj]

I dont have a problem with populating the listbox....
my main problem is to display only selected values from the database depending on what was selected in the combo box.

for example if in my table i have two columns Book Name, Publication
I populate the combo box with all the publications listed in the table....
now when i click on a particular publication in the combo box.....it should display all the book names under that publiction in the listbox...
e.g. like all the book names under wrox publication

how do i achieve that?

 

[jpetter7]

create a stored procedure.

create variable in stored procedure
Create Procedure procgetbooks

@publication as varchar
as
"Select *
from mytable
where publication = @publication"

in vb code do this

dim cmd as sqlcommand = sqlconnection1.createcommand
cmd.type = storedprocedure
cmd.text = "procgetbooks"

'add input parameters
'its a little fuzzy at this part.. dont have a book with me but its basically this:
cmd.addparameters.value = cbopublication.selecteditem
cmd.something = @publication

'also, click on server explorer, your database then r-click stored procedure create new to create s.proc.
'play with this, and i am sure you will get it.

 

[jpetter7]

cmdInsert.Parameters.Add(New SqlParameter("@TNames", SqlDbType.Char, 20, "TName"))

'cmdInsert.Parameters("@TNames").Value = txtTeamName.Text