Solved

Please help with workaround on primary key as autoinc

Posted on 2004-10-14
18
483 Views
Last Modified: 2012-05-05
Hi all,

I have a table with a primary key which is of type uniqueidentifier and its value get set by the database (MSSQL2000) automatically. (Similar in concept to autoinc field)

I want to refresh the recordset without losing my place, so that I can do edits etc immediately after an insert.

As workaround I have doen the following, but am a bit stuck at the last step:

1) Defined another unique index on some other fields (in my case, type and name)
2) Set the TADOTable.IndexName to use this index.
3) Defined a global variable in the DataModel called LastUpdateState of TDataSetState;
4) Defined the following in the Before Post event:   LastUpdateState := DataSet.State;
5) Final step, do the following in the After Post event. I KEEP ON GETTING LIST 'LIST INDEX OUT OF BOUNDS(0)'

procedure TdmMain.ADOTable1AfterPost(DataSet: TDataSet);
var
  I: Integer;
  strKeyFields: string;
  vKeyValues: variant;
begin
  if LastUpdateState = dsInsert then
  begin
    if DataSet is TADOTable then
    begin
      with TADOTable(DataSet) do
      begin
        if IndexName <> '' then
        begin
          disablecontrols;

          strKeyFields := IndexFields[0].FieldName;
          vKeyValues := vararrayof([IndexFields[0].CurValue]);
          for I := 1 to IndexFieldCount - 1 do    // Iterate
          begin
            strKeyFields := strKeyFields + ';' + IndexFields[I].FieldName;
            vKeyValues := vararrayof([vKeyValues, IndexFields[I].CurValue]);
          end;    // for

          requery;
          Locate (strKeyFields, vKeyValues, [loCaseInsensitive]);
          enablecontrols;
        end;    // if
      end;    // with
    end;    // if
  end;    // if
end;

{Please note that this procedure is not complete as I want to implement something similar for where the LastUpdateState was dsEdit. For the dsEdit state I want to use the Primary key fields to do the refresh since this field may not change.}

Regards,
Johan Swart

PS Any comments will be much appreciated!!!!



0
Comment
Question by:johan777
  • 7
  • 5
  • 2
  • +3
18 Comments
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 12312425
put a breakpoint at the beginning of this procedure, then step through the code to see *exactly* which statement raises the Out of Bounds exception?
0
 

Author Comment

by:johan777
ID: 12313253
Hi DragonSlayer,

That the funny of it. It falls out on the following line (which does not make sense), but then it jumps to the Post statement where it falls out.  

falls out on:
          strKeyFields := IndexFields[0].FieldName;

It seems the internal Post event is expecting more or less fields or fieldvalues than the number I pass on.

I'm still baffled.

Regards,
Johan Swart
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12313545
may be will be simpler to use
GetBookmark
GotoBookmark
FreeBookmark
0
 
LVL 17

Expert Comment

by:mokule
ID: 12314077
Hi,
maybe You can create stored procedure for retrieving lastly inserted identity to database. Something like

CREATE PROCEDURE GetIdentity AS
SET NOCOUNT ON
RETURN @@IDENTITY
SET NOCOUNT OFF
GO

And then use this identity for locating the required record
0
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 12315826
Erm... this sounds silly, but did you specify the Index at the Object Inspector?

> mokule
It's actually quite possible for the stored procedure to return the wrong value. Imagine if there were a few concurrent INSERTs from multi-users, that way, the "last inserted identity" would not be the right value.

> johan
Therefore, instead of auto-inc, what I do nowadays is to use GUID as my primary key. I will generate the GUID at client-side and INSERT it together with my INSERT SQL. There have been cases where I created a stored procedure just to return a GUID as well, for easier maintenance. In any case, by using the GUID, after INSERTing, you can do a ReQuery with the same GUID as parameter.
0
 

Author Comment

by:johan777
ID: 12316689
Hi all,

The Bookmark does not work as it uses the primary key to define the bookmark. Since this is the value that was changed on the server. I agree this would have been the easiest, .. if it worked :-)

I specified the index on the Object Inspector (a unique multi-field index), so the problem is not that either. :-s

mokule, I agree with DragonSlayer, using a stored proc opens up the possibility of concurrency issues. This is a multi-user system :-(

DragonSlayer, that is exactly what I am trying to do! I also prefer to use GUIDs rather than Autoinc. I figured out the same workaround you have just suggested late last night (01:00 GMT+2) and if I cannot get this to work, I will use this. I created a query that executes 'select newid()' to reurn the next GUID to use. I then execute this query in the BeforePost event of the ADOTable coponent. However, I do not like the workaround for two reasons:
a) This now makes the insertion dependent on the client-side again and I would have liked to keep the database manipulations together on the server.
b) By allowing the client to specify the GUID, it opens up the database to hacking as what would prevent a malicious person to write an app in which the GUID can be supplied from the front-end.

Just to explain: I have set triggers on the database that ignores any GUID passed on an insert and populating it in the trigger. This is the safest way to control the GUIDs as even the MS preferred way (default newid()) does not force a correct value in the field. For instance, by using Query Analyzer, you may specify any value for a GUID as part of an insert and it will be accepted (as long as it is unique in my case).

I have a feeling we are digging too deep. My feeling is that there is something worng with the way I am using variants etc? Anyone have a take on that?

Thanx for the help so far!

Regards,
Johan Swart
0
 
LVL 17

Expert Comment

by:geobul
ID: 12317108
Hi,

Perhaps I'm wrong but can't you read that unique field value after the insert in AfterInsert event? At that moment you are on the new record and the new value should be already generated and visible (you're using a trigger I suppose).

TheNewUniqueID := ADOTable1.FieldByName('YourAutoIncField').AsWhateverItIs;

and use that value in AfterPost for locating after requering. You won't need an additional index if the above works.

Regards, Geo
0
 

Author Comment

by:johan777
ID: 12317412
Hi Geo,

My understanding is that the AfterInsert event kicks in after setting the DataSet into insert mode. This means that nothing has been populated or posted to the server, the DataSet has only been made ready to receive inputs. Only when the Post method gets called does the data get written to the server upon which the server (using triggers or defaults) change the data.

I hope I'm wrong, but I think not .. thanx for the suggetion.

Anymore?

Regards,
Johan Swart
0
 
LVL 17

Expert Comment

by:geobul
ID: 12317592
I don't think so. Insert method creates a new record and sets the cursor to that record (therefore an insert trigger should be performed). Just try it.

You may also try reading that field value in AfterPost and see if you'll get something meaningful. I hope you will.

Regards, Geo
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:mokule
ID: 12317793
Hi,
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope

So @@IDENTITY returns the last identity in the current session.
It works OK for multiuser.
0
 
LVL 17

Expert Comment

by:geobul
ID: 12318316
mokule, you are, of course, right if we are speaking about standard identity field generated by the SQL server itself. As far as I understand there is a trigger that calculates a (let say integer) field value using custom algorithm and that field is not IDENTITY field actually.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12357579
Johan,

1. This seems like a GUI sync problem to me.  Am I correct or not in my perception.
2. I assume you want the user to be able to change the newly inserted row as opposed to programmatically changing the new row's data.  Since ADO automatically repositions you to the newly inserted row, there is no need to navigate to that row.

==================
Something to think about and try...
1. insert your new row
2. inspect the new row's autoinc column and save that number
3. (optional) refresh the GUI, if necessary to reflect the new recordset
4. use an ADO Find method to reposition to the new row, based on the saved autoinc value (from 2 above).

Note: if you do your new record append through the GUI's recordset, then you might be able to simplify this process.  Of course, you can't use dynamic SQL or stored queries for this configuration, which presents other problems such as upsizing.
0
 
LVL 17

Expert Comment

by:geobul
ID: 12357799
aikimark, isn't it the same I've already explained?
0
 

Author Comment

by:johan777
ID: 12362696
Hi all,

PLEASE READ THE ENTIRE RESPONSE AS IT RESPONDS TO ALL PREVIOUS RESPONSES.

An update:

In summary:
---------------
1) To simplify the explanation, lets say that the primary key field is a varchar(16) and that it gets generated by the database server automatically upon an insert.
2) So, yes, it is a GUI sync problem.
3) No, I cannot read the value from the front-end during the insertion as the value gets generated upon the physical writing of the record to the database (during the Post event).
4) Because the PK value thus changed upon writing it to the database (from null to a value), ADO and thus the GUI front-end has no way of knowing where to find the record. It also does not reposition to the new record as it needs a non-changing PK to be able to do that.
5) Using bookmarks did not work either and I suspect that the bookmark functionality also uses the primary key.

I have a successful workaround in the following:
-------------------------------------------------------
1) In the AfterInsert event I query the database for a valid new primary key (this key will never be duplicated ever as guaranteed by Microsoft).
2) I then insert this value into the primary key field abd voila it works!
3) This workararound let me continue, BUT it has a security flaw.
4) The implementation allows for the insertion of a record with a specific primary key value which can then again be used for further malicious practices by an user out to do harm.

What I am trying to do is:
------------------------------
1) I assume that all my tables have a unique index (with one or more fields) over and above the primary key.
2) Then upon an insert, I allow the server to populate the primary key
3) ,but I obtain a list of the values of the unique index in the BeforePost event.
4) In the AfterPost event I then do a locate using these values and voila it works!

My problem is that I got it all working up to the point where I do the locate. The program then complains about my ArrayofVar that is not set up correctly.

So, if I can sort out this  issue I have a viable workaround that does not pose a security thread and still works exactly as planned. That is why I suspect my issue is not so complicated as a design flaw, but rather a tricky syntax issue.

Whew! Sorry for the long response, I'm only trying to be as complete as possible.

Regards,
Johan Swart
0
 
LVL 17

Expert Comment

by:geobul
ID: 12367302
Congratulations, if all questioneers were like you ... ;-)

About reading that unique value in AfterPost event of the ADOTable: CursorLocation property of the table is set to clUseClient in your case. Set it to clUseServer (if that is possible with your configuration) and you should be able to read it. A simple example follows (using clUseServer):

procedure TForm1.Button1Click(Sender: TObject);
begin
  with ADOTable1 do begin
    Insert;
    FieldByName('name').AsString := Edit1.Text;
    Post;
  end;
end;

procedure TForm1.ADOTable1AfterPost(DataSet: TDataSet);
begin
  Edit2.Text := IntToStr(DataSet.FieldByName('id').AsInteger); // here I'm getting the new value displayed
end;

Regards, Geo
0
 
LVL 17

Expert Comment

by:geobul
ID: 12367493
And for your VarArrayOf problem you may try something like:

var
  I: Integer;
  strKeyFields: string;
  vKeyValues: variant;
begin
  ...
  strKeyFields := IndexFields[0].FieldName;
  vKeyValues := VarArrayCreate([0..IndexFieldCount-1], varVariant);
  vKeyValues := IndexFields[0].CurValue;
  for I := 1 to IndexFieldCount - 1 do    // Iterate
    strKeyFields := strKeyFields + ';' + IndexFields[I].FieldName;
    vKeyValues[i] := IndexFields[I].CurValue;
  end;    // for
  ...
  Locate (strKeyFields, vKeyValues, [loCaseInsensitive]);
  ...
end;
0
 
LVL 17

Accepted Solution

by:
geobul earned 500 total points
ID: 12367503
vKeyValues := IndexFields[0].CurValue;
above should be:
vKeyValues[0] := IndexFields[0].CurValue;

Regards, Geo
0
 

Author Comment

by:johan777
ID: 12565182
Hi all,

I have through trial and error found out that the VarArryOf works differently when all the elements are of type string than when the elements are of mixed type.

This was as I suspected my only problem and I now have a working solution whereby I request the next unique key from the database in the BeforePost.

Thanx for the help on this one all.

Regards,
johan Swart

PS Sorry for taking so long to answer, but the project I'm on kept me quite busy.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
delphi exception 7 58
Printing problem 2 72
Print Graphic and Text to Epson TM-T88v 12 113
Unique identifier on a terminal server (rdp) 4 28
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now