C# connection to SQL Server using multiple comboboxes to data in multiple tables

-1

I have the following C# code that I need to pass the information from a SQL Server database into a combobox and listbox. I can get the customer information to work but as soon as I add the products information (in bold), I get an error.

I am not sure how to write the select query so the information from the relevant tables will pass to the required cb and lb fields?

private void frmSalesAdd_Load(object sender, EventArgs e)
{
     string selectQuery;

     selectQuery = "SELECT * FROM Customers";
     **selectQuery += "SELECT * FROM Products";**

     SqlConnection conn = ConnectionManager.DatabaseConnection();
     SqlDataReader rdr = null;

     try
     {
         conn.Open();

         SqlCommand cmd = new SqlCommand(selectQuery, conn);
         rdr = cmd.ExecuteReader();

         while (rdr.Read())
         {
             lbCustomerID.Items.Add(rdr["CustomerID"].ToString());
             cbCustomer.Items.Add(rdr["LastName" ].ToString());
             **lbProductID.Items.Add(rdr["ProductID"].ToString());
             cbProduct.Items.Add(rdr["Product"].ToString());**
         }

         rdr.Close();
    }
}

The error I get is:

UnsuccessfulSystem.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '*',

Any help would be appreciated.

c#
sql-server
asked on Stack Overflow Nov 25, 2018 by JulieM • edited Nov 25, 2018 by marc_s

2 Answers

0

ExecuteReader will execute ONE valid SQL command. You cannot add several commands to SqlCommand and execute it with one read.

This should work:

private void frmSalesAdd_Load(object sender, EventArgs e)
{
    string selectQuery;

    selectCustomers = "SELECT * FROM Customers";
    selectProducts = "SELECT * FROM Products";

    SqlConnection conn = ConnectionManager.DatabaseConnection();

    conn.Open();

    SqlCommand cmd = new SqlCommand(selectCustomers, conn);

    using(SqlDataReader rdr = cmd.ExecuteReader()) 
    {
        while (rdr.Read())
        {
            lbCustomerID.Items.Add(rdr["CustomerID"].ToString());
            cbCustomer.Items.Add(rdr["LastName" ].ToString());
        }
    }

    cmd = new SqlCommand(selectProducts, conn);

    using(SqlDataReader rdr = cmd.ExecuteReader()) 
    {
        while (rdr.Read())
        {
            lbProductID.Items.Add(rdr["ProductID"].ToString());
            cbProduct.Items.Add(rdr["Product"].ToString());
        }
    }
}
answered on Stack Overflow Nov 25, 2018 by H.G. Sandhagen • edited Nov 25, 2018 by marc_s
0

If you really want to return multiple result sets from a query, you need to also read them as such with your database reader.

Try this code:

private void frmSalesAdd_Load(object sender, EventArgs e)
{
     // make sure to have a VALID SQL statement here!
     string selectQuery = "SELECT * FROM Customers; SELECT * FROM Products";

     SqlConnection conn = ConnectionManager.DatabaseConnection();

     try
     {
         conn.Open();

         SqlCommand cmd = new SqlCommand(selectQuery, conn);
         SqlDataReader rdr = cmd.ExecuteReader();

         // read the first result set - the return values from "SELECT * FROM Customers"
         while (rdr.Read())
         {
             lbCustomerID.Items.Add(rdr["CustomerID"].ToString());
             cbCustomer.Items.Add(rdr["LastName" ].ToString());
         }

         // when you're done reading the first result set - 
         // try to move ahead to the next result set
         if (rdr.NextResult())
         {
             // read the second result set - the return values from "SELECT * FROM Products"
             while (rdr.Read())
             {
                 lbProductID.Items.Add(rdr["ProductID"].ToString());
                 cbProduct.Items.Add(rdr["Product"].ToString());
             }
         }

         rdr.Close();
    }
}
answered on Stack Overflow Nov 25, 2018 by marc_s

User contributions licensed under CC BY-SA 3.0