December 5, 2005 at 7:09 am
Hi,
I have a stored proc that will update a certain row if it hasn't already been updated already. It checks the datetime stamp (LastModified) to ensure that this is the case. The problem is that if I copy the LastModified date and run the procedure using this value, it won't update the row. I use exactly this logic on another table and it works perfectly.
I'm looking at this too long to see the problem any more. Any help greatly appreciated. stored proc and table def posted below.
ALTER PROCEDURE dbo.Test2
(
@RowCount int = 0 output,
@Error int = 0 output,
@LastModified datetime output,
@CheckModified datetime,
@DocumentID int,
@DocTitle varchar(100),
@ReviewEvery int,
@Obsolete bit,
@ObsoleteDate DateTime,
@Comments varchar(100),
@OldDocNo varchar(50)
)
AS
SET @LastModified = GetDate()
BEGIN TRAN
UPDATE dbo.Document
SET DocTitle = @DocTitle, ReviewEvery = @ReviewEvery, Obsolete = @Obsolete, ObsoleteDate = @ObsoleteDate,
Comments = @Comments, OldDocNo = @OldDocNo,
LastModified =@LastModified
WHERE DocumentID = @DocumentID AND
LastModified = @CheckModified
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
/* Check for no error and 1 row updated */
IF (@Error <> 0 OR @RowCount <> 1)
ROLLBACK TRAN
ELSE
COMMIT TRAN
RETURN
***********************table def******************
ExecuteScript += "CREATE TABLE dbo.Document("
ExecuteScript += "DocumentID int NOT NULL IDENTITY (1, 1), "
ExecuteScript += "DocTitle varchar(100) NOT NULL, "
ExecuteScript += "Dept int NOT NULL, "
ExecuteScript += "DocClass int NOT NULL, "
ExecuteScript += "DocType int NOT NULL, "
ExecuteScript += "SeqNo int NOT NULL, "
ExecuteScript += "ReviewEvery int NOT NULL, "
ExecuteScript += "Obsolete bit NOT NULL, "
ExecuteScript += "ObsoleteDate datetime NULL, "
ExecuteScript += "Comments varchar(100) NULL, "
ExecuteScript += "OldDocNo varchar(50) NULL, "
ExecuteScript += "LastModified datetime NOT NULL);"
ExecuteScript += "ALTER TABLE dbo.Document ADD CONSTRAINT "
ExecuteScript += "DF_Document_Obsolete DEFAULT 0 FOR Obsolete; "
ExecuteScript += "ALTER TABLE dbo.Document ADD CONSTRAINT "
ExecuteScript += "PK_Document PRIMARY KEY CLUSTERED (DocumentID); "
December 5, 2005 at 7:50 am
If you run the following with actual parameter values, does that work?
UPDATE dbo.Document
SET DocTitle = @DocTitle, ReviewEvery = @ReviewEvery, Obsolete = @Obsolete, ObsoleteDate = @ObsoleteDate,
Comments = @Comments, OldDocNo = @OldDocNo,
LastModified =@LastModified
WHERE DocumentID = @DocumentID
--I am not using AND LastModeifiedDate parameter.
If the above works there is a problem with the AND LastmodifiedDate parameter.
As you know datetime includes hour, minutes and seconds. So your code requires exact match of the datetime value. Do a select and find out an exact match of the Lastmodifieddate that meets the criteria. U can use the CONVERT function to match just the date wihout hours, minutes and seconds OR use smalldatetime as datatype in the table and for the variable. GetDate() retrieves the current datetime with Hours, min and sec. U have already modified the row at a previous datetime in the previous second - so there is no match.
December 5, 2005 at 8:05 am
Hi,
Thanks for the reply. Yes, the update works without the LastModified = @CheckModified. But it wont work even when I copy the LastModified value (inc. hours. mins and secs) from the table and put it in as the parameter value. There are essentially two LastModified values.
@LastModified = The new value I am going to write into the table during this update (and that is set with GetDate())
@CheckModified = the value I picked up from the table on my last application refresh.
If there hasn't been a change in the data since my last refresh
@CheckModified should be the same as the value currently stored in teh table: if not, they wont match and I don't want to do the update.
This strategy works for me on another table all of the time but not this one. What could possibly be different on this one??
December 5, 2005 at 8:30 am
What r the exact table names for both tables. Turn on the profiler trace and run UPDATE on both check what happens?
December 5, 2005 at 8:31 am
If u do a Select with LastModifiedDate in the paramenter - does the select return data?
December 5, 2005 at 8:35 am
The one that works is:
ALTER PROCEDURE dbo.UpdRevision
(
@RowCount int = 0 output,
@Error int = 0 output,
@LastModified datetime output,
@CheckModified datetime,
@RevisionID int,
@DocumentID int,
@RevNo int,
@Description int,
@EffectiveDate DateTime,
@LastReviewedDate DateTime,
@RevisedBy varchar(50),
@MasterAddress varchar(100),
@PDFAddress varchar(100)
)
AS
SET @LastModified = GetDate()
BEGIN TRAN
UPDATE dbo.Revision
SET DocumentID = @DocumentID, RevNo = @RevNo, Description = @Description, EffectiveDate = @EffectiveDate, LastReviewedDate = @LastReviewedDate, RevisedBy = @RevisedBy,
MasterAddress = @MasterAddress, PDFAddress = @PDFAddress, LastModified = @LastModified
WHERE RevisionID = @RevisionID AND LastModified = @CheckModified
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
/* Check for no error and 1 row updated */
IF (@Error 0 OR @RowCount 1)
ROLLBACK TRAN
ELSE
COMMIT TRAN
RETURN
*****************for table ****************************
ExecuteScript += "CREATE TABLE dbo.Revision ("
ExecuteScript += "RevisionID int NOT NULL IDENTITY (1, 1), "
ExecuteScript += "DocumentID int NOT NULL, "
ExecuteScript += "RevNo int NOT NULL, "
ExecuteScript += "Description varchar(50) NULL, "
ExecuteScript += "EffectiveDate datetime NOT NULL, "
ExecuteScript += "LastReviewedDate datetime NULL, "
ExecuteScript += "RevisedBy varchar(50) NOT NULL, "
ExecuteScript += "CurrentRev bit NOT NULL, "
ExecuteScript += "MasterAddress varchar(100) NOT NULL, "
ExecuteScript += "PDFAddress varchar(100) NULL, "
ExecuteScript += "LastModified datetime NOT NULL); "
ExecuteScript += "ALTER TABLE dbo.Revision ADD CONSTRAINT "
ExecuteScript += "DF_Revision_CurrentRev DEFAULT 0 FOR CurrentRev; "
ExecuteScript += "ALTER TABLE dbo.Revision ADD CONSTRAINT "
ExecuteScript += "PK_Revision PRIMARY KEY CLUSTERED ( "
ExecuteScript += "RevisionID);"
December 5, 2005 at 8:37 am
I'm accessing SQL through Visual Studio so I don't have access to Profiler.
December 5, 2005 at 9:50 am
Yes,
I knew it had to be something silly.....and it was. I hadn't marked my parameter as type output in my .Net code and as a result it wasn't returning the @RowCount parameter and therefore my code was telling me it hadn't worked.
Sorry for taking up your time. I know I'll not need a haircut this month (all pulled out after today!!)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply