Solved

Datagridview values not wreiting to database

Posted on 2014-09-23
6
308 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 62

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 62

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 62

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now