Member Avatar for Jesi523
Jesi523 0 Junior Poster in Training

Hi, I have a question please I am using a DetailView to Insert into a SQL Server database. I then want to get the last identity id and use it at the end of url to redirect it to another page. I've done a lot of research and I think I have it right but it is not working...here's my code, please help...

asp.net code:

<asp:SqlDataSource ID="eventDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:RSVPApplicationConnectionString %>"
        InsertCommand="INSERT INTO Events(eventName, description, host, eventRegistrationClosed) VALUES (@eventName, @description, '{a53e98e4-0197-4513-be6d-49836e406aaa}', @eventRegistrationClosed); SET @NewID = Scope_Identity()"
        SelectCommand="SELECT eventAuid, eventName, description, host, isActive, eventRegistrationClosed 
                    FROM Events
                       WHERE eventAuid = @eventAuid">
        <InsertParameters>
            <asp:Parameter Name="eventName" />
            <asp:Parameter Name="description" />
            <asp:Parameter Name="host" />
            <asp:Parameter Name="eventRegistrationClosed" />
            <asp:Parameter Direction = "Output" Name="NewId" Type="Int16" />
        </InsertParameters>
        <SelectParameters>
            <asp:QueryStringParameter Name="eventAuid" QueryStringField="eventAuid" />
        </SelectParameters>
    </asp:SqlDataSource>

then my c# code:

protected void eventDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
        {
            
                string newId = e.Command.Parameters["@NewID"].Value.ToString();
                Response.Redirect("AddEventOccurrence.aspx?eventAuid=" + newId);
           
        }

Please please tell me what I am doing wrong. I do not understand. The insert works but it will not redirect it to other page. Thank you!!!!

  • 1 Contributor
  • 0 Replies
  • 92 Views

Dani AI

Generated

For — the insert-plus-redirect pattern usually fails for one of a few simple reasons: the SqlDataSource Inserted event handler is not actually hooked up, the output-parameter name used in SQL/markup/code does not match, the parameter type is too small for an identity, or the DetailsView/SqlDataSource is inside an UpdatePanel (partial postbacks need a client-side redirect). Quick checklist before code: verify OnInserted="eventDataSource_Inserted" on the SqlDataSource; use the exact same parameter name everywhere (e.g., NewID); use Int32 for identity output; and check DBNull before converting the value.

A robust pattern is to use a stored procedure that returns the new identity via an OUTPUT parameter (this avoids race conditions and keeps SQL tidy). Example stored-proc shape:

CREATE PROCEDURE dbo.InsertItem
  @Name NVARCHAR(200),
  @Flag BIT,
  @NewID INT OUTPUT
AS
BEGIN
  INSERT INTO dbo.YourTable (NameCol, FlagCol)
  VALUES (@Name, @Flag);

  SET @NewID = SCOPE_IDENTITY();
END

Use the SqlDataSource with InsertCommandType="StoredProcedure" and include an <asp:Parameter Name="NewID" Direction="Output" Type="Int32" /> in the <InsertParameters>; make sure the SqlDataSource has OnInserted="eventDataSource_Inserted" so your handler runs.

In the Inserted handler, guard against DBNull and then redirect. Using Response.Redirect(url, false) plus CompleteRequest() is a safe way to avoid ThreadAbortExceptions:

protected void eventDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    object val = e.Command.Parameters["@NewID"].Value;
    if (val != null && val != DBNull.Value)
    {
        int newId = (int)val;
        Response.Redirect("AddEventOccurrence.aspx?eventAuid=" + newId, false);
        Context.ApplicationInstance.CompleteRequest();
    }
}

If the DetailsView lives inside an UpdatePanel, use ScriptManager.RegisterStartupScript to issue a client-side window.location instead. For background on getting the identity and the SqlDataSource Inserted event, see the Microsoft docs on SCOPE_IDENTITY and the SqlDataSource.Inserted event.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.