Combo Box from SQL

[5070707]

Hi all
this question might be more SQL oriented or at least database
structure...
i created a table in my SQL which contains 2 columns:
1. state
2. country

the way i input the data is:
State Country
UK London
UK Manchaster
USA NY
USA Texas
etc'

then, created two combo boxes on a simple form (visual basic 2005)
added connection string to the SQL and bound them to the combo boxes.
first STATE to combo "State" then COUNTRY to combo "Counrty"

now STATE: contains a list of states(UK, USA etc') and COUNTRY
contains a list of countries.(Lond on, NY etc')
what i want to create is this:
when a user choose from the combo the UK state, then only the relevant
countries will
appear in the other combo box..IE: London, Manchester etc'

By the question you probably understand i don't know much about
programming, so go easy on me :)

Thanks,
50.

[rowe_newsgroups]

On May 8, 5:43 am, 5070...@gmail.c om wrote:

Hi all
this question might be more SQL oriented or at least database
structure...
i created a table in my SQL which contains 2 columns:
1. state
2. country

the way i input the data is:
State Country
UK London
UK Manchaster
USA NY
USA Texas
etc'

then, created two combo boxes on a simple form (visual basic 2005)
added connection string to the SQL and bound them to the combo boxes.
first STATE to combo "State" then COUNTRY to combo "Counrty"

now STATE: contains a list of states(UK, USA etc') and COUNTRY
contains a list of countries.(Lond on, NY etc')
what i want to create is this:
when a user choose from the combo the UK state, then only the relevant
countries will
appear in the other combo box..IE: London, Manchester etc'

By the question you probably understand i don't know much about
programming, so go easy on me :)

Thanks,
50.

You can do this with parameterized databindings, but it might be
easier to do it manually if you are only worrying about the combobox.

Something Like:

' Typed in message

Imports System.Data.Sql Client

Private Sub stateComboBox_S electedValueCha nged(sender as object, e as
EventArgs) Handles stateComboBox.S electedValueCha nged
Dim conn As New SqlConnection(" your conn string") ' Use OleDb or
ODBC objects if needed
Using (conn)
conn.Open()
Dim com As SqlCommand = conn.CreateComm and()
Using (com)
com.CommandType = CommandType.Tex t
com.CommandText = String.Format(" Select Country From
MyTable Where State = '{0}' Order By Country Asc", stateComboBox.T ext)
Dim dr As SqlDataReader = com.ExecuteRead er()
Using (dr)
countryComboBox .Clear()
While (dr.Read())

countryComboBox .Items.Add(dr.G etValue(0).ToSt ring())
End While
End Using
End Using
End Using
End Sub

That code might need some work, but it should be close. Let me know if
you need further help.

Thanks,

Seth Rowe

[diAb0Lo]

On 8 mayo, 05:43, 5070...@gmail.c om wrote:

Hi all
this question might be more SQL oriented or at least database
structure...
i created a table in my SQL which contains 2 columns:
1. state
2. country

the way i input the data is:
State Country
UK London
UK Manchaster
USA NY
USA Texas
etc'

then, created two combo boxes on a simple form (visual basic 2005)
added connection string to the SQL and bound them to the combo boxes.
first STATE to combo "State" then COUNTRY to combo "Counrty"

now STATE: contains a list of states(UK, USA etc') and COUNTRY
contains a list of countries.(Lond on, NY etc')
what i want to create is this:
when a user choose from the combo the UK state, then only the relevant
countries will
appear in the other combo box..IE: London, Manchester etc'

By the question you probably understand i don't know much about
programming, so go easy on me :)

Thanks,
50.

Hi, there

Try this. This solution is using Ole DB Connection. You must set the
UserID, Password, Database and Data server.

cb1(combobox) = State
cb2(combobox) = Country

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Try
Dim OleDBQuery As String = "SELECT DISTINCT State FROM
Table1"
Dim OleDBConn As New
System.Data.Ole Db.OleDbConnect ion("Provider=S QLOLEDB.1;Persi st
Security Info=False;User ID=<user>;Passw ord=<pwd>;Initi al
Catalog=<databa se>;Data Source=<dataser ver>")
Dim OleDBComm As New
System.Data.Ole Db.OleDbCommand (OleDBQuery, OleDBConn)
Dim OleDBRec As System.Data.Ole Db.OleDbDataRea der

OleDBConn.Open( )
OleDBRec = OleDBComm.Execu teReader()

While OleDBRec.Read
cb1.Items.Add(O leDBRec("State" ))
End While

OleDBRec.Close( )
OleDBConn.Close ()
Catch dbError As Exception
MsgBox(dbError. Message, MsgBoxStyle.OKO nly, "Error")
End Try
End Sub

Private Sub cb1_SelectedInd exChanged(ByVal sender As
System.Object, ByVal e As System.EventArg s) Handles
cb1.SelectedInd exChanged
Try
cb2.Items.Clear ()
Dim OleDBQuery As String = "SELECT Country " + _
"FROM Table1 " + _
"WHERE State = '" + cb1.Text +
"'"
Dim OleDBConn As New
System.Data.Ole Db.OleDbConnect ion("Provider=S QLOLEDB.1;Persi st
Security Info=False;User ID=<user>;Passw ord=<pwd>;Initi al
Catalog=<databa se>;Data Source=<dataser ver>")
Dim OleDBComm As New
System.Data.Ole Db.OleDbCommand (OleDBQuery, OleDBConn)
Dim OleDBRec As System.Data.Ole Db.OleDbDataRea der

OleDBConn.Open( )
OleDBRec = OleDBComm.Execu teReader()

While OleDBRec.Read
cb2.Items.Add(O leDBRec("Countr y"))
End While

OleDBRec.Close( )
OleDBConn.Close ()
cb2.SelectedInd ex = 0

Catch dbError As Exception
MsgBox(dbError. Message, MsgBoxStyle.OKO nly, "Error")
End Try
End Sub

:)