Solved

Datagridview values not wreiting to database

Posted on 2014-09-23
6
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Stressed Out?

Watch some penguins on the livecam!

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Simple Linear Regression
Six Sigma Control Plans

688 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