StrataFrame Forum

Check record uniqueness (multi record uodate)

http://forum.strataframe.net/Topic6633.aspx

By Chan - 2/7/2007

Hi,

I allow user to add more than one record (let say order lineitems), then only press save button to in one batch.

I need to check for record uniqueness after new record added.

1. How to check uniquness before actually save to db?

2. If I loop thru the records to check for uniqueness, any impact if my "record pointer" moved?

Thank you

By Trent L. Taylor - 2/8/2007

You need to just create a scalar method that checks to see if there is another record with this uniquness.  It would look something like this:

Public Function AreThereDuplicates(Byval MyTestValue As String) As Boolean
        '-- Establish Locals
        Dim loCommand As New SqlClient.SqlCommand
        Dim lnPK As Integer
        '-- Get the primary key
        If Me.Count = 0 Then
            lnPK = -1
        Else
            lnPK = Me.MyPrimaryKey
        End If
        '-- Build the SELECT string
        loCommand.CommandText = "SELECT Count(*) FROM MyTable WHERE MyPrimaryKey != @MyPrimaryKey"
        loCommand.CommandText &= " AND MyTestField = @MyTestField"
        '-- Create the parameters
        loCommand.Parameters.Add(New SqlClient.SqlParameter("@MyPrimaryKey", SqlDbType.Int))
        loCommand.Parameters.Add(New SqlClient.SqlParameter("@MyTestField", SqlDbType.VarChar))

        '-- Set the values
        loCommand.Parameters.Item("@MyPrimaryKey").Value = lnPK
        loCommand.Parameters.Item("@MyTestField").Value = MyTestValue
        '-- Return Results
        Return Me.ExecuteScalar(loCommand) > 0
End Function

Then just call this in your business rules when checking (or whenever you need to):

If AreThereDuplicates("TestValue") Then
    Me.AddBrokenRule(...)
End If
By Chan - 2/8/2007

Hi,

Thank you for reply.

Your sample code is to check if any duplicate record already stored in DB.

How about still in memory? My form allow user to add multiple records then only save.

BTW, anything need to set for multi records update? I remember that is a property to set for multiple record update but I cant recall where exactly. I failed to get it back from doc.

Thank you

By Trent L. Taylor - 2/8/2007

You can modify the source code to look through the BO as well.  You can do this a lot of different ways.  Save off the record pointer and scan through the BO.  Or reference the CurrentDataTable and do a select.

MyBO.CurrentDataTable.Select("MyTestField='TestVa'ue'").Length > 0

Combine the local and the server values to get your final result.  Be sure to always exclude the current record otherwise this will return an incorrect value.