1

I have a Webforms project and I have the information going into a database. My question is, after submitting the page, how can I retrieve and display the newly created ID (primary key) on the next page in a label? Is this even possible to do?

I have this code for retrieving the ID based of an old project I did:

SqlCommand da3 = new SqlCommand(strSelectCmd3, conn);
da3.CommandType = CommandType.StoredProcedure;

SqlDataAdapter ds3 = new SqlDataAdapter();
ds3.SelectCommand = da3;

DataSet dsPerson3 = new DataSet();
ds3.Fill(dsPerson3);

Response.Redirect("Submitted.aspx?ID=" + dsPerson3.Tables[0].Rows[0]["CremationID"].ToString());
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthony J
  • 53
  • 5
  • You can only retrieve by date if the table has a date field. Then use OrderBy Descending Date and take first item. – jdweng Aug 31 '18 at 17:14
  • Are you passing in raw SQL to create the new item yourself, or are you using a stored procedure? – Brisbe Aug 31 '18 at 17:15
  • 2
    You absolutely need to return the ID in the method that creates the new row, no other method will work 100%. If you do it any other way, you may get a row that was inserted by another user at the same time. – DavidG Aug 31 '18 at 17:17
  • @Brisbe42 I am using a stored procedure, even if i get the procedure to pull the most recent Id i'll have a hard time passing this to the next page. – Anthony J Aug 31 '18 at 17:17
  • @DavidG & jdweng That was my issue with ordering by date. I have the same test submissions going in so it always pulls all records with same date. Maybe I should change from date to Datetime? – Anthony J Aug 31 '18 at 17:19
  • 1
    No! Use the primary key you have defined for the table to identify the row. And if you don't have a PK, make one now. There's no excuse for not having them on all of your tables. When you have that, return the PK when creating the row (for example https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) and pass that to the next page. – DavidG Aug 31 '18 at 17:21
  • 1
    Thank you @DavidG and Brisbe42! I got it working now finally! – Anthony J Aug 31 '18 at 17:55

2 Answers2

4

In your insert SQL stored procedure, either SELECT the SCOPE_IDENTITY() directly, or set an output variable to that. This will give you the PK for this table.

You can then use that id as a part of the query string for the next page. This will allow you to load that page without needing to make a separate call to the database, and you will be guaranteed to have the correct id.

Trying to figure out the last id without using something scope-specific like this (such as SCOPE_IDENTITY), will have the issue that you might receive an Id inserted by another process / user on the page, rather than the one just handled.

Brisbe
  • 1,588
  • 2
  • 20
  • 42
1

I added this statement inside of my stored procedure:

SELECT TOP (1) CremationID, Last, First
FROM dbo.Cremation
WHERE (Last = @Last) AND (First = @First)
ORDER BY CremationID DESC

And then on the new pages inserted a label and put this code inside of the Page_Load

CID.Text = Request.QueryString["ID"].ToString();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthony J
  • 53
  • 5