Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
hello masters,
 
I want to ask the help of the master, I'd like an AutoNumber like this in the source code for the final lecture using vb.net or vb 6.0
 
text1. text = "0207110001"
 
02 = the introduction of a number of documents
07 = introduction of months
11 = the introduction of year
0001 = sorting number
 
so while still in the same month a sorting number will grow "1" and if the month increased "1" and the year also increased "1" then back to the beginning of sorting number = "0001"
 
Please help and enlightenment from the masters love my source code that has a connection to the database and there are checks to the database
Posted 11-Jul-11 17:52pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Assuming you have a table called ‘NextNumber’ where you store your incremental number. Assume your table has the following fields(columns) ("DocPrefix","YearPrefix","MonthPrefix","NextNo","mCounter")
you can try this code segments:

 

Public Function GetNextAutoNo() As String
Dim mstrDocPrefix, mstrMonthPrefix, mstrNextNo As String
Dim intYearPrefix, intmCounter, intNewmCounter As Integer
Dim cmdNextAutoNumber As OleDbCommand = gbConn.CreateCommand
Dim sqlGetNextAutoNo As String = "SELECT * FROM NextAutoNo"
Dim drDataReader As OleDbDataReader
'*********Initialise Variables
GetNextAutoNo = ""
'
gstrDocPrefix = "" : mstrDocPrefix = ""
gbstrMonthPrefix = "" : mstrMonthPrefix = ""
gbstrNextNo = "" : mstrNextNo = ""
gbintYearPrefix = 0 : intYearPrefix = 0
gbintCounter = 0 : intmCounter = 0
'Note: gstrDocPrefix,gbstrMonthPrefix,gbstrNextNo,gbIntYearPrefix, and gbintCounter are global variables declared outside this public function
Try
OpenDBConnection()
With cmdNextAutoNumber
.Connection = gbConn
.CommandText = sqlGetNextAutoNo
drDataReader = .ExecuteReader
If drDataReader.HasRows Then
While (drDataReader.Read())
mstrDocPrefix = IIf(Not IsDBNull(drDataReader.Item("DocPrefix").ToString), drDataReader.Item("DocPrefix").ToString, “01”)
intYearPrefix = IIf(Not IsDBNull(drDataReader.Item("YearPrefix").ToString), CInt(drDataReader.Item("YearPrefix").ToString), Year(Today))
mstrMonthPrefix = IIf(Not IsDBNull(drDataReader.Item("MonthPrefix").ToString), drDataReader.Item("MonthPrefix").ToString, Month(Today))
mstrNextNo = IIf(Not IsDBNull(drDataReader.Item("NextNo").ToString), drDataReader.Item("NextNo").ToString, "0001")
intmCounter = IIf(Not IsDBNull(drDataReader.Item("mCounter").ToString), CInt(drDataReader.Item("mCounter").ToString), 1)
End While
Else
mstrDocPrefix = "01"
intYearPrefix = Year(Today)
mstrMonthPrefix = Month(Today)
mstrNextNo = "0001"
intmCounter = 1
End If
End With
'
'intNewmCounter = intmCounter + 1
If Not (intYearPrefix = Year(Today)) Then
intYearPrefix = Year(Today)
intNewmCounter = 1
End If
'
mstrCurrentMonth = Month(Today)
mstrCurrentMonth = IIf(Len(mstrCurrentMonth) < 2, "0" & mstrCurrentMonth, mstrCurrentMonth)
If Not (mstrMonthPrefix.Trim = mstrCurrentMonth.Trim) Then
mstrMonthPrefix = mstrCurrentMonth
intNewmCounter = 1
End If
'
If (intNewmCounter = 1) Or (Len(mstrNextNo) < 4) Then
intmCounter = intNewmCounter
mstrNextNo = GenerateNextNumber(intNewmCounter)
End If
'
GetNextAutoNo = mstrDocPrefix.Trim & mstrMonthPrefix.Trim & intYearPrefix.ToString & mstrNextNo.Trim
 
intmCounter += 1
 
mstrNextNo = GenerateNextNumber(intmCounter)
'Transfer values to global variables
‘***the variables on the left are global variables declared outside this sub routine
gstrDocPrefix = mstrDocPrefix
gbstrMonthPrefix = mstrMonthPrefix
gbstrNextNo = mstrNextNo
gbintYearPrefix = intYearPrefix
gbintCounter = intmCounter
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Auto Number Error!")
Finally
CloseDBConnection()
End Try
Return GetNextAutoNo
End Function
 
Public Function GenerateNextNumber(ByVal NextCounter As Integer) As String
GenerateNextNumber = ""
Select Case NextCounter
Case 1 To 9
GenerateNextNumber = "000" & NextCounter
Case 10 To 99
GenerateNextNumber = "00" & NextCounter
Case 100 To 999
GenerateNextNumber = "0" & NextCounter
Case 1000 To 9999
GenerateNextNumber = NextCounter
End Select
Return GenerateNextNumber
End Function
 
In your form you can add this code:
 
Private Sub Button_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
Text1.Text=GetNextAutoNo
End Sub
 
I hope this will assist you.
 
Thanks
  Permalink  
Comments
ilham zamzami from jakarta at 17-Jul-11 22:54pm
   
thank you for your help
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The best way to do this is to store your sequence number in the DB, and then reset it every time the other numbers increase. The overall number would be built in code, or in the DB, you could write a proc to do it and increment the number as needed.
  Permalink  
Comments
ilham zamzami from jakarta at 11-Jul-11 23:00pm
   
can you give me example of code ??? I was ignorant to this problem
Christian Graus at 11-Jul-11 23:07pm
   
For your convenience, I've deleted where you asked this twice. You store the 'sorting number' in a table, by itself. You get the date using the DATEPART method. You pass in the number of documents, or get it from the DB. You increment the sorting number every time and reset it to 0 or 1 when the month or year changes ( which I guess means you want to store the date and year you're using in the table with the sorting number ). This is pretty easy stuff, and really you should try to do your own work and ask us for help, instead of asking us to do your job for you, without even trying.
ilham zamzami from jakarta at 11-Jul-11 23:51pm
   
I apologize if you think so, but sesungguhnay I've tried it myself but still have not seen the results, so I ask you please to you. if you could help me with a happy heart I thank you very much. if not, it was not a problem

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

  Print Answers RSS
0 OriginalGriff 200
1 PIEBALDconsult 150
2 BillWoodruff 148
3 Jochen Arndt 135
4 DamithSL 130
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 13 Jul 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100