bmorrison1982 bmorrison1982 - 1 month ago 6
MySQL Question

c# fill datatable with multiple queries mysql database

I have multiple tables in a mysql database and I am trying to put the information from one table into a gridview. That was easy but some of the rows are just the id numbers from other tables. so my row looks like

proposal_Line_Id , proposal_Id, day_Name , proposal_Desc, proposal_Vol , proposal_Vol_Unit, item_Id , product_Id, proposal_Qty , proposal_Discount, proposal_Cost , proposal_Total,

the 2 in bold are number that are the key ID numbers for 2 other tables

***_items and ***_products are the other tables

what i need to do is fill my data table with the names from those so I know its a where statement or a join anyway here is my connection

string ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
MySqlConnection connection;
MySqlDataAdapter adapter;







connection = new MySqlConnection(ConnectionString);

try
{
//prepare query to get all records from items table
string query = "select * from ***_proposal_line where proposal_Id = " + b + "";



//prepare adapter to run query
adapter = new MySqlDataAdapter(query, connection);

//create a DataTable to hold the query results
DataTable dTable = new DataTable();


//get query results in dataset
adapter.Fill(dTable);

//set the BindingSource DataSource
dataGridView1.DataSource = dTable;

}
catch (MySqlException ex)
{


}
}


any questions let me know i will try to explain better

Brent

Answer

As far as understand the question, I think you want something like this in query and bind into GridView.

SELECT 
  pl.proposal_Line_Id,
  pl.proposal_Id,
  pl.day_Name,
  pl.proposal_Desc,
  pl.proposal_Vol,
  pl.proposal_Vol_Unit,
  * * it.ITEMNAME * *,
  * * pd.PRODUCTNAME * *,
  pl.proposal_Qty,
  pl.proposal_Discount,
  pl.proposal_Cost,
  pl.proposal_Total 
FROM
  * * ? ? ? _proposal_line * * pl,
  * * ? ? ? _items * * it,
  * * ? ? ? _products * * pd 
WHERE pl.item_id = it.item_id 
  AND pl.product_id = pd.product_id 
  AND pl.proposal_id = 1 
Comments