Student Student - 1 year ago 77 Question

VB.NET MYSQL How to run two separate query on a single string?

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
MsgBox("Connection Open.")
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
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 Source

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

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)

    Dim da As New MySqlDataAdapter(cmd)

End Using


If I look at the output window, it will print 2 (tables), 10000 (rows in T(0)) and 6 (rows in T(1)). 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 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 SelectedValue and SelectedItem to access the actual data.