在插入语句后从SQL数据库中检索自动增量ID

debugcn 发表于 Dev

java404

下面是我在向数据库中插入数据后检索自动增量ID的代码。

但是，在将数据插入数据库之前，我得到了自动增量ID。

插入数据库后如何获得自动增量ID？

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

//检索ID方法

   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();
            }
          }
        }

//请求按钮方法

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

java404

通过引用@ Mx.Wolf的示例答案，我做了一些修改以获得正确的答案，下面是正在运行的代码：

     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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。