ADO - Retrieve and update values

Hi,

D5, WinXP, SQLServer 2000, MDAC 2.7

I have a table with one row that contains key values for other tables. To
get the next key value I query the table, store the key value, close the
query. I then use another query to update the table with an incremented
value as the next available key. The code is shown below (uses ADO)

try
// Retrieve id value
DM.qryKey.Close;
DM.qryKey.Open;
DM.PeopleId := DM.qryKey.FieldByName('people').Value;
DM.qryKey.Close;
// Increment id value for next tme
DM.qryGP.Close;
DM.qryGP.SQL.Clear;
DM.qryGP.SQL.Add('UPDATE FI_KEYS SET people =
'+IntToStr((DM.PeopleId+1)));
DM.qryGP.ExecSQL;
except
ShowMessage('Unable to retrieve an ID number'+#13#10+
'Case details have NOT BEEN SAVED'+#13#10+
#13#10+'Click Save to try again');
DM.PeopleId := -1;
end;

On occasions (it appears to be random) the above code doesn't work and the
'except' block is executed but one of the queries appears to lock and
remains 'locked' no matter how often you retry. The application has to be
closed before the code works again.

I'm fairly new to this stuff but could anyone see why the above would
occasionally lock or suggest a better way of retrieving a value and then
updating it (for a variety of reasons, I can't use an autoinc \ identity
number type).

Many thanks

Ian



.

Relevant Pages

  • Re: On ADSI and LDAP
    ... the problem is how can I retrieve the value for myuser using the ... would be more efficient to use ADO to query AD for the attributes values. ... For more on using ADO, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ...
    (microsoft.public.scripting.vbscript)
  • Re: Internet class differences...
    ... The database and web interface to the server is produced by a different ... Initially a WebClient class was used. ... This worked fine for submitting the query. ... retrieve the results but, instead, is sent to a login page from which it ...
    (microsoft.public.dotnet.general)
  • Re: Column Order Problem
    ... I understand the reasons for not using the BLOB data types but as I ... >> I'm working on required extending an existing query to retrieve some ... The query is written correctly and returns the ...
    (microsoft.public.data.ado)
  • Re: Returning only a subset of groups in AD
    ... Output.WriteLine "There are no members in this group." ... I assume that ADSICommand is an ADO command object, ... value assigned to the CommandText property, which is the ADO query. ... ' Comma delimited list of attribute values to retrieve. ...
    (microsoft.public.scripting.vbscript)
  • Re: Record source at runtime
    ... Clause, but is in the data requested, so it will return the PKs ... retrieve indexes for Lastname and Firstname. ... had I written the query: ...
    (microsoft.public.access.formscoding)