Member Avatar for lycoslocos

I had a form that requiring a Job Order Code, "the Job Order Code is just like number of transaction". We had 12 months in 1 year. I need to reset the starting Job Order Code in every month. For Example:

January: In month of January I had 10 transaction, I started to number 1 up to the last transaction which is number 10. Notice the following month.

February: In month of February I had 8 transaction, I started to number 1 up to the last transaction which is number 8.

I had to reset the Job Order Code or number of transaction per month.

Every month must start to number 1 transaction, how can I do that? I know that you're knowledgeable and I know that you can help me. I am trying to solve this problem because on Monday will be the evaluation for the SYSTEM.

Thank you very much and more power!

  • 2 Contributors
  • 3 Replies
  • 183 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by choudhuryshouvi

Dani AI

Generated

A practical, reliable way to reset the Job Order Code each month is to make the database own the monthly counter instead of relying on a stored “start date” in the registry or on application logic. ’s idea to detect month changes is useful, but in a multi-user environment the registry or client-side checks are fragile and can produce duplicate numbers. Use a small table keyed by period (YYYYMM) that holds the last sequence number and increment it inside a single database transaction.

Example table and atomic operation (SQL Server-style):

CREATE TABLE PeriodSequence (
  Period CHAR(6) PRIMARY KEY,  -- e.g. 200802
  LastSeq INT NOT NULL
);

-- Get next sequence for @period (run inside a transaction)
BEGIN TRAN;
UPDATE PeriodSequence SET LastSeq = LastSeq + 1 WHERE Period = @period;
IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO PeriodSequence(Period, LastSeq) VALUES(@period, 1);
  SELECT 1 AS NextSeq;
END
ELSE
BEGIN
  SELECT LastSeq AS NextSeq FROM PeriodSequence WHERE Period = @period;
END
COMMIT TRAN;

Simple VB client-side pattern (pseudocode):

Dim period As String = Date.Now.ToString("yyyyMM")
' Execute parameterized SQL above with @period, read NextSeq
Dim jobOrderCode As String = period & "-" & NextSeq.ToString()

Notes and cautions: 1) Always use parameterized queries and keep the increment inside a DB transaction to avoid race conditions. 2) Use server time (or a single trusted time source) so month boundaries are consistent for all users. 3) Test edge cases (multiple concurrent inserts and transactions spanning midnight). 4) If you must support legacy storage, migrate the counter into the database before rollout. This approach is simple to test before Monday’s evaluation and scales safely for concurrent users.

Recommended Answers

All 3 Replies

Member Avatar for choudhuryshouvi

when a month is starting store the starting date. u can store the date in a database or in windows registry. it is better that u store it into the registry.

then while performing a transaction compare current system date with the stored one and find total month difference. if its 1 then one month is covered up then reset the transaction no. to 1 and start counting from it. store current transaction number also so that you can track it while creating the next no. if the month diff. is not equal to 1 then retrieve last transaction no. and increament it by 1 to get the new no.

i hope this logic can be implementable.
try this out and tell me if u need any further assistance.

good luck

regards
Shouvik

Member Avatar for lycoslocos

thank you very much. I will try the logic that you had formulated.

Member Avatar for choudhuryshouvi

you are welcome.
don't forget to tell me what have u got finally

i'll be waiting to hear from you.

regards
Shouvik

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.