J
Jeremy Ames
I have a datagrid that updates the table using a stored procedure. The stored procedure is confirmed to complete correctly, yet the sql data adapter is returning an error that my application is catching.
Everything within my stored procedure is contained within a transaction. When utilizing the sql profiler, we can see that the transaction successfully begins and commits. The data in the sql table is updated at this point.
My data adapter, daOnCall, returns an error on update. The error has to do with a primary key violation that does not exist. It is pointing to one of the records that is being updated within the stored proc, but there is no reason for that to be returned, granted that particular update statement can alter part of the primary key, being a date field. However, the data being passed to it is not being updated because it does not meet the where clause.
Here is the stored procedure:
<code>
CREATE Procedure procUpdateOnCallSchedule
@GroupId VARCHAR(25),
@StartDate DATETIME,
@EndDate DATETIME = NULL,
@BusinessHoursUser VARCHAR(30),
@AfterHoursUser VARCHAR(30) = NULL,
@Notes VARCHAR(1000) = NULL,
@OldGroupId VARCHAR(25),
@OldStartDate DATETIME,
@OldEndDate DATETIME = NULL,
@OldBusinessHoursUser VARCHAR(30),
@OldAfterHoursUser VARCHAR(30) = NULL,
@OldNotes VARCHAR(1000) = NULL
AS
BEGIN
BEGIN TRAN mainTran
-- IF THE EndDate is null, then make the end date the end of the day(11:59:59 PM)
-- one year out from the start date
IF @EndDate IS NULL
SET @EndDate = DATEADD(s, 86399, DATEADD(yy, 1, @StartDate))
-- update the record first and then make all of the necessary changes
-- this will keep from changing our record before we can update it
-- and possibly loosing the record all together.
UPDATE irOnCall
SET GroupId = @GroupId,
StartDate = @StartDate,
EndDate = @EndDate,
BusinessHoursUser = @BusinessHoursUser,
AfterHoursUser = @AfterHoursUser,
Notes = @Notes
WHERE GroupId = @OldGroupId
AND StartDate = @OldStartDate
AND (EndDate = @OldEndDate OR (@OldEndDate IS NULL AND EndDate IS NULL))
AND BusinessHoursUser = @OldBusinessHoursUser
AND (AfterHoursUser = @OldAfterHoursUser OR (@OldAfterHoursUser IS NULL AND AfterHoursUser IS NULL))
AND (Notes = @OldNotes OR (@OldNotes IS NULL AND Notes IS NULL))
-- check for oncall schedules that end after this schedule begins
PRINT 'OLD ENDS AFTER NEW STARTS'
UPDATE irOncall
SET EndDate = DATEADD(s, -1, @StartDate)
WHERE @StartDate BETWEEN StartDate AND EndDate
AND StartDate <> CONVERT(DATETIME, @StartDate)
AND GroupId = @GroupId
-- check for oncall schedules that start before the new schedule ends
PRINT 'NEW STARTS BEFORE OLD ENDS'
UPDATE irOnCall
SET StartDate = DATEADD(s, 1, @EndDate)
WHERE @EndDate BETWEEN StartDate AND EndDate
AND StartDate <> CONVERT(DATETIME, @StartDate)
AND StartDate <> CONVERT(DATETIME, @OldStartDate)
AND GroupId = @GroupId
IF @@ERROR = 0
COMMIT TRAN mainTran
ELSE
ROLLBACK TRAN mainTran
END
</code>
Here is the code behind that is generating the errors:
<code>
Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
Select Case e.CommandName
Case "Edit"
DataGrid1.EditItemIndex = e.Item.ItemIndex
DataGrid1.ShowFooter = False
Case "Cancel"
DataGrid1.EditItemIndex = -1
DataGrid1.ShowFooter = True
Case "Insert"
' insert command logic
Case "Update"
Try
LoadOnCallDataAdapter()
Dim dr As dsOnCall.irOnCallRow = DsOnCall1.irOnCall.Item(e.Item.DataSetIndex)
SqlUpdateCommand1.Parameters("@OldGroupId").Value = ddlGroup.SelectedValue
SqlUpdateCommand1.Parameters("@GroupId").Value = ddlGroup.SelectedValue
SqlUpdateCommand1.Parameters("@OldStartDate").Value = dr("StartDate")
SqlUpdateCommand1.Parameters("@OldEndDate").Value = dr("EndDate")
SqlUpdateCommand1.Parameters("@OldBusinessHoursUser").Value = dr("BusinessHoursUser")
SqlUpdateCommand1.Parameters("@OldAfterHoursUser").Value = dr("AfterHoursUser")
SqlUpdateCommand1.Parameters("@OldNotes").Value = dr("Notes")
Dim myText As TextBox
myText = e.Item.FindControl("txtStartDateEdit")
dr.StartDate = myText.Text + " 12:00:00 AM"
SqlUpdateCommand1.Parameters("@StartDate").Value = myText.Text + " 12:00:00 AM"
myText = e.Item.FindControl("txtEndDateEdit")
If myText.Text <> "" Then
dr.EndDate = myText.Text + " 11:59:59 PM"
SqlUpdateCommand1.Parameters("@EndDate").Value = myText.Text + " 11:59:59 PM"
Else
dr.EndDate = Nothing
SqlUpdateCommand1.Parameters("@EndDate").Value = Nothing
End If
Dim myList As DropDownList
myList = e.Item.FindControl("ddlOfficeHoursEdit")
dr.BusinessHoursUser = myList.SelectedValue
SqlUpdateCommand1.Parameters("@BusinessHoursUser").Value = myList.SelectedValue
myList = e.Item.FindControl("ddlAfterHoursEdit")
If myList.SelectedValue <> "" Then
dr.AfterHoursUser = myList.SelectedValue
SqlUpdateCommand1.Parameters("@AfterHoursUser").Value = myList.SelectedValue
Else
dr.AfterHoursUser = Nothing
SqlUpdateCommand1.Parameters("@AfterHoursUser").Value = Nothing
End If
myText = e.Item.FindControl("txtNotesEdit")
If myText.Text <> "" Then
dr.Notes = myText.Text
SqlUpdateCommand1.Parameters("@Notes").Value = myText.Text
Else
dr.Notes = Nothing
SqlUpdateCommand1.Parameters("@Notes").Value = Nothing
End If
daOnCall.Update(DsOnCall1, "irOnCall")
DsOnCall1.Clear()
DataGrid1.EditItemIndex = -1
DataGrid1.ShowFooter = True
lblMessage.Text = "Record Updated"
Catch ex As Exception
lblMessage.Text = "Error Updating: " & ex.Message
End Try
End Select
BindDetailData()
End Sub
</code>
Here is the error message being caught in the code-behind page:
Column 'GroupId, StartDate' is constrained to be unique. Value 'EBIZ, 9/12/2005 12:00:00 AM' is already present.
When the page loads after the exception handling, this is the error that is returned:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Let me know if you want some test data to work with as well. This is really killing me. I have been working hand-in-hand with my dba to get this going.
Everything within my stored procedure is contained within a transaction. When utilizing the sql profiler, we can see that the transaction successfully begins and commits. The data in the sql table is updated at this point.
My data adapter, daOnCall, returns an error on update. The error has to do with a primary key violation that does not exist. It is pointing to one of the records that is being updated within the stored proc, but there is no reason for that to be returned, granted that particular update statement can alter part of the primary key, being a date field. However, the data being passed to it is not being updated because it does not meet the where clause.
Here is the stored procedure:
<code>
CREATE Procedure procUpdateOnCallSchedule
@GroupId VARCHAR(25),
@StartDate DATETIME,
@EndDate DATETIME = NULL,
@BusinessHoursUser VARCHAR(30),
@AfterHoursUser VARCHAR(30) = NULL,
@Notes VARCHAR(1000) = NULL,
@OldGroupId VARCHAR(25),
@OldStartDate DATETIME,
@OldEndDate DATETIME = NULL,
@OldBusinessHoursUser VARCHAR(30),
@OldAfterHoursUser VARCHAR(30) = NULL,
@OldNotes VARCHAR(1000) = NULL
AS
BEGIN
BEGIN TRAN mainTran
-- IF THE EndDate is null, then make the end date the end of the day(11:59:59 PM)
-- one year out from the start date
IF @EndDate IS NULL
SET @EndDate = DATEADD(s, 86399, DATEADD(yy, 1, @StartDate))
-- update the record first and then make all of the necessary changes
-- this will keep from changing our record before we can update it
-- and possibly loosing the record all together.
UPDATE irOnCall
SET GroupId = @GroupId,
StartDate = @StartDate,
EndDate = @EndDate,
BusinessHoursUser = @BusinessHoursUser,
AfterHoursUser = @AfterHoursUser,
Notes = @Notes
WHERE GroupId = @OldGroupId
AND StartDate = @OldStartDate
AND (EndDate = @OldEndDate OR (@OldEndDate IS NULL AND EndDate IS NULL))
AND BusinessHoursUser = @OldBusinessHoursUser
AND (AfterHoursUser = @OldAfterHoursUser OR (@OldAfterHoursUser IS NULL AND AfterHoursUser IS NULL))
AND (Notes = @OldNotes OR (@OldNotes IS NULL AND Notes IS NULL))
-- check for oncall schedules that end after this schedule begins
PRINT 'OLD ENDS AFTER NEW STARTS'
UPDATE irOncall
SET EndDate = DATEADD(s, -1, @StartDate)
WHERE @StartDate BETWEEN StartDate AND EndDate
AND StartDate <> CONVERT(DATETIME, @StartDate)
AND GroupId = @GroupId
-- check for oncall schedules that start before the new schedule ends
PRINT 'NEW STARTS BEFORE OLD ENDS'
UPDATE irOnCall
SET StartDate = DATEADD(s, 1, @EndDate)
WHERE @EndDate BETWEEN StartDate AND EndDate
AND StartDate <> CONVERT(DATETIME, @StartDate)
AND StartDate <> CONVERT(DATETIME, @OldStartDate)
AND GroupId = @GroupId
IF @@ERROR = 0
COMMIT TRAN mainTran
ELSE
ROLLBACK TRAN mainTran
END
</code>
Here is the code behind that is generating the errors:
<code>
Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
Select Case e.CommandName
Case "Edit"
DataGrid1.EditItemIndex = e.Item.ItemIndex
DataGrid1.ShowFooter = False
Case "Cancel"
DataGrid1.EditItemIndex = -1
DataGrid1.ShowFooter = True
Case "Insert"
' insert command logic
Case "Update"
Try
LoadOnCallDataAdapter()
Dim dr As dsOnCall.irOnCallRow = DsOnCall1.irOnCall.Item(e.Item.DataSetIndex)
SqlUpdateCommand1.Parameters("@OldGroupId").Value = ddlGroup.SelectedValue
SqlUpdateCommand1.Parameters("@GroupId").Value = ddlGroup.SelectedValue
SqlUpdateCommand1.Parameters("@OldStartDate").Value = dr("StartDate")
SqlUpdateCommand1.Parameters("@OldEndDate").Value = dr("EndDate")
SqlUpdateCommand1.Parameters("@OldBusinessHoursUser").Value = dr("BusinessHoursUser")
SqlUpdateCommand1.Parameters("@OldAfterHoursUser").Value = dr("AfterHoursUser")
SqlUpdateCommand1.Parameters("@OldNotes").Value = dr("Notes")
Dim myText As TextBox
myText = e.Item.FindControl("txtStartDateEdit")
dr.StartDate = myText.Text + " 12:00:00 AM"
SqlUpdateCommand1.Parameters("@StartDate").Value = myText.Text + " 12:00:00 AM"
myText = e.Item.FindControl("txtEndDateEdit")
If myText.Text <> "" Then
dr.EndDate = myText.Text + " 11:59:59 PM"
SqlUpdateCommand1.Parameters("@EndDate").Value = myText.Text + " 11:59:59 PM"
Else
dr.EndDate = Nothing
SqlUpdateCommand1.Parameters("@EndDate").Value = Nothing
End If
Dim myList As DropDownList
myList = e.Item.FindControl("ddlOfficeHoursEdit")
dr.BusinessHoursUser = myList.SelectedValue
SqlUpdateCommand1.Parameters("@BusinessHoursUser").Value = myList.SelectedValue
myList = e.Item.FindControl("ddlAfterHoursEdit")
If myList.SelectedValue <> "" Then
dr.AfterHoursUser = myList.SelectedValue
SqlUpdateCommand1.Parameters("@AfterHoursUser").Value = myList.SelectedValue
Else
dr.AfterHoursUser = Nothing
SqlUpdateCommand1.Parameters("@AfterHoursUser").Value = Nothing
End If
myText = e.Item.FindControl("txtNotesEdit")
If myText.Text <> "" Then
dr.Notes = myText.Text
SqlUpdateCommand1.Parameters("@Notes").Value = myText.Text
Else
dr.Notes = Nothing
SqlUpdateCommand1.Parameters("@Notes").Value = Nothing
End If
daOnCall.Update(DsOnCall1, "irOnCall")
DsOnCall1.Clear()
DataGrid1.EditItemIndex = -1
DataGrid1.ShowFooter = True
lblMessage.Text = "Record Updated"
Catch ex As Exception
lblMessage.Text = "Error Updating: " & ex.Message
End Try
End Select
BindDetailData()
End Sub
</code>
Here is the error message being caught in the code-behind page:
Column 'GroupId, StartDate' is constrained to be unique. Value 'EBIZ, 9/12/2005 12:00:00 AM' is already present.
When the page loads after the exception handling, this is the error that is returned:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Let me know if you want some test data to work with as well. This is really killing me. I have been working hand-in-hand with my dba to get this going.