How to reset running number ID when the new day come

Question

0.00/5 (No votes)

See more:

i want to print label with label_id
label id format contain running number which is 0001 (0001,0002,..) -i want to reset the running number to 0001 when the new day come.
can someone give me some ideas or solution regarding my problem?

currently, my system reset the running number if new day come but the problem is the running number keep repeating 00001 again and again

What I have tried:

Public Function get_ExistingSequenceNoOld(ByVal PN As String, ByVal q As String, ByVal vID As String)
       On Error GoTo ErrorHandler
       Dim dateL As Date
       Dim date1 As String
       Dim lastRecord As String
       Dim lastDate As String
       dateL = cls_PICS.get_SERVER_Datetime
       date1 = dateL.ToString("yyyy-MM-dd")


        PrinterDateTime DESC"

       Dim SQL_str As String =
        "SELECT * " &
           "FROM setup ORDER BY PrinterDateTime DESC"

       '"SELECT * " &
       '"FROM setup where PartNo='" & PN & "' AND MOQ='" & q & "' AND VendorID='" & vID & "' AND Status <> 'INACTIVE' ORDER BY PrinterDateTime DESC"

       Dim adLWS As New OdbcDataAdapter(SQL_str, cnPICS)
       Dim dsLWS As New DataSet
       adLWS.Fill(dsLWS, "BOXID")

       LWS_CONT = dsLWS.Tables("BOXID").Rows.Count
       ' lastRecord = dsLWS.Tables("BOXID").Rows.Count - 1
       'lastDate = dsLWS.Tables("BOXID").Rows.Item(lastRecord).Item("LastDate")

       If LWS_CONT < 1 Then
           get_ExistingSequenceNoOld = False
           'get_cstTBL_Insert(val)
           Exit Function
       End If

       ReDim PartNo(LWS_CONT)
       ReDim BoxEnd(LWS_CONT)
       ReDim DateEnd(LWS_CONT)
       ReDim PrintQty(LWS_CONT)
       ReDim MOQ(LWS_CONT)
       ReDim venID(LWS_CONT)
       ReDim DeliveryQty(LWS_CONT)
       ReDim qcInspection(LWS_CONT)

       Dim i As Integer
       For i = 0 To LWS_CONT - 1
           PartNo(i) = dsLWS.Tables("BOXID").Rows(i)("PartNo").ToString
           MOQ(i) = dsLWS.Tables("BOXID").Rows(i)("MOQ").ToString
           BoxEnd(i) = dsLWS.Tables("BOXID").Rows(i)("EndBox").ToString
           PrintQty(i) = dsLWS.Tables("BOXID").Rows(i)("PrintQty").ToString
           venID(i) = dsLWS.Tables("BOXID").Rows(i)("VendorID").ToString
           DeliveryQty(i) = dsLWS.Tables("BOXID").Rows(i)("DeliveryQty").ToString
           qcInspection(i) = dsLWS.Tables("BOXID").Rows(i)("QC").ToString
           BoxEnd(i) = dsLWS.Tables("BOXID").Rows(i)("EndBox").ToString
           DateEnd(i) = dsLWS.Tables("BOXID").Rows(i)("LastDate").ToString
       Next

       If Today.Date > CDate(DateEnd(i)).Date Then
           get_ExistingSequenceNoOld = False

       Else
           get_ExistingSequenceNoOld = True
       End If



       Exit Function

Posted 4-Jul-17 18:41pm

Member 13293441

Updated 4-Jul-17 20:24pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-07-04T19:59:00

Start by adding two columns to your Table:
The first is an INT IDENTITY column called Ident, autoincrementing by one
The second is a DATE column called InsDat which defaults to GETDATE()
Then when you want the rows with a daily sequence use this:

SQL

SELECT m.Ident, m.InsDat, m.OtherColumnList, m.Ident - s.MinSeq + 1 AS Seq FROM MyTable m
JOIN (SELECT Insdat, MIN(Ident) AS MinSeq FROM MyTable GROUP BY InsDat) s ON s.InsDat = m.InsDat

What you get is an integer column which starts at 1 each day - you can format that to a leading zered string if you need to, or let your presentation layer handle that.

Ident	InsDat	Val	       Seq
1	2017-07-05	A         	1
2	2017-07-05	A         	2
3	2017-07-05	A         	3
4	2017-07-05	A         	4
5	2017-07-05	A         	5
6	2017-07-06	B         	1
7	2017-07-06	B         	2
8	2017-07-06	B         	3
9	2017-07-06	B        	4
10	2017-07-06	B         	5
11	2017-07-06	B         	6
12	2017-07-07	C         	1
13	2017-07-07	C         	2
14	2017-07-07	C         	3
15	2017-07-07	C         	4
16	2017-07-08	D         	1