[raelkun00]

My problem is a Button that will loop through a Datagridview and check if row already exist in the Database it will just Update, If not it will insert another that row. Help me please,Thanks.

  Dim dr As New System.Windows.Forms.DataGridViewRow
        For Each dr In Me.dgvOrders.Rows
            Dim pno As String = dr.Cells(0).Value
            Dim price As String = dr.Cells(5).Value
            Dim qty As String = dr.Cells(6).Value

            Dim objCon As New dbConnect
            objCon.con.Open()
            Dim cmd As New OleDbCommand("Select * from SalesDetail", objCon.con)
            Dim da As New OleDbDataAdapter(cmd)
            Dim dreader As OleDbDataReader = cmd.ExecuteReader
            While dreader.Read

                If txtSalesID.Text = dreader(0).ToString And pno = dreader(1).ToString Then
                    Dim cmd1 As New OleDbCommand("UPDATE SalesDetail set price=@price,qty=@qty where sales_id=@salesid and p_no=@pno", objCon.con)
                    cmd1.Parameters.AddWithValue("@price", price)
                    cmd1.Parameters.AddWithValue("@qty", qty)
                    cmd1.Parameters.AddWithValue("@salesid", txtSalesID.Text)
                    cmd1.Parameters.AddWithValue("@pno", pno)
                    cmd1.ExecuteNonQuery()
                Else

                    Dim cmd2 As New OleDbCommand("INSERT INTO SalesDetail values(@salesid,@pno,@price,@qty)", objCon.con)
                    cmd2.Parameters.AddWithValue("@salesid", txtSalesID.Text)
                    cmd2.Parameters.AddWithValue("@pno", pno)
                    cmd2.Parameters.AddWithValue("@price", price)
                    cmd2.Parameters.AddWithValue("@qty", qty)
                    cmd2.ExecuteNonQuery()
                End If

            End While

            dreader.Close()
            objCon.con.Close()
        Next

[andrewsw]

Does your code work? If not, what does it do instead? What errors do you receive and what line do the errors refer to (in your posted code)?

[andrewsw]

However, your code is very labour intensive, and you would need to loop through all the table-rows each time before you could determine that you need to insert a record. (Once you've updated a record you should also break out of the inner loop.)

Study the SQL WHERE clause so that you could just check whether a record exists before deciding to UPDATE or INSERT.

This post has been edited by andrewsw: 25 August 2013 - 07:19 AM

[raelkun00]

I solved my problem. I used dataadapter and datatable instead of datareader. Thanks BTW.
This is my code.

 Dim dr As New System.Windows.Forms.DataGridViewRow
        For Each dr In Me.dgvOrders.Rows
            Dim pno As String = dr.Cells(0).Value
            Dim price As String = dr.Cells(5).Value
            Dim qty As String = dr.Cells(6).Value

            Dim objCon As New dbConnect
            objCon.con.Open()
            Dim cmd As New OleDbCommand("Select * from SalesDetail where sales_id=@salesid and p_no=@pno", objCon.con)
            cmd.Parameters.AddWithValue("@salesid", txtSalesID.Text)
            cmd.Parameters.AddWithValue("@pno", pno)
            Dim da As New OleDbDataAdapter(cmd)
            Dim dt As New DataTable
            da.Fill(dt)
            If dt.Rows.Count = 0 Then
                Dim cmd2 As New OleDbCommand("INSERT INTO SalesDetail values(@salesid,@pno,@price,@qty)", objCon.con)
                cmd2.Parameters.AddWithValue("@salesid", txtSalesID.Text)
                cmd2.Parameters.AddWithValue("@pno", pno)
                cmd2.Parameters.AddWithValue("@price", price)
                cmd2.Parameters.AddWithValue("@qty", qty)
                cmd2.ExecuteNonQuery()
            Else
                Dim cmd1 As New OleDbCommand("UPDATE SalesDetail set price=@price,qty=@qty where sales_id=@salesid and p_no=@pno", objCon.con)
                cmd1.Parameters.AddWithValue("@price", price)
                cmd1.Parameters.AddWithValue("@qty", qty)
                cmd1.Parameters.AddWithValue("@salesid", txtSalesID.Text)
                cmd1.Parameters.AddWithValue("@pno", pno)
                cmd1.ExecuteNonQuery()

            End If

            objCon.con.Close()
        Next

This post has been edited by andrewsw: 25 August 2013 - 07:44 AM
Reason for edit:: Removed duplication

[andrewsw]

Erm, you solved it by using the WHERE clause, but glad you sorted