时间戳

首页 > 解决方案 > WPF使用for循环将数据从datagrid插入sql数据库

wpf - WPF使用for循环将数据从datagrid插入sql数据库

问题描述

我正在尝试使用for循环将 WPF Datagrid 中的行数据插入 SQL 数据库。不幸的是，我收到一个错误，我不知道我的代码有什么问题。请问你能检查我的代码吗？

For Z As Integer = 0 To Dawam_Grid.Items.Count - 1
Dim rd As SqlDataReader
Dim conn As New SqlConnection
Dim cmd As New SqlCommand

If Not conn Is Nothing Then conn.Close()

conn.ConnectionString = "User Id='" & sb2.UserID & "';Pwd='" & sb2.Password & "';DATA SOURCE='" & sb2.DataSource & "';Initial Catalog='" & sb2.InitialCatalog & "';MultipleActiveResultSets=true"
conn.Open()
Dim ds As DataSet = New DataSet
ds.Clear() `?
cmd.Connection = conn

' dt.Columns.Add("ShowRoom_col")
' dt.Columns.Add("Job")
Dim mycommand As New SqlCommand("insert into Scedual (emp_code,Name_col,Date_Col,Shift_Col,Mor_from,Mor_to,eve_from, " _
                                                  & "eve_to,BasicSalary_col,Hour_Value_col2,Over_Hours_col2,Trans_Value_col2, " _
                                                  & "Total_OverTime_col2,Month_No,create_user,create_date,create_time,Ikama_No,Official_Hours,Day_Shift_Hours, " _
                                                  & "Day_Shift_Extra_Hours,Month_Shift_Hours,Month_Shift_Extra_Hours,Month_Official_Hours,from_date,to_date,ShowRoom_col,Job,Year_No) " _
                                                      & "values (@emp_code,@Name_col,@Date_Col,@Shift_Col,@Mor_from,@Mor_to,@eve_from, " _
                                                  & "@eve_to,@BasicSalary_col,@Hour_Value_col2,@Over_Hours_col2, " _
                                                  & "@Trans_Value_col2,@Total_OverTime_col2,@Month_No,@create_user,@create_date,@create_time,@Ikama_No,@Official_Hours,@Day_Shift_Hours, " _
                                                  & "@Day_Shift_Extra_Hours,@Month_Shift_Hours,@Month_Shift_Extra_Hours,@Month_Official_Hours,@from_date,@to_date,@ShowRoom_col,@Job,@Year_No)", conn)




mycommand.Parameters.AddWithValue("@ShowRoom_col", Dawam_Grid.Items(Z).Cells("ShowRoom_col1").Value.ToString)
mycommand.Parameters.AddWithValue("@Job", Dawam_Grid.Items(Z).Cells("Job1").Value.ToString)
mycommand.Parameters.AddWithValue("@emp_code", Dawam_Grid.Items(Z).Cells("Emp_Code1").Value.ToString)
mycommand.Parameters.AddWithValue("@Name_col", Dawam_Grid.Items(Z).Cells("Emp_Name1").Value.ToString)
mycommand.Parameters.AddWithValue("@Date_Col", Dawam_Grid.Items(Z).Cells("Date_Col1").Value)
mycommand.Parameters.AddWithValue("@Shift_Col", Dawam_Grid.Items(Z).Cells("Shift_Col1").Value.ToString)
mycommand.Parameters.AddWithValue("@Mor_from", Dawam_Grid.Items(Z).Cells("Time_Mor_from1").Value)
mycommand.Parameters.AddWithValue("@Mor_to", Dawam_Grid.Items(Z).Cells("Time_Mor_to1").Value)
mycommand.Parameters.AddWithValue("@eve_from", Dawam_Grid.Items(Z).Cells("Time_eve_from1").Value)
mycommand.Parameters.AddWithValue("@eve_to", Dawam_Grid.Items(Z).Cells("Time_eve_to1").Value)
mycommand.Parameters.AddWithValue("@BasicSalary_col", Dawam_Grid.Items(Z).Cells("Salary_col1").Value)
mycommand.Parameters.AddWithValue("@Hour_Value_col2", Dawam_Grid.Items(Z).Cells("Hour_Value_col1").Value)
mycommand.Parameters.AddWithValue("@Over_Hours_col2", Dawam_Grid.Items(Z).Cells("Over_Hours_col1").Value)
mycommand.Parameters.AddWithValue("@Trans_Value_col2", Dawam_Grid.Items(Z).Cells("Trans_Value_col1").Value)
mycommand.Parameters.AddWithValue("@Total_OverTime_col2", Dawam_Grid.Items(Z).Cells("Total_OverTime_col1").Value)
mycommand.Parameters.AddWithValue("@Month_No", Dawam_Grid.Items(Z).Cells("Month_No1").Value)
mycommand.Parameters.AddWithValue("@Ikama_No", Dawam_Grid.Items(Z).Cells("Ikama_No1").Value)
mycommand.Parameters.AddWithValue("@create_user", Main_UserName)
mycommand.Parameters.AddWithValue("@create_date", DateTime.Today.Date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture))
mycommand.Parameters.AddWithValue("@create_time", DateTime.Now.ToString("HH:mm:ss"))
mycommand.Parameters.AddWithValue("@Official_Hours", Dawam_Grid.Items(Z).Cells("Official_Hours1").Value)
mycommand.Parameters.AddWithValue("@Day_Shift_Hours", Dawam_Grid.Items(Z).Cells("Day_Shift_Hours1").Value)
mycommand.Parameters.AddWithValue("@Day_Shift_Extra_Hours", Dawam_Grid.Items(Z).Cells("Day_Shift_Extra_Hours1").Value)
mycommand.Parameters.AddWithValue("@Month_Official_Hours", Dawam_Grid.Items(Z).Cells("Month_Official_Hours1").Value)
mycommand.Parameters.AddWithValue("@Month_Shift_Hours", Dawam_Grid.Items(Z).Cells("Month_Shift_Hours1").Value)
mycommand.Parameters.AddWithValue("@Month_Shift_Extra_Hours", Dawam_Grid.Items(Z).Cells("Month_Shift_Extra_Hours1").Value)
mycommand.Parameters.AddWithValue("@from_date", Dawam_Grid.Items(Z).Cells("from_date1").Value)
mycommand.Parameters.AddWithValue("@to_date", Dawam_Grid.Items(Z).Cells("to_date1").Value)
mycommand.Parameters.AddWithValue("@Year_No", Format(From_Date.EditValue, ("yyyy")))
mycommand.ExecuteNonQuery()

Next

错误信息：

System.MissingMemberException：在“Dawam_Grid_Items”类型中找不到“单元格”。

Dawam_Grid_Items是我创建的用于向数据网格添加行的公共类

Public Class Dawam_Grid_Items

    Public Property Emp_Code1 As String
    Public Property Emp_Name1 As String
    Public Property Salary_col1 As String
    Public Property Hour_Value_col1 As String
    Public Property Over_Hours_col1 As String
    Public Property Trans_Value_col1 As String
    Public Property Total_OverTime_col1 As String
    Public Property Ikama_No1 As String
    Public Property ShowRoom_col1 As String
    Public Property Time_Mor_from1 As String
    Public Property Time_Mor_to1 As String
    Public Property Time_eve_from1 As String
    Public Property Time_eve_to1 As String
    Public Property Date_Col1 As String
    Public Property Official_Hours1 As String
    Public Property Day_Shift_Hours1 As String
    Public Property Day_Shift_Extra_Hours1 As String
    Public Property Month_Official_Hours1 As String
    Public Property Month_Shift_Hours1 As String
    Public Property Month_Shift_Extra_Hours1 As String
    Public Property Shift_Col1 As String
    Public Property Month_No1 As String
    Public Property from_date1 As String
    Public Property to_date1 As String
    Public Property Job1 As String

End Class

标签： wpfvb.net

解决方案

  1. 删除Dim rd As SqlDataReader它从不使用。
  2. 将 conn 创建移到循环之外，并在构造函数中传递连接字符串。您不希望循环的每次迭代都有新的连接。
  3. 将 cmd 创建移到循环之外。与连接的原因相同。
  4. 删除If Not conn Is Nothing Then conn.Close()你刚刚创建它怎么可能是Nothing或Open？
  5. 将 `conn.Open() 移到循环外。您不想在每次迭代时打开另一个连接。将它直接放在循环之前，以便在最后一刻打开它。
  6. 删除Dim ds As DataSet = New DataSet并ds.Clear() '?从不使用它。顺便说一句，VB 中的注释字符是单引号而不是反引号。
  7. 为什么你有 2 个命令？cmd 和我的命令？我巩固他们。
  8. 将参数添加到循环外的命令参数集合中。参数不会改变，只有它们的值会改变。使用添加方法。检查您的数据库以获取正确的 SqlDbType。
  9. 在循环中引用参数集合的成员并设置它们的值。
  10. Format(From_Date.EditValue, ("yyyy"))看起来不对。
  11. Using...End Using 块确保即使出现错误，您的数据库对象也已关闭和处置。
Private Sub OPCode2()
        Using conn As New SqlConnection("User Id='" & sb2.UserID & "';Pwd='" & sb2.Password & "';DATA SOURCE='" & sb2.DataSource & "';Initial Catalog='" & sb2.InitialCatalog & "';MultipleActiveResultSets=true")
            Using mycommand As New SqlCommand("insert into Scedual (emp_code,Name_col,Date_Col,Shift_Col,Mor_from,Mor_to,eve_from, " _
                                                  & "eve_to,BasicSalary_col,Hour_Value_col2,Over_Hours_col2,Trans_Value_col2, " _
                                                  & "Total_OverTime_col2,Month_No,create_user,create_date,create_time,Ikama_No,Official_Hours,Day_Shift_Hours, " _
                                                  & "Day_Shift_Extra_Hours,Month_Shift_Hours,Month_Shift_Extra_Hours,Month_Official_Hours,from_date,to_date,ShowRoom_col,Job,Year_No) " _
                                                      & "values (@emp_code,@Name_col,@Date_Col,@Shift_Col,@Mor_from,@Mor_to,@eve_from, " _
                                                  & "@eve_to,@BasicSalary_col,@Hour_Value_col2,@Over_Hours_col2, " _
                                                  & "@Trans_Value_col2,@Total_OverTime_col2,@Month_No,@create_user,@create_date,@create_time,@Ikama_No,@Official_Hours,@Day_Shift_Hours, " _
                                                  & "@Day_Shift_Extra_Hours,@Month_Shift_Hours,@Month_Shift_Extra_Hours,@Month_Official_Hours,@from_date,@to_date,@ShowRoom_col,@Job,@Year_No)", conn)
                mycommand.Parameters.Add("@ShowRoom_col", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Job", SqlDbType.VarChar)
                mycommand.Parameters.Add("@emp_code", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Name_col", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Date_Col", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Shift_Col", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Mor_from", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Mor_to", SqlDbType.VarChar)
                mycommand.Parameters.Add("@eve_from", SqlDbType.VarChar)
                mycommand.Parameters.Add("@eve_to", SqlDbType.VarChar)
                mycommand.Parameters.Add("@BasicSalary_col", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Hour_Value_col2", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Over_Hours_col2", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Trans_Value_col2", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Total_OverTime_col2", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Month_No", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Ikama_No", SqlDbType.VarChar)
                mycommand.Parameters.Add("@create_user", SqlDbType.VarChar)
                mycommand.Parameters.Add("@create_date", SqlDbType.VarChar)
                mycommand.Parameters.Add("@create_time", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Official_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Day_Shift_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Day_Shift_Extra_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Month_Official_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Month_Shift_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Month_Shift_Extra_Hours", SqlDbType.VarChar)
                mycommand.Parameters.Add("@from_date", SqlDbType.VarChar)
                mycommand.Parameters.Add("@to_date", SqlDbType.VarChar)
                mycommand.Parameters.Add("@Year_No", SqlDbType.VarChar)

                conn.Open()
                For Z As Integer = 0 To Dawam_Grid.Items.Count - 1
                    mycommand.Parameters("@ShowRoom_col").Value = Dawam_Grid.Items(Z).Cells("ShowRoom_col1").Value.ToString
                    mycommand.Parameters("@Job").Value = Dawam_Grid.Items(Z).Cells("Job1").Value.ToString
                    mycommand.Parameters("@emp_code").Value = Dawam_Grid.Items(Z).Cells("Emp_Code1").Value.ToString
                    mycommand.Parameters("@Name_col").Value = Dawam_Grid.Items(Z).Cells("Emp_Name1").Value.ToString
                    mycommand.Parameters("@Date_Col").Value = Dawam_Grid.Items(Z).Cells("Date_Col1").Value
                    mycommand.Parameters("@Shift_Col").Value = Dawam_Grid.Items(Z).Cells("Shift_Col1").Value.ToString
                    mycommand.Parameters("@Mor_from").Value = Dawam_Grid.Items(Z).Cells("Time_Mor_from1").Value
                    mycommand.Parameters("@Mor_to").Value = Dawam_Grid.Items(Z).Cells("Time_Mor_to1").Value
                    mycommand.Parameters("@eve_from").Value = Dawam_Grid.Items(Z).Cells("Time_eve_from1").Value
                    mycommand.Parameters("@eve_to").Value = Dawam_Grid.Items(Z).Cells("Time_eve_to1").Value
                    mycommand.Parameters("@BasicSalary_col").Value = Dawam_Grid.Items(Z).Cells("Salary_col1").Value
                    mycommand.Parameters("@Hour_Value_col2").Value = Dawam_Grid.Items(Z).Cells("Hour_Value_col1").Value
                    mycommand.Parameters("@Over_Hours_col2").Value = Dawam_Grid.Items(Z).Cells("Over_Hours_col1").Value
                    mycommand.Parameters("@Trans_Value_col2").Value = Dawam_Grid.Items(Z).Cells("Trans_Value_col1").Value
                    mycommand.Parameters("@Total_OverTime_col2").Value = Dawam_Grid.Items(Z).Cells("Total_OverTime_col1").Value
                    mycommand.Parameters("@Month_No").Value = Dawam_Grid.Items(Z).Cells("Month_No1").Value
                    mycommand.Parameters("@Ikama_No").Value = Dawam_Grid.Items(Z).Cells("Ikama_No1").Value
                    mycommand.Parameters("@create_user").Value = Main_UserName)
                    mycommand.Parameters("@create_date").Value = Date.Today.Date.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
                    mycommand.Parameters("@create_time").Value = DateTime.Now.ToString("HH:mm:ss")
                    mycommand.Parameters("@Official_Hours").Value = Dawam_Grid.Items(Z).Cells("Official_Hours1").Value
                    mycommand.Parameters("@Day_Shift_Hours").Value = Dawam_Grid.Items(Z).Cells("Day_Shift_Hours1").Value
                    mycommand.Parameters("@Day_Shift_Extra_Hours").Value = Dawam_Grid.Items(Z).Cells("Day_Shift_Extra_Hours1").Value
                    mycommand.Parameters("@Month_Official_Hours").Value = Dawam_Grid.Items(Z).Cells("Month_Official_Hours1").Value
                    mycommand.Parameters("@Month_Shift_Hours").Value = Dawam_Grid.Items(Z).Cells("Month_Shift_Hours1").Value
                    mycommand.Parameters("@Month_Shift_Extra_Hours").Value = Dawam_Grid.Items(Z).Cells("Month_Shift_Extra_Hours1").Value
                    mycommand.Parameters("@from_date").Value = Dawam_Grid.Items(Z).Cells("from_date1").Value
                    mycommand.Parameters("@to_date").Value = Dawam_Grid.Items(Z).Cells("to_date1").Value
                    mycommand.Parameters("@Year_No").Value = Format(From_Date.EditValue, ("yyyy"))
                    mycommand.ExecuteNonQuery()
                Next
            End Using
        End Using
End Sub

推荐阅读