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, it will only execute the first query because there is no way to signal the end of the data from one query and the start of data from the other. If it could work that way, your "double read" loop would fail when 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 ' 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)). 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 table. The user sees the friendly name (
DisplayMember), which the code can easily access the unique identifier for the selection ("ValueMember").
When using bound list controls, rather than using
SelectedIndex and the
SelectedIndexChanged event, you'd use
SelectedItem to access the actual data.