How do I generate automatic numbers for order number and inquiry numbers in vb.net

[tomexlfc]

Hello everyone.

Please I am writing an system application for a company that must generate inquiry id and order id automatically. Whenever a customer makes a new inquiry or new order by clicking the add button, an automatic number should be displayed in the text box and when it's saved, it should be saved in the database access.
The order and inquiry numbers should consist of integers and strings. e.g: "CT0001, RT0002"

Please can someone kindly help me with this as soon as possible, hopefully?

Thank you all for your assistance in advance :)

[Pgmer]

Make the primary key in table for Enquiry and order table.with autoincrement as 1. I am not sure how to do this in MS access DB. while showing allways u can append some fixed text and u can save that in another column of table.

[tomexlfc]

Hi, thanks for the reply.

I want the automatic generating be done in VB.Net which is the front end and not in the database. Anytime the add button is clicked on the VB form, a new order or inquiry number should be displayed. The strings will be constant, but only the integers will increase by 1 each time the "Add Button" is clicked.

Thank you

[Pgmer]

try generating random number in vb.net and use them and make sure its unique all the time..
Declare this at form level

Dim objRandom As New System.Random( _
   CType(System.DateTime.Now.Ticks Mod System.Int32.MaxValue, Integer))

Public Function GetRandomNumber( _
    Optional ByVal Low As Integer = 1, _
    Optional ByVal High As Integer = 100) As Integer
        ' Returns a random number,
        ' between the optional Low and High parameters
        Return objRandom.Next(Low, High + 1)
    End Function

[tomexlfc]

Sorry for late reply, I will try this out now and let you know if it works. Thanks :)

[codeorder]

See if this helps.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        With TextBox1
            If Not .Text = "" Then '// check if not empty.
                Dim myCoolIDletters As String = "" '// new String to get all letters at beginning of ID.
                For i As Integer = 0 To .Text.Length - 1 '// loop until all letters located and added to String.
                    If Not IsNumeric(.Text(i)) Then myCoolIDletters &= .Text(i) Else Exit For
                Next
                '// set letters back and increase the #+1, with the format of 4#'s, as "0000".
                .Text = myCoolIDletters & (CInt(.Text.Substring(myCoolIDletters.Length)) + 1).ToString("0000")
            End If
        End With
    End Sub

[tomexlfc]

Hi Codeorder,

Thanks so much for the code, it works pretty well! :)

But there's one more problem I'm encountering there. After adding new records and I click the "Commit Button" so it can add the new record to my database, it gives me an error message saying, "Syntax error in INSERT INTO statement." This error comes up under the click event of the "Commit Button".
I don't know where this error comes from.

Please can you help me out on this?

Thanks so much

Here's my code for the "Add & Commit Button" to add the new record to the database.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'txtStudID.Clear()
        txtStudName.Clear()
        txtSex.Clear()
        txtFaculty.Clear()
        txtStudID.Focus()

        With txtStudID
            If Not .Text = "" Then
                Dim myIDalpha As String = ""
                For i As Integer = 0 To .Text.Length - 1
                    If Not IsNumeric(.Text(i)) Then myIDalpha &= .Text(i) Else Exit For
                Next
                .Text = myIDalpha & (CInt(.Text.Substring(myIDalpha.Length)) + 1).ToString("0000")
            End If
        End With

    End Sub


Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click

        If inc <> -1 Then

            Dim cb As New OleDb.OleDbCommandBuilder(daTest)
            Dim dsNewRow As DataRow

            dsNewRow = dsTest.Tables("SouthCity").NewRow()

            dsNewRow.Item(0) = txtStudID.Text
            dsNewRow.Item(1) = txtStudName.Text
            dsNewRow.Item(2) = txtSex.Text
            dsNewRow.Item(3) = txtFaculty.Text

            dsTest.Tables("SouthCity").Rows.Add(dsNewRow)

            daTest.Update(dsTest, "SouthCity")

        End If
    End Sub

[ryanjayson]

Hello everyone.

Please I am writing an system application for a company that must generate inquiry id and order id automatically. Whenever a customer makes a new inquiry or new order by clicking the add button, an automatic number should be displayed in the text box and when it's saved, it should be saved in the database access.
The order and inquiry numbers should consist of integers and strings. e.g: "CT0001, RT0002"

Please can someone kindly help me with this as soon as possible, hopefully?

Thank you all for your assistance in advance :)

Hi! try this.

When clicking the new order button perform the query below and put the retrieved data in the order textbox ID.

SELECT MAX(ColumnID) + 1 FROM OrderTable

[tomexlfc]

Hi! try this.

When clicking the new order button perform the query below and put the retrieved data in the order textbox ID.

SELECT MAX(ColumnID) + 1 FROM OrderTable

Hello,

I don't quite get what you mean please.

Should i enter this lines of code in the "Add Button" click event or where?

And what do you mean by "put the retrieved data in the order textbox ID"?

[princenathan]

Private Sub autonum()
        Call main()
        Con.Open()
        Dim UserSelect As SqlCommand
        Dim myreader As SqlDataReader
        Dim sql As String
        Dim sum As Integer
        Dim no As Integer
    
        sql = "select max(work_order) from DOpwo"
        UserSelect = New SqlCommand(sql, Con)
        myreader = UserSelect.ExecuteReader
        If (myreader.Read = True) Then
            If txt1.Text = "" Then
                txt1.Text = "091101"
            Else
                no = 1
                txt1.Text = myreader(0)
                sum = txt1.Text + no
                txt1.Text = 0 & sum
                'clear()
            End If
      
        End If

            Con.Close()
    End Sub

[jertos]

Hi Guys,

this topic is very interesting for a little project I'm doing right now. I used the code provided by codeorder, thanks for that!

I want my ID tot start at 0001 and go untill ... everytime I click the Add button. I implement the code and changed it to the name I gave my textbox.

But here's the problem, when I run my application, the first I see at my ID is 0000, which is good, but when I click the add button, no entry is in my ID textbox, someone has any idea why this may be the case?

Greets,

Jeroen