locked
Auto Incriment textbox value RRS feed

  • Question

  • User1717218719 posted

    Hi All

    I am looking to Auto Incriment my textbox value. I have an SQL database which stores TktID which is an integer. at the moment I am typing a value into the textbox an it fills the TktID column in the database with that textbox value.

    I am using an insert statement to store TktID  in the database. then I just used the following parameter method

    .Parameters.AddWithValue("@TktID", Me.txtSave.Text).

    To get the last TktID number in the database I have wrote the following code:

    SELECT TOP 1 WITH TIES * FROM tbl ORDER BY TktID DESC

    I am unsure as to what to do next

    I would like to have it that if the vaule in the textbox is blank then get the last TktID value and add 1. eg previous TktID = 404 so New Textbox value = 405. I am unsure how to code for this scenario. Any help would be great.

    Friday, July 19, 2019 10:30 AM

Answers

  • User3690988 posted

    Why not set the TktID column in your table as IDENTITY and have SQL Server take care of it?

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 19, 2019 4:19 PM
  • User288213138 posted

    Hi E.RU,

    According to your description, I made a demo for your reference.

    To judge whether the value in the Textbox is empty, if not, insert the value of the Textbox.

    If it is empty, then find the latest TktID, and increment the value of the TextBox(TktID) by 1.

    And shouldn't Auto Incriment value be added automatically, why do you have to set it manually?

    The code:
    <div>
                Auto Incriment Value:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
                last TktID:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            
            </div>
    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    Bind();
                }
    
            }
            public void Bind()
            {
                string query = "select * from Test25";
                GridView1.DataSource = SqlHelper.ExecuteDataTable(query);
                GridView1.DataBind();
            }
    
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrWhiteSpace(TextBox1.Text))
                {
                    string query = "SELECT TOP 1 * FROM Test25 ORDER BY TktID DESC ";
                    string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand(query))
                        {                  
                            cmd.Connection = con;
                            con.Open();
                            TextBox2.Text=cmd.ExecuteScalar().ToString();
                            TextBox1.Text = (Convert.ToInt32(TextBox2.Text) + 1).ToString();
                            con.Close();
                        }
                    }
                }
                else
                {
                    string TktID = TextBox1.Text;
                    TextBox1.Text = "";
                    string query = "INSERT INTO Test25 VALUES(@TktID)";
                    string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand(query))
                        {
                            cmd.Parameters.AddWithValue("@TktID", TktID);
                            cmd.Connection = con;
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
                    }
                    Bind();
                }
                
    
            }
    

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 9:32 AM

All replies

  • User3690988 posted

    Why not set the TktID column in your table as IDENTITY and have SQL Server take care of it?

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 19, 2019 4:19 PM
  • User288213138 posted

    Hi E.RU,

    According to your description, I made a demo for your reference.

    To judge whether the value in the Textbox is empty, if not, insert the value of the Textbox.

    If it is empty, then find the latest TktID, and increment the value of the TextBox(TktID) by 1.

    And shouldn't Auto Incriment value be added automatically, why do you have to set it manually?

    The code:
    <div>
                Auto Incriment Value:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
                last TktID:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            
            </div>
    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    Bind();
                }
    
            }
            public void Bind()
            {
                string query = "select * from Test25";
                GridView1.DataSource = SqlHelper.ExecuteDataTable(query);
                GridView1.DataBind();
            }
    
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrWhiteSpace(TextBox1.Text))
                {
                    string query = "SELECT TOP 1 * FROM Test25 ORDER BY TktID DESC ";
                    string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand(query))
                        {                  
                            cmd.Connection = con;
                            con.Open();
                            TextBox2.Text=cmd.ExecuteScalar().ToString();
                            TextBox1.Text = (Convert.ToInt32(TextBox2.Text) + 1).ToString();
                            con.Close();
                        }
                    }
                }
                else
                {
                    string TktID = TextBox1.Text;
                    TextBox1.Text = "";
                    string query = "INSERT INTO Test25 VALUES(@TktID)";
                    string constr = ConfigurationManager.ConnectionStrings["constr527"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand(query))
                        {
                            cmd.Parameters.AddWithValue("@TktID", TktID);
                            cmd.Connection = con;
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
                    }
                    Bind();
                }
                
    
            }
    

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 9:32 AM