concurrency and timestamp or datetime

Hi everyone!

I've read a lot of document about optimistic concurrency and different implementations which made me decide to chose the timestamp/datetime approuch to validate if another user has editet the record.

I'm saying timestamp OR datetime because I dont really care which one to use but I can't make any of them work as expected.

Here is my setup:

I'm using a DataSet (autogenerated by Visual Studio 2005) with 4 stored procedures to select, update, delete and insert records.
I'm using a GridView to show these values but when using a timestamp in the database the parameter type in my ObjectDataSource is an Object which ofcause ins't right and I can't change it to Byte[].
If I instead use a Datetime I believe that the date formatting is done somehow (even though i make the field ReadOnly in the GridView) - I can see the date is shown as:"01-01-1900 00:01:07" but the actually SQL that is executed is: 'Jan 1 1900 12:01:07:000AM' why this differense?

So my question is which one should I use and how - the datetime/timestamp dosn't have to be shown - I would actually prefer that the datetime/timestamp was somehow hidden from the presentationlayer and only present in the data access layer but still would be transfered to and from the database when doing updates etc.

Best of all I could use a working example.

Thanks in advance :-)

There is a big difference between DateTime and Timestamp, the former is a data type while the late is a derived data type SQL Server uses internally for housing keeping. So Timestamp will not move with your data. Try the link below for a tutorial on concurrency. Hope this helps.

http://www.15seconds.com/issue/030604.htm

|||

Thanks for you reply.

I know there is a big difference between the two datatypes but that is not my problem - my problem is HOW to use one of them.

You are wrong about that "timestamp vil not move with your data" - what I've done to overcome my Byte[] problem is to cast the timestamp to char(8) - when I later want to make an update I compare with the timestamp (which is casted back from char(8) to timestamp). This works like it should but I just don't like this casting thing - there must be a better way.

I've allready read the artickel you've linked - among a lot of others. This one is unfortunally in VB which is not my strong side. Is there another one in C# that you know of?

Concatenation in stored procedure

hi everyone
i am writing a stored procedure and i am new in this field
i want to concatenate a string, i got an error
hope someone could help.
my stored procedure is:
CREATE PROCEDURE dbo.ProdCatComp
(
@.Product nvarchar(40),
)
AS
DECLARE @.str nvarchar(100)
SET @.str='Products.ProductName like '%' + @.Product + '%''

SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.HomePage
FROM Products INNER JOIN
Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName<>'' AND @.str
GO

The error is :
Error 403: Invalid operator for data type. Operator equals modulo, type equals varchar.you have to make the whole query dynamic
and then use exec/sp_executesql|||luber is correct in most cases you will need to build a sql string and then execute it, but in this particular case the code below should work - not tested.

CREATE PROCEDURE dbo.ProdCatComp
 @.Product nvarchar(40)
AS
 DECLARE @.str nvarchar(100)
 SET @.str = '%' + @.Product + '%'
 SELECT Products.ProductName,
 Products.UnitPrice,
 Categories.CategoryName,
 Suppliers.CompanyName,
 Suppliers.ContactName,
 Suppliers.HomePage
 FROM Products
 INNER JOIN Suppliers
 ON Products.SupplierID = Suppliers.SupplierID
 INNER JOIN Categories
 ON Products.CategoryID = Categories.CategoryID
 WHERE Products.ProductName <> ''
 AND Products.ProductName LIKE @.str
GO