c# fill datatable with multiple queries mysql database

Question

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

By using a JOIN you give up the (generated) insert/update statements. A DataSet with linked tables can also help you. See here — Henk Holterman
– Henk Holterman, Commented Mar 26, 2012 at 16:24

Ganesh Kamath - 'Code Frenzy' · Accepted Answer · 2016-11-03 11:37:53Z

1

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

edited Nov 3, 2016 at 11:37

Ganesh Kamath - 'Code Frenzy'

5,3513 gold badges48 silver badges65 bronze badges

answered Mar 26, 2012 at 16:48

Min Min

6,2282 gold badges21 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# fill datatable with multiple queries mysql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related