Solved

Datagridview values not wreiting to database

Posted on 2014-09-23
6
313 Views
Last Modified: 2014-09-24
I have a datagridview I updated and now I want to write it to the database, I wrote it to the database but the values I updated in the datagridview are not there in the database does anyone know why and how I can fix that

   private void btnRun_Click(object sender, EventArgs e)
        {
            ToolNumbers = new List<string>();
            if (cbControllerNumber.Text == "")
            {
                MessageBox.Show("Please select a controller number");
                return;
            }
            controllernumber = Convert.ToInt32(cbControllerNumber.Text.ToString());
            DataTable dt = FillFirstGrid(controllernumber);
            dgvFirst.DataSource = dt;
            foreach (DataGridViewRow row in this.dgvFirst.Rows)
            {
                DataGridViewCell cell = row.Cells[2];
                if (cell.Value == null || cell.Value.Equals(""))
                {
                    continue;
                }


                ToolNumbers.Add(cell.Value.ToString());

            }


            for (int i = 0; i < dgvFirst.Rows.Count - 1; i++)
            {
               
                DataGridViewRow row = dgvFirst.Rows[i];
                DataGridViewRow row2 = dgvFirst.Rows[i + 1];
                row.Cells["EndDate"].Value = row2.Cells["StartDate"].Value;


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

                if (string.IsNullOrEmpty(dgvFirst.Rows[i].Cells["EndDate"].Value.ToString()) && dgvFirst.Rows[i].Cells["StartDate"].Value != null)
                {
                    dgvFirst.Rows[i].Cells["EndDate"].Value = "1/1/2041 00:00:00";
                }
            }
            dgvFirst.EndEdit();
            dgvFirst.Refresh();
            runInsert();
        }

Open in new window

0
Comment
Question by:r3nder
  • 3
  • 3
6 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40340551
Where do you write it back to the database?
0
 
LVL 6

Author Comment

by:r3nder
ID: 40341544
 void runInsert()
        {
            
            try
            {
                using(MySqlConnection con = new MySqlConnection(conStr))
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.CommandText = "insert into precont(StartDate,EndDate,ToolNumber,JobType) values (@i1,@i2,@i3,@i4)";
                    
                    cmd.Parameters.Add("@i1", MySqlDbType.DateTime);
                    cmd.Parameters.Add("@i2", MySqlDbType.DateTime);
                    cmd.Parameters.Add("@i3", MySqlDbType.VarChar);
                    cmd.Parameters.Add("@i4", MySqlDbType.VarChar);
                   
                    cmd.Parameters["@i4"].Value = "surefire";
                    for (int i = 0; i < this.dgvFirst.Rows.Count - 1; i++)
                    {
                        cmd.Parameters["@i1"].Value = Convert.ToDateTime(this.dgvFirst.Rows[i].Cells[0].Value.ToString());
                        cmd.Parameters["@i2"].Value = Convert.ToDateTime(this.dgvFirst.Rows[i].Cells[1].Value.ToString());
                        cmd.Parameters["@i3"].Value = this.dgvFirst.Rows[i].Cells[2].Value;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (MySqlException er)
            {
                MessageBox.Show("Error:" + er.ToString());
            }
        }

Open in new window

0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40341643
Are the initial state of the DataGridView retrieved from the database and if so did you used a DataAdapter? If so you could use the DataAdapter.Update(dt) to accomplish updating the database. If the statement to get the data is retrieving data from multiple tables then this becomes a little be more difficult because you would then need to make sure that the DataAdapter has the DeleteCommand, InsertCommand and UpdateCommand properly set.

In your runInsert you are using the Insert SQL command. If these records already exist in the database you would need to do an update not an insert.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 6

Author Comment

by:r3nder
ID: 40341881
found the problem - I was creating a table precont with code
Create Table precont  as(select toolnumber, uploadtime as 'StartTime', '' AS 'EndTime' ......

This made the endtime  field a char field not a datetime
Thanks for hanging in there Mr Soto
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40341885
Thanks Mr Soto
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40341902
Not a problem r3nder, always glad to help.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This is about my first experience with programming Arduino.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question