Student Student - 6 months ago 12
MySQL Question

How to execute two separate queries from one DBCommand object?

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.



Try
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
ComboBox1.Items.Clear()
sqlConn.Open()
MsgBox("Connection Open.")
dR = myCommand.ExecuteReader()
Do While dR.Read()
ComboBox1.Items.Add(dR("DevCompanyName"))
ComboBox2.Items.Add(dR("DevType")) 'Error shows here Could not find specified column in results: DevType
Loop
Catch ex As MySqlException
MsgBox(ex.ToString)
Finally
dR.Close()
sqlConn.Close()
End Try


I can think of another way which is to do it in multiple query but can the code be simplified to something like this?

Answer

Using a 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.

Using 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 2 (tables), 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:

  • DBConnections and DBCommand objects need to be disposed. They allocate resources, so they need to be created, used and disposed to release those resources.
  • The Using block does that for us: The target objects are created at the start and closed and disposed at End Using.
  • The code above stacks or combines 2 such blocks.

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 DataSource:

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 (ValueMember).

When using bound list controls, rather than using SelectedIndex and the SelectedIndexChanged event, you'd use SelectedValue and SelectedItem to access the actual data.