How To Save Data From DatagridView To Sql Server 2008 In Vb.NEt

[rd009]

I have tried using the following it saves the data but the data is not visible in database i have attached the Screen SHot of the database below. i am pasting my code below. Kindly help me out as m not getting what to do further.

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim comd As SqlCommand = con.CreateCommand()
        If txtPONumber.Text <> " " Then
            Try
                con.ConnectionString = obj.getconnectionstring
                con.Open()
                comd.Connection = con
                comd.CommandText = "Insert into PurchaseOrder(PoNumber,PoDate,PoCompanyName,PoSiteName,PoContactNumber,ItemNumber,itemName,Quantity,Rate,TotalAmount,GrossAmount,Discount,tax,FinalAmount) values('" & txtPONumber.Text & "','" & lblDatetime.Text & "','" & cbxCompanyName.Text & "','" & txtSiteName.Text & "','" & txtContact.Text & "',@ItemNum,@ItemNam,@Qty,@Rte,@TotalAmt,'" & txtGrossAmount.Text & "','" & txtDiscount.Text & "','" & txtTax.Text & "','" & txtFinalAmount.Text & "')"

                'Adding Parameters
                comd.Parameters.Add("@ItemNum", SqlDbType.VarChar, 50)
                comd.Parameters.Add("@ItemNam", SqlDbType.VarChar, 50)
                comd.Parameters.Add("@Qty", SqlDbType.VarChar, 50)
                comd.Parameters.Add("@Rte", SqlDbType.VarChar, 50)
                comd.Parameters.Add("@TotalAmt", SqlDbType.VarChar, 50)

                comd.Prepare()
                'Data Inserted

                For Each row As DataGridViewRow In DGVPO.Rows
                    If Not row.IsNewRow Then
                        comd.Parameters("@ItemNum").Value = row.Cells(0).Value.ToString
                        comd.Parameters("@ItemNam").Value = row.Cells(1).ToString
                        comd.Parameters("@Qty").Value = row.Cells(2).ToString
                        comd.Parameters("@Rte").Value = row.Cells(3).ToString
                        comd.Parameters("@TotalAmt").Value = row.Cells(4).ToString
                    End If
                Next
                comd.ExecuteNonQuery()
                MessageBox.Show("Data Inserted")
            Catch ex As Exception
                MessageBox.Show("Error :" & ex.ToString())
            Finally
                con.Close()
            End Try
        Else
            MessageBox.Show("Enter PoNumber")
        End If

    End Sub

[hericles]

How can it save the data but the data not be visible in the database? Is it just saving the last row of data from the gridview?It looks like you are running through all of the rows of the datagrid, setting the parameters to the values from each row (overwriting the values from the previous row) and calling executeNonQuery once at the end. This will cause the insertion to just record the lastrow only. Is that what you are seeing?

[jezguitarist30]

I didn't do much here but could you please try to insert your

comd.ExecuteNonQuery() in your for Loop.

From this:

    For Each row As DataGridViewRow In DGVPO.Rows
                    If Not row.IsNewRow Then
                        comd.Parameters("@ItemNum").Value = row.Cells(0).Value.ToString
                        comd.Parameters("@ItemNam").Value = row.Cells(1).ToString
                        comd.Parameters("@Qty").Value = row.Cells(2).ToString
                        comd.Parameters("@Rte").Value = row.Cells(3).ToString
                        comd.Parameters("@TotalAmt").Value = row.Cells(4).ToString
                    End If
                Next
                comd.ExecuteNonQuery()

To this:

         For Each row As DataGridViewRow In DGVPO.Rows

                    If Not row.IsNewRow Then
                        comd.Parameters("@ItemNum").Value = row.Cells(0).Value.ToString
                        comd.Parameters("@ItemNam").Value = row.Cells(1).ToString
                        comd.Parameters("@Qty").Value = row.Cells(2).ToString
                        comd.Parameters("@Rte").Value = row.Cells(3).ToString
                        comd.Parameters("@TotalAmt").Value = row.Cells(4).ToString
                    End If

                comd.ExecuteNonQuery()


        Next

Hope it helps..

[rd009]

@jezguitarist30 : I tried this out but its nt working.

[jezguitarist30]

If it's okay I want you to try two things.

1.) From this

       For Each row As DataGridViewRow In DGVPO.Rows
            If Not row.IsNewRow Then
                   comd.Parameters("@ItemNum").Value = row.Cells(0).Value.ToString
                            comd.Parameters("@ItemNam").Value = row.Cells(1).ToString
                            comd.Parameters("@Qty").Value = row.Cells(2).ToString
                            comd.Parameters("@Rte").Value = row.Cells(3).ToString
                            comd.Parameters("@TotalAmt").Value = row.Cells(4).ToString
                        End If
                    comd.ExecuteNonQuery()
            Next

To This: I just removed your If statement to make sure that It really insert data or If your If statement is really neccesary then don't removed it and just add a messagebox inside your if statement to make sure that your code passes through the message box.

           For Each row As DataGridViewRow In DGVPO.Rows


                    comd.Parameters("@ItemNum").Value = row.Cells(0).Value.ToString
                    comd.Parameters("@ItemNam").Value = row.Cells(1).ToString
                    comd.Parameters("@Qty").Value = row.Cells(2).ToString
                    comd.Parameters("@Rte").Value = row.Cells(3).ToString
                    comd.Parameters("@TotalAmt").Value = row.Cells(4).ToString

                    comd.ExecuteNonQuery()

                msgbox("Checked")

            Next