Solved

Need some help with Delphi and MS Access

Posted on 2004-09-30
14
237 Views
Last Modified: 2010-04-05
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
0
Comment
Question by:pjelias
  • 4
  • 3
  • 2
  • +4
14 Comments
 
LVL 5

Expert Comment

by:Voodooman
ID: 12198060
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
 
LVL 17

Expert Comment

by:geobul
ID: 12198195
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12198265
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
 
LVL 1

Expert Comment

by:Balshe
ID: 12199819
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
 
LVL 1

Expert Comment

by:ranjfoad
ID: 12206520
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
 
LVL 1

Expert Comment

by:ranjfoad
ID: 12206587
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
 

Expert Comment

by:alc4emy5t
ID: 12245332
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 5

Expert Comment

by:Voodooman
ID: 12246699
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
 
LVL 12

Expert Comment

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

Expert Comment

by:Voodooman
ID: 12270480
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12272515
It's my mistake Voodooman,
I did read only your comment from 10/07/2004 .... Where was my eyes ?
Please excuse me !
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 12272792
Hi esoftbg

Good to talk.... see you around EE

Thanks

Voodooman
0
 

Expert Comment

by:alc4emy5t
ID: 12272817
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
 
LVL 1

Accepted Solution

by:
fidel83 earned 125 total points
ID: 12859762
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

15 Experts available now in Live!

Get 1:1 Help Now