WPF insert data from datagrid to sql database using for loop

Question

I am trying to insert rows data from a WPF Datagrid to a SQL database using a for loop. Unfortunately I receive an error and I don't know what is wrong with my code. Please can you check my code?

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

the error message :

System.MissingMemberException: cannot find 'Cells'in type 'Dawam_Grid_Items'.'

Dawam_Grid_Items is a public class I created to add rows to the datagrid

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

Have you check what types you are passing into your method? put a breakpoint and confirm if you have the correct type — mahlatse, Dec 06 '18 at 15:12
If your types are of type ShowGridItems, the the line `Dawam_Grid.Items(Z).Cells("ShowRoom_col1").Value.ToString)` would simple be `Dawam_Grid.Items(Z).ShowRoom_col1)`, im geusing you might need to type cast the values first — mahlatse, Dec 06 '18 at 15:22
Here is how to iterate over the rows of the datagrid. Only downside is that the code is in C# but you can probably see what it would be in VB https://stackoverflow.com/a/15686971/169714 — JP Hellemons, Dec 06 '18 at 15:30
i have done it and got that error System.Data.SqlClient.SqlException: 'The parameterized query '(@emp_code nvarchar(30),@Name_col nvarchar(14),@Shift_Col nvarchar(11)' expects the parameter '@create_user', which was not supplied.' — Mohamad Salama, Dec 06 '18 at 15:31
@JP Hellemons exactly that was the problem and now it works perfectly thank you so much — Mohamad Salama, Dec 06 '18 at 16:08

score 1 · Answer 1 · answered Dec 07 '18 at 03:07

Delete Dim rd As SqlDataReader It is never used.
Move conn creation outside the loop and pass the connection string in the constructor. You do NOT want a new connection on each iteration of the loop.
Move cmd creation outside the loop. Same reason as for connection.
Delete If Not conn Is Nothing Then conn.Close() You just created it how can it be Nothing or Open?
Move `conn.Open() to outside the loop. You don't want to open another connection on each iteration. Place this directly before the loop so it is opened at the last possible moment.
Delete Dim ds As DataSet = New DataSet and ds.Clear() '? It is never used. BTW the comment character in VB is the single quote not a back tick.
Why do you have 2 commands? cmd and mycommand? I consolidate them.
Add the parameters to the commands parameter collection outside the loop. The parameters don't change, only their values change. Use the Add method. Check your database for the proper SqlDbType.
Reference the member of the parameters collection in the loop and set their values.
Format(From_Date.EditValue, ("yyyy")) doesn't look right.
The Using...End Using blocks ensure that your database objects are closed and disposed even if there is an error.

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

WPF insert data from datagrid to sql database using for loop

1 Answers1