generating primary key value 
Author Message
 generating primary key value

Does anyone have any suggestions about how to go about generating a
sequential integer for the primary key field in a table?

This is the situation: I have a table of customer info and each record needs
to be assigned a customer_ID when it is created. I'd like the Ids to start
at 1 and go up from there but I'm wondering what the best way to do this
would be.

I'm kind of hoping that there's some cool SQL command that will
automatically look up the last value, increment it and enter in the new
number.

I've seen something like this when creating the field that would indicate
that you simply assign that field to be auto-incrementing when you create
it, but RB's GUI for creating fields doesn't seem to have this option.

Cheers,

--


I thought it would be neat to put "MD" after my name if not for the
annoyance of constantly explaining to everyone that it stood for Mac Doctor.
(As if that wasn't obvious... ;-)



Mon, 15 Nov 2004 07:45:27 GMT  
 generating primary key value

writes:

Quote:
>I'm kind of hoping that there's some cool SQL command that will
>automatically look up the last value, increment it and enter in the new
>number.

AFAIK, there isn't. But there is an SQL command that will find the
highest number so far, which you can then dig out of the database cursor
object and increment yourself:

   SELECT MAX([id_number]) FROM [your_table];

Have fun,

 - Jeff



Mon, 15 Nov 2004 10:41:13 GMT  
 generating primary key value

Quote:

> Does anyone have any suggestions about how to go about generating a
> sequential integer for the primary key field in a table?

Here is what I use:

Function GetNextID(inDB As Database, inField As String, inTable As String,
inDefault As Integer) As Integer
   Dim aCursor As DatabaseCursor
   Dim theID As Integer

   aCursor = inDB.SQLSelect("SELECT Max(" + inField + ") FROM " + inTable)

   If aCursor <> Nil Then
      If aCursor.EOF Then
         theID = inDefault ' no records
      Else
         theID = aCursor.IdxField(1).IntegerValue + 1
      End If
      aCursor.Close
   Else
      theID = inDefault ' no records
   End If

   Return theID

Exception
   MsgBox "An exception occurred in GetNextID()."
End Function

Hope this helps,
Mark Linton



Mon, 15 Nov 2004 11:20:43 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. using a form to update, clear the Primary key value blank or 0

2. no keys showing up on odbc - only primary key

3. Generating a value from a VALUE ERROR

4. REALdatabase not null and primary key not working

5. Using STRING for binary primary keys??

6. I need 64 bit for primary key....

7. Primary Keys

8. How do I get the contents of the primary key field

9. Stumped on a many-to-many relationship--Must use Primary Keys

10. CW2003 - Convereion sets Primary key to zero

11. All components of the primary key must be linked - Dictionary Relationship

12. Using primary keys

 

 
Powered by phpBB® Forum Software