How to populate a combobox from two different SQL Server database tables

Question

I am trying to create a system that will load items from a database. There are two comboboxes; combobox1 which loads items from database table 1 and combox2 which loads items from database table 2.

Both tables are in the same database.

Here is was I tried but when I run the system I get this error:

(Conversion from string "SELECT * FROM dbo.Dishes" to type 'Long' is not valid.)

Here is the code I'm using:

Dim connection As New SqlConnection("Server = DESKTOP-1373H91; Initial Catalog = MealPreOrderSystem; Integrated Security = True")
connection.Open()

Dim query As String = "SELECT * FROM dbo.Dishes" And "SELECT * FROM dbo.Desserts"

Dim cmd As SqlCommand
cmd = New SqlCommand(query, connection)

Dim reader As SqlDataReader
reader = cmd.ExecuteReader

While reader.Read
     cbxType.Items.Add(reader.Item("MealName"))
     cbxType.Items.Add(reader.Item("DessertName"))
End While

connection.Close()

Answer 1

In VB.NET,AND is an operator.It is used to perform conjunction between either Booleans or Integers/Doubles/any numeric expression.Lets take your query string as an example :

  Dim query As String = "SELECT * FROM dbo.Dishes" And "SELECT * FROM dbo.Desserts"

You are using AND here to join 2 sentences/strings which wouldn't result in anything rather it is trying to cast it as a Long.

Try to execute this command in SQL and you won't find any luck :(.

Your statements are correct :

  SELECT * FROM dbo.Dishes
  SELECT * FROM dbo.Desserts

But the way you are trying to achieve your goals is incorrect :(.

To get the data from the database into your combobox, what you can do is either use two comboboxes with separated SQL Queries/SQL Commands or you can use one combobox where you add data from both the databases but separate them with some special characters such as a comma ,

A sample may look like :

With one combobox

Dim cmd1 as new SqlCommand("SELECT * FROM dbo.Dishes",connection)
Dim dr as SqlDatareader = cmd1.ExecuteReader
While dr.Read
 mycombo1.Items.Add(dr(0))  ' Here 0 is the column count,change it as required 
End while
Dim cmd2 as new SqlCommand("SELECT * FROM dbo.Desserts",connection)
Dim dr2 as SqlDatareader = cmd2.ExecuteReader
While dr2.Read
 mycombo2.Items.Add(dr2(0))  ' Here 0 is the column count,change it as required 
End while

With 1 combobox

Here it gets a bit complicated.Firstly you need to populate your combobox from the data received from the first dataReader.Then, when the 2nd datareader is reading the data , you need to update the existing data/Item of the combobox keeping the existing data/item but adding new data/item to each existing data/item(separating them with ,).

Sample :

 Dim i as Integer
 Dim cmd1 as new SqlCommand("SELECT * FROM dbo.Dishes",connection)
 Dim dr as SqlDatareader = cmd1.ExecuteReader
 While dr.Read
   mycombo1.Items.Add(dr(0))
 End while
 Dim cmd2 as new SqlCommand("SELECT * FROM dbo.Desserts",connection)
 Dim dr2 as SqlDatareader = cmd2.ExecuteReader
 While dr2.Read
  mycombo1.Items(i) = myconbo1.Items(i) & "," & dr2(0)
  i = i + 1
 End while

Now, NOTE THAT I AM USING MULTIPLE DATAREADERS WITH THE SAME CONNECTION ,SO YOU MAY NEED TO INCLUDE MultipleActiveResultSets=True IN YOUR CONNECTION STRING or ENCLOSE THE DATAREADERS IN USING STATEMENTS or CALL dataReader.Close AFTER EACH DATAREADER HAS COMPLETED READING FROM THE DATABASE

This will solve your issue :)

Answer 2

You should be a bit more specific on what columns you are pulling from the 2 tables. if they are similar, you could write a sql query to UNION ALL the fields with a simple control to identify which record came from which table.

Example of SQL command string:

"SELECT 'M' AS Ctl, MealName AS aName FROM dbo.Dishes " &
"UNION ALL " &
"SELECT 'D' AS Ctl, DessertName AS aName FROM dbo.Desserts" 

As mentioned by many here already, it seems like you are referencing only 1 ControlBox to list the fields returned cbxType

below is the reader (adapted to 2 ComboBoxes):

While reader.Read
Select Case reader.Item("Ctl")
    Case "M"
        cbxMType.Items.Add(reader.Item("aName"))
    Case "D"
        cbxDType.Items.Add(reader.Item("aName"))
    End Select
End While

Hope this helps

Answer 3

Looks like you don't know how to write SQL queries (and your VB syntax itself looks faulty - string AND string?).

    Dim connection As New SqlConnection("Server = DESKTOP-1373H91; Initial Catalog = MealPreOrderSystem; Integrated Security = True")
    Dim query As String = <cmdString>
    SELECT MealName as Name FROM dbo.Dishes
    union
    SELECT DessertName as Name FROM dbo.Desserts
    </cmdString>
    Dim cmd As SqlCommand
    Dim reader As SqlDataReader
    connection.Open()
    cmd = New SqlCommand(query, connection)
    reader = cmd.ExecuteReader
    While reader.Read
        cbxType.Items.Add(reader.Item("Name"))
    End While

    connection.Close()

Note: You are saying 2 comboboxes but your code seemed to be loading all the items to a single combobox. If you really need 2 comboboxes then use 2 SqlCommand and Reader loops (actually it would be better if you simply have used Linq for this).