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.(London, 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]

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.(London, 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.SqlClient

Private Sub stateComboBox_SelectedValueChanged(sender as object, e as
EventArgs) Handles stateComboBox.SelectedValueChanged
Dim conn As New SqlConnection("your conn string") ' Use OleDb or
ODBC objects if needed
Using (conn)
conn.Open()
Dim com As SqlCommand = conn.CreateCommand()
Using (com)
com.CommandType = CommandType.Text
com.CommandText = String.Format("Select Country From
MyTable Where State = '{0}' Order By Country Asc", stateComboBox.Text)
Dim dr As SqlDataReader = com.ExecuteReader()
Using (dr)
countryComboBox.Clear()
While (dr.Read())

countryComboBox.Items.Add(dr.GetValue(0).ToString())
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]

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.(London, 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
Dim OleDBQuery As String = "SELECT DISTINCT State FROM
Table1"
Dim OleDBConn As New
System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB.1;Persist
Security Info=False;User ID=<user>;Password=<pwd>;Initial
Catalog=<database>;Data Source=<dataserver>")
Dim OleDBComm As New
System.Data.OleDb.OleDbCommand(OleDBQuery, OleDBConn)
Dim OleDBRec As System.Data.OleDb.OleDbDataReader

OleDBConn.Open()
OleDBRec = OleDBComm.ExecuteReader()

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

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

Private Sub cb1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cb1.SelectedIndexChanged
Try
cb2.Items.Clear()
Dim OleDBQuery As String = "SELECT Country " + _
"FROM Table1 " + _
"WHERE State = '" + cb1.Text +
"'"
Dim OleDBConn As New
System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB.1;Persist
Security Info=False;User ID=<user>;Password=<pwd>;Initial
Catalog=<database>;Data Source=<dataserver>")
Dim OleDBComm As New
System.Data.OleDb.OleDbCommand(OleDBQuery, OleDBConn)
Dim OleDBRec As System.Data.OleDb.OleDbDataReader

OleDBConn.Open()
OleDBRec = OleDBComm.ExecuteReader()

While OleDBRec.Read
cb2.Items.Add(OleDBRec("Country"))
End While

OleDBRec.Close()
OleDBConn.Close()
cb2.SelectedIndex = 0

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