Inserting Values from DataGridView to Mysql Table

Question

0.00/5 (No votes)

See more:

Hi All,
I tried with many cases but not getting proper result.
I have one DataGridView and Button,

I have one table which contains 5 columns, In my windows application i will give 3 column values and 2 column values i want to get from datagridview and insert to mysql table.

In the run time, i will insert values to datagridview when i clicked on the button i want to get all values from datagridview to string or string array. Then i will insert to mysl table.</pre>

C#

con = new MySqlConnection(str);
           try
           {
               con.Open();
               MySqlCommand cmd = con.CreateCommand();
               //cmd.CommandText = "insert into balance values" + "(@i1,@i2,@i3,@i4,@i5)";
               //cmd.Parameters.Add("@i1", MySqlDbType.VarChar);
               //cmd.Parameters.Add("@i2", MySqlDbType.VarChar);
               //cmd.Parameters.Add("@i3", MySqlDbType.VarChar);
               //cmd.Parameters.Add("@i4", MySqlDbType.Float);
               //cmd.Parameters.Add("@i5", MySqlDbType.VarChar);
               //cmd.Parameters["@i1"].Value = busname;
               //cmd.Parameters["@i2"].Value = cusname;
               //cmd.Parameters["@i3"].Value = this.dataGridView1[0, 0].Value;
               //cmd.Parameters["@i4"].Value = this.dataGridView1[1, 0].Value;
               //cmd.Parameters["@i5"].Value = "345";
               //cmd.ExecuteNonQuery();
               string[] st = new string[this.dataGridView1.Rows.Count];

               for (int i = 0; i <= this.dataGridView1.Rows.Count; i++)
               {
                   cmd.CommandText = "insert into balance values" + "(@p1,@p2,@p3,@p4,@p5)";
                   cmd.Parameters.Add("@p1", MySqlDbType.VarChar);
                   cmd.Parameters.Add("@p2", MySqlDbType.VarChar);
                   cmd.Parameters.Add("@p3", MySqlDbType.VarChar);
                   cmd.Parameters.Add("@p4", MySqlDbType.Float);
                   cmd.Parameters.Add("@p5", MySqlDbType.VarChar);
                   cmd.Parameters["@p1"].Value = busname;
                   cmd.Parameters["@p2"].Value = cusname;
                   cmd.Parameters["@p3"].Value = this.dataGridView1[0, i].Value;
                   cmd.Parameters["@p4"].Value = this.dataGridView1[1, i].Value;
                   cmd.Parameters["@p5"].Value = "345";
                   cmd.ExecuteNonQuery();

               }

If i use the insert option within the for loop then i am getting error as

Parameter '@p1' has already been defined. same error for '@p2', '@p5' also.

I tried without using the for loop, which i commented. That is working fine. It is inserting into table also. But i dnt know whats wrong when i am using with the for loop.

Posted 16-Jul-13 1:31am

SrinivasTiru

Updated 16-Jul-13 21:26pm

v2

Add a Solution

Comments

Maciej Los 16-Jul-13 9:18am

What have you done so far? Where are you stuck? What is an exact question?

SrinivasTiru 16-Jul-13 9:26am

In my windows form one datagridview and button is their. In DataGridView i added two columns named Date and Balance. I have to give values in the run time, if i insert 4 values for date and balance and click on button then it should insert into mysql MgrTable.

in MgrTable there are 5 columns, so i cant directly insert from datagrid to table because i want another 3 column values to insert. so if i get all the values from datagrid then i can loop through it and insert into table along with other 3 column values.

I checked with InsertCommand in the datagrdiview, but it is not working when i want to insert those gridvalues along with 3 other values. Can I insert using InsertCommand only or have to take values from datagridview and then insert into table.

Priyanka7777 17-Jul-13 3:34am

Try defining the parameters outside and before the for loop.

jaideepsinh 17-Jul-13 3:43am

Try priyanka's solution.

Rain Dancer 17-Jul-13 4:17am

What if you try the INSERT within a case statement? Case 1 being the first three columns, and case 2 being the second two, default case being an error. I'm no genius, but I try what works. As for converting to strings or string array, just use the Convert.ToString() or Convert.ToArray() before you export the data to the SQL table.

1 solution

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)

SrinivasTiru · Answer 1 · 2013-07-16T23:38:00

C#

private void button1_Click(object sender, EventArgs e)
      {
          con = new MySqlConnection(str);
          try
          {
              con.Open();
              MySqlCommand cmd = con.CreateCommand();
              cmd.CommandText = "insert into balance values" + "(@i1,@i2,@i3,@i4,@i5)";
              cmd.Parameters.Add("@i1", MySqlDbType.VarChar);
              cmd.Parameters.Add("@i2", MySqlDbType.VarChar);
              cmd.Parameters.Add("@i3", MySqlDbType.VarChar);
              cmd.Parameters.Add("@i4", MySqlDbType.Float);
              cmd.Parameters.Add("@i5", MySqlDbType.VarChar);
              cmd.Parameters["@i1"].Value = busname;
              cmd.Parameters["@i2"].Value = cusname;
              cmd.Parameters["@i5"].Value = "345";

              for (int i = 0; i < this.dataGridView1.Rows.Count-1; i++)
              {

                  cmd.Parameters["@i3"].Value = this.dataGridView1[0,i].Value;
                  cmd.Parameters["@i4"].Value = this.dataGridView1[1,i].Value;
                  cmd.ExecuteNonQuery();
              }
              this.Close();
          }
          catch (MySqlException er)
          {
              MessageBox.Show("Error:" + er.ToString());
          }
      }

Finally i got expected output for this. The problem is, in DataGridView at the run time when we give values for 1 row then it will automatically takes another row in Row.Count property. So i gave "this.dataGridView1.Rows.Count-1" in for loop.