none
CHECK duplicate VALUE TO DATATABLE RRS feed

  • Question

  • HI

    I have a column of customer names with the Access database
    Added data TO COLUMN  from TextBox
    I want to check the name of the new client when he writes on the teXtbox if he DUPLICATES
    IN database and a message appears .

    Wednesday, December 12, 2018 12:28 PM

Answers

  • The best way is to Change the index property of the field to not allow duplicates then in your code use a Transaction which will thrown an exception.

    Example code. IIn the Catch sqlex, inspect the error message and if it has reference to a constraint violation you know it would had been a duplicate record.

    Public Function AddNewRow(
        ByVal pName As String,
        ByVal pContact As String,
        ByVal pContactTitle As String,
        ByVal pJoinDate As Date,
        ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
    
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Dim transaction As OleDbTransaction = Nothing
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) 
                        Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)
                    </SQL>.Value
    
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContact)
                cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                Try
                    cn.Open()
                    transaction = cn.BeginTransaction()
                    cmd.Transaction = transaction
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
                Catch sqlex As OleDbException
                    transaction.Rollback()
                Catch ex As Exception
                    Success = False
                End Try
            End Using
        End Using
    
        Return Success
    
    End Function
    Otherwise write a SELECT WHERE query, if one record is returned then you have a duplicate.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Wednesday, December 12, 2018 2:16 PM
    Wednesday, December 12, 2018 1:20 PM
    Moderator

All replies

  • The best way is to Change the index property of the field to not allow duplicates then in your code use a Transaction which will thrown an exception.

    Example code. IIn the Catch sqlex, inspect the error message and if it has reference to a constraint violation you know it would had been a duplicate record.

    Public Function AddNewRow(
        ByVal pName As String,
        ByVal pContact As String,
        ByVal pContactTitle As String,
        ByVal pJoinDate As Date,
        ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
    
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Dim transaction As OleDbTransaction = Nothing
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) 
                        Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)
                    </SQL>.Value
    
                cmd.Parameters.AddWithValue("@CompanyName", pName)
                cmd.Parameters.AddWithValue("@ContactName", pContact)
                cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                Try
                    cn.Open()
                    transaction = cn.BeginTransaction()
                    cmd.Transaction = transaction
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
                Catch sqlex As OleDbException
                    transaction.Rollback()
                Catch ex As Exception
                    Success = False
                End Try
            End Using
        End Using
    
        Return Success
    
    End Function
    Otherwise write a SELECT WHERE query, if one record is returned then you have a duplicate.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by ahmeddc Wednesday, December 12, 2018 2:16 PM
    Wednesday, December 12, 2018 1:20 PM
    Moderator
  • THANKS 

    Wednesday, December 12, 2018 2:16 PM