how to make AutoNumber with vb.net and sql server or ms. access

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