1 Replies - 3970 Views - Last Post: 10 November 2011 - 12:52 PM Rate Topic: -----

#1 rekha_kj  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 14-September 11

auto generate number in vb.net

Posted 09 November 2011 - 11:36 PM

Hi all,
I am trying to generate autonumber the sequence is like this. From left to right, last two digit from Year, Month in numeric and the last three digit number be from 001 to 999. I tried with the below coding but the sequence is not coming correctly. Can any body tell me that is the mistake i have done in the logic.


Imports System.IO
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbDataAdapter
Imports System.Data.OleDb.OleDbDataReader
Imports System.Data.OleDb.OleDbCommand
Imports CrystalDecisions.CrystalReports.Engine

Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader

    Dim sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim second As String
        Dim third As String

        Dim firstall, first As String
        Dim rmnformsub, rmnform As String
        

        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\vb\officeproject\officedatabase.mdb;")
        cn.Open()


        'cmd = New OleDbCommand("select * from login_table1 where Ename=" & " " & txt_name.Text & " " & " and Password =" & " " & txt_password.Text & " " & " ", cn)
        'cmd = New OleDbCommand("select * from login_table1 where Ename= trim('" & TextBox1.Text & "')  and Password =trim( '" & TextBox2.Text & "' ) ", cn)
        'sql = "insert into party values  ('" & code.Text & "','" & name1.Text & "','" & add1.Text & "', '" & add2.Text & "', '" & add3.Text & "','" & city.Text & "', '" & State.Text & "','" & pincode.Text & "', '" & country.Text & "','" & isd.Text & "','" & std.Text & "','" & tel1.Text & "','" & tel2.Text & "','" & fax.Text & "','" & contact1.Text & "','" & mobile1.Text & "','" & contact2.Text & "','" & mobile2.Text & "')"

        cmd = New OleDbCommand("select rmnno from rmnform order by rmnno desc", cn)
        'sql = "insert into rmnform values  ('" & number_txt.Text & "','" & date_txt.Text & "','" & address_txt.Text & "', '" & item_txt.Text & "', '" & reference_txt.Text & "','" & serial_txt.Text & "', '" & complaint_txt.Text & "','" & remarks_txt.Text & "', '" & dcno_txt.Text & "')"
        'cmd = New OleDbCommand("select * from login_table1", cn)
        dr = cmd.ExecuteReader
        'cmd = New OleDbCommand(sql, cn)
        'cmd.ExecuteNonQuery()
        dr.Read()

        third = dr.GetValue(0)
        cn.Close()
        MsgBox(third)
        Dim third_1 = third.Substring(0, 1)
        Dim third_2 = third.Substring(1, 1)
        Dim third_3 = third.Substring(2, 1)


        'MsgBox(third_3)
        'TextBox1.Text = third_3
        If (third_3 < 9) Then
            third_3 = third_3 + 1
        Else
            third_3 = 0
            If (third_2 < 9 And third_3 < 9) Then
                third_2 = third_2 + 1
            Else
                third_2 = 0
                'If (third_3 < 9 And third_2 < 9) Then
                'third_3 = third_3 + 1
                'Else
                '   third_3 = 0
                'End If
            End If


        End If

        third = String.Concat(third_1, third_2, third_3)
        third_1 = third.Substring(0, 1)
        third_2 = third.Substring(1, 1)
        third_3 = third.Substring(2, 1)


        second = Month(Now)
        firstall = Year(Now)
        first = firstall.Substring(2, 2)
        rmnformsub = String.Concat(first, second)
        rmnform = String.Concat(rmnformsub, third)
        'MsgBox(third)
        'TextBox1.Text = rmnform






        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\vb\officeproject\officedatabase.mdb;")
        cn.Open()


        'cmd = New OleDbCommand("select * from login_table1 where Ename=" & " " & txt_name.Text & " " & " and Password =" & " " & txt_password.Text & " " & " ", cn)
        'cmd = New OleDbCommand("select * from login_table1 where Ename= trim('" & TextBox1.Text & "')  and Password =trim( '" & TextBox2.Text & "' ) ", cn)
        'sql = "insert into party values  ('" & code.Text & "','" & name1.Text & "','" & add1.Text & "', '" & add2.Text & "', '" & add3.Text & "','" & city.Text & "', '" & State.Text & "','" & pincode.Text & "', '" & country.Text & "','" & isd.Text & "','" & std.Text & "','" & tel1.Text & "','" & tel2.Text & "','" & fax.Text & "','" & contact1.Text & "','" & mobile1.Text & "','" & contact2.Text & "','" & mobile2.Text & "')"
        'cmd = New OleDbCommand("select * from login_table1", cn)
        dcno_txt.Text = rmnform
        sql = "insert into rmnform values  ('" & number_txt.Text & "','" & date_txt.Text & "','" & address_txt.Text & "', '" & item_txt.Text & "', '" & reference_txt.Text & "','" & serial_txt.Text & "', '" & complaint_txt.Text & "','" & remarks_txt.Text & "', '" & dcno_txt.Text & "')"
        'cmd = New OleDbCommand("select * from login_table1", cn)
        'dr = cmd.ExecuteReader
        cmd = New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
        'TextBox1.Text = dr.RecordsAffected()
        'TextBox2.Text = "succesS"
        Dim cryRpt As New ReportDocument
        cryRpt.Load("e:\vb\officeproject\CrystalReport1.rpt")
        CrystalReportViewer1.ReportSource = cryRpt
        CrystalReportViewer1.Refresh()
        CrystalReportViewer1.Show()

    End Sub
End Class




Thanks & Regards
Rekha

Is This A Good Question/Topic? 0
  • +

Replies To: auto generate number in vb.net

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: auto generate number in vb.net

Posted 10 November 2011 - 12:52 PM

Ok, so it looks like third is being initially set as a three digit number. So instead of trying to break it up into the three numbers, why not keep it as the number, increment it by 1 and if it goes over 999 then have it roll around to 001? You can do this easily with something like this...

Dim num as Integer

' Set num to the three digit value
num = dr.value(0)


' Get the remainder of the number divided by 999, then increment it
num = (num Mod 999) + 1



So when you put this all together you can do it like this...

Dim first As Integer = Month(Now)
Dim second As String = Year(Now).ToString().Substring(2, 2)
Dim num As Integer = (third Mod 999) + 1
Dim newID As String = String.Format("{0}{1}{2}", first, second, num.ToString("D3"))



Now assuming that "third" was originally set to 998. This would make "newID" the value "1111999". Now assume "third" was originally set to 999. This would generate "1111001".

Hopefully this is what you were looking to do.

:)

This post has been edited by Martyr2: 10 November 2011 - 12:53 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1