Re: Returning SCOPE_IDENTITY from SQLDataSource and DetailsView

Tech-Archive recommends: Speed Up your PC by fixing your registry

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?


"David Lozzi" <dlozzi@xxxxxxxxxxxxx> wrote in message
news:2F2A62CA-230C-4343-BB24-F640C5BD76AD@xxxxxxxxxxxxxxxx
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>
<asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="strName" Type="String" />
<asp:Parameter Name="dtDate" Type="DateTime" />
<asp:Parameter Name="intPages" Type="Int32" />
<asp:Parameter Name="strWrittenBy" Type="String" />
<asp:Parameter Name="dtAdded" Type="DateTime" />
<asp:Parameter Name="dtModified" Type="DateTime" />
<asp:Parameter Name="intType" Type="int32" />
<asp:ControlParameter Name="strPDF"
ControlID="dvDetails$Label5" PropertyName="Text" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="PID"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="strName" Type="String" />
<asp:Parameter Name="dtDate" Type="DateTime" />
<asp:Parameter Name="intPages" Type="Int32" />
<asp:Parameter Name="strWrittenBy" Type="String" />
<asp:Parameter Name="intType" Type="int32" />
<asp:Parameter 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()



.

Relevant Pages

  • Returning SCOPE_IDENTITY from SQLDataSource and DetailsView
    ... Protected Sub SqlDataSource1_InsertingHandles SqlDataSource1.Inserting ... @intPages as int, ... @returnValue as int OUTPUT ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.access.queries)
  • Re: using OpenXML in T-sql?
    ... Columnist, SQL Server Professional ... The author makes a reference to using comma delimited strings and opts for the xml string instead. ... @ID1 INT, @ID2 INT, @ID3 INT, @ID4 INT AS ... ... you're betting you'll never have to specify more values than the number of input parameters you create. ...
    (microsoft.public.sqlserver.programming)
  • Re: 17805 Starting up Java App server, with SQL Server 2000 Backend
    ... network issue although you would expect a network issue to be more random. ... declare @P1 int ... On another machine running SQL Server 2000 Standard on Win2K advanced, ...
    (microsoft.public.sqlserver.clients)