Error: Cannot insert a non-null value into a timestamp column

Hi,

I have a typed dataset with tables including timestamp type field, when ready to commit the change back to the SQL server trough the data adapter and my storeprocedure, I receive the error.

How can I force SQL server inside a storeprocedure a NULL value for that timestamp field?
Is there a better solution from my c# code?

I try the following,
----------------------------------------------------------------------------
ALTER PROCEDURE dbo.InsertUser
(
@last_name char(25),
@first_name char(20),
@phone char(20),
@login_name char(30),
@email char(100),
@last_login datetime,
@datemodif datetime,
@timestamp timestamp,
@user_id uniqueidentifier OUTPUT
)
AS
SET NOCOUNT OFF;
SET @user_id = NEWID()
SET @timestamp = NULL

INSERT INTO [user] (user_id, last_name, first_name, phone, login_name, email, last_login, timestamp, datemodif) VALUES (@user_id, @last_name, @first_name, @phone, @login_name, @email, @last_login, @timestamp, @datemodif);
SELECT user_id, last_name, first_name, phone, login_name, email, last_login, timestamp, datemodif FROM [user] WHERE (user_id = @user_id)

Miha
Tue Jan 20 12:55:36 CST 2004

Hi Carl,

I don't think that you can insert null value into timestamp column.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Carl" <anonymous@discussions.microsoft.com> wrote in message
news:B49391FB-B3CC-470F-9ED1-98AF9DD7CE12@microsoft.com...
> Hi,
>
> I have a typed dataset with tables including timestamp type field, when
ready to commit the change back to the SQL server trough the data adapter
and my storeprocedure, I receive the error.
>
> How can I force SQL server inside a storeprocedure a NULL value for that
timestamp field?
> Is there a better solution from my c# code?
>
> I try the following,
> --------------------------------------------------------------------------
--
> ALTER PROCEDURE dbo.InsertUser
> (
> @last_name char(25),
> @first_name char(20),
> @phone char(20),
> @login_name char(30),
> @email char(100),
> @last_login datetime,
> @datemodif datetime,
> @timestamp timestamp,
> @user_id uniqueidentifier OUTPUT
> )
> AS
> SET NOCOUNT OFF;
> SET @user_id = NEWID()
> SET @timestamp = NULL
>
> INSERT INTO [user] (user_id, last_name, first_name, phone, login_name,
email, last_login, timestamp, datemodif) VALUES (@user_id, @last_name,
@first_name, @phone, @login_name, @email, @last_login, @timestamp,
@datemodif);
> SELECT user_id, last_name, first_name, phone, login_name, email,
last_login, timestamp, datemodif FROM [user] WHERE (user_id = @user_id)
>
>

Derrick
Tue Jan 20 13:26:11 CST 2004

Carl,

The timestamp, like the rowversion and rowguid, are automatically generated.
Trying to force a specific value into that column will result in an error.
From the MSDN:

"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes."

The key there is "automatically generated". Remove that element from your
"INSERT INTO" T-SQL statement and it should work fine.

HTH,
Derrick



"Carl" <anonymous@discussions.microsoft.com> wrote in message
news:B49391FB-B3CC-470F-9ED1-98AF9DD7CE12@microsoft.com...
> Hi,
>
> I have a typed dataset with tables including timestamp type field, when
ready to commit the change back to the SQL server trough the data adapter
and my storeprocedure, I receive the error.
>
> How can I force SQL server inside a storeprocedure a NULL value for that
timestamp field?
> Is there a better solution from my c# code?
>
> I try the following,
> --------------------------------------------------------------------------
--
> ALTER PROCEDURE dbo.InsertUser
> (
> @last_name char(25),
> @first_name char(20),
> @phone char(20),
> @login_name char(30),
> @email char(100),
> @last_login datetime,
> @datemodif datetime,
> @timestamp timestamp,
> @user_id uniqueidentifier OUTPUT
> )
> AS
> SET NOCOUNT OFF;
> SET @user_id = NEWID()
> SET @timestamp = NULL
>
> INSERT INTO [user] (user_id, last_name, first_name, phone, login_name,
email, last_login, timestamp, datemodif) VALUES (@user_id, @last_name,
@first_name, @phone, @login_name, @email, @last_login, @timestamp,
@datemodif);
> SELECT user_id, last_name, first_name, phone, login_name, email,
last_login, timestamp, datemodif FROM [user] WHERE (user_id = @user_id)
>
>

anonymous
Tue Jan 20 13:51:05 CST 2004

Derrick, Thanks for this fast reply...

It work ok now

Carl,