Retrieve Auto Increment ID from SQL Database after Insert Statement

Question

Below here is my code to Retrieve Auto Increment ID After Inserting data into database.

However, I am getting Auto Increment ID before Inserting data into database.

How can I get auto increment ID after insert into database?

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {           
            RetrievePRReqID();
        }
    }

//Retrieve ID method

   private void RetrievePRReqID()
     {
        try
        {
            string query = "Select IDENT_CURRENT('tblPRRequest')";
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();

            }
            SqlCommand cmd = new SqlCommand(query, sqlCon);
            SqlDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                int value = int.Parse(reader[0].ToString()) ;
                txt_PRNO.Text = value.ToString();
            }
        }
        catch(Exception)
        {
            throw;
        }
        finally
        {
            if(con.State == ConnectionState.Open)
            {
                con.Close();
            }
          }
        }

//Request button Method

     protected void btn_Request(object sender, EventArgs e)
    {
        string insertCmd = "INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName) " +
            "VALUES (@RequestTo,@RequestFrom,@RequestedByName)";

        using (SqlConnection conn = new SqlConnection(cs))
        {
            conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(insertCmd, conn))
            {
                sqlcmd.Parameters.Clear();
                SqlCommand sqlCmd = new SqlCommand(insertCmd, sqlCon);
                sqlcmd.Parameters.AddWithValue("@RequestTo", lblPurchasingDept.Text);
                sqlcmd.Parameters.AddWithValue("@RequestFrom", ddlDept.SelectedItem.Text);
                sqlcmd.Parameters.AddWithValue("@RequestedByName", SUserName.Text);                   
                sqlcmd.ExecuteNonQuery();
            }
        }
        ***//After Insert into the table, I want to retrieve latest generated Auto Increment ID in here.***
      }

score 3 · Accepted Answer · answered Mar 03 '20 at 01:44

By referring sample answer from @Mx.Wolf, I modified a bit to get the right answer, below here is the codes that is working :

     protected void btn_Request(object sender, EventArgs e)
    {
        object id ;
        string insertCmd = "INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName) " +
            "output inserted.PRReqID " +
            "VALUES (@RequestTo,@RequestFrom,@RequestedByName)";

        using (SqlConnection conn = new SqlConnection(cs))
        {
            conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(insertCmd, conn))
            {
                sqlcmd.Parameters.AddWithValue("@RequestTo", lblPurchasingDept.Text);
                sqlcmd.Parameters.AddWithValue("@RequestFrom", ddlDept.SelectedItem.Text);
                sqlcmd.Parameters.AddWithValue("@RequestedByName", SUserName.Text);

                id = sqlcmd.ExecuteScalar(); //the result is of Object type, cast it safely
            }
        }

        Debug.WriteLine(id.ToString()); // Access it like this

Mx.Wolf · Answer 2 · 2022-01-27T15:26:13.170

As stated in SQL Server documentation https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

You have to change your SQL statement

INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName)  
OUTPUT inserted.ID
-------^^^^^^^^_^^
VALUES (@RequestTo,@RequestFrom,@RequestedByName)

and now you can use ExecuteScalar to get the inserted value

 protected void btn_Request(object sender, EventArgs e)
 { 
    int id= 0;
    string insertCmd = "INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName) " +
        "output inserted.ID" +
        "VALUES (@RequestTo,@RequestFrom,@RequestedByName)";

    using (SqlConnection conn = new SqlConnection(cs))
    {
        conn.Open();
        using (SqlCommand sqlcmd = new SqlCommand(insertCmd, conn))
        {
            sqlcmd.Parameters.AddWithValue("@RequestTo", lblPurchasingDept.Text);
            sqlcmd.Parameters.AddWithValue("@RequestFrom", ddlDept.SelectedItem.Text);
            sqlcmd.Parameters.AddWithValue("@RequestedByName", SUserName.Text);
                            
            id = (int)sqlcmd.ExecuteScalar(); //the result is of Object type, cast it safely
        }
    }
    
    Debug.WriteLine(id.ToString()); // Access it like this
  }

Please change "... OUTOUT inserted.ID ..." to "... OUTPUT inserted.ID ...". — Márcio Antônio Slivak, Jan 27 '22 at 12:23

Athanasios Kataras · Answer 3 · 2020-03-02T07:56:40.080

Try this:

     protected void btn_Request(object sender, EventArgs e)
    {
        string insertCmd = "INSERT INTO tblPRRequest (RequestTo,RequestFrom,RequestedByName) " +
            "VALUES (@RequestTo,@RequestFrom,@RequestedByName)";

        using (SqlConnection conn = new SqlConnection(cs))
        {
            conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(insertCmd, conn))
            {
                sqlcmd.Parameters.Clear();
                SqlCommand sqlCmd = new SqlCommand(insertCmd, sqlCon);
                sqlcmd.Parameters.AddWithValue("@RequestTo", lblPurchasingDept.Text);
                sqlcmd.Parameters.AddWithValue("@RequestFrom", ddlDept.SelectedItem.Text);
                sqlcmd.Parameters.AddWithValue("@RequestedByName", SUserName.Text);
                sqlcmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;                
                sqlcmd.ExecuteNonQuery();
            }
        }
        ***//After Insert into the table, I want to retrieve latest generated Auto Increment ID in here.***
        sqlcmd.Parameters["@ID"].value; // Access it like this
      }

In case you can chage the ExecuteNonQuery to ExecuteScalar, then it would be even easier: What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

Retrieve Auto Increment ID from SQL Database after Insert Statement

3 Answers3