Solved

Delphi DB increment column when inserting data

Posted on 2010-11-10
16
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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:Ephraim Wangoya
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:
Ephraim Wangoya 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
 
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:Ephraim Wangoya
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:Ephraim Wangoya
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:Ephraim Wangoya
Ephraim Wangoya 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:Ephraim Wangoya
ID: 34105144
Correct
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 97
How to build JSON File in Delphi 6 3 94
update joined tables 2 73
Delphi TListView and TEdit (Select item and input into TEdit) 10 81
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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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