Solved

Delphi DB increment column when inserting data

Posted on 2010-11-10
16
491 Views
Last Modified: 2012-05-10
Hi guys

This is a follow on question to which i got a great answer from experts on here previously. my question is if i carry out the following procedure the database populates great but i need to take it one step further and import data into the same database tables whilst filling alternate columns but i need to pass an instruction for the destination column 1 which is the id. my first import fills these with a number sequence but this is simply copied from the local DB but the second import does not have this detail because it is child data to the first lot of father data.

so the columns of interest are columns cat_id, cat_desc, cat_father_id


//The following populates my root categorys

var
  CodeField, DescField: TField;
begin
  ADOQuery2.Close;
  ADOQuery1.Close;
  ADOQuery1.SQL.text := ('SELECT dept.deptcode, dept.description FROM dept');
  ADOQuery1.Open;

  CodeField := ADOQuery1.FieldByName('deptcode');
  DescField := ADOQuery1.FieldByName('description');

  ADOQuery2.Connection := ADOConnection2;  //your destination connection
  ADOQuery2.SQL.text := ('INSERT INTO category VALUES(:cat_id, :cat_desc, :cat_father_id)');

  ADOQuery1.DisableControls;
  while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('cat_id').Value := CodeField.Value;
    ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;
    ADOQuery2.Parameters.ParamByName('cat_father_id').Value := '0';
    ADOQuery2.ExecSQL;
    ADOQuery1.Next;
  end;
  ADOQuery1.EnableControls;
  adoconnection1.Close;
  adoconnection2.Close;
end;

//The following will populate my sub categorys and utiliizes the cat_father_id so that the software understand that they are sub departments of the master cat_id. the problem being here that although everything populates fine i am left with my cat_id ending up with some correct results and then followed by many rows of nulls being populated from this procedure which causes an issue and although i know the reason to why the nulls appear i dont know the answer to solve this issue

var
  CodeField, DescField: TField;
begin
  ADOQuery2.Close;
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.text := ('SELECT subdept.deptcode, subdept.description FROM subdept');
  ADOQuery1.Open;

  CodeField := ADOQuery1.FieldByName('deptcode');
  DescField := ADOQuery1.FieldByName('description');

  ADOQuery2.Connection := ADOConnection2;  //your destination connection
  adoquery2.SQL.Clear;
  ADOQuery2.SQL.text := ('INSERT INTO category VALUES(:cat_id, :cat_desc, :cat_father_id )');

  ADOQuery1.DisableControls;
  while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('cat_id').?????????
    ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;
    ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;
    ADOQuery2.ExecSQL;
    ADOQuery1.Next;
  end;
  ADOQuery1.EnableControls;
  adoconnection1.Close;
  adoconnection2.Close;
end;
0
Comment
Question by:TG-Steve
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 14

Expert Comment

by:systan
ID: 34100808
>>  ADOQuery2.Parameters.ParamByName('cat_id').?????????

try
if cat_id is integer;
  ADOQuery2.Parameters.ParamByName('cat_id').value = CodeField.AsInteger;
or
ADOQuery2.Parameters.ParamByName('cat_id').AsInteger = CodeField.AsInteger;

or if cat_id is string;
  ADOQuery2.Parameters.ParamByName('cat_id').value = CodeField.AsString;
or
ADOQuery2.Parameters.ParamByName('cat_id').AsString = CodeField.AsString;
0
 
LVL 14

Expert Comment

by:systan
ID: 34100848
Ops, its my bad;

try this instead above;
var

  IDField, CodeField, DescField: TField;

begin

  ADOQuery2.Close;

  ADOQuery1.Close;

  ADOQuery1.SQL.Clear;

  //you miss the ID here

  ADOQuery1.SQL.text := ('SELECT SUBDEPT.deptID, subdept.deptcode, subdept.description FROM subdept');

  ADOQuery1.Open;



  IDField := ADOQuery1.FieldByName('deptID');  

  CodeField := ADOQuery1.FieldByName('deptcode');

  DescField := ADOQuery1.FieldByName('description');



  ADOQuery2.Connection := ADOConnection2;  //your destination connection

  adoquery2.SQL.Clear;

  ADOQuery2.SQL.text := ('INSERT INTO category VALUES(:cat_id, :cat_desc, :cat_father_id )');



  ADOQuery1.DisableControls;

  while not ADOQuery1.Eof do

  begin



    //ADOQuery2.Parameters.ParamByName('cat_id').?????????

    ADOQuery2.Parameters.ParamByName('cat_id').Value := IDField.Value;



    ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;

    ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;

    ADOQuery2.ExecSQL;

    ADOQuery1.Next;

  end;

Open in new window

0
 

Author Comment

by:TG-Steve
ID: 34100962
the problem i have is that although subdept has a column 1 named cat_id i cannot use these values because they clash with the values given by the cat_id from the previous query. so lets say the first procedure fills my destination DB with 25 lines and all numbered from 1 to 25 these will determine how they display within our end user software. but if i could somehow tell the second procedure to carry on from 25 or x a number and increase incremantly so that the numbers dont duplicate that will be fine.

the cat_id from the second procedure is irrelevant in the end user software because it simply see's that example line cat_id 26 has a father id from the 3 referenced column and treats it has a child object (sub menu) but if the numbers within cat_id get duplicates then it starts causing issues the end results.

lets say i wanted to rearrange the menus within the end user software it would be basically indexing the cat_id and forcing the order of viewing but if two had the same number then it starts getting messy. so if it new they were children to father category and no errors within the cat_id then it would just work with lines 1-25 and arrange them accordingly.

i hope this makes sense and the above example is only one of the ways in which an error would occure if the cat_id's were the same.

i did think about doing this to get arround the problem
ADOQuery2.Parameters.ParamByName('cat_id').Value := '26';
within the second procedure because i am making sure it does not fall within the 1-25 and then all children will be out of that range. but i dont think thats the answer and i am simply taking the approach of plastering over a crack and not tackling the issue of giving them a numbered sequence

the cat_id column in destination table is type INT. appologies for not mentioning this factor previously. i see from the first post that this line stands out to me straight away as being on the right lines
ADOQuery2.Parameters.ParamByName('cat_id').value := CodeField.AsInteger;
i have tried this but when i look at the end results i see it has done a very good job but no understanding of the number pattern. so procedure 1 creates lines 1-25 with the correct data. procedure 2 works but after lines 25 i then see 4 lines with cat_id 1's then 4 lines of 2's then 4 lines of 3's and so on

i then tried ADOQuery2.Parameters.ParamByName('cat_id').AsInteger = CodeField.AsInteger;
but i get an undeclared identifier for the .Asinteger
at this point i am at a stop because i am very novie in this area
0
 

Author Comment

by:TG-Steve
ID: 34100965
correction on last line
*at this point i am at a stop because i am very novice in this area
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34101296
If you want to add bigger number than the one exist in the Dept database then you can do this:

  ADOQuery1.SQL.text := ('SELECT dept.deptcode, dept.description FROM dept');
  ADOQuery1.Open;
  DeptFieldsCount := ADOQuery1.RecordCount;

But that might result in duplication if your data is not following an order or not in sequence so it is better to be:

var
DeptFieldsCount := Integer;

  ADOQuery1.SQL.text := ('SELECT Max(deptcode) DeptNo FROM dept');
  ADOQuery1.Open;
  DeptFieldsCount := ADOQuery1.FieldByName('DeptNo').AsInteger;

  ADOQuery2.Parameters.ParamByName('cat_id').AsInteger := DeptFieldsCount +1;


0
 
LVL 14

Expert Comment

by:systan
ID: 34101547
Ok;
About id problem;
At your msaccess or mssql, set cat_id to auto_increment, at this time you dont have to declare a value for that ID, the dataset itself will increment the id number.

Codes like this;
var

  CodeField, DescField: TField;

begin

  ADOQuery2.Close;

  ADOQuery1.Close;

  ADOQuery1.SQL.Clear;

  ADOQuery1.SQL.text := ('SELECT deptcode, description FROM subdept');

  ADOQuery1.Open;



ADOQuery2.Connection := ADOConnection2;connection

adoquery2.SQL.Clear;

ADOQuery2.SQL.text := ('INSERT INTO category VALUES(:cat_id, :cat_desc, :cat_father_id )');



ADOQuery1.DisableControls;

while not ADOQuery1.Eof do

  begin



  CodeField := ADOQuery1.FieldByName('deptcode');

  DescField := ADOQuery1.FieldByName('description');



//This can be auto_incremented field in you database, so dont declare

//ADOQuery2.Parameters.ParamByName('cat_id').Value := IDField.Value;



ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;

ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;





ADOQuery2.ExecSQL;

ADOQuery2.Parameters.CLEAR;





ADOQuery1.Next;

end;

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34101993
var
  CodeField, DescField: TField;
  RollingID: Integer;
begin
  ADOQuery2.Close;
  ADOQuery2.SQL.Text := 'SELECT MAX(cat_id) FROM category';
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('cat_id').AsInteger
  else
    RollingID := 1;
  ADOQuery2.Close;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  //you miss the ID here
  ADOQuery1.SQL.text := ('SELECT SUBDEPT.deptID, subdept.deptcode, subdept.description FROM subdept');
  ADOQuery1.Open;

  IDField := ADOQuery1.FieldByName('deptID');
  CodeField := ADOQuery1.FieldByName('deptcode');
  DescField := ADOQuery1.FieldByName('description');

  ADOQuery2.Connection := ADOConnection2;  //your destination connection
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.text := ('INSERT INTO category (cat_id, cat_desc, cat_father_id) VALUES(:cat_id, :cat_desc, :cat_father_id )');

  ADOQuery1.DisableControls;
  while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('cat_id').Value := RollingID;
    Inc(StartingID);
    ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;
    ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;
    ADOQuery2.ExecSQL;
    ADOQuery1.Next;
  end;

end;
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34102004
var
  CodeField, DescField: TField;
  RollingID: Integer;
begin
  ADOQuery2.Close;
  ADOQuery2.SQL.Text := 'SELECT MAX(cat_id) FROM category';
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('cat_id').AsInteger
  else
    RollingID := 1;
  ADOQuery2.Close;

  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  //you miss the ID here
  ADOQuery1.SQL.text := ('SELECT SUBDEPT.deptID, subdept.deptcode, subdept.description FROM subdept');
  ADOQuery1.Open;

  CodeField := ADOQuery1.FieldByName('deptcode');
  DescField := ADOQuery1.FieldByName('description');

  ADOQuery2.Connection := ADOConnection2;  //your destination connection
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.text := ('INSERT INTO category (cat_id, cat_desc, cat_father_id) VALUES(:cat_id, :cat_desc, :cat_father_id )');

  ADOQuery1.DisableControls;
  while not ADOQuery1.Eof do
  begin
    ADOQuery2.Parameters.ParamByName('cat_id').Value := RollingID;
    Inc(RollingID);
    ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;
    ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;
    ADOQuery2.ExecSQL;
    ADOQuery1.Next;
  end;

end;
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 14

Expert Comment

by:systan
ID: 34103467
hi
in connection of my last post;
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_26604454.html#34101547

I think you should put your variables inside the loop, to get records currently

while not ADOQuery1.Eof do
  begin

 //Here
  CodeField := ADOQuery1.FieldByName('deptcode');
  DescField := ADOQuery1.FieldByName('description');

//Leave this commented. //This can be auto_incremented field in you database, so dont declare
//ADOQuery2.Parameters.ParamByName('cat_id').Value := IDField.Value;

ADOQuery2.Parameters.ParamByName('cat_father_id').Value := CodeField.Value;
ADOQuery2.Parameters.ParamByName('cat_desc').Value := DescField.Value;

...
...
...
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34104527
@systan

You don't put the fields inside the loop, the CodeField and DescField are just pointers so just set them once outside the loop

...........
CodeField := ADOQuery1.FieldByName('deptcode');
DescField := ADOQuery1.FieldByName('description');

while not ADOQuery1.Eof do
begin
  ..............
0
 

Author Comment

by:TG-Steve
ID: 34104774
ewangoya:

i tried the procedure in 34102004 but i am getting an exception error adoquery2:field 'cat_id' not found on line     RollingID := ADOQuery2.FieldByname('cat_id').AsInteger

systan:

sorry i have not tried your code yet because another expert as found possible issue arrising from the code change. please could you confirm this

regards
steve
0
 
LVL 14

Expert Comment

by:systan
ID: 34104816
Ah, Ok;
I forgot to put ?

I think  ?


Thanks
But auto_increment should work on his case.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34104874
Ohh, you have to name the field

change it to

ADOQuery2.SQL.Text := 'SELECT MAX(cat_id) AS CNT FROM category';
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('CNT').AsInteger
  else
    RollingID := 1;
  ADOQuery2.Close;
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 500 total points
ID: 34104897
Actually a better name would be NEXTID, then add 1 to it

ADOQuery2.SQL.Text := 'SELECT MAX(cat_id) AS NEXTID FROM category';
  ADOQuery2.Active := True;
  if not ADOQuery2.IsEmpty then
    RollingID := ADOQuery2.FieldByname('NEXTID').AsInteger + 1
  else
    RollingID := 1;
  ADOQuery2.Close;
0
 

Author Closing Comment

by:TG-Steve
ID: 34105059
Thank you guys for all the input. i was just about to ask the question that when this procedure runs it i see two lines with number 25 but then from the last post i see there as been a change 'NEXTID').AsInteger + 1
which now works perfect.

ewangoya i did get another exception in line
ADOQuery1.SQL.text := ('SELECT SUBDEPT.deptID, subdept.deptcode, subdept.description FROM subdept');

it was with the subdept.deptID, quote so i removed just that part and it seems to be working perfect. i presume this is safe to remove
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34105144
Correct
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

21 Experts available now in Live!

Get 1:1 Help Now