[SOLVED] Retrieve Auto Increment ID from SQL Database after Insert Statement

Issue

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.***
      }

Solution

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

Answered By – java404

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *