I'm converting a small program from using table to using all queries. The
database uses all autoinc columns for primary keys, after doing an insert
what is the best method of getting back the new primary key?
Thanks
Randy

Randy,

<< I'm converting a small program from using table to using all queries. The
database uses all autoinc columns for primary keys, after doing an insert
what is the best method of getting back the new primary key? >>

Are you using an INSERT statement to perform the insert ?  If so, then just
parameterize the insert like this:

INSERT INTO MyTable VALUES (:MyIdentityValue, etc.....)

After executing the INSERT with a TEDBQuery, you can use this to retrieve
the assigned value:

MyEDBQuery.ParamByName('MyIdentityValue').AsInteger

--
Tim Young
Elevate Software
www.elevatesoft.com

Okay, I'll bite.  How do you get the AutoInc value if you're doing an insert with databound controls (with a TEDBQuery and TDatasource)?

I tried the code below, but the value in "doc" is always zero.


procedure TDocumentsForm.DocumentQueryAfterPost(DataSet: TDataSet);
var
 doc: integer;
begin
 doc := DataSet.FieldByName('DocumentID').AsInteger;
end;

Greg,

<< Okay, I'll bite.  How do you get the AutoInc value if you're doing an
insert with databound controls (with a TEDBQuery and TDatasource)?

I tried the code below, but the value in "doc" is always zero.

procedure TDocumentsForm.DocumentQueryAfterPost(DataSet: TDataSet);
var
 doc: integer;
begin
 doc := DataSet.FieldByName('DocumentID').AsInteger;
end; >>

That code should work.  Are you sure that the identity column (autoinc) is
actually being assigned a value ?  In EDB, an identity column can be defined
so that it is generated by default (if the column is NULL) or always - which
way did you define the identity column ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> That code should work.  Are you sure that the identity column (autoinc) is
> actually being assigned a value ?  In EDB, an identity column can be
> defined so that it is generated by default (if the column is NULL) or
> always - which way did you define the identity column ?

Hi, Tim:

The DocumentID identity/autoinc column is getting assinged a value that is
incremented by one each time a new row is created.  The DocumentID field is
"Generated as Identity / By Default Only".  The database was created by
importing from Access 2003 if that provides any hints.  The contents of the
TEDBQuery.SQL property is:

SELECT * FROM Documents
WHERE (DocumentID IN
 (SELECT DocumentID FROM Document2Site
 WHERE SiteID = :SiteID))

Greg

Greg,

<< The DocumentID identity/autoinc column is getting assinged a value that
is incremented by one each time a new row is created.  The DocumentID field
is "Generated as Identity / By Default Only".  The database was created by
importing from Access 2003 if that provides any hints.  The contents of the
TEDBQuery.SQL property is: >>

Okay, I'm a bit confused now.   How are you inserting the rows ?  Via SQL or
via code ?  If via code, are you inserting a row in a query result set ?  If
that is true, is the query the one that you posted in the last message ?
That query will generate an insensitive result set due to the correlated
sub-query (it's just like a join, basically), therefore you shouldn't be
able to insert any rows into the result set since it should be read-only.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim:

I have a DevExpress cxGrid attached to a DataSource component that is
attached to an EDBQuery component.  At runtime, in the cxGrid component, I
click on the "Insert" button (part of their built-in navigator) and a row is
inserted.  When I check later with the ElevateDB Manager, the rows in fact
are inserted.

By the way, if I create a View in the ElevateDB Manager and use the
following code, ElevateDB Manager will allow me to insert a row also (all I
changed was the Param to a hard-coded "10"):

 SELECT * FROM Documents
 WHERE (DocumentID IN
   (SELECT DocumentID FROM Document2Site
   WHERE SiteID = 10))

The database for this is still very much a work-in-progress, but it contains
some confidential data.  So, instead of posting it to the binaries group,
I'm e-mailing you the database along with the catalog file with a subject
line of "Re: Table to Query".

I was under the impression that the query I provided *should* result in a
sensitive set (which is what I'm after) --- that the WHERE clause is
basically just a filter.

Thanks for your help.

Greg

Greg,

<< By the way, if I create a View in the ElevateDB Manager and use the
following code, ElevateDB Manager will allow me to insert a row also (all I
changed was the Param to a hard-coded "10"): >>

Crap, I'm sorry.   I mis-read the parameter comparison as being a correlated
sub-query.  Yes, the parameter and the constant should both be able to
return a sensitive result set provided that you set the
TEDBQuery.RequestSensitive property to True.

<< The database for this is still very much a work-in-progress, but it
contains some confidential data.  So, instead of posting it to the binaries
group, I'm e-mailing you the database along with the catalog file with a
subject line of "Re: Table to Query". >>

Hmmm, with the TEDBQuery.RequestSensitive property set to True, this query
returns a live result set in the 1.08 release:

SELECT * FROM Documents
WHERE (DocumentID IN
(SELECT DocumentID FROM Document2Site
WHERE SiteID = :SiteID))

--
Tim Young
Elevate Software
www.elevatesoft.com

> Hmmm, with the TEDBQuery.RequestSensitive property set to True, this query
> returns a live result set in the 1.08 release:

I was using the 1.07b3 release when I first posted.  I just upgraded to the
1.08b1 release.  I have TEDBQuery.RequestSensitive set to True and I am
getting a live result set.  This still leaves the issue of not being able to
retrieve the primary key/autoinc value correctly as in the code sample below
(in 1.07b3 doc was always assigned a value of zero; in 1.08b1, doc appears
to be assigned the value of whatever row was selected prior to the insert):

procedure TDocumentsForm.DocumentQueryAfterPost(DataSet: TDataSet);
var
 doc: integer;
begin
 doc := DataSet.FieldByName('DocumentID').AsInteger;
end;

If I modify the TEDBQuery.SQL property to be "SELECT * FROM Documents" then
the code above works correctly.  The probelm appears to be with the WHERE
clause:

SELECT * FROM Documents
WHERE (DocumentID IN
 (SELECT DocumentID FROM Document2Site
 WHERE SiteID = :SiteID))

Any suggestions?

Thanks.

Greg

Greg,

<< If I modify the TEDBQuery.SQL property to be "SELECT * FROM Documents"
then the code above works correctly.  The probelm appears to be with the
WHERE clause: >>

Are you trying to insert a row that falls outside of the WHERE conditions ?
If so, then the AfterPost will see the current row that falls within the
query conditions, not the newly-inserted row.   You can set the
TEDBQuery.Constrained property to True to have EDB reject any insert that
would cause such a situation.

--
Tim Young
Elevate Software
www.elevatesoft.com