Data Binding At Run Time

[turrican]

Thanks to everyone who have helped me in my previous posts. Here is how i ended up making my searchable movie list. Iknow that there is a better way but this works. I would appreciate any advice on how I could have done this more efficiently. This is my first time creating a windows form like this. I am connecting to my dvd database and I have a list box of categories like action,drama etc. the user selects the genre and then a datagrid displays the title rating and other info of all of the movies in that genre. there must be a better way then having to make six data adapters and six datasets. Any advice would be appreciated. Here is my bloated code.


Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
If ListBox1.SelectedItem = "Kids" Then
DataGrid1.DataSource = Dskids1.Movies
OleDbDataAdapter1.Fill(Dskids1)
ElseIf ListBox1.SelectedItem = "Comedy" Then
DataGrid1.DataSource = Dscomedy1.Movies
OleDbDataAdapter2.Fill(Dscomedy1)
ElseIf ListBox1.SelectedItem = "Drama" Then
DataGrid1.DataSource = Dsdrama1.Movies
OleDbDataAdapter3.Fill(Dsdrama1)
ElseIf ListBox1.SelectedItem = "Action" Then
DataGrid1.DataSource = Dsaction1.Movies
OleDbDataAdapter4.Fill(Dsaction1)
ElseIf ListBox1.SelectedItem = "Documentary" Then
DataGrid1.DataSource = Dsdoc1.Movies
OleDbDataAdapter5.Fill(Dsdoc1)
ElseIf ListBox1.SelectedItem = "Chick Flick" Then
DataGrid1.DataSource = Dschick1.Movies
OleDbDataAdapter6.Fill(Dschick1)
ElseIf ListBox1.SelectedItem = "Horror" Then
DataGrid1.DataSource = Dshorror1.Movies
OleDbDataAdapter2.Fill(Dshorror1)
End If
End Sub

Thanks for your time!!!

 

[quwiltw]

YOu shouldnt neeed multiple dataadapters and I doubt you need multiple datasets, maybe you could post the code your using for populating one of the selectcommands on one of the dataadapters and the database model and well figure out how to parameterize it?

 

[turrican]

Okay, as an example I used the following select statement for the first dataadapter:

select id, title, rating from Movies where category = "kids"

every other data set is the same with only the category being different obviously.

I am only querying one table. Of an Access DB.

BTW the only reason that I am doing this is to try and learn.
I can read and read but I wont learn unless I create some projects for myself to stumble through. Thanks for your help and your time.

 

[quwiltw]

For starters, lets change the select statement to accept a parameter which well determine at runtime. For example
select id, title, rating from Movies where category=?

Then add a parameter to your SelectCommand object. Something like,
Me.OleDbSelectCommand1.Parameters.Add("@category", System.Data.OleDb.OleDbType.VarChar, 50).Value = "kids"

get it to work like this, then you see where you can change the value of the parameter at runtime to whichever category is selected. Also, since all of the datasets have the same structure you really dont need multiples.

 

[turrican]

Thank you for your response. I dont understand some of the syntax. Some of it I do. I will figure out how to make it work though. I stumbled across the Parameterized query walkthrough in the VS help file.
One more dumb question if I may,
I know where to chage the sql statement but how do I add the parameter to the selectCommand object?
Sorry if this is a lame question but I couldnt find a forum for beginners like me.

 

[quwiltw]

Originally posted by turrican
I know where to chage the sql statement but how do I add the parameter to the selectCommand object?

This is actually what I was showing you in my previous post. Here it is again. Obviously the Me.OleDbSelectCommand1 needs to be changed to whatever variable name you gave your select command.

Me.OleDbSelectCommand1.Parameters.Add("@category", System.Data.OleDb.OleDbType.VarChar, 50).Value = "kids"

... but I couldnt find a forum for beginners like me.

Looks like you found it to me
I think the folks here enjoy answering questions at all skill levels so keepem coming

 

[turrican]

Hey quwiltw!

This is what I finaly came up with and it works!
I used the wizard to create my dataadapter with an sql statement
with the ? after the where clause and this:

ListBox1.SelectedIndexChanged
OleDbDataAdapter1.SelectCommand.Parameters("Category").Value = ListBox1.SelectedItem
DsChoice1.Clear()
OleDbDataAdapter1.Fill(DsChoice1)

Much cleaner. Thanks. I ve been trying to figure this out all week.
Just wanted to let you know your time wasnt wasted.