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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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 said:
Hi,

I have a typed dataset with tables including timestamp type field, when
Click to expand...
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,
Click to expand...
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,
Click to expand...
last_login, timestamp, datemodif FROM [user] WHERE (user_id = @user_id)
 
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 said:
Hi,

I have a typed dataset with tables including timestamp type field, when
Click to expand...
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,
Click to expand...
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,
Click to expand...
last_login, timestamp, datemodif FROM [user] WHERE (user_id = @user_id)
 
Back
Top