How to get last inserted ID to update the SAME row in a table?

Question

How do I bring across the id so that on the second page, it will update on the same row, I keep getting errors, such as scalar variable, etc. Any help? So, this is what I have on the first page.

command.CommandText = "INSERT INTO [User] (Sex,Name,DOB) VALUES(@Sex,@Name,@DOB); SELECT SCOPE_IDENTITY()";         

        command.Parameters.AddWithValue("@Sex", Sex);
        command.Parameters.AddWithValue("@Name", Name);
        command.Parameters.AddWithValue("@DOB", DOB);

        try
        {
            Con.Open();
            int userID = (int)command.ExecuteScalar();
            command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            Con.Close();
        }

this is on the second page

 c.CommandText = "UPDATE [User] SET Ans1 = @Ans1 WHERE Id = @id";
 c.Parameters.AddWithValue("@Ans1", checkboxSelection);
 c.Parameters.AddWithValue("@id", userID);

        try
        {
            Con.Open();
            c.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            Con.Close(); 
        }

This is what I have now [STILL NOT WORKING]

First page

command.CommandText = "INSERT INTO [User] (Sex,Name,DOB) VALUES(@Sex,@Name,@DOB); SELECT SCOPE_IDENTITY()";



        command.Parameters.AddWithValue("@Sex", Sex);
        command.Parameters.AddWithValue("@Name", Name);
        command.Parameters.AddWithValue("@DOB", DOB);





        try
        {
            Con.Open();
            int userID = Convert.ToInt32(command.ExecuteScalar());
            Session["LastID"] = userID;
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {

            Con.Close();
        }

Second page

int userID = Convert.ToInt32(Session["LastID"]);

        c.CommandText = "UPDATE [User] SET Ans1 = @Ans1 WHERE Id = @id";

        c.Parameters.AddWithValue("@Ans1", checkboxSelection);
        c.Parameters.AddWithValue("@id", userID);

        try
        {
            Con.Open();
            c.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            Con.Close();
        }

Answer 1

On your insert page, before the end of the try block:

 Response.Redirect("SecondPage.aspx?id=" + userID.ToString());  

And on your update page:

int userID;
if(int.TryParse(Request.QueryString["id"], userID)){
    c.CommandText = "UPDATE [User] SET Ans1 = @Ans1 WHERE Id = @id";

    c.Parameters.AddWithValue("@Ans1", checkboxSelection);
    c.Parameters.AddWithValue("@id", userID);

    try
    {
        Con.Open();
        c.ExecuteNonQuery();

    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        Con.Close(); 
    }
}

Answer 2

ExecuteScalar returns an object, if you try to cast (int) you get an Invalid Cast Exception.
You need to:

    try
    {
        Con.Open();
        int userID = Convert.ToInt32(command.ExecuteScalar());
    }

and there is no need to call ExecuteNonQuery for the same command
(this will insert two records unless there is some key violation)

Then, you need to pass the userID to the second page.
This could achieved storing the userID in a Session variable

   Session["LastID"] = userID;

and retrieve it in the code that update the record

  int userID = Convert.ToInt32(Session["LastID"]);

or (as indicated in the other answer) passing it on the Redirect to the second page.

Answer 3

First of all, you need to declare userID outside the try clause. Second, the line int userID = (int)command.ExecuteScalar(); don't return an int because the command is an insert, not a select. You should rather:

    int userID = null;
    try
    {
        Con.Open();
        command.ExecuteNonQuery();

        command.CommandText = "Select Top 1 `ID` From [User] Order by `ID` desc";

        userID = (int)command.ExecuteScalar();


    }