AutoNumber in MSDE

[PlayKid]

Hi all,

I am trying to create a program with Client Number, but I want the program to automatically generate an auto number for the Client Table, how can I do that?

Note that I am using MSDE

Thanks

PlayKid

[DaVinci]

The rule of thumb when it comes to relational databases is that if the data has significance, you should use a unique identifying value. In this case, it sounds as though your client number will be at least somewhat important. Might I suggest writing a bit of code to increment the current maximum value existing in your client number field? This way, you can always be assured that you're assigning the next available unique client number to a new record. Do some research on finding the maximum value existing in a field and incrementing it by one to obtain the next value.

[techgnome]

It sounds like that's what he wants, but the ClientID is the auto number....

In SQL Server to create an autonumber, use the IDENTITY keyword:

CREATE TABLE tblClinet (
  ClientID int NOT NULL IDENTITY(1,1),
.
.
.
)

int - tells it that it's an integer type
NOT NULL - the field cannot be empty, it must contain a vlaue
IDENTITY - this is what makes the field an autonumber
(1,1) - Start with the number 1, and increment by 1 each time. Using (10,10) would tell it to start with 10, and increment by each time (10,20,30,40....)

Tg

[DaVinci]

My suggestion was to increment the value from the VB/VB.NET Frontend, not the MSDE Backend. It's been my experience that if you use an autonumber data type for the clientNo field and you begin entering a new record and fail to save it (let's say your frontend form closes before the record is saved), then you end up with gaps in your clientNo field. It may jump from say 001 to 003 if you fail to successfully save 002. This would probably make more sense if you have ever used MS Access with JET as a backend.

[PlayKid]

Thanks for both of you, I will take DaVinci's advise, first is that I am not really sure how to do it in MSDE, and second, it may be easily for me to do it in VB.Net, but somehow I cannot do that in VB.Net, I donno why, after I have added the first value, the second value cannot be added, so I am stuck.

[Briantcva]

Another option for PK generation