How i can add a group of rows from DGV to DB table

[HibaPro]

i have binding datagridview with 27 rows , i need to add these row to sqlserver table with other field how i can do that by insert statment???

[Pgmer]

U need to looptrhough each row of grid to get the values and prepare the insert statement and pass these values and execute the command.

[HibaPro]

can u provide me the syntax of the loop, i am trying but it still dosnt insert it just insert last row

[Pgmer]

please post ur code. so that we can see where the problem is

[HibaPro]

 Dim con As New SqlConnection
        con.ConnectionString = My.Settings.TenderingSysConnectionString
        con.Open()
        Dim nonqueryCommand As SqlCommand = con.CreateCommand()
        Try
            '  nonqueryCommand.CommandTimeout = ActivityCodeBindingSource.AddNew("the following table could be not be ")
            nonqueryCommand.CommandText = _
               "INSERT  INTO BOQSectionsAndParts (ProjectTenderingNO,BOQSection,Part) VALUES (@ProjectTenderingNO,@BOQSection,@Part)"
            ' Add Parameters to Command Parameters collection

            nonqueryCommand.Parameters.Add("@ProjectTenderingNO", SqlDbType.NVarChar, 50)
            nonqueryCommand.Parameters.Add("@BOQSection", SqlDbType.NVarChar, 50)
            nonqueryCommand.Parameters.Add("@Part", SqlDbType.NVarChar, 50)
            ' Prepare command for repeated execution

            nonqueryCommand.Prepare()
            ' Data to be inserted

            For Each row As DataGridViewRow In BOQSectionPartsDataGrid1.Rows
                If Not row.IsNewRow Then
                    nonqueryCommand.Parameters("@ProjectTenderingNO").Value = txtPTNO.Text
                    nonqueryCommand.Parameters("@BOQSection").Value = row.Cells(0).Value.ToString
                    nonqueryCommand.Parameters("@Part").Value = row.Cells(1).Value.ToString
                    'MsgBox("record save")

                End If
            Next
            nonqueryCommand.ExecuteNonQuery()
            MessageBox.Show("BOQ Section And Parts Added Sucessfuly")

        Catch ex As SqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            con.Close()
            'Console.WriteLine("Connection Closed")
        End Try

[Pgmer]

Just try something like below.

 Private Sub test()
            Dim cntest As SqlConnection
            Dim strCnn As String = "ur Connection string"
            cntest = New SqlConnection(strCnn)
            cntest.Open()
            Try
                Dim cmdInsert As New SqlCommand
                cmdInsert.Connection = cntest
                Dim strCommandText As String = String.Empty
                Dim values As String = String.Empty
                strCommandText = "INSERT INTO TableName(COL1,COL2,COL3)VALUES("
                For Each row As DataGridViewRow In BOQSectionPartsDataGrid1.Rows

                    values = strCommandText & txtPTNO.Text & "," & row.Cells(0).Value.ToString & "," & row.Cells(1).Value.ToString & "'" & ")"
                    cmdInsert.CommandText = values
                    cmdInsert.ExecuteNonQuery()
                    values = ""
                Next
            Catch ex As Exception

            End Try
        End Sub

[HibaPro]

there is no error in this code but nothing happen. no row inserted

[Pgmer]

Can you post ur updated code? Have u tried to take the insert statement from values variable and running it in SQL server?

[HibaPro]

        Dim cntest As SqlConnection
        Dim strCnn As String = "Data Source=HABOUSH-PC\SQLEXPRESS;Initial Catalog=TenderingSystem;Integrated Security=True"
        cntest = New SqlConnection(strCnn)
        cntest.Open()
        Try
            Dim cmdInsert As New SqlCommand
            cmdInsert.Connection = cntest
            Dim strCommandText As String = String.Empty
            Dim values As String = String.Empty
            strCommandText = "INSERT INTO BOQSectionsAndParts(ProjectTenderingNO,BOQSection,Part)VALUES("
            For Each row As DataGridViewRow In BOQSectionPartsDataGrid1.Rows
                values = strCommandText & txtPTNO.Text & "," & row.Cells(0).Value.ToString & "," & row.Cells(1).Value.ToString & "'" & ")"
                cmdInsert.CommandText = values
                cmdInsert.ExecuteNonQuery()
                values = ""
            Next
        Catch ex As Exception
        End Try

[HibaPro]

    i found the solution on other thread of this site :


      Try
                Dim I As Integer
                For I = 0 To BOQSectionPartsDataGrid1.Rows.Count - 1
                    Dim PTNO As String = txtPTNO.Text
                    Dim BOQSE As String = BOQSectionPartsDataGrid1.Rows(I).Cells(0).Value
                    Dim Part As String = BOQSectionPartsDataGrid1.Rows(I).Cells(1).Value

                    Dim conn As New SqlConnection(My.Settings.TenderingSysConnectionString)
                    conn.Open()
                    Dim query As String = "Insert Into BOQSectionsAndParts(ProjectTenderingNO, BOQSection, Part) values (@ProjectTenderingNO, @BOQSection, @Part)"
                    Dim cmd As New SqlCommand(query, conn)
                    cmd.Parameters.AddWithValue("@ProjectTenderingNO", PTNO)
                    cmd.Parameters.AddWithValue("@BOQSection", BOQSE)
                    cmd.Parameters.AddWithValue("@Part", Part)

                    cmd.ExecuteScalar()
                    cmd.Dispose()
                Next
            Catch ex As Exception
                MessageBox.Show("BOQ Section And Parts Successfully Added")

            End Try


'         '   and this is work correctly , i appreciate your work and i ask from you to 'tell me where i miss add something to previous code.