从excel读取后，如何将datagridview插入SQL数据库 [英] How to insert datagridview into SQL database after reading it from excel
问题描述
如何将从excel导入的datagridview中的数据导入我的SQL数据库？
[我的datagridview图片]
我的datagridview：
日期| EmployeeID1 | EmployeeID2 | EmployeeID3
--------------------------------------------- ------------------------
18/1/2018 1 2 3
19/1/2018 2 3 1
20/1/2018 1 2 3
我希望它在我的SQL数据库中如何：
DutyID |日期| 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
我尝试过：
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）值（@ 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）;
}
}
}
我收到错误无法将参数值从DataGridViewCellCollection转换为DateTime
How do I import the data from my datagridview that was import from excel into my SQL Database? 这篇关于从excel读取后，如何将datagridview插入SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
其次，这不是一个教程网站所以你需要至少尝试自己。
但是......请查看本页右下角...具体请查看相关问题列表。这是你自己的类似问题列表。
如果该列表中的这篇文章对你没有帮助（应该）... 从datagridview插入SQL数据库 [ ^ ] ...然后尝试下一个，等等。 />
如果您在编写代码时仍然遇到问题，请回复后续问题
[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 DateTimeFirstly - 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
