Solved

Delphi DB increment column when inserting data

Posted on 2010-11-10
16
485 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
Comment Utility
>>  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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
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 14

Expert Comment

by:systan
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
Ah, Ok;
I forgot to put ?

I think  ?


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

Expert Comment

by:ewangoya
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Correct
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now