How to execute two separate queries from one DBCommand object?

Question

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?

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-06-26T15:49:27.357

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 query after the other - the same way they are sent to the DBCOmmand); if it did 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 rather than copying data into them:

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.

MSDN: Using Statement (Visual Basic)

Thank you so much for the clear explanation. But can I know more about the `Using` ? Does that mean I do not need to close the connection when i use `Using`? — Student, May 21 '16 at 16:57
`Using` closes and disposes of it. Generally anything which has a `Dispose` method should be enclosed in a `Using` block. — Ňɏssa Pøngjǣrdenlarp, May 21 '16 at 17:07

genespos · Answer 2 · 2016-05-21T07:33:00.400

You can use .net connector (download here)
Then you can install it and add it to your project (project -> references -> add -> browse).
Finally add import:

Imports MySql.Data.MySqlClient

So you'll be able to use this:

Dim connStr as String = "Server = localhost; Database = gen_database; Uid = root; Pwd ="
Dim SqlStr as String = "Select DevCompanyName from developer_name_table; Select DevType from development_type_table"
Dim ds As DataSet = MySqlHelper.ExecuteDataset(CnStr, SqlStr)

ds will contain two datatables: one for each query

How to execute two separate queries from one DBCommand object?

2 Answers2