Board index » delphi » TQuery SQL question

TQuery SQL question

Just a quick Delphi question (ver 1.0) about TQuery objects

If I have a pdox table with the following structure
   CustID - Autoinc
   CustName - Char[40]
   CustPhone - Char[20]

and want to use code to insert a new record, such as

   Query1.SQL.Clear;
   Query1.SQL.Add('Insert into customer');
   Query1.SQL.Add('(CustName, CustPhone)');
   Query1.SQL.Add('values ');
   Query1.SQL.Add('(''John Smith'',''1234567'')');
   Query1.ExecSQL;

how can I get the value of CustID (the autoincrement field)
from the customer table? I have thought of querying the last
record in the table with 'Select MAX(CustID) from customer'
however, if another user adds a record to the table in the
mean time, my result will be incorrect. Any ideas?
thanx in advance.

 

Re:TQuery SQL question


If you are using CustID as the link to any detail tables, I would
strongly advise against using AutoInc.  If you ever find yourself
having to repair the master or detail tables using Tutility, the
AutoInc fields will be renumbered and you'll lose all those vital
links.

The solution I would recommend is to set up a table with one field
and one record to hold the last used number.  When a user starts to
add a new customer record, open the table with Exclusive set to
True, get the number, increment it, post it back and close the table.

In article <6er1ud$vb...@reader1.reader.news.ozemail.net>, "Fred Troncone" (troncon...@SPAMozemail.com.au) writes:

Quote
>Just a quick Delphi question (ver 1.0) about TQuery objects

>If I have a pdox table with the following structure
>   CustID - Autoinc
>   CustName - Char[40]
>   CustPhone - Char[20]

>and want to use code to insert a new record, such as

>   Query1.SQL.Clear;
>   Query1.SQL.Add('Insert into customer');
>   Query1.SQL.Add('(CustName, CustPhone)');
>   Query1.SQL.Add('values ');
>   Query1.SQL.Add('(''John Smith'',''1234567'')');
>   Query1.ExecSQL;

>how can I get the value of CustID (the autoincrement field)
>from the customer table? I have thought of querying the last
>record in the table with 'Select MAX(CustID) from customer'
>however, if another user adds a record to the table in the
>mean time, my result will be incorrect. Any ideas?
>thanx in advance.

Re:TQuery SQL question


Hi Fred,

Quote
you wrote:
>If I have a pdox table with the following structure
>   CustID - Autoinc
>   CustName - Char[40]
>   CustPhone - Char[20]

>and want to use code to insert a new record, such as

>   Query1.SQL.Clear;
>   Query1.SQL.Add('Insert into customer');
>   Query1.SQL.Add('(CustName, CustPhone)');
>   Query1.SQL.Add('values ');
>   Query1.SQL.Add('(''John Smith'',''1234567'')');
>   Query1.ExecSQL;

>how can I get the value of CustID (the autoincrement field)
>from the customer table? I have thought of querying the last
>record in the table with 'Select MAX(CustID) from customer'
>however, if another user adds a record to the table in the
>mean time, my result will be incorrect. Any ideas?

Two solutions:
1) If (CustName, CustPhone) is a unique key you can use:
select CustId from customer where
CustName= "John Smith"
and CustPhone =  "12345678"

2) Usually I don't use autoincrement because there is
this problem (and other...). You can use an "exclusive"
counter table.

Hope it helps
Carlo

Re:TQuery SQL question


TTable objects are a lot easier to use with PDOX.   Then it would be....

{Assumes Table1 exists and is set up to point to customer table}
var iCustId : LongInt;
begin
  table1.Open;
  table1.Edit;
  table1['CustName'] := 'Whatever';
  table1['CustPhone'] := '1234567';
  table1.Post;
  iCustId := table1.FieldByName('CustId').AsInteger;
  table1.Close;
end;

Of course the OPEN could be at the beginnig and the CLOSE at the end of ALL
customer table activity.

Quote
Fred Troncone wrote in message

<6er1ud$vb...@reader1.reader.news.ozemail.net>...
Quote
>Just a quick Delphi question (ver 1.0) about TQuery objects

>If I have a pdox table with the following structure
>   CustID - Autoinc
>   CustName - Char[40]
>   CustPhone - Char[20]

>and want to use code to insert a new record, such as

>   Query1.SQL.Clear;
>   Query1.SQL.Add('Insert into customer');
>   Query1.SQL.Add('(CustName, CustPhone)');
>   Query1.SQL.Add('values ');
>   Query1.SQL.Add('(''John Smith'',''1234567'')');
>   Query1.ExecSQL;

>how can I get the value of CustID (the autoincrement field)
>from the customer table? I have thought of querying the last
>record in the table with 'Select MAX(CustID) from customer'
>however, if another user adds a record to the table in the
>mean time, my result will be incorrect. Any ideas?
>thanx in advance.

Re:TQuery SQL question


What I have done is something like this:

var
  t:TDateTime;
begin
  t:=now;
  Query1.Sql.Add('insert into table (Custname,CustPhone)');
  Query1.Sql.Add(' Values( '''+Str(t)+''','''+Custphone+''')');
  query1.execsql;
  query1.sql.clear;
  Query1.Sql.Add('select * from table');
  Query1.Sql.Add('where Custname='''+str(t)+'''');
  Query1.RequestLive:=True;
  Query1.open;
  Query1.edit
  Query1.fieldByName('Custname').AsString:=custname;
  Query1.Post;
end;

however, if the phone number includes area code, and you only have one
account per phone # (seems likely, but not necessarily)  then you could use
that to look it up later.  alternatively, you could use the phone + the name
to look up the key, on the assumption that you won't have two people with
exactly the same name at the same phone number (again, seems likely, but not
neccessarily the case).

chris.

Quote
Fred Troncone wrote in message

<6er1ud$vb...@reader1.reader.news.ozemail.net>...
Quote
>Just a quick Delphi question (ver 1.0) about TQuery objects

>If I have a pdox table with the following structure
>   CustID - Autoinc
>   CustName - Char[40]
>   CustPhone - Char[20]

>and want to use code to insert a new record, such as

>   Query1.SQL.Clear;
>   Query1.SQL.Add('Insert into customer');
>   Query1.SQL.Add('(CustName, CustPhone)');
>   Query1.SQL.Add('values ');
>   Query1.SQL.Add('(''John Smith'',''1234567'')');
>   Query1.ExecSQL;

>how can I get the value of CustID (the autoincrement field)
>from the customer table? I have thought of querying the last
>record in the table with 'Select MAX(CustID) from customer'
>however, if another user adds a record to the table in the
>mean time, my result will be incorrect. Any ideas?
>thanx in advance.

Other Threads