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

M

Martin Z

INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filename,@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.
 
S

Stefan Hoffmann

hi Martin,

Martin said:
INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filename,@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.
Click to expand...
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 <--
 
M

Martin Z

hi Martin,




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 <--
Click to expand...

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.
 
S

Stefan Hoffmann

hi Martin,

Martin said:
As I said, it's an SQL Query procedure defined in the Typed
TableAdapter for the Transmission table.
Click to expand...
Which still leaves the question above unanswered. Can you post the
complete defintitions (SQL, C#) that are involved?


mfG
--> stefan <--
 
M

Martin Z

hi Martin,



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
Click to expand...

<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,@Filename,@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(System.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?
 
S

Stefan Hoffmann

hi Martin,

Martin said:
Is there any case where a SCOPE_IDENTITY(); will return null just
after an insert?
Click to expand...
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 <--
 
M

Martin Z

hi Martin,



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 <--
Click to expand...

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.
 
S

Stefan Hoffmann

hi Martin,

Martin said:
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.
Click to expand...
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 <--
 
M

Martin Z

hi Martin,



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 <--
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Returning SCOPE_IDENTITY from a Stored Procedure 2
returning SCOPE_IDENTITY() in Insert command with C# DataSets 6
Help with typed dataset and tableadapters update 1
TableAdapter, stored procedure, optional parameters 0
DataAdapter.Update calling Insert Command with DbNull values 2
updating child records in a DataSet w/ new autoinc values after parent insert? 3
Multiple Inserts and Identity problem 6
Data Access Block and Identity Column Return... 1

Top