locked
How To Get Date In Sequeences RRS feed

  • Question

  • User-807418713 posted

    Hello

    Assume I have Table1 in which i have one field In Textbox1 i will give you 3 and assume todays date is 23-Dec-2019

    Now in gridview i want to show

    J20191223001

    J20191223002

    J20191223003

    On click it will save database.. that will do later again i have in textbox1 value :4

    now i want 

    J20191223004

    J20191223005

    J20191223006

    J20191223007

    Next Date is 24-Dec-2019 now i have enter Textbox1 is 2

    now i want like this again

    J20191224001

    J20191224002

    How to do so 

    Thanking You

    Monday, December 23, 2019 10:29 AM

Answers

  • User665608656 posted

    Hi Gopi.MCA,

    According to your description, I need to confirm a few questions with you.

    First, does your Textbox1 only allow numbers, and does it have a range limit?

    Second, is the content displayed in gridview based on the largest data record corresponding to the current date in the database?

    For example, when you enter 3, should you first go to the database to get the maximum value of the current date, and increase the number of corresponding input values ​​up according to the maximum value. (You enter 3, the same date records in database max value is 2 ,then you will show 3,4,5, right?)

    Finally, do you save the current data only when you click the save button?

    Based on the above issues, I set a limit on Textbox1 in the range of 1-99 and added validation using the server validation control.

    Then I created a table named DateRecord in database to store the data every time I click the save button.

    My idea is to trigger the OnTextChanged event of Textbox1 , first get the maximum number of the DateRecord table in database that coincides with the current date, decide which data to display from, display the data in GridView through a loop, and save the data of the current page with session.

    Then, by saving the click event of the button, the current data saved in the session is obtained and stored in the database.

    Here is my detailed code, which you can refer to:

    DateRecord table:

    CREATE TABLE [dbo].[DateRecord] (
        [Id]     INT            IDENTITY (1, 1) NOT NULL,
        [Number] NVARCHAR (MAX) NOT NULL,
        CONSTRAINT [PK_DateRecord] PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    Add this setting in your web.config:

     <appSettings>
        <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
      </appSettings>

    aspx code :

      <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
                <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="true" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1"
                    ControlToValidate="TextBox1" runat="server"
                    ErrorMessage="Only Numbers between 1-99 allowed"
                    ValidationExpression="^(0?[1-9]|[1-9][0-9])$">
                </asp:RegularExpressionValidator>
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
                <asp:Button ID="Button1" runat="server" Text="Save"  OnClick="Button1_Click"/>

    aspx.cs code:

            public string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
            }
    
            protected void TextBox1_TextChanged(object sender, EventArgs e)
            {
                Page.Validate();
                if (!Page.IsValid)
                    return;
                
    string date = DateTime.Today.ToString("yyyyMMdd"); DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("select top 1 * from DateRecord where Number like '%" + date + "%' order by Number desc")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; sda.SelectCommand = cmd; sda.Fill(dt); } } } int startNumber = 1; if (dt.Rows.Count > 0) { startNumber = Convert.ToInt32(dt.Rows[0]["Number"].ToString().Substring(9, dt.Rows[0]["Number"].ToString().Length - 9)) + 1; } DataTable dtGridView = new DataTable(); dtGridView.Columns.Add("Records"); for (int i = startNumber; i < startNumber + Convert.ToInt32(TextBox1.Text); i++) { DataRow row = dtGridView.NewRow(); int length = i.ToString().Length; string zero = string.Empty; switch (length) { case 1: zero = "00"; break; case 2: zero = "0"; break; default: break; } row["Records"] = "J" + date + zero + i.ToString(); dtGridView.Rows.Add(row); } GridView1.DataSource = dtGridView; GridView1.DataBind(); Session["NewRecords"] = dtGridView; } protected void Button1_Click(object sender, EventArgs e) { DataTable dt = (DataTable)Session["NewRecords"]; if (dt != null) { try { foreach (DataRow item in dt.Rows) { using (SqlConnection connection = new SqlConnection(constr)) { connection.Open(); string sql = "INSERT INTO DateRecord(Number) VALUES(@Number)"; using (SqlCommand cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddWithValue("@Number", item["Records"]); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } } Response.Write("<script>alert('Save success!');</script>"); } catch { Response.Write("<script>alert('Save fail!');</script>"); } } }

    Here is the result of this work demo:

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 24, 2019 6:44 AM

All replies

  • User475983607 posted

    You've asked this question many many times.  My question to you us why do you ignore the solution when the solution is so very simple.  All you have to do is create separate columns to hold the "J", the date, and the sequence.  Once you create a proper design, again this has been explained many many times, all you have left is a basic "if" condition. If today;s date does not exist then then the sequence is 001 otherwise increment the sequence column.  Very Very simple...  Please at least try the solution.

    Monday, December 23, 2019 11:26 AM
  • User665608656 posted

    Hi Gopi.MCA,

    According to your description, I need to confirm a few questions with you.

    First, does your Textbox1 only allow numbers, and does it have a range limit?

    Second, is the content displayed in gridview based on the largest data record corresponding to the current date in the database?

    For example, when you enter 3, should you first go to the database to get the maximum value of the current date, and increase the number of corresponding input values ​​up according to the maximum value. (You enter 3, the same date records in database max value is 2 ,then you will show 3,4,5, right?)

    Finally, do you save the current data only when you click the save button?

    Based on the above issues, I set a limit on Textbox1 in the range of 1-99 and added validation using the server validation control.

    Then I created a table named DateRecord in database to store the data every time I click the save button.

    My idea is to trigger the OnTextChanged event of Textbox1 , first get the maximum number of the DateRecord table in database that coincides with the current date, decide which data to display from, display the data in GridView through a loop, and save the data of the current page with session.

    Then, by saving the click event of the button, the current data saved in the session is obtained and stored in the database.

    Here is my detailed code, which you can refer to:

    DateRecord table:

    CREATE TABLE [dbo].[DateRecord] (
        [Id]     INT            IDENTITY (1, 1) NOT NULL,
        [Number] NVARCHAR (MAX) NOT NULL,
        CONSTRAINT [PK_DateRecord] PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    Add this setting in your web.config:

     <appSettings>
        <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
      </appSettings>

    aspx code :

      <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
                <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="true" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1"
                    ControlToValidate="TextBox1" runat="server"
                    ErrorMessage="Only Numbers between 1-99 allowed"
                    ValidationExpression="^(0?[1-9]|[1-9][0-9])$">
                </asp:RegularExpressionValidator>
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
                <asp:Button ID="Button1" runat="server" Text="Save"  OnClick="Button1_Click"/>

    aspx.cs code:

            public string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
            }
    
            protected void TextBox1_TextChanged(object sender, EventArgs e)
            {
                Page.Validate();
                if (!Page.IsValid)
                    return;
                
    string date = DateTime.Today.ToString("yyyyMMdd"); DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("select top 1 * from DateRecord where Number like '%" + date + "%' order by Number desc")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; sda.SelectCommand = cmd; sda.Fill(dt); } } } int startNumber = 1; if (dt.Rows.Count > 0) { startNumber = Convert.ToInt32(dt.Rows[0]["Number"].ToString().Substring(9, dt.Rows[0]["Number"].ToString().Length - 9)) + 1; } DataTable dtGridView = new DataTable(); dtGridView.Columns.Add("Records"); for (int i = startNumber; i < startNumber + Convert.ToInt32(TextBox1.Text); i++) { DataRow row = dtGridView.NewRow(); int length = i.ToString().Length; string zero = string.Empty; switch (length) { case 1: zero = "00"; break; case 2: zero = "0"; break; default: break; } row["Records"] = "J" + date + zero + i.ToString(); dtGridView.Rows.Add(row); } GridView1.DataSource = dtGridView; GridView1.DataBind(); Session["NewRecords"] = dtGridView; } protected void Button1_Click(object sender, EventArgs e) { DataTable dt = (DataTable)Session["NewRecords"]; if (dt != null) { try { foreach (DataRow item in dt.Rows) { using (SqlConnection connection = new SqlConnection(constr)) { connection.Open(); string sql = "INSERT INTO DateRecord(Number) VALUES(@Number)"; using (SqlCommand cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddWithValue("@Number", item["Records"]); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } } Response.Write("<script>alert('Save success!');</script>"); } catch { Response.Write("<script>alert('Save fail!');</script>"); } } }

    Here is the result of this work demo:

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 24, 2019 6:44 AM