Need some help with Delphi and MS Access

I am developing an Asset tracking system in Delphi 6, with an Access DB as a backend.

Basically I have two tables, one called ASSETS, one called COMPONENTS. Basically an Asset can have many Components, therefore ASSETS is the Master, COMPONENTS is the child.

The following Table structures Apply

[ASSETS]
AssetID                                AUTONUMBER
AssetNo                               TEXT
AssetType                            TEXT
................ other fields not needed


[COMPONENTS]
AssetID                                INT
ComponentID                       AUTONUMBER
ComponentType                   TEXT
ComponentDescription          TEXT
ComponentSerial                  TEXT
...................... other fields not needed


What I want to do is, be able to Insert a Record in the ASSET table, GET the AssetID Number, and then use this to Insert multiple records in the COMPONENTS Table. I have thought about using a query to Get the MAXIMUM value, the problem with this is, because the Application will be accessed by Multiple users, Many staff could be adding records at the same time, so how do I get the Current AssetID No for the Current User.

I am using the following code to Insert Records in the ASSET Table at the moment

  if rbComp.Checked then sType:='COMPUTER';
  if rbNoteBk.Checked then sType:='NOTEBOOK';
  if rbPrinter.Checked then sType:='PRINTER';

  sSQL:=''+
           'INSERT INTO detsAssets ( AssetNo, AssetType)'+#13+
           'VALUES ("'+sAsset+'", "'+sType+'"')';

  dmLookups.qWork.Close;
  dmLookups.qWork.SQL.Text:=sSQL;
  dmLookups.qWork.ExecSQL;

Therefore, when I do an INSERT, I want to get the Appropriate AssetID. I have thought about using the MAX and the AssetNo, BUT the AssetNo Field could contain BLANK values.


Any ideas would be greatly appreciated

Regards
PJE
pjeliasAsked:
Who is Participating?
 
fidel83Commented:
pjelias, it really isnt a good idea to use max when working with autonumbers. here's the way to ensure you are getting the id of the recently inserted record - we use a dataset:


function storeMessage(fromUser, toUser, thisMsg: string): string;
var
ds: TADODataSet;
begin
        ds:=nil;

        try
                //We use a dataset so we can return the
                //id of the inserted record.

                ds:=TADODataSet.Create(nil);
                ds.Connection:=form1.ADOConnection1;

                //use some statement that returns nothing
                ds.CommandText:='select * from messages where id = -1';
                ds.Open;
                ds.insert;

                ds.FieldByName('FROM_USER').Value:=fromUser;
                ds.FieldByName('TO_USER').Value:=toUser;
                ds.FieldByName('MESSAGE').Value:=form1.base64Encoder.Encode(thisMsg);

                ds.UpdateBatch(arCurrent);

                result:=(ds.FieldByName('id').Value);
        except
                result:='-1';
        end;

        try
                ds.Close;
                ds.Free;
        except

        end;
end;




cheers,
fidel
0
 
VoodoomanCommented:
Hi

How this is normally done is simple. Inserting the asset with a query is the wrong way to go.

You have two datasets one for assetts and one for components.

You add a record to the assett dataset and save it. Once you have saved it you are left sitting on the record you have created (as long as you havent done any record move or refresh actions).

Now you can get the record id

getId:=dsAssett.fieldValues['ID'];     //or whatever your ID field is

its up to you how to add multiple components, I would use  a recordset rather than a query myself - normally this would be a simple form with an Add and Cancel button to add lines to the Components.

Voodooman

0
 
geobulCommented:
Hi,

If AssetNo value is unique (or AssetNo + AssetType) then you may find that record using SELECT query immediately after the insert

  dmLookups.qWork.Close;
  dmLookups.qWork.SQL.Text:='SELECT AssetID FROM assets WHERE AssetNo = ' + QuotedStr(sAsset) + ' AND AssetType = ' + QuotedStr(sType);
  dmLookups.qWork.Open;
  if not dmLookups.qWork.eof then longintAssetId := dmLookups.qWork.Fields[0].AsInteger
  else ... problem with the previous insert;
  dmLookups.qWork.Close;

Regards, Geo
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
esoftbgCommented:
download a super example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21151903.zip

//........

procedure TForm1.spbTestClick(Sender: TObject);
var
  sType:  string;
  sAsset: string;
begin
  sAsset := FormatDateTime('HHmmss', Now);
  dbgASSETS.Enabled := False;
  dbgCOMPONENTS.Enabled := False;
  try
    adoqASSETS.Append;
    if rbComp.Checked then
      sType:='COMPUTER';
    if rbNoteBk.Checked then
      sType:='NOTEBOOK';
    if rbPrinter.Checked then
      sType:='PRINTER';
    adoqASSETS.FieldByName('AssetNo').AsString := sAsset;
    adoqASSETS.FieldByName('AssetType').AsString := sType;
    adoqASSETS.Post;

    adoqCOMPONENTS.Append;
    adoqCOMPONENTS.FieldByName('ComponentType').AsString := 'MOTHERBOARD';
    adoqCOMPONENTS.FieldByName('ComponentDescription').AsString := 'INTEL 845 PE';
    adoqCOMPONENTS.FieldByName('ComponentSerial').AsString := FormatDateTime('yyyyMMddHHmmss', Now);
    adoqCOMPONENTS.Post;

    adoqCOMPONENTS.Append;
    adoqCOMPONENTS.FieldByName('ComponentType').AsString := 'CPU';
    adoqCOMPONENTS.FieldByName('ComponentDescription').AsString := 'INTEL';
    adoqCOMPONENTS.FieldByName('ComponentSerial').AsString := FormatDateTime('yyyyMMddHHmmss', Now);
    adoqCOMPONENTS.Post;

    adoqCOMPONENTS.Append;
    adoqCOMPONENTS.FieldByName('ComponentType').AsString := 'HDD';
    adoqCOMPONENTS.FieldByName('ComponentDescription').AsString := 'QUANTUM';
    adoqCOMPONENTS.FieldByName('ComponentSerial').AsString := FormatDateTime('yyyyMMddHHmmss', Now);
    adoqCOMPONENTS.Post;
  finally
    dbgCOMPONENTS.Enabled := True;
    dbgASSETS.Enabled := True;
  end;
end;
0
 
BalsheCommented:
do you have to use Autonumber?
because you can simulate "Sequences" oracle by doing the following:
-open a table for last used values          
           Tb_Sequences
                                  Field_Name       Field_Value
                                  Assets                 1
                                  .....                     ....
                                  .....                     ....

now before adding a record to Assests ,you get the last value from "TB_Sequences" and increment it by one and update table "TB_Sequences"

           Tb_Sequences
                                  Field_Name       Field_Value
                                  Assets                 2
                                  .....                     ....
                                  .....                     ....

and add use this value in table "Assests" and "Components"


0
 
ranjfoadCommented:
for the ASSETID use ReplicationID (in the both tables) (not autonumber)
when you want to add a record first get a GUID (Replication id) using vb code (see http://www.buygold.net/v01n01/guid.zip)
using the GUID you got insert the master table record then the detail.
0
 
ranjfoadCommented:
sorry i thought we are talking about vb, but any way you can use the same api functions used in the vb code to get a GUID.
0
 
alc4emy5tCommented:
Well i dont know much about MS Access but in MySQL you can do the following

LOCK TABLE ASSETS WRITE;

  Insert into Assets(...);
  Select MAX(AssetID) From Assets;

UNLOCK TABLES;

This means other thread will have to wait for the lock to be released doesnt matter they are reading or writting.  

Anyway in a multi user environment you should use a more suitabe Database list mysql, postgresql, etc.  i worked with ms access for about 2 years, just maintaining the system so i dont know much about it but i found it preformed unreliably in an office with about 8 to 10 staff so you might want to consider upgrading.
0
 
VoodoomanCommented:
Hi

The question was

<<What I want to do is, be able to Insert a Record in the ASSET table, GET the AssetID Number, and then use this to Insert multiple records in the COMPONENTS Table. .....so how do I get the Current AssetID No for the Current User.>>

I said:

<<..add a record to the assett dataset and save it. Once you have saved it you are left sitting on the record you have created (as long as you havent done any record move or refresh actions).>>

You then have immediate access to the assett idea and you can post other records into the Component table.

There are no record locking issues around this - although there are around editing these records.

When editing the recs, use unbound controls and you will get a concurrency error when posting if the records have been changed or deleted by another user.

Voodooman
0
 
esoftbgCommented:
Voodooman,
you describe (by words) at 10/07/2004 my full working example from 10/01/2004 (that could be downloaded and tested) ....
;-))
0
 
VoodoomanCommented:
Hi esoftbg

I described this solution firstly on 10/01/2004 (see the top of the topic).

You and I agree that this is the simplest and best way to go.

I was not intending to criticize you in any way - my apologies if you were offended.

My comment was only to mean that the question was moving away into abstract areas of record locking that are of no consqequence to the question.

I also notice that the questioner pjelias has not participated in the discussion and has not been polite enough to accept any answer.

Voodooman
0
 
esoftbgCommented:
It's my mistake Voodooman,
I did read only your comment from 10/07/2004 .... Where was my eyes ?
Please excuse me !
0
 
VoodoomanCommented:
Hi esoftbg

Good to talk.... see you around EE

Thanks

Voodooman
0
 
alc4emy5tCommented:
Or another simple was is to put a userid field into assets and just do:

Select the MAX(AssetID) where UserID = 'Bob';

then insert into Components values(...);

not sure if others agree with this method but its a suggestion
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.