0

Short background: I'm making a booking system which once it has the booking form filled out will allow the user to print it out before it's stored. For the information to be accurate, the Tracking Number must be displayed when "New record" is clicked. This is the primary key which auto increments in a table (Repair). Currently I'm trying to pull through that unique value when "New Record" is clicked to the "tracking number" textbox. To do this i find the last record and add 1.

When i click the "New record" button a 0 pops up in the tracking number textbox instead of the last record's number (+1) which should be 1002 as the last record in the table is 1001.

Below is my code:

Protected Sub btnNewRepair_Click(sender As Object, e As EventArgs) Handles btnNewRepair.Click

    Dim NewTrackingNum As Integer
    Dim con As SqlConnection = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ITrepair.mdf;Integrated Security=True")
    Dim cmd As SqlCommand = New SqlCommand("SELECT MAX(Tracking_Number) +1 As NewTrackingNum FROM Repair")

    sqlConn.Open()
    sqlCmd.ExecuteNonQuery()
    sqlConn.Close()

    txtTrackingNumber.Text = NewTrackingNum
End Sub

Note: Only one user will be able to use this system at a time which will prevent the errors of more than one user getting the same Tracking Number

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
CormacD
  • 23
  • 8
  • 1
    You haven't executed the command, and even if you had, you aren't assigning the value from the query to `NewTrackingNum`. – A Friend Apr 05 '17 at 11:08
  • whoops totally forgot to execute that. Amended the code there now to suit that, and how do i assign the value from the query to NewTrackingNum with the code i have there? – CormacD Apr 05 '17 at 11:20
  • 3
    Use `ExecuteScalar` instead of `ExecuteNonQuery` to get a single value from your query. C# example found [here](http://stackoverflow.com/a/15832272/6144259) – A Friend Apr 05 '17 at 11:23
  • 1
    If `Tracking_Number` is an `Identity` column that is auto incrementing, it [could skip a few numbers](http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database), so just adding 1 could lead to an incorrect reference ID. – VDWWD Apr 05 '17 at 11:31
  • 2
    Don't try to roll your own auto-increment system. It's bound to fail. Use the already built in identity mechanizm. – Zohar Peled Apr 05 '17 at 11:51
  • Still struggling with this trying to use ExecuteScalar as im rather newish to all of this and using vb.net. @ZoharPeled but how am i to retrieve the current tracking number thats currently being booked in? Above the "Book" button i have a "print" option which prints the form which contains the essential "Tracking Number". Or is it viable to remove the tracking number field once the "Book" button is clicked it executes and brings up an option to print the last record in the database? – CormacD Apr 05 '17 at 12:09
  • Use the output clause of the insert statement. – Zohar Peled Apr 05 '17 at 12:34

1 Answers1

0

I haven't done this personally, although we use these for certain systems.

I would recommend using a SQL sequence to generate the next number.
Use that value, display it and save it as your key, and do not auto-increment your PK column.

It may be possible for your primary key column to have a default constraint that requests a number from the sequence if one is not supplied meaning if something is inserted into the table without a value, one it still applied.

Once a number is requested from a sequence, it is gone, so you should not end up with conflicts. Equally if the form with the number is never saved, you will get "missing" numbers in the table's key column. (Not that this is really a problem).
However, any system that relies on "look at the last number and adding 1" is susceptible to "what happens if two people request a form at the same time" - they'll be given the same number, which is a problem.

I'm (probably obviously from this answer) not a database guru, but it might be worth some investigation.

Craig H
  • 2,001
  • 1
  • 14
  • 18