Duplicate value check upon Datagrid updatecommand 2

[clanm]

I'm trying to check upon the update command for my datagrid that the user can't insert duplicate values of an Activity ID per Work Id. So, I want to block the user if they try to update an Activity Id to one that already exists in that same Work ID. So, it's a Work ID can have one to many Activity Ids, but we don't want duplicate Activity IDs per Work Id.

I can't necessarily put a constraint on the Activity ID via the DBMS, because another Work ID can have the same Activity Id(s) seen in other Work IDs.

I'm trying the following code, which works when I try to insert records in the datagrid, but for some reason this UpdateCommand let's it go through:

'start check for duplicate P3ActivityID
Dim cmdchkdup As New SqlCommand("usp_SelectCountActIDUpdateDGAct", sCon1)
cmdchkdup.CommandType = CommandType.StoredProcedure
cmdchkdup.Parameters.Add(New System.Data.SqlClient.SqlParameter("@WPID", System.Data.SqlDbType.NVarChar, 50))
cmdchkdup.Parameters.Add(New System.Data.SqlClient.SqlParameter("@P3ActID", System.Data.SqlDbType.NVarChar, 4))
cmdchkdup.Parameters.Add(New System.Data.SqlClient.SqlParameter("@intPK", System.Data.SqlDbType.Int, 4))

cmdchkdup.Parameters(0).Value = Server.HtmlEncode(Request.QueryString("WPID"))
cmdchkdup.Parameters(1).Value = Trim(txtNewActP3ActID.Text)
cmdchkdup.Parameters(2).Value = e.Item.Cells(7).Text
sCon1.Open()
Dim intCheckDupActID As Integer = cmdchkdup.ExecuteScalar
sCon1.Close()
If intCheckDupActID >= 1 Then 'This ActivityID already exists for this work package...show alert
Exit Sub
End If
'end check for duplicate P3ActivityID

The SPROC is:
CREATE PROCEDURE [dbo].[usp_SelectCountActIDUpdateDGAct]
@WPID nvarchar(100), @P3ActID nvarchar (100), @intPK int
AS
Select count(*) from Activities where WPID = @WPID and ActivityId = @P3ActID and [PKActivityNum] <> @intPK
GO

--PKActivityNum is the primary key for the Activity Table

 

[jbenson001]

As I said in the response to your last post, you should let the DB do this. You should not let the user create or modify and IDs. Create and insert trigger that gets the next (max(activityID) + 1) ActivityID for a particular WorkID. This will create the ID for you. Then when you use the grid for updaing, you don't allow the user to modify the ID, or better yet, don't show it at all.

Jim

 

[ca8msm]

If you go with the database approach, you are looking for what is known as a "composite key" (i.e. a key that is made up of more than one attribute) and most databases will be capable of providing this constraint.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[clanm]

jbenson001,

The users have to have the ability to create their own. We can't limit them to autonumbering since they user personalized schemas to create their numbers. I would be nice though if this weren't the case.

ca8msm, I'll google that suggestion for the composite key.

Thanks for the help!

 

[clanm]

ca8msm,

I want to add a composite Unique Constraint Index, but the constraint fails because the previous business rule allowed the duplication of ActIDs per same WorkID.

I don't see anything via the Properties table where I can ignore existing data.....like the Constraints tab does.

CONSTRAINT [IX_WP_Activities_1] UNIQUE NONCLUSTERED
(
[WorkID],
[ActID]
)
Any ideas are welcome!

Thanks!

 

[jbenson001]

You cannot ignore the data. If you want them to be unique, you will have to go and fix the data manually.

 

[clanm]

too bad they don't work like "Constraints"

Thanks!

 

[jbenson001]

Check constraints" are different than unique constraints. The terms are too close in naming and cause confusion.