Solved

Datagridview values not wreiting to database

Posted on 2014-09-23
6
304 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

705 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

20 Experts available now in Live!

Get 1:1 Help Now