transaction serial number overwrite each other?

[Tengkorak]

I have code to generate transaction number.
hire my code :

Function TransNumber() As String
        Dim oCmd As New OleDb.OleDbCommand
        Dim sNo As String = "P"
 
        Try
            oCmd.CommandText = "select top 1 pono from PURCHASE where order by pono  desc"
            Dim dr As OleDb.OleDbDataReader
            dr = oCmd.ExecuteReader
            If dr.Read Then
                sNo = dr.Item("pono") + 1
            Else
                sNo += "001"
            End If
            dr.Close()
        Catch ex As Exception
 
        End Try
    End Function

I had a problem when there are multiple users using the transaction or the serial number is, because the data overwrite each other.

how to make the correct serial number?

[kareninstructor]

Why not use a field that is of type auto-increment for your primary key? This way the database engine ensures you are not duplication the key.

[Tengkorak]

I need the format of a sequential number.
for example:
PO-2010-12-001

[kareninstructor]

I need the format of a sequential number.
for example:
PO-2010-12-001

Even without (as you said in your first post) data overwrite the following can not ever become PO-2010-12-001 as shown in the extracted code block below you always start with "P" then a numeric value but in the example you start with "PO", also a true numeric field does not have dashes. So what are the rules to create your transaction value?

This can not produce PO-2010-12-001 as shown in your original post unless you are leaving out some details.

Dim sNo As String = "P"

sNo = dr.Item("pono") + 1
sNo += "001"

Do you have Option Strict On ?

[Tengkorak]

that only sample number. not refer to the example of my code

results generated by my code is P001

[Tengkorak]

Option Strict off

[kareninstructor]

Option Strict off

You should be coding with Option Strict On, 99.9% of the time.

[kareninstructor]

that only sample number. not refer to the example of my code

results generated by my code is P001

I do not know of any guaranteed method to create a transaction number via code in a multi-user environment.

If you are using MS-Access then I highly suggest changing to something like MS-SQL Server. Using SQL-Server, you could write a stored procedure to handle inserting a new row with the correct transaction number.

The following was taken from the web which shows how you might write a stored procedure for SQL-Server as suggested above.

Create proc spInsertInProduct
(
@PName varchar(50),
@Qty int
)
AS
Begin
DECLARE @PId VARCHAR(15)
DECLARE @NId INT
DECLARE @COUNTER INT
SET @PId = "P00"
SET @COUNTER = 0
--This give u max numeric id from the alphanumeric id
SELECT 
   @NId = Cast(substring(id, 3, len(id)) as int) 
FROM 
   tblProduct group by left(id, 2) 
order by 
   IDAlpha

--here u increase the value to numeric id by 1
SET @NId = @NId + 1
--GENERATE ACTUAL APHANUMERIC ID HERE
SET @PId = @PId + CAST(@NId AS VARCAHR)
INSERT INTO tblProduct(id,Pname,Qty) values(@PId,@PName,@Qty)
end

I use IBM-DB2 and would have our DBA team write a stored proc to handle this as suggested with SQL-Server.

[Tengkorak]

thank you @kavininstructor

but i don't know about

You should be coding with Option Strict On, 99.9% of the time.

I know about option strict is if strict option true that we are not allowed to convert two different variables.

[kareninstructor]

thank you @kavininstructor

but i don't know about


I know about option strict is if strict option true that we are not allowed to convert two different variables.

You are allowed to convert different types of variables with Option Strict On. When Option Strict is Off your code is prone to errors at run time rather than design time which means if your code is in error with Option Strict On the chances of them being caught at design time is high while with the same error having Option Strict Off the chances are low that the error would be caught at design time.

Example, with Option Strict Off the code below will compile fine and generate an exception at runtime on the first line of code

Error Conversion from string "Bad" to type 'Double' is not valid.

Dim Value1 As Double = "Bad"
Dim Value2 As Double = 1
Dim Result As Double = Value1 + Value2

The code above will fail to compile with Option Strict On. Again this is an extremely simple example which could be much worst in a real app.

[Tengkorak]

thanks, I'll use it in my application.
when I use Option Strict On so many error in my program code..
For example:

Dim sdata As TextBox = sender

I changed into:

Dim sdata As TextBox = CType(sender, TextBox)

whether the way that I use is correct?

[kareninstructor]

when I use Option Strict On so many error in my program code..

That is because you are not coding properly and should always be using Option Strict On so you do not have errors.

For example:

Dim sdata As TextBox = sender

I changed into:

Dim sdata As TextBox = CType(sender, TextBox)

whether the way that I use is correct?

Close, you should be checking the type of sender if there is the possiblity it can be more than one type

    Private Sub Demo(ByVal sender As System.Object)
        If TypeOf sender Is TextBox Then
            Dim sdata As TextBox = CType(sender, TextBox)
            MsgBox(sdata.Text)
        Else
            MsgBox("Handle other types similar to above . . .")
        End If
    End Sub

Better yet is to strongly type parameters

    Private Sub Demo(ByVal sender As ComboBox)
        MsgBox(sender.Items.Count.ToString)
    End Sub
    Private Sub Demo(ByVal sender As TextBox)
        MsgBox(sender.Text)
    End Sub

[Tengkorak]

there are many codes that I make an error.
thank you for share @kevininstructor