What I am trying to achieve is get the identity value of a new record during a SqlDataSource.Insert. However, I am getting the following error: 'Object cannot be cast from DBNull to other types.'  Any help would be appreciated.

My query and code are listed below:

INSERT INTO SR_Table (UserName)
VALUES (@UserName);
SELECT @newID = SCOPE_IDENTITY()

Dim newSRID As Integer

Protected Sub CreateSRDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles CreateSRDataSource.Inserted

Dim newID As Integer = Convert.ToInt32(e.Command.Parameters("@newID").Value)
newSRID = newID

End Sub

Are you sure your parameter is defined as Output. Does the record get inserted? Actually, in your case it would be return value, not the output parameter.

I usually create stored procedure and then I use

if (e.Command.Parameters["@NewPersonID"].Value != DBNull.Value)

else

The parameters properties are:

ConvertEmptyStringToNull: True
DBType: Object
Direction: Output
Name: newID
Size: 0
Type: INt32

And No, the insert fails due to a null value for the identity. Any other suggestions? How could I convert my query to a Stored Procedure?

Here is a sample of my SP

USE [Test]

-- Author: my name

-- Create date: 06-27-2008

@NewPersonID INT = NULL Output,

@PersonType CHAR(1) = 'A', -- default is Adult Volunteer

@SiteID INT,

@FirstName VARCHAR(25),

@MiddleName VARCHAR(25) = NULL,

@LastName VARCHAR(30),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

IF EXISTS

(

FROM dbo.People

AND SiteID = @SiteID

)

BEGIN

RAISERROR (N'UserName %s already exists. Please choose another username.',

16, -- Severity,

1, -- State,

@UserName)

END

IF EXISTS

(

FROM dbo.People

WHERE FirstName = LTRIM(@FirstName)

AND LastName = LTRIM(@LastName)

AND SiteID = @SiteID

)

BEGIN

RAISERROR (N'User %s %s already exists.',

16, -- Severity,

1, -- State,

@FirstName, @LastName)

END

BEGIN TRY

--set @DefaultPicture = CONVERT(varbinary(max),@DefaultPicture)

BEGIN TRANSACTION

-- All types first insert into People table

INSERT

INTO People

(

)

(

LTRIM(@FirstName) ,

LTRIM(ISNULL(@MiddleName,'')) ,

LTRIM(@LastName) ,

etc. ,

@DefaultPicture)

--print 'Inserted new record into People'

SET @NewPersonID = scope_identity()

other code

COMMIT TRANSACTION

END TRY

BEGIN

CATCH

DECLARE @ErrorSeverity INT,

@ErrorNumber INT,

@ErrorMessage nvarchar(4000),

@ErrorState INT,

@ErrorLine INT,

@ErrorProc nvarchar(200)

-- Grab error information from SQL functions

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

SET @ErrorLine = ERROR_LINE()

SET @ErrorProc = ERROR_PROCEDURE()

-- Not all errors generate an error state, to set to 1 if it's zero

SET @ErrorState = 1

-- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback

BEGIN

--print 'Rollback transaction'

ROLLBACK TRANSACTION

END

RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)

END CATCH

RETURN @@ERROR

END

Hi,rock

rock056:

Any other suggestions? How could I convert my query to a Stored Procedure?

Try Like This

In sql

Stored Proc

Go

Create Proc Insert_SR_Table

@UserName varchar(30),

INSERT INTO SR_Table (UserName)

VALUES (@UserName);

SELECT @newID = SCOPE_IDENTITY()

GO

In Front End

In ur sql datasource Like this

Inaspx

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="xxxxxxx--UrconnectionString-------xxxxxxxxxxx"

InsertCommand="Insert_SR_Table" InsertCommandType="StoredProcedure" >

<InsertParameters>

<asp:Parameter Name="UserName" Type="String" Direction="Input" />

<asp:Parameter Name="newID" Type="Int32" Direction="Output" />

</InsertParameters>

</asp:SqlDataSource>

Inaspx.vb

Dim newID As Integer = Convert.ToInt32(e.Command.Parameters("@newID").Value)

End Sub

Hi,rock

If U want To Stuck With Simple Text Query

Then U can Go For This

Inaspx

I created the stored procedure, corrected all my parameters.  I am still getting the same error. What else could be causing this error?

<asp:SqlDataSource ID="CreateSRDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

InsertCommand="Insert_SR_Table" InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:Parameter Name="Open_Date" Type="DateTime" Direction="Input" />

<asp:ControlParameter ControlID="lblIdentity" Name="UserFullName" PropertyName="Text"

Direction="Input" />

<asp:ProfileParameter Name="UserName" PropertyName="Username" Type="String" Direction="Input" />

<asp:ProfileParameter DefaultValue="" Name="UserEmail" PropertyName="EmailAddress"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRType" DefaultValue="" Name="ServiceRequestType"

PropertyName="Text" Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRSubTypeOne" Name="ServiceRequestSubType" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRSubTypeTwo" Name="ServiceRequestSubType2" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="txtBody" Name="Descr" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ProfileParameter DefaultValue="" Name="Dept_Name" PropertyName="Department"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblStatus" Name="Status_Type" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblPriority" Name="Priority_Name" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblManagerDecision" Name="ManagerDecision" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblAssigned" Name="AssignedToName" PropertyName="Text"

Type="String" Direction="Input" />

<asp:Parameter Name="newID" Type="Int32" Direction="Output" />

</InsertParameters>

</asp:SqlDataSource>

@Open_Date varchar(50),

@UserName nvarchar(25),

@ServiceRequestType nvarchar(25),

@ServiceRequestSubType2 nvarchar(25),

@Dept_Name nvarchar(50),

@Priority_Name nvarchar(25),

@AssignedToName nvarchar(25),

VALUES (GETDATE(),@UserFullName,@UserName,@UserEmail,@ServiceRequestType,@ServiceRequestSubType,@ServiceRequestSubType2,@Descr,@Dept_Name, @Status_Type, @Priority_Name, @ManagerDecision, @AssignedToName);

SELECT @newID = SCOPE_IDENTITY()

end

Hi,rock

As I hve not comlete data of urs I have Tested With below example for ur case

It is working for me

Ucan Add Try catch Block To see that is there any exception causing

I have checked with below example

In sql

CREATE TABLE [dbo].[SR_Table](

[id] [int] IDENTITY(1,1) NOT NULL,

@Open_Date varchar(50),

@UserFullName nvarchar(50),

@UserName nvarchar(25),

@UserEmail nvarchar(50),

@ServiceRequestType nvarchar(25),

@ServiceRequestSubType nvarchar(25),

@ServiceRequestSubType2 nvarchar(25),

@Descr nvarchar(255),

@Dept_Name nvarchar(50),

@Status_Type nvarchar(25),

@Priority_Name nvarchar(25),

@ManagerDecision nvarchar(25),

@AssignedToName nvarchar(25),

insert into SR_Table

(UserName)

(@UserName )

SELECT @newID = SCOPE_IDENTITY()

<head runat="server">

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:SqlDataSource ID="CreateSRDataSource" runat="server" ConnectionString="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

InsertCommand="Insert_SR_Table" InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:Parameter Name="Open_Date" Type="DateTime" Direction="Input" />

<asp:ControlParameter ControlID="lblIdentity" Name="UserFullName" PropertyName="Text"

Direction="Input" />

<asp:ProfileParameter Name="UserName" PropertyName="Username" Type="String" Direction="Input" />

<asp:ProfileParameter DefaultValue="" Name="UserEmail" PropertyName="EmailAddress"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRType" DefaultValue="" Name="ServiceRequestType"

PropertyName="Text" Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRSubTypeOne" Name="ServiceRequestSubType" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblSRSubTypeTwo" Name="ServiceRequestSubType2" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="txtBody" Name="Descr" PropertyName="Text" Type="String"

Direction="Input" />

<asp:ProfileParameter DefaultValue="" Name="Dept_Name" PropertyName="Department"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblStatus" Name="Status_Type" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblPriority" Name="Priority_Name" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblManagerDecision" Name="ManagerDecision" PropertyName="Text"

Type="String" Direction="Input" />

<asp:ControlParameter ControlID="lblAssigned" Name="AssignedToName" PropertyName="Text"

Type="String" Direction="Input" />

<asp:Parameter Name="newID" Type="Int32" Direction="Output" />

</InsertParameters>

</asp:SqlDataSource>

<asp:Label ID="lblIdentity" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblSRType" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblSRSubTypeOne" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblSRSubTypeTwo" runat="server" Text="Label"></asp:Label>

<asp:TextBox ID="txtBody" runat="server"></asp:TextBox>

<asp:Label ID="lblStatus" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblPriority" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblManagerDecision" runat="server" Text="Label"></asp:Label>

<asp:Label ID="lblAssigned" runat="server" Text="Label"></asp:Label>

<br />

<asp:Label ID="lblError" runat="server" Text="Label"></asp:Label>

</div>

</body>

</html>

in web config

<properties>

<add name="EmailAddress" type="String" defaultValue ="Test"/>

</properties>

</profile>

inaspx.vb

Imports System.Data

Imports System.Data.SqlClient

Inherits System.Web.UI.Page

Try

End Try

Dim newID As Integer = Convert.ToInt32(e.Command.Parameters("@newID").Value)

End Class

Did the record get inserted? I bet it was not. I posted some code about how to trap for exceptions in SPs and also a code for checking against DBNull.Value for the returned parameter.