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.
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());
}
}
}
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();
}
}
User contributions licensed under CC BY-SA 3.0