Click here to Skip to main content
14,392,354 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hai. I'm a beginner of C#.NET and MySQL. I would like to know how to display selected sql statement to specific column of datagridview. here is my code so far.

public void BindingMetaboliteName(frmMetCon config)
        {
            schemaForm = new SchemaName();
            string connStr = "datasource=localhost;port=3306;username=root;password=root;";
            conn = new MySqlConnection(connStr);
            command = conn.CreateCommand();
            string database = schemaForm.getData;

            try
            {
                dtable = new DataTable();
                bindingSource = new BindingSource();

                conn.Open();
                dbSumMetabolite.ResetBindings();
                switch (cmbMetabolite.SelectedIndex)
                {
                    case 0:
                        command.CommandText = "SELECT MetaboliteID, Metabolite_Name FROM " + database +
                                              ".Metabolites " +
                                              "WHERE MetaboliteID IN ('met1', 'met2');";
                        MySqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                        dtable.Load(dr);
                        dbSumMetabolite.AutoGenerateColumns = false;
                        dbSumMetabolite.DataSource = dtable;
                        break;

                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


The column i defined in datagridview is

Metabolite ID | Metabolites Name

and the dbSumMetabolite is my datagridview.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

you are setting datatable as data source and it would add the fields in datagridview. instead of this use "for loop" and fill data in datagridview.
   
Comments
arave0521 12-Nov-13 6:37am
   
Hai. may I know more details? I'm quite beginner in mysql and datagridview
Rate this:
Please Sign up or sign in to vote.

Solution 2

see there are 2 ways of showing data in datagridview :-
1) by setting datasource :- by setting datasource , datagridview takes all the columns and record as it is and add it to datagridview (NOTE:- there is no need to add columns if you have req. fields available in the query).

2) by looping:- by looping you need to add columns before runtime and than through looping you can assign value of each cell according to your need.
   
Comments
arave0521 12-Nov-13 6:50am
   
I still don't get it. Okay right now I have two predefined column in design. And I want to insert the sql statement based on the respective column. Can you show any tutorial on this. Thanks
agent_kruger 12-Nov-13 7:32am
   
but you have already got the record in datatable and set the datasource now what is missing?
arave0521 12-Nov-13 7:38am
   
the data is not placed on respective column. what code should i put to make it inserted in respective column
agent_kruger 12-Nov-13 7:40am
   
have you already created data columns before runtme
arave0521 12-Nov-13 7:45am
   
yes. I have predefine the column of the datagridview. 1st column DataPropertyName is "Metabolite ID" and Header Text "Metabolite ID". 2nd Column DataPropertyName is "Metabolite Name" and Header Text is "Metabolite Name"
Rate this:
Please Sign up or sign in to vote.

Solution 3

for(int i=0;i<dtable.rows.count;i++)>
{
    dbSumMetabolite.Rows.Add();
    dbSumMetabolite.Rows[dbSumMetabolite.Rows.Count - 1].Cells["Metabolite ID"].Value = dtable.rows[i]["MetaboliteID"].ToString();
    dbSumMetabolite.Rows[dbSumMetabolite.Rows.Count - 1].Cells["Metabolite Name"].Value = dtable.Rows[i]["Metabolite_Name "].ToString();

}


reply if the code works
   
Comments
arave0521 12-Nov-13 8:04am
   
There is an error saying that rows cannot be programamtically added to datagridview rows collection when control is data-bound
agent_kruger 12-Nov-13 8:06am
   
do not set datasource but manually add datagridview columns
arave0521 12-Nov-13 8:46am
   
It works. thanks.
arave0521 12-Nov-13 10:25am
   
Hi, It has a bit problem. how to make sure the data is not duplicate when we update? I try put dbSumMetabolite.ResetBindings(); it does not work
agent_kruger 12-Nov-13 10:36am
   
write "dbSumMetabolite.DataSource=null;" in the beigning of the code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100