Add mysql to datagridview on predefined column

Question

0.00/5 (No votes)

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.

C#

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 11-Nov-13 17:34pm

arave0521

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

agent_kruger · Answer 1 · 2013-11-12T00:32:00

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.

Posted 12-Nov-13 0:32am

agent_kruger

Comments

arave0521 12-Nov-13 6:37am

Hai. may I know more details? I'm quite beginner in mysql and datagridview

agent_kruger · Answer 2 · 2013-11-12T00:41:00

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.

Posted 12-Nov-13 0:41am

agent_kruger

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"

agent_kruger · Answer 3 · 2013-11-12T01:50:00

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

Posted 12-Nov-13 1:50am

agent_kruger

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