Get certain records out of a database table

[Xavier]

Hello,
I need help, let's say i have a table "Persons" with two columns "Name" and "Houses". A person can have more than one house.

What i want to do is:

The names are in a combobox, when i select a name, i want to show all the houses for that name in an other combobox on the same form. I know i have to work with a dataset but i can't hack it.

Can someone help me with the code, or give me an example?

thanks

 

[kulrom]

Dim selectquery As String = "SELECT Houses FROM Persons WHERE Name='" & ComboBox1.SelectedItem.ToString & "'"

 

[Xavier]

Thanks, but can you give me the whole code on how to do this, ik have also trouble to make de dataset in combination with the connection to the database. thannks in advance

 

[kulrom]

Hello Xavier,

What database you use?

 

[Xavier]

i found the solution on the net, thanks a lot

 

[kulrom]

Good that you resolved it but, please notice that you don't need to use dataset necessary. You could do the same job with a datatable or dataReader.

 

[cjard]

Dim selectquery As String = "SELECT Houses FROM Persons WHERE Name='" & ComboBox1.SelectedItem.ToString & "'"

I'd much prefer you use parameterized queries instead of string concatenation when teaching newbies how to code; as their mentor you have a responsibility to set them off on the right track.

 

[cjard]

Hello,
Can someone help me with the code, or give me an example?

The DW2 link in my sig has a section "create a form to search data" - this will show you how to make queries that take parameters. You'd fill your first combo, and then use the chosen value to fill the second combo as a parameter. The term used to describe this is "cascading comboboxes" - if you search this forum for it you should turn up some examples

 

[kulrom]

I'd much prefer you use parameterized queries instead of string concatenation when teaching newbies how to code; as their mentor you have a responsibility to set them off on the right track.

I agree with you cjard. Ok here we go.

I assumed that you use SQL Server so please try something like this:

        Dim connection As New SqlConnection("ConnectionString")
        Try

            Dim command As SqlCommand = connection.CreateCommand
            command.CommandText = "SELECT Houses FROM Persons WHERE Name=@Name"
            command.Parameters.Add("@Name", SqlDbType.VarChar).Value = ComboBox1.SelectedItem.ToString
            connection.Open()
            Dim table As New DataTable
            table.Load(command.ExecuteReader)
            ComboBox[B]2[/B].DataSource = table
            ComboBox[B]2[/B].ValueMember = "Houses" 
            ComboBox[B]2[/B].DisplayMember = "Houses"
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            connection.Close()
        End Try

 

[Tom]

I just wanted to point out that if Combobox1 is bound to a datasource (such as table Persons) using the ComboxBox1.SelectedItem.ToString method wont actually return the item text expected. (Returns: System.Data.DataRowView) You would need to use the GetItemText or another method of extracting the item text.

command.Parameters.Add("@Name", SqlDbType.VarChar).Value = [COLOR="Red"]ComboBox1.SelectedItem.ToString[/COLOR]

 

[kulrom]

Hi Tom,

I am not sure for what you are suggesting. SelectedItem is OBJECT datatype which gets or sets currently selected item in the ComboBox. Of course, you can avoid ToString (casting) and use late binding but anyway i couldn't reproduce the issue you are talking about. It always returns selected item text for me.
However if it doesn't work, as mentioned you can use GetItemText:

command.Parameters.Add("@Name", SqlDbType.VarChar).Value = ComboBox1.GetItemText(ComboBox1.SelectedItem)