Databases for a membership system

[fitzgerald]

Hi,
Sorry if the question is a bit stupid, but I'm pretty new to delphi.

Right, I'm making a program which would be used in a video rental shop to help manage details of members and the videos that they are currently renting etc.

I've hit a few problems on the database side of things. The main one is that when you add a new member to the database I want a 6 digit member number to be assigned to the new member. This number has to be the last member number + 1 (e.g. 1st member has member number 000001 and therefore I'd like the next member number assigned to be 000002 then 000003 etc).

The member number is stored within a field of a database and I'm not sure how to get delphi to "add 1" to that field and thus give me a new member number without it having to manually enter the new member number.

Any help is appreciated!
Thanks!

 

[LucieLastic]

hi

I have several IDs that need to be incremented. You could look at autoinc fields, but I never use these.

I have a separate table that contains the tablename and the latest ID. This is my function which I use to get the latest id:

function TDataMod.GetNewID(TableName : string): integer;
begin
with TQuery.Create(self) do
begin
try
DatabaseName := ODBCds;
RequestLive := true;
SQL.Add('SELECT lastID FROM ID_Generator');
SQL.Add('WHERE TableName = '''+TableName+'''');
Open;
if IsEmpty then
Result := NO_ID
else
begin
try
Result := Fields[0].AsInteger;
Edit;
Inc(Result);
Fields[0].AsInteger := Result;
Post;
except
Result := NO_ID;
end;
end;
Close;
finally
Free;
end;//Try-Finally
end;//with
end;

 

[fitzgerald]

Many thanks Weez. I'll try that when i next go to work on my program.

 

[lespaul]

I also have to increment from a table, but instead of storing the last ID I run a query, assign to a variable and increment:

qryMax.SQL.Add('SELECT MAX(MEMBERID) AS MAXMEMBER FROM MEMBERS');

maxmember := qryMax.FieldByName('MAXMEMBER').AsInteger + 1;

Then I use maxmember in my update to add the new record

'INSERT INTO MEMBERS (MEMBERID) VALUES(' + maxmember + ')';


Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[LucieLastic]

hi lespaul,

You can use this method but I think it can be slower and you can run into problems. In my current app, when a user creates a new something, I need the id right at the beginning before the user commits anything, so (with your method) you run the risk of getting a duplicate id when the record is committed on a multiuser environment - i.e. whilst user A as been edited, user B has also done a Max() and returned the same id value.

lou

 

[lespaul]

I'll have to think about that. I only use that in a few places, one is a totally automated process that is only run once every two weeks so the chance of duplicate there is nill. Thanks for the insight!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned