How To Get Date In Sequeences [Answered]RSS

• • Reply
  Gopi.MCA

  Member

  202 Points

  477 Posts

  How To Get Date In Sequeences

  Dec 23, 2019 10:29 AM|Gopi.MCA|LINK

  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

• • Reply
  mgebhard

  All-Star

  44401 Points

  19191 Posts

  Re: How To Get Date In Sequeences

  Dec 23, 2019 11:26 AM|mgebhard|LINK

  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.

• • Reply
  Yongqing Yu

  Contributor

  3130 Points

  913 Posts

  Re: How To Get Date In Sequeences

  Dec 24, 2019 06:44 AM|Yongqing Yu|LINK

  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.

  MSDN Community Support
  
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.