combo box filter

[sigridish]

hi all! i have two combo box in my program

one has the subteam for example if you click the 1st combo box, the items would be
A1
A2
A3
A4
A5
what i want to happen is that if i chose A1 only the members of team A1 would appear in my combo box2. and by that, you need an sql statement. but how do i code that? can someone help me?

[hericles]

Hi,
In the selectedIndexChanged event of the first combo box you determine the selected index and include that into your SQL string.

string selectedTeam = comboBox1.SelectedItem.ToString();
string sql = "SELECT * FROM yourTable WHERE team = ?selectedTeam;";
cmd.CommandText = sql;
cmd.Parameters.Add("?selectedTeam", MySqlDbType.Varchar);
cmd.Parameters["?selectedTeam"].Value = selectedTeam;

I'm assuming you have a command object called cmd of course. After that open your connection, execute the command, catch the results in a dataTable or reader and import into the second comboBox. I have made so assumptions about your database table design too of course.

[sigridish]

hi hericles!
is that for vb.net code? can you give me the full code?

[hericles]

Thats c# (i'm always making that mistake)...
I'll post something more complete in a couple of hours. I'm going to assume your database is set up with a table of teams, a table of players and a table that links the two. I hope thats accurate.

[bhagawatshinde]

Try this,

Conn.Open()
   Dim str_query As String = "select * from tableName where Field=" & Combo1.Text &"
   cmd = New SqlCommand(str_query, Conn)
   dr = cmd.ExecuteReader
   Combo2.Items.Clear()  
   Do While dr.Read()
       Combo2.Items.Add(dr.Item("Fieldname"))
   Loop
   dr.Close()
 Conn.Close()

[sigridish]

hi bhagawatshinde!
is that vb.net?

[sigridish]

hericles,
ok i'll wait

[bhagawatshinde]

Yes it's vb.net 2008 framework 3.5

[sigridish]

well the dr part has an error cause its not declared. if i declare dr what kind of declaration should i use?

[sigridish]

Type 'SqlCommand' is not defined.
Name 'dr' is not declared.
Name 'dr' is not declared.
Name 'dr' is not declared.
Name 'dr' is not declared.

those are the errors

[bhagawatshinde]

use namespace as
Imports System.Data.SqlClient
Imports System.Data.Sql

use this declaration,
Dim cmd As New SqlCommand
Dim dr as new SqlDataReader
Dim da As New SqlDataAdapter

[bhagawatshinde]

Import-----

Imports System.Data.SqlClient
Imports System.Data.Sql
Create an object------
   Public dr As SqlDataReader
    Public cmd As New SqlCommand

[bhagawatshinde]

Import the following on top of the page

Imports System.Data.Sql
Imports System.Data.SqlClient

and declare following

Dim cmd As New SqlCommand
 Dim dr As SqlDataReader

[danielgr]

Dim dr As System.Data.SqlDataReader

[hericles]

He has left out the initial declaration of the command object and the data reader. Add this:

Dim cmd As New SqlCommand()
cmd = New SqlCommand(str_query, Conn)
Dim dr As New DataReader()
dr = cmd.ExecuteReader

[sigridish]

hericles are you still there?

I'm assuming I saw your thread on code project.

This should do what your doing.

I used SqlCe as the database if using .mdf db then change the variables to SqlCommand instead of SqlCeCommand and so on.

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient 'if using .mdf
Public Class Form2
    Private Const ConnectionString As String = "Your Connection String"

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        If TypeOf ComboBox1.SelectedItem Is String Then

            Dim Connection As New SqlCeConnection(ConnectionString)
            Dim Command As SqlCeCommand = Connection.CreateCommand
            Dim Reader As SqlCeDataReader = Nothing

            Command.CommandText = "Select *" & vbCrLf & _
                                  "From finaltickettable" & vbCrLf & _
                                  "Where Subteam = @SubTeam"
            Command.Parameters.AddWithValue("@SubTeam", CStr(ComboBox1.SelectedItem))

            ComboBox2.BeginUpdate()
            ComboBox2.Items.Clear()
            Try
                Connection.Open()
                Reader = Command.ExecuteReader
                Do While Reader.Read
                    ComboBox2.Items.Add(CStr(Reader("YourColumnName")))
                Loop

            Catch ex As Exception
            Finally
                Connection.Dispose()
                Command.Dispose()
                'dispose may not be available with sqldatareader
                If Reader IsNot Nothing Then Reader.Dispose()
            End Try

            ComboBox2.EndUpdate()
        End If
    End Sub

End Class

[sigridish]

hi thanks for your reply unhnd exception! i changed it to sqlcommand but its still error

[sigridish]

Dim Connection As New SqlConnection(ConnectionString)
Dim Command As SqlCommand = Connection.CreateCommand
Dim Reader As SqlDataReader = Nothing

the bold part are the error

Did you add imports System.Data.SqlClient to the top of your form??

[sigridish]

i just imported it thanks! i have an error on

Dim Connection As New SqlConnection(ConnectionString)

[sigridish]

should i place there my connection string?

Did you add a global ConnectionString variable like my original post depicted?

Did you set the ConnectionString variable to the connection string of your db?

[sigridish]

hi. sorry for that i already fixed that. theres no error anymore but whenever i execute my program if i select A1 theres nothing on my combo box2

From my original post. Add this under Catch ex as exception

Try
Catch ex as exception
   'add this
    MsgBox(ex.Message)
finally

See what errors are coming up. If no errors then you need to check your table.

Let me know how that goes.

[BlurrieBlue]

For [Type 'SqlCommand' is not defined.] error, u got to do

Imports System.Data.SqlClient

For [Name 'dr' is not declared. ] error, do

Dim dr as SqlDataReader = cmd.ExecuteReader

@BlurrieBlue: We're past that.

[sigridish]

hi Unhnd Exception! its fixed but i still got one problem though. in my record, name jake held 3 tickets so he has 3 records in my database. he is in team A4. whenever i choose A4 his name appears thrice. how will i fix that?

You need to change your Select statement to add Distinct
.

From my orignal code; Change to

Command.CommandText = "Select Distinct *" & vbCrLf & _
                        "From finaltickettable" & vbCrLf & _
                        "Where Subteam = @SubTeam"

[BlurrieBlue]

@BlurrieBlue: Where past that.

Oops.. When i post it, the page refresh and many other comments and solution had been posted.. I guess I'm slow XD