2

I have an application that adds pets to an SQL database.

Currently the user must know what PetIDs are existing and so must know what is available to be added. If the user tries to enter an already existing ID the program gives an error.

Im thinking i need the PetID (top text box) value to be automatically decided upon page load, with a value which wont clash with an already existing value...

Can someone help? i have no idea how to do this

I need the page_load to automatically search the SQL database for PetIDs that are available, pick the one with the lowest value and have it in the text box automatically, ready for the user (so the user wont have to worry about picking one which isn't already taken)...

How can i have an ID which is available, waiting in the top text box, upon page load? also make it so the user cannot attempt to change it.

Picture of current Pets table data Picture of web application

BingoDingo
  • 71
  • 7
  • 2
    Possible duplicate of [Identity column Vs Primary Key](https://stackoverflow.com/questions/12932209/identity-column-vs-primary-key) – mjwills Dec 12 '18 at 11:45
  • What do you mean by search for available id? There are pre-generated ids or do you mean the last id + 1? You should be using an identity value as per other comment. – Crowcoder Dec 12 '18 at 11:48
  • You should not give the id to be inserted manually rather than you can get identity value for id after insert in output clause. – Suraj Kumar Dec 12 '18 at 11:49
  • @BingoDingo Can you talk us through why you don't want to use an IDENTITY column? – mjwills Dec 12 '18 at 11:54
  • @kennyzx the database doesnt ensure auto incremented ID, i get an error. – BingoDingo Dec 12 '18 at 11:55
  • ExecuteNonQuery returns the number of rows changed in the database. When you have a Primary key set the database does one of the following with ExecuteNonQuery 1) Insert :return zero when key is already in database. Then you must use Update to change value 2) Update : returns zero if key is not in database. Then you need to use Insert to put new value. – jdweng Dec 12 '18 at 11:56
  • The short answer is you can't (easily). Imagine if you could. Let's say there were 6 pets in the database. Both you and I load the application. What pet number should it show for each of us? Now what if there were 100 users? Now, you could use @JesúsLópez solution below - but it will create gaps **every time someone loads the app**. Also, think of it from the customer's point of view. They don't want to specify the ID - they just want to add the Pet! They will be more than happy to let the DB generate an ID for them. – mjwills Dec 12 '18 at 12:00
  • @mjwills there will be just a single user, i also need the user to be aware of which ID will be allocated to the pet – BingoDingo Dec 12 '18 at 12:10
  • I'd suggest using IDENTITY, and use something like https://stackoverflow.com/a/31215537/34092 to **predict** the next ID to use. – mjwills Dec 12 '18 at 12:14

4 Answers4

2

You can create the PetId as an Identity Column as below:

[PetId] [int] IDENTITY(10,1) NOT NULL

Thus you will not have to add it manually, each time you add a record of Pet, the PetId will be generated automatically and that will be unique. So, no clashing of PetId will occur.

Here 10 represents the first Id number and 1 represents how your PetIds will be incremented.

Regards, Pratik

Pratik Somaiya
  • 695
  • 5
  • 18
  • Yes agreed, that can be the case when the user wants to keep the PetId as per their wish. If that doesn't bother the user, then we can make it as identity column. – Pratik Somaiya Dec 12 '18 at 11:58
  • can you describe the function of the "(10,1)" in your answer? i have done this and it has worked, i had 6 pets in the database but then the pet ID's started from 10, it left out IDs 7,8 and 9... – BingoDingo Dec 12 '18 at 12:05
  • how can i make it so the ID which will be chosen is shown to the user in the top textbox upon page load? – BingoDingo Dec 12 '18 at 12:06
  • 1
    IDENTITY(10, 1) means first PetId will be 10 and others will be 11, 12, 13, 14.... etc. You can also write, IDENTITY(1, 1) to start with 1 and increment by 1 – Pratik Somaiya Dec 12 '18 at 12:13
  • I will try (1,1) and see what happens as i already got 6 pets in the database, some pet records will be deleted over time, so i will test to see what happens when a pet is deleted - will a new pet take over the 'now-free' pet ID? – BingoDingo Dec 12 '18 at 12:16
  • So i deleted a pet and a new pet took over the old pets ID, however i still have gaps in the petID, 7,8,9 wont be taken by the program – BingoDingo Dec 12 '18 at 12:20
  • 1
    The PetId which you'll delete will not be free, it is saved at the backend. When you delete a Pet record with Id as 6 and you have 10 pet records in total then you'll have 1-5, 7-10 ids. When you insert a new pet record, it will be assigned PetId as 11 and not 6. To add the new record as PetId 6, you need to add: SET IDENTITY_INSERT [].[] OFF, setting this to OFF enables you to manually enter the PetId value and not incremental. – Pratik Somaiya Dec 12 '18 at 12:21
  • It worked however i cant figure out how to have the PetID value displayed upon page_load (so the user will know which ID will be allocated to the pet) – BingoDingo Dec 12 '18 at 12:36
  • For displaying the PetId to the user you can display a label instead of a text box, and at the backend you can write a select query to the table. Hope you get my path. – Pratik Somaiya Dec 12 '18 at 12:39
1

It is usual to enter your data and return the ID after the record is added to the database (using the Identity Insert on the Id column).

If you want the next number to be displayed BEFORE the data is created then use an integer column for ID without the Identity Insert option and create a SQL Server sequence. Create a stored procedure to return the next sequence number to show in your creation page

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

One option would be to use a sequence:

CREATE SEQUENCE pet_id_seq START WITH 7

I'm starting it with 7 because it is the first available pet_id

Then, on page load, get the next value for the sequence by executing the following query:

SELECT (NEXT VALUE FOR pet_id_seq) AS next_pet_id

And show the value returned by the query on the pet_id text box.

This approach has one disadvantage, it produce gaps, if you open the page, but you don't actually insert the pet row, the id is lost forever. But I think you should don't care, there are many numbers, you are not going to exhaust them.

Another option is to use the following query to fill the pet_id text box:

SELECT MAX(pet_id) + 1 AS next_pet_id FROM pets

But this one has another disadvantage. It doesn't work well on concurrent scenarios. If two users open the create pet page at the same time they get the same next_pet_id and one of them will get a primary key violation error.

Jesús López
  • 8,338
  • 7
  • 40
  • 66
  • there will be just a single user – BingoDingo Dec 12 '18 at 12:09
  • how can i go about implementing this? – BingoDingo Dec 12 '18 at 12:09
  • @BingoDingo Do you know how to execute a query that returns a single scalar value? Do you know how to show an scalar value on a text box?. I you don't, learn and try, if you get stuck, ask here. But I'm not going to write the code for you, sorry – Jesús López Dec 12 '18 at 12:12
  • How can i do these 2? No, i don't know how to execute a query to return a single scalar value however i think i might be able to show the value in a text box... *googling scalar value* – BingoDingo Dec 12 '18 at 12:15
0

Set the id column to auto increment and don't let the user insert it, let the database decide it for itself. Or you can change the id column type and use Guid.NewGuid() to generate a new id.

Mohammad Karimi
  • 387
  • 2
  • 8