Auto Number in Text Box.

[vks.gautam1]

I am making a project in which if emp 001 issaved .after this EMP ID textbox should display 002 automatically .

i think first i have to make connection with sql server database & then with table store in Dataset. after that wt should i do.
Pls explain me in detail.
How can do this in vb.net 2005

 

[JuggaloBrotha]

parse the textbox to get the number, then increment it by 1:

Dim TheNumber As Integer
If Integer.TryParse(Textbox.Text.Trim.Split(" ")(1I), TheNumber) = True Then
  'The Number parsed correctly
  TextBox.Text = Textbox.Text.Trim.Split(" ")(0I) & " " & CStr(TheNumber + 1I)
End If

Or something similar

 

[vks.gautam1]

Error

It is giving me error " Index was outside the bounds of array "

Here is my code

Imports System.Data.OleDb
Public Class Form1

    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim q As String
    Dim num As Integer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            q = "select * from tablename"
            cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=Database path with name")
            cn.Open()
            cmd = New OleDbCommand(q, cn)
            Dim dr As OleDbDataReader = cmd.ExecuteReader
            If dr.Read Then
                TextBox1.Text = dr.Item(0)
            End If
            If Integer.TryParse(TextBox1.Text.Trim.Split(" ")(1I), num) = True Then
                'The Number parsed correctly
                TextBox1.Text = TextBox1.Text.Trim.Split(" ")(0I) & " " & CStr(num + 1I)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

 

[JuggaloBrotha]

I used the "emp 001" input example and I'm guessing my code doesn't work because there's no space between the 'emp' and the '001' in the string which would cause an index out of bounds exception.

Since I don't know what your input (the data from the DB) actually is and I don't know what any of the "rules" for that field are, I can't help you further.

 

[Bernie]

One sure fire way of doing it is to query the database for the MAX emp number, and then increment that number. That way your are sure of having the next possible number.

Bernie

 

[vks.gautam1]

Pls define me hoW this function is working
wt do mean by integer.tryparse.
wt is meaning of split.
wt is (1I).
Define next line also.

As i want to modify this code as per my input
Input is = > emp-001 " - " instead of space


Dim TheNumber As Integer
If Integer.TryParse(Textbox.Text.Trim.Split(" ")(1I), TheNumber) = True Then
'The Number parsed correctly
TextBox.Text = Textbox.Text.Trim.Split(" ")(0I) & " " & CStr(TheNumber + 1I)
End If

 

[jmcilhinney]

What you're asking for is not possible if you're expecting SQL Server to generate the IDs for you. The ID isn't generated until the record is inserted so you can't get the actual value until then. There's no way to know for sure what that value will be based on the existing data because there's all sorts of reasons that the next value generated wouldn't be 1 greater than the current maximum.

 

[vks.gautam1]

ok

Wt i want to know. If there is not value in our table. In that case a automatic number like emp-001 should appear in textbox. When i saved that data. After that textbox should show emp-002.

This code will generate a value in text box if there is no value in particular column of a table. Now i register 1 value EMP-001. AFTER THAT TEXT BOX SHOULD SHOW EMP-002

Imports System.Data.OleDb
Public Class Form1
Dim cn As OleDbConnection
Dim cmd As OleDbCommand

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=e:\database name.mdb;")
cn.Open()
cmd = New OleDbCommand("select * from table name ", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
If dr.Read Then
TextBox1.Text = dr.Item(0)
Else
TextBox1.Text = "EMP-001"
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

 

[JuggaloBrotha]

Wt i want to know. If there is not value in our table. In that case a automatic number like emp-001 should appear in textbox. When i saved that data. After that textbox should show emp-002.

This code will generate a value in text box if there is no value in particular column of a table. Now i register 1 value EMP-001. AFTER THAT TEXT BOX SHOULD SHOW EMP-002

Imports System.Data.OleDb
Public Class Form1
    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            cn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=e:\database name.mdb;")
            cn.Open()
            cmd = New OleDbCommand("select * from table name ", cn)
            Dim dr As OleDbDataReader = cmd.ExecuteReader
            If dr.Read Then
                TextBox1.Text = dr.Item(0)
            Else
                TextBox1.Text = "EMP-001"
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

So now you're wanting the same thing except a hyphen instead of a space between the 'emp' and the number, my code example will work with that, just use "-"c instead of " "c when splitting the string

 

[vks.gautam1]

thanks

Thanks it is working.