0

How to create receipt number like this "BSP000001" and in the next load of the receipt form, the receipt number will be "BSP000002" and save again to database..I'm using vb2010 and MySql database...It is just like auto increment receipt number in my database table.

And I really don't know how to make it.

It looks like this to my transaction table and I want show for Receipt_No column only, just to figure out..

Receipt_No
BSP000001
BSP000002
BSP000003

badly needed your help, to anyone who read this post...Big Please for Mr and Mrs Programmers outside the world..I Thankyou..

LovExpert
  • 17
  • 7
  • Is this application used by more than one user concurrently? (meaning that two people could insert a receipt in the same moment) – Steve Dec 19 '14 at 10:49
  • No Sir..one user as cashier only..Because this is stand alone application. – LovExpert Dec 19 '14 at 11:05
  • Then you could adopt many easy ways to keep the last number of your receipts. You could add a table with just one column with an integer value that you read back before insert. You could have a Configuration table where you have two columns for Key and value. Key=LastReceipt and Value=XXXXX, read back this record and increment. Finally you could get the MAX(Receipt_no) of the Receipt table and increment the numeric part. – Steve Dec 19 '14 at 11:09
  • how do I query that one sir,I am beginner of mysql and vb.net – LovExpert Dec 19 '14 at 11:15
  • I mean,How do I query to get the MAX(Receipt_no).If it is okey to you sir,Can I have the code?sorry sir because it is my first time to do all those stuff. – LovExpert Dec 19 '14 at 11:59
  • See the answer below. It doesn't use the MAX but the ORDER BY with LIMIT to one row. Probably faster if you have an index on receipt_no – Steve Dec 19 '14 at 12:05

1 Answers1

0

In a single user scenario, usually you could use the MAX function or order your receipts in DESCENDING order to find the last inserted one. Once you have this last record it is easy to extract the numeric part, increase the value by one and build the new receipt_no

Function GetNewReceiptNumber(prefix as string ) as String
    using cn = new MySqlConnection(connectionstring)
    using cmd = cn.CreateCommand()
       cn.Open()
       cmd.CommandText = "select receipt_no from receipts order by receipt_no DESC limit 1"
       Dim result = cmd.ExecuteScalar
       if result IsNot DbNull.Value AndAlso result IsNot Nothing Then
           Dim number = Convert.ToInt32(result.ToString().SubString(prefix.Length))
           return prefix & (number + 1).ToString("D6")
       else
           return prefix & "000001"
       End If
    End Using
    End Using
End Function

or perhaps it is better to use MAX(receipt_no) for a slightly faster performance (but you really need to have an index of receipt_no)

cmd.CommandText = "select MAX(receipt_no) from receipts"

You could call this method from the code that tries to insert a new receipt in this way

Dim newReceiptNumber = GetNewReceiptNumber("BSP")

In this answer there are some assumptions:

  • You have a global variable that keeps the details of your connection string to MySql called connectionstring
  • The name of the table where you store the receipts is called receipts
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Looking at [this answer](http://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) perhaps I am wrong and it is better to use MAX, will update the answer now – Steve Dec 19 '14 at 13:07
  • Sir,Ive got an error of line "Dim result = cmd.ExecuteScalar" the error was NullReferencesException was unhandled – LovExpert Dec 19 '14 at 13:09
  • Error sir..the error is Operator '<>' is not defined for types 'Object' and 'System.DBNull' – LovExpert Dec 19 '14 at 13:27
  • It's okey sir.I will wait for your updates of this.I need your help sir.For me,it is a big problem for me as a beginner of programming. – LovExpert Dec 19 '14 at 13:59
  • Error again sir at executescalar – LovExpert Dec 19 '14 at 14:46
  • I think sir,we are locking a cn.open() and cn.close().I try it but when I run the application at first the value of receipt_no of my transaction table is BSP00001 but when I close the program and run it again, the value are still the same.I notice also that there is no value inserted at my receipts table. – LovExpert Dec 19 '14 at 16:33
  • This code doesn't add anything to your receipt table. This code just looks into this table to find the latest receipt number and add one to it. It is AFTER this code that you should build your record to insert using the newly defined receipt_no. You are right about cn.Open but Close is not required because the Using statement will close the connection in the End Using – Steve Dec 19 '14 at 16:39
  • Oh yeah,I got it..I forgot to put a query for saving the receipt value to the table receipts, that's why the value the column Receipt_no of Transaction table are not incremented.It works fine and awesome.. – LovExpert Dec 19 '14 at 17:10
  • I should save the value that been added to receipts table because each time we get the max value from receipts table, it looks like auto incremented.At the same time I save also the incremented receipt value to other table. – LovExpert Dec 21 '14 at 05:20