SCOPE_IDENTITY() returning DBNULL

[gillivt]

I am using SQL Server 2005 express. I want to insert a record into a table and return its primary key. Here is my SQL Procedure:

ALTER PROCEDURE InitialiseHistory
	
	
AS
	INSERT INTO StatusHistory
	                      (StatusCode, DateChanged, Comments)
	VALUES     (0, { fn NOW() }, 'Status initialised to UNKNOWN')
	
	
	RETURN SCOPE_IDENTITY()

I then use the following asp.net code to run the procedure and return the primary key:

Dim dbConnection As SqlConnection = New SqlConnection(GetConnectionString())

        'Initialise History

        Dim InsertCommand As SqlCommand = New SqlCommand("InitialiseHistory", dbConnection)
        Dim ID As Guid
        InsertCommand.CommandType = CommandType.StoredProcedure
        Try
            dbConnection.Open()
            _statusHistoryID = CType(InsertCommand.ExecuteScalar(), String)
            ID = New Guid(_statusHistoryID)

        Catch sqlex As SqlException
            MsgBox(sqlex.Message)
            Response = errorCode.Database_Unavailable

        Catch ex As Exception
            MsgBox(ex.Message)
            Response = errorCode.Unknown_Error

        Finally
            dbConnection.Close()
            InsertCommand.Dispose()

        End Try

After checking the table, I can see that the INSERT command functioned correctly. However I get an exception on line ID = New Guid(_statusHistoryID) The exception message is: "value cannot be null prameter name: g

Any ideas?

[gillivt]

I have found the solution. The problem was in the fact that I was using a Unique Identifier (Guid) for my primary key. SCOPE_IDENTITY for for identity columns only.

The following cjanges to my SQL code fixed the the problem:

ALTER PROCEDURE InitialiseHistory
	@DateChanged	SmallDateTime
AS
	SET NOCOUNT ON
	DECLARE @ReturnID uniqueidentifier
	SET @ReturnID = NEWID()
	INSERT INTO StatusHistory
	                      (StatusCode, DateChanged, Comments)
	VALUES     (0, @DateChanged, 'Status initialised to UNKNOWN')
	SELECT @ReturnID
	SET NOCOUNT OFF
	RETURN