Click here to Skip to main content
14,977,302 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How do I import the data from my datagridview that was import from excel into my SQL Database?

[Picture of my datagridview]



My datagridview:

      Date    |    EmployeeID1  |   EmployeeID2  |  EmployeeID3
---------------------------------------------------------------------
 18/1/2018            1                          2                       3
 19/1/2018            2                          3                       1
 20/1/2018            1                          2                       3



How I want it to be like in my SQL Database:
DutyID        |       Date      |          EmployeeID       |      Shift
------------------------------------------------------------------------------------
       1               18/1/2018	       EmployeeID1                1
       2               18/1/2018               EmployeeID2                2
       3               18/1/2018               EmployeeID3                3
       4               19/1/2018               EmployeeID1                2
       5               19/1/2018               EmployeeID2                3
       6               19/1/2018               EmployeeID3                1
       7               20/1/2018               EmployeeID1                1
       8               20/1/2018               EmployeeID2                2
       9               20/1/2018               EmployeeID3                3


What I have tried:

private void btnInsert_Click(object sender, EventArgs e)
        {

                for (int r = 1; r < dataGridView1.Rows.Count; r++)
                {
                    for (int c = 1; c < dataGridView1.Columns.Count; c++)
                    {
                        try
                        {
                            SqlConnection con = new SqlConnection(constring);
                            SqlCommand query = new SqlCommand("INSERT into RosterTest (EmployeeID, Date, ShiftType) Values (@EmployeeID,@Date,@ShiftType", con);
                            query.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = dataGridView1.Columns[c].HeaderText;
                            query.Parameters.Add("@Date", SqlDbType.Date).Value = dataGridView1.Rows[r].Cells;
                            con.Open();
                            query.ExecuteNonQuery();

                           

                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }


I am getting an error of "Failed to convert parameter value from a DataGridViewCellCollection to a DateTime
Posted
Updated 16-Jan-18 1:51am
v2
Comments
F-ES Sitecore 16-Jan-18 8:03am
   
Always give the line the error occurs on. If it's this one;

query.Parameters.Add("@Date", SqlDbType.Date).Value = dataGridView1.Rows[r].Cells;

"Cells" is a collection of cells in a grid view. It might be 5 cells with numbers, text, booleans etc in it. How does .net know how to convert a collection of disparate objects into a single date value?

1, "Hello", False, null

What is the above as a date?

You need to reference an single specific cell and depending on what datatype is at that cell do what it takes to convert it into a valid date. Your strategy of getting the data from a gridview is wrong though, you should insert the data into SQL as you read it from the Excel file, the gridview is simply a display tool, not a data store.

1 solution

Firstly - avoid posting links to images unless they are absolutely necessary. You have shown the data in the datagridview in your post anyway. Many employers block imgur so I can't see it anyway.

Secondly, this isn't a tutorial site so you will need to at least make an attempt for yourself.

However ... look at the bottom right corner of this page ... specifically look at the "Related Questions" list. That's a list of similar questions to your own.

If this post from that list doesn't help you (it should) ... Insert into SQL Database from datagridview[^] ... then try the next one down, etc.

If you still have problems when you have written some code to do this, then please do come back with follow up questions
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900