SQL problem: scope_identity() returning dbnull in insert statement.

[Martin Z]

INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filenam e,@TransmittedData);
SELECT @retVal = SCOPE_IDENTITY();

Pretty simple. There is an additional TransmissionID column that is
an autonumber and primary key. @retVal is always null in my table
adapter function, where I'm setting @retval (in the Parameters
collection of the typed dataset tableadapter) to be an output
parameter of type in.

[Stefan Hoffmann]

hi Martin,

Martin Z wrote:

INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filenam e,@TransmittedData);
SELECT @retVal = SCOPE_IDENTITY();

Pretty simple. There is an additional TransmissionID column that is
an autonumber and primary key. @retVal is always null in my table
adapter function, where I'm setting @retval (in the Parameters
collection of the typed dataset tableadapter) to be an output
parameter of type in.

Why not sending this as a SQL batch?
Otherwise how is your function/procedure defined?
Are you using SET NOCOUNT ON?
How do you call this function?
mfG
--stefan <--

[Martin Z]

On Mar 21, 11:13 am, Stefan Hoffmann <[e-mail]>
wrote:

hi Martin,

Martin Z wrote:

INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filenam e,@TransmittedData);
SELECT @retVal = SCOPE_IDENTITY();

Pretty simple. There is an additional TransmissionID column that is
an autonumber and primary key. @retVal is always null in my table
adapter function, where I'm setting @retval (in the Parameters
collection of the typed dataset tableadapter) to be an output
parameter of type in.

Why not sending this as a SQL batch?
Otherwise how is your function/procedure defined?
Are you using SET NOCOUNT ON?
How do you call this function?

mfG
--stefan <--

As I said, it's an SQL Query procedure defined in the Typed
TableAdapter for the Transmission table. It's being used in several
different places in my app, which is a thick-client that talks
directly to the database. And no, I'm not using SET NOCOUNT ON.

[Stefan Hoffmann]

hi Martin,

Martin Z wrote:

>Otherwise how is your function/procedure defined?

As I said, it's an SQL Query procedure defined in the Typed
TableAdapter for the Transmission table.

Which still leaves the question above unanswered. Can you post the
complete defintitions (SQL, C#) that are involved?
mfG
--stefan <--

[Martin Z]

On Mar 21, 11:58 am, Stefan Hoffmann <[e-mail]>
wrote:

hi Martin,

Martin Z wrote:

Otherwise how is your function/procedure defined?

As I said, it's an SQL Query procedure defined in the Typed
TableAdapter for the Transmission table.

Which still leaves the question above unanswered. Can you post the
complete defintitions (SQL, C#) that are involved?

mfG
--stefan <--

>From the XSD, the InsertReturnCommand XSD

<DbSource ConnectionRef="DmtConnectionString (Settings)"
DbObjectName="" DbObjectType="Unknown" GenerateShortCommands="True"
GeneratorSourceName="InsertReturnTransmissionID" Modifier="Public"
Name="InsertReturnTransmissionID" QueryType="NoData"
ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089"
UseOptimisticConcurrency="True" UserGetMethodName="GetDataBy"
UserSourceName="InsertReturnTransmissionID">
<InsertCommand>
<DbCommand CommandType="Text" ModifiedByUser="True">
<CommandText>INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filenam e,@TransmittedData);
SELECT @retVal = SCOPE_IDENTITY();</CommandText>
<Parameters>
<Parameter AllowDbNull="True"
AutogeneratedName="TransmissionDate" ColumnName="TransmissionDate"
DataSourceName="Dmt.dbo.Transmission" DataTypeServer="datetime"
DbType="DateTime" Direction="Input" ParameterName="@TransmissionDate"
Precision="0" ProviderType="DateTime" Scale="0" Size="8"
SourceColumn="TransmissionDate" SourceColumnNullMapping="False"
SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False"
AutogeneratedName="TransmissionDirection"
ColumnName="TransmissionDirection"
DataSourceName="Dmt.dbo.Transmission" DataTypeServer="int"
DbType="Int32" Direction="Input"
ParameterName="@TransmissionDirection" Precision="0"
ProviderType="Int" Scale="0" Size="4"
SourceColumn="TransmissionDirection" SourceColumnNullMapping="False"
SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True"
AutogeneratedName="Filename" ColumnName="Filename"
DataSourceName="Dmt.dbo.Transmission" DataTypeServer="varchar(128)"
DbType="AnsiString" Direction="Input" ParameterName="@Filename"
Precision="0" ProviderType="VarChar" Scale="0" Size="128"
SourceColumn="Filename" SourceColumnNullMapping="False"
SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False"
AutogeneratedName="TransmittedData" ColumnName="TransmittedData"
DataSourceName="Dmt.dbo.Transmission" DataTypeServer="text"
DbType="AnsiString" Direction="Input" ParameterName="@TransmittedData"
Precision="0" ProviderType="Text" Scale="0" Size="2147483647"
SourceColumn="TransmittedData" SourceColumnNullMapping="False"
SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="False"
AutogeneratedName="" DataSourceName="" DbType="Int32"
Direction="Output" ParameterName="@retVal" Precision="0"
ProviderType="Int" Scale="0" Size="0" SourceColumnNullMapping="False"
SourceVersion="Current">
</Parameter>
</Parameters>
</DbCommand>
</InsertCommand>
</DbSource>

which autogenerates the code:

[System.Diagnostics.DebuggerNonUserCodeAttribute()]

[System.ComponentModel.Design.HelpKeywordAttribute( "vs.data.TableAdapter")]

[System.ComponentModel.DataObjectMethodAttribute(Sy stem.ComponentModel.DataObjectMethodType.Insert,
false)]
public virtual int
InsertReturnTransmissionID(System.Nullable<System. DateTime>
TransmissionDate, int TransmissionDirection, string Filename, string
TransmittedData, out int retVal) {
System.Data.SqlClient.SqlCommand command =
this.CommandCollection[2];
if ((TransmissionDate.HasValue == true)) {
command.Parameters[0].Value = ((System.DateTime)
(TransmissionDate.Value));
}
else {
command.Parameters[0].Value = System.DBNull.Value;
}
command.Parameters[1].Value = ((int)
(TransmissionDirection));
if ((Filename == null)) {
command.Parameters[2].Value = System.DBNull.Value;
}
else {
command.Parameters[2].Value = ((string)(Filename));
}
if ((TransmittedData == null)) {
throw new
System.ArgumentNullException("TransmittedData");
}
else {
command.Parameters[3].Value = ((string)
(TransmittedData));
}
System.Data.ConnectionState previousConnectionState =
command.Connection.State;
if (((command.Connection.State &
System.Data.ConnectionState.Open)
!= System.Data.ConnectionState.Open)) {
command.Connection.Open();
}
int returnValue;
try {
returnValue = command.ExecuteNonQuery();
}
finally {
if ((previousConnectionState ==
System.Data.ConnectionState.Closed)) {
command.Connection.Close();
}
}
if (((command.Parameters[4].Value == null)
|| (command.Parameters[4].Value.GetType() ==
typeof(System.DBNull)))) {
throw new System.Data.StrongTypingException("The value
for parameter \'retVal\' is DBNull.", null);
}
else {
retVal = ((int)(command.Parameters[4].Value));
}
return returnValue;
}

for the table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[FK_Request_TransmissionResponse]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Request] DROP CONSTRAINT
FK_Request_TransmissionResponse
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[FK_Request_TransmissionSent]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Request] DROP CONSTRAINT
FK_Request_TransmissionSent
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Transmission]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transmission]
GO

CREATE TABLE [dbo].[Transmission] (
[TransmissionID] [int] IDENTITY (1, 1) NOT NULL ,
[TransmissionDate] [datetime] NULL ,
[TransmissionDirection] [int] NOT NULL ,
[Filename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TransmittedData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CreatedMoment] [datetime] NULL ,
[ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ModifiedMoment] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm always hitting the " throw new
System.Data.StrongTypingException("The value for parameter \'retVal\'
is DBNull.", null);" problem.

Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?

[Stefan Hoffmann]

hi Martin,

Martin Z wrote:

Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?

Yes, when no IDENTITY value was generated in your current scope.

Are there any constraints active on your table? Try to execute your SQL
manually in the Query Analyzer. Does it work?

mfG
--stefan <--

[Martin Z]

On Mar 21, 12:33 pm, Stefan Hoffmann <[e-mail]>
wrote:

hi Martin,

Martin Z wrote:

Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?

Yes, when no IDENTITY value was generated in your current scope.

Are there any constraints active on your table? Try to execute your SQL
manually in the Query Analyzer. Does it work?

mfG
--stefan <--

I can execute manually in the Query Analyzer after replacing all the
variables with immediate data and converting the "select @reval =
SCOPE_IDENTITY()" into "select SCOPE_IDENTITY()", which inserts the
row but returns an empty resultset.

[Stefan Hoffmann]

hi Martin,

Martin Z wrote:

>>Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?

Yes, when no IDENTITY value was generated in your current scope.
Are there any constraints active on your table? Try to execute your SQL
manually in the Query Analyzer. Does it work?

I can execute manually in the Query Analyzer after replacing all the
variables with immediate data and converting the "select @reval =
SCOPE_IDENTITY()" into "select SCOPE_IDENTITY()", which inserts the
row but returns an empty resultset.

This is odd. When there is no instead of trigger active, then you must
have an error somewhere in your TableAdapter. But i can't see it right
now, sorry.
mfG
--stefan <--

[Martin Z]

On Mar 22, 5:48 am, Stefan Hoffmann <[e-mail]>
wrote:

hi Martin,

Martin Z wrote:

>Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?
Yes, when no IDENTITY value was generated in your current scope.
Are there any constraints active on your table? Try to execute your SQL
manually in the Query Analyzer. Does it work?

I can execute manually in the Query Analyzer after replacing all the
variables with immediate data and converting the "select @reval =
SCOPE_IDENTITY()" into "select SCOPE_IDENTITY()", which inserts the
row but returns an empty resultset.

This is odd. When there is no instead of trigger active, then you must
have an error somewhere in your TableAdapter. But i can't see it right
now, sorry.

mfG
--stefan <--

Well, I foudn a work around (used IDENT_CURRENT and did it as two
seperate actions within a transaction)... but I just found out my DBA
had some cruel-and-unusual auditing triggers involved anyways that may
have been contributing to it (and other) bugs.

Thanks for all your help and suggestions.

-- Martin