Datagridview values not wreiting to database

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

LVL 6
r3nderAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
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
 
Fernando SotoRetiredCommented:
Where do you write it back to the database?
0
 
r3nderAuthor Commented:
 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
r3nderAuthor Commented:
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
 
r3nderAuthor Commented:
Thanks Mr Soto
0
 
Fernando SotoRetiredCommented:
Not a problem r3nder, always glad to help.
0
All Courses

From novice to tech pro — start learning today.