Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bubberz
Constraint Violating Yak Guru
289 Posts |
 Posted - 2006-11-15 : 12:31:12
|
| 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 P3ActivityIDThe SPROC is:CREATE PROCEDURE [dbo].[usp_SelectCountActIDUpdateDGAct] @WPID nvarchar(100), @P3ActID nvarchar (100), @intPK intASSelect count(*) from Activities where WPID = @WPID and ActivityId = @P3ActID and [PKActivityNum] <> @intPKGO--PKActivityNum is the primary key for the Activity Table |
|
|
|
|
|
|
|
Block duplicate values on Datagrid UpdateCommand