create id with multiple prefix

Abhinav Kansal

i want different id generate as i have a combo box having different cities name and a textbox. when i choose a city from combo box then according to it city name two initial values will pick and create a id.
if i do ten entries as same city then it will generate as DE01,DE02,....DE10.
AND IF next time i choose different city then it will generate PU01,PU02,...PU08.
next time if i want again do entry using delhi city then it will check from database that how many it has previous entries then it will generate DE11,DE12,...DE16.
Next time on choose punjab it will generate PU09,PU10,...PU17.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
@Abhinav
Have you tried something to achieve this?

Please share your code to assist you better
Nitin Sontakke9y ago
If you are using SQL Server, you may also consider using SEQUENCE object for each city and get the next value from an appropriate SEQUENCE. Do all the coding in stored procedure, if possible.
Abhinav Kansal9y ago
Private Sub cmbArea_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbArea.SelectedIndexChanged
MYID()
End Sub

Private Sub MYID()

Try

Dim sql As String = "SELECT Max(BorrowerNo) FROM borrower"
ConnDB()
cmd = New MySqlCommand(sql, conn)

dr = cmd.ExecuteReader

If dr.Read() Then

If dr.IsDBNull(0) Then

Dim number As Integer = 1

Dim emplang As String = cmbArea.Text.Substring(0, 2)

Dim append As String = emplang + number.ToString().PadLeft(2, "0")

txtBorrowerNo.Text = append

Else

Dim sql1 As String = "SELECT Max(BorrowerNo) FROM borrower WHERE Area='" & cmbArea.Text & "'"
ConnDB()
Dim cmd = New MySqlCommand(sql1, conn)

If dr.HasRows Then

Dim sql2 As String = "SELECT Max(BorrowerNo) FROM borrower WHERE Area='" & cmbArea.Text & "' order by ID desc"
ConnDB()

Dim empString As String = Val(dr(0)).ToString.Substring(0, 2)
Dim empID As Integer = Val(dr(0)).ToString.Substring(2) + 1
Dim append As String = empString + empID.ToString().PadLeft(2, "0")
txtBorrowerNo.Text = append.ToString

Else

Dim number As Integer = 1

Dim emplang As String = cmbArea.Text.Substring(0, 2)

Dim append1 As String = emplang + number.ToString().PadLeft(2, "0")

txtBorrowerNo.Text = append1

End If

End If
End If

Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
dr.Close()
End Try
End Sub