4 Replies - 3043 Views - Last Post: 25 August 2013 - 07:44 AM Rate Topic: -----

#1 raelkun00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 61
  • Joined: 01-October 12

Check if record already exist, if not Insert new record.

Posted 25 August 2013 - 05:40 AM

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



Is This A Good Question/Topic? 0
  • +

Replies To: Check if record already exist, if not Insert new record.

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Check if record already exist, if not Insert new record.

Posted 25 August 2013 - 07:07 AM

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)?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Check if record already exist, if not Insert new record.

Posted 25 August 2013 - 07:16 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 raelkun00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 61
  • Joined: 01-October 12

Re: Check if record already exist, if not Insert new record.

Posted 25 August 2013 - 07:34 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Check if record already exist, if not Insert new record.

Posted 25 August 2013 - 07:44 AM

Erm, you solved it by using the WHERE clause, but glad you sorted :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1