timestamp Trouble

Michael Hildner posted on Thursday, October 19, 2006

I'm trying to implement first-write-wins optomistic concurrency. Using 2.0.3 and Sql Server 2005. I based my code and sprocs off the book, but I can't seem to get my update sproc to work. Specifically, I'm having troubles with the timestamp column.

My business object has a  _lastChanged byte array, and in the update routine, I do this:

cm.Parameters.AddWithValue("@LastChanged", _lastChanged);

cm.Parameters["@LastChanged"].Direction = ParameterDirection.Output;

The sproc checks for LastChanged:

AND

[LastChanged] = @LastChanged

IF @@ROWCOUNT = 0

RAISERROR('Row has been edited by another user.', 16, 1)

My problem is that @LastChanged is not what I set in _lastChanged. Using the profiler, I can see my timestamp is a varbinary(1):

declare @p8 varbinary(1)
set @p8=0x00

Yet in the sproc it's defined as

@LastChanged timestamp OUTPUT

That's what I don't understand. The sproc defines a timestamp, yet the command seems to create a varbinary(1). So my updates always fail with "Row has been edited ...".

How can I pass the proper value to the sproc?

Thanks,

Mike

ajj3085 replied on Friday, October 20, 2006

How are you sending the value to the server in Ado.net?

Michael Hildner replied on Friday, October 20, 2006

Hi Andy,

I'm using a SqlCommand with a CommandType of .StoredProcedure. Here's the code:

private void ExecuteUpdate(SqlConnection cn)

{

using (SqlCommand cm = cn.CreateCommand())

{

cm.CommandType = CommandType.StoredProcedure;

cm.CommandText = "UpdateSlotMachine";

AddUpdateParameters(cm);

cm.ExecuteNonQuery();

_lastChanged = (byte[])cm.Parameters["@LastChanged"].Value;

}//using

}

In the AddUpdateParameters() method I set a bunch of parameters, including my _lastChanged field:

cm.Parameters.AddWithValue("@LastChanged", _lastChanged);

cm.Parameters["@LastChanged"].Direction = ParameterDirection.Output;

_lastChanged here is 8 bytes, but only one byte gets sent to Sql Server. I'm trying to figure out what to do to make sure I'm sending 8 bytes to the stored procedure.

Thanks,

Mike

ajj3085 replied on Friday, October 20, 2006

After you add the LastChanged parameter, try manually setting the DataType propety on the parameter to timestamp.

Michael Hildner replied on Friday, October 20, 2006

Thanks Andy,

That worked, kind of. I changed my parameter stuff to

cm.Parameters.Add("@LastChanged", SqlDbType.Timestamp);

cm.Parameters["@LastChanged"].Value = _lastChanged;

cm.Parameters["@LastChanged"].Direction = ParameterDirection.Output;

And now the full 8 bytes get sent to Sql Server.

Of course now I have another issue :)

This is pretty strange. I try to update, and I still get the "Row has been edited by another user" message. This is the whole statement as shown in the profiler:

declare @p8 binary(8)
set @p8=0x0000000000001F41
exec UpdateSlotMachine @SlotMachinePK='1B8E1E96-A027-42E7-B20C-CFE74392BB0C',@SerialNumber=N'00-02544E',@BarCodeNumber=N'00-02544E',@CreatedDate=''2006-10-19
10:04:53:267'',@CreatedByFK='F76A05FB-33F8-4674-AB83-8AC742CB7502',@ModifiedDate=''2006-10-19
10:04:53:267'',@ModifiedByFK='F76A05FB-33F8-4674-AB83-8AC742CB7502',@LastChanged=@p8
output,@Manufacturer=N'Tandyxxx',@AssetNumber=N'123',@Denomination=N'1',@Location=N'B12',@HoldPercent=N'12',@Description=N'Luck Sevens',@HitFrequency=N'2'
select @p8

Now, when I copy and paste that into a new query window, run it, it works fine. I don't understand that at all.

Mike

ajj3085 replied on Friday, October 20, 2006

that is strange.. My only suggestion is to open query analyzer and set it to read uncommited transactions.  Check out the row just before and just after the update..