I have the following code that tries to get records from two different tables and then add them to the particular comboboxes. Only the first query works and the second one is ignored.
sqlConn = New MySqlConnection
connStr = New String("Server = localhost; Database = gen_database; Uid = root; Pwd =")
sqlConn.ConnectionString = connStr
myCommand = New MySqlCommand("Select DevCompanyName from developer_name_table; Select DevType from development_type_table")
myCommand.CommandType = CommandType.Text
myCommand.Connection = sqlConn
dR = myCommand.ExecuteReader()
Do While dR.Read()
ComboBox2.Items.Add(dR("DevType")) 'Error shows here Could not find specified column in results: DevType
Catch ex As MySqlException
DBDataReader with 2 queries, only only the first executes because there is no way to signal which table/query each read item is from. Your "double read" loop seems to assume they will be returned at the same time (rather than one after the other); if it could work that way, it would fail whenever there are not the same number of rows in each table.
DataTables affords you the chance to simply bind the result to your combos:
Dim SQL = "SELECT * FROM Sample; SELECT * FROM Simple" Dim ds As New DataSet Using dbcon As New MySqlConnection(MySQLConnStr), cmd As New MySqlCommand(SQL, dbcon) dbcon.Open() Dim da As New MySqlDataAdapter(cmd) da.Fill(ds) End Using ' debug results Console.WriteLine(ds.Tables.Count) Console.WriteLine(ds.Tables(0).Rows.Count) Console.WriteLine(ds.Tables(1).Rows.Count)
If I look at the output window, it will print
10000 (rows in T(0)) and
6 (rows in T(1)). Not all DBProviders have this capability. Access for instance will choke on the SQL string. Other changes to the way your code is composed:
Usingblock does that for us: The target objects are created at the start and closed and disposed at
The code fills a
DataSet from the query, in this case creating 2 tables. Rather than copying the data from one container to another (like a control), you can use a
DataTable as the
cboDevName.DataSource = ds.Tables(0) cboDevName.DisplayMember = "DevName" ' column names cboDevName.ValueMember = "Id" cboDevType.DataSource = ds.Tables(1) cboDevType.DisplayMember = "DevType" cboDevType.ValueMember = "DevCode"
The result will be all the rows from each table appearing in the respective combo control. Typically with this type of thing, you would want the ID/PK and the name which is meaningful to the user in the query. The user sees the friendly name (
DisplayMember), which the code can easily access the unique identifier for the selection (
When using bound list controls, rather than using
SelectedIndex and the
SelectedIndexChanged event, you'd use
SelectedItem to access the actual data.