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?
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.
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.
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()hericles,
ok i'll wait
well the dr part has an error cause its not declared. if i declare dr what kind of declaration should i use?
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
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
Import-----
Imports System.Data.SqlClient
Imports System.Data.Sql
Create an object------
Public dr As SqlDataReader
Public cmd As New SqlCommandImport the following on top of the page
Imports System.Data.Sql
Imports System.Data.SqlClientand declare following
Dim cmd As New SqlCommand
Dim dr As SqlDataReaderDim dr As System.Data.SqlDataReader
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.ExecuteReaderhericles 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 Classhi thanks for your reply unhnd exception! i changed it to sqlcommand but its still error
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??
i just imported it thanks! i have an error on
Dim Connection As New SqlConnection(ConnectionString)
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?
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)
finallySee what errors are coming up. If no errors then you need to check your table.
Let me know how that goes.
For [Type 'SqlCommand' is not defined.] error, u got to do
Imports System.Data.SqlClientFor [Name 'dr' is not declared. ] error, do
Dim dr as SqlDataReader = cmd.ExecuteReader@BlurrieBlue: We're past that.
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: 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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.