Returning SCOPE_IDENTITY from SQLDataSource and DetailsView

[David Lozzi]

Howdy,

ASP.Net 2.0 using VB on SQL 2005

This is a two fold issue.

I have a DetailsView control which users can insert or edit items. Editing
works great. Insert works great however I need to display the form once the
user has entered the information and clicked Add.

1) Trying to get the record ID of the inserted record. ReturnValue doesnt
appear to work properly so i'm using an OUTPUT value instead. I get Null
reference errors when working with RETURN SCOPE_IDENTITY()

2) Getting "Procedure or function cp_InsertPublication has too many
arguments specified" error when trying to insert. See code below. All
parameters match up fine.

3) I've tried the following as well, and recieve the same errors:

Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles
SqlDataSource1.Inserting
Dim para As New System.Data.SqlClient.SqlParameter("returnValue",
TypeCode.Int32, 4, Data.ParameterDirection.ReturnValue)
e.Command.Parameters.Add(para)

End Sub


Thanks!!

David Lozzi


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SMALLConnectionString %>"
DeleteCommand="DELETE FROM [tblPublications] WHERE [ID] = @ID"
InsertCommand="cp_InsertPublication"
InsertCommandType="StoredProcedure"
SelectCommand="SELECT * FROM [tblPublications] WHERE ([ID] =
@ID)"
UpdateCommand="UPDATE [tblPublications] SET [strName] =
@strName, [dtDate] = @dtDate, [strPDF] = @strPDF, [intPages] = @intPages,
[strWrittenBy] = @strWrittenBy, [intType] = @intType, [dtModified] = {fn
Now()} WHERE [ID] = @ID">
<DeleteParameters>
<asparameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="strName" Type="String" />
<asparameter Name="dtDate" Type="DateTime" />
<asparameter Name="intPages" Type="Int32" />
<asparameter Name="strWrittenBy" Type="String" />
<asparameter Name="dtAdded" Type="DateTime" />
<asparameter Name="dtModified" Type="DateTime" />
<asparameter Name="intType" Type="int32" />
<asp:ControlParameter Name="strPDF"
ControlID="dvDetails$Label5" PropertyName="Text" />
<asparameter Name="ID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="PID"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asparameter Name="strName" Type="String" />
<asparameter Name="dtDate" Type="DateTime" />
<asparameter Name="intPages" Type="Int32" />
<asparameter Name="strWrittenBy" Type="String" />
<asparameter Name="intType" Type="int32" />
<asparameter Name="returnValue" Type="int32"
Direction="output" />
</InsertParameters>
</asp:SqlDataSource>



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[cp_InsertPublication]
@strName as varchar(500),
@dtDate as datetime,
@intPages as int,
@strWrittenBy as varchar(50),
@intType as int,
@returnValue as int OUTPUT
AS

INSERT INTO [tblPublications]
([strName], [dtDate], [intPages], [strWrittenBy], [intType], [dtAdded],
[dtModified])
VALUES
(@strName, @dtDate, @intPages, @strWrittenBy, @intType, {fn Now()}, {fn
Now()})

SET @returnValue = SCOPE_IDENTITY()

 

[Light]

Hi, David,

After posting my post today (Re: Problem with the Legacy ASP files and the
Sql Server Express) and then I saw this post by you, I realize we are
actually haiving the same problem, so do you have any resolution for it yet?

Howdy,

ASP.Net 2.0 using VB on SQL 2005

This is a two fold issue.

I have a DetailsView control which users can insert or edit items. Editing
works great. Insert works great however I need to display the form once
the
user has entered the information and clicked Add.

1) Trying to get the record ID of the inserted record. ReturnValue doesnt
appear to work properly so i'm using an OUTPUT value instead. I get Null
reference errors when working with RETURN SCOPE_IDENTITY()

2) Getting "Procedure or function cp_InsertPublication has too many
arguments specified" error when trying to insert. See code below. All
parameters match up fine.

3) I've tried the following as well, and recieve the same errors:

Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles
SqlDataSource1.Inserting
Dim para As New System.Data.SqlClient.SqlParameter("returnValue",
TypeCode.Int32, 4, Data.ParameterDirection.ReturnValue)
e.Command.Parameters.Add(para)

End Sub


Thanks!!

David Lozzi


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SMALLConnectionString %>"
DeleteCommand="DELETE FROM [tblPublications] WHERE [ID] = @ID"
InsertCommand="cp_InsertPublication"
InsertCommandType="StoredProcedure"
SelectCommand="SELECT * FROM [tblPublications] WHERE ([ID] =
@ID)"
UpdateCommand="UPDATE [tblPublications] SET [strName] =
@strName, [dtDate] = @dtDate, [strPDF] = @strPDF, [intPages] = @intPages,
[strWrittenBy] = @strWrittenBy, [intType] = @intType, [dtModified] = {fn
Now()} WHERE [ID] = @ID">
<DeleteParameters>
<asparameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="strName" Type="String" />
<asparameter Name="dtDate" Type="DateTime" />
<asparameter Name="intPages" Type="Int32" />
<asparameter Name="strWrittenBy" Type="String" />
<asparameter Name="dtAdded" Type="DateTime" />
<asparameter Name="dtModified" Type="DateTime" />
<asparameter Name="intType" Type="int32" />
<asp:ControlParameter Name="strPDF"
ControlID="dvDetails$Label5" PropertyName="Text" />
<asparameter Name="ID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="PID"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asparameter Name="strName" Type="String" />
<asparameter Name="dtDate" Type="DateTime" />
<asparameter Name="intPages" Type="Int32" />
<asparameter Name="strWrittenBy" Type="String" />
<asparameter Name="intType" Type="int32" />
<asparameter Name="returnValue" Type="int32"
Direction="output" />
</InsertParameters>
</asp:SqlDataSource>



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[cp_InsertPublication]
@strName as varchar(500),
@dtDate as datetime,
@intPages as int,
@strWrittenBy as varchar(50),
@intType as int,
@returnValue as int OUTPUT
AS

INSERT INTO [tblPublications]
([strName], [dtDate], [intPages], [strWrittenBy], [intType], [dtAdded],
[dtModified])
VALUES
(@strName, @dtDate, @intPages, @strWrittenBy, @intType, {fn Now()}, {fn
Now()})

SET @returnValue = SCOPE_IDENTITY()