Hi
can you pls help me for code to check the duplicate data before instert or update records....
i work with Ms Access database 2003 with disconnection mode (dataset) into vb.net 2008

I can't give you a specific query without knowing the structure of your table but in general if you do something like

SELECT COUNT(*)
  FROM mytable
 WHERE pkfieldname = value

where pkfieldname is the name of your primary key field and value is the value of that field then the result is 1 if the record exists and 0 if it does not.

my table named SpecialColor included 5 col " Order No , Print on ,Special color , color,reference "
how i can use your code in vb.net
and pls noted i will not used the primary key in my table ....

i was use your advise in code below but it's not stope the updated can you pls look and help me

Dim sure As String = " select * from SpecialColor Where  Ref = '" & RefTxt.Text & "' And OrderNo = '" & OrderNoTxt.Text & "' And PrintOn = '" & PrintOntxt.Text & "' "
        Dim Dasure As New OleDbDataAdapter(sure, Con)
        Dim dssure As New DataSet
        Dasure.Fill(dssure, "SpecialColor")
        MsgBox(dssure.Tables("SpecialColor").AsEnumerable.Count())

        If dssure.Tables("SpecialColor").AsEnumerable.Count() > 0 Then
            MsgBox("This Item was add beforr ")
            Return
        End If

Edited 3 Years Ago by أبو_1

It looks like you are adding the record before you check the count. Move the update into an Else clause of your If...Then statement and it will only occur if the record does not exist.
I think it should look like:

Dim sure As String = " select * from SpecialColor Where  Ref = '" & RefTxt.Text & "' And OrderNo = '" & OrderNoTxt.Text & "' And PrintOn = '" & PrintOntxt.Text & "' "
        Dim Dasure As New OleDbDataAdapter(sure, Con)
        Dim dssure As New DataSet
         If dssure.Tables("SpecialColor").AsEnumerable.Count() > 0 Then
            MsgBox("This Item was add beforr ")
‘Add an Else statement so that the update only occurs if the record was not already added.
        Else
            Dasure.Fill(dssure, "SpecialColor")
            MsgBox(dssure.Tables("SpecialColor").AsEnumerable.Count())
            Return
        End If
This article has been dead for over six months. Start a new discussion instead.