first free primary key

[Konrad]

Hi

What is the speedest way to obtain
first free value of primary key, id
in the table from database on disk.
I want to add record must find unique
id.

Thanks
Konrad

 

[Guillaume Hanique]

I had the same problem. I changed the insert and update commands the
developer studio gerated, so it didn't write the ID or didn't update the ID.
That prevents a lot of error messages, because if you write the ID, you set
a new value for the primary key (even though it may be the same value) and
that is not allowed. This way the database automatically generates a new
primary key.

I also had the problem that I needed the primary key when I added the
record, before writing it to the database. I solved it dirty by having an
other column, MyID, where I could set the value myself. I used new GUIDS to
ensure the values would be unique. This way the Database would generate its
own primary key and I could use a value that uniquely identifies a record.

An other workaround would be to save the new record without saving the ID,
and then reloading the dataset. When you saved the new record, the database
generated a primary key. When you reload the dataset, you get the ID from
the database. In my case that wasn't an option because it took way too much
time to refill the dataset every time a record was added.

Hope this helps.

Guillaume Hanique.

 

[Scot Rose [MSFT]]

If the Primary Key is an Autonumber field, you cannot add that to the record, the database adds it for you. If you are handling the primary keys yourself, then yes, you could pull in
the highest primary key value by using the Top value in your query (Select Top 1 ID from Table) similar to below. This isn't really a good idea in a multi user environment
however...

Dim con As New System.Data.SqlClient.SqlConnection("server=YOURSERVER;uid=sa;pwd=******;database=northwind")
Dim daCust As New System.Data.SqlClient.SqlDataAdapter("Select Top 1 EmployeeID From employees", con)
daCust.Fill(DS, "Employees")
TextBox1.DataBindings.Add("Text", DS, "Employees.EmployeeID")

Now IF you need to know the value of the ID of a Newly added record then you would want to use @@Identity from SQL Server or Access

815629 HOW TO: Retrieve the Identity Value While Inserting Records into Access
http://support.microsoft.com/?id=815629

320141 HOW TO: Retrieve an Identity Value from a Newly Inserted Record from SQL
http://support.microsoft.com/?id=320141


Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------

 

[Glenn Wilson]

have a look at the dbcc checkident command in the Books online, It can
return the next availibly id value, but in a multi user environment, it wont
be much goog as this will always be changing.

Glenn