clientdataset - text file ... Applyupdates for join table

[beginner81]

i'm too new with delphi programming.. and i'm curious is that anyway for me to copy the .txt file to the clientdataset??

and one more question here.. i'm get all the value(for each field[TEdit]) in a form to a single clientdataset.. and i need to apply updates the clientdataset to several table (join tables) in the server.. so how am i going to do it.. i can't simply put applyupdates method, cos different field in clientdataset is designated for different table in the server...

thanks for ur help.. ur help will b appreciated.. and yeah.. sorry for my english.. cos it's not my main language..

 

[beginner81]

i think i need to make it clear since i'd found it a bit confusing to understand...

i'm doing a program now.. and the client did give me a txt file .. so i need to extract the data from the file, and post in to Clientdataset.. so is that anyway for me to do so ??

And then, the data in that clientdataset is going to applyupdates to several tables in the server.. so what should i do then ??
example: the clientdataset has the field like CustomerID, Customer Name, Product_ID, productdetails and etc... so i'm going to update CustomerID and Customer Name field from clientdataset to Customer tables in server.. and i need to update product_id and product description field to PRODUCT table in the server..
so what should i do ??

I hope u guys get what i mean with the example i'm giving.. and thx alot..

 

[beginner81]

can some one help me to answer this question.. i'm really in crying need for it .... tyhx in advance..

 

[DaZZleD]

you need to make a program to read the txt file and assign values to variables for each of the fields you need to insert to the db. the way you make the actual insertion in the db depends on the server you're using. once you get that figured out, all you need is to insert the values you read from the txt in the tables you need them.

be more specific about the db server type and the structure of the txt if you need a code snippet.

 

[beginner81]

you need to make a program to read the txt file and assign values to variables for
each of the fields you need to insert to the db. the way you make the actual insertion]
in the db depends on the server you're using. once you get that figured out, all you need
is to insert the values you read from the txt in the tables you need them.

be more specific about the db server type and the structure of the txt if you need a code snippet.


thanks in advance...
ok.. the server type that i'm using is Microsoft SQL.. and i'm link it with the following 5 componenets..
1) TSQLConnection
2) TSQLDataset
3) TDataSetProvider
4) TClientDataSet
5) TDataSource

i guess u know how each of them is linking since u r more expert than me..

the text file that the client giving to me is like the followingi just take some of the field as example):


CustID CustName Country ItemCode ItemDesc
"Cust001", "John", "Japan", "Item001", "Shirt"
"Cust002", "Ken" , "USA ", "Item002", "Book"


P/S: In the SQL Server, i'd create 2 table which is CUSTOMER and PRODUCT.. and i need to some modifying of the data in the
text file that they giving to me b4 i update it to the server.. that's why i'm using ClientDataset to temporary hold the data
and then goin to update it to the server after the modifying is made.... i hope u can help
me wif some code cos i'm too new in delphi.. thanks alot...

 

[beginner81]

something to add here :

the fieldname of each table in db server are as following:

CUSTOMER => CustID CustName Country (i only take 3 of the fields)
ITEM => ItemCode ItemDesc



and i create temp table using TClientDataSet with the following code:

With ClientDataSet.FieldDefs do

begin
Add('CustID', ftString,10);
Add('CustName',ftString,25);
Add('Country',ftString,15);
Add('ItemCode',ftString,10);
Add('ItemDesc',ftString, 10);
end;

The value in the TClientDataSet temp table will b assign corresponding to the user input...
The problem now is how am i going to update the data in TClientDataSet to different table in db server ??

 

[DaZZleD]

you could skip using the TCLientDataSet component because you can do the modifs 'On The Fly' and insert them directly to the DB.
you need to add a TQuery component to your form, and link it to the TDatabase Component. Also make sure your dataset for the DataSource1 is set to Query1. If you want to 'connect' the two tables (customer and item) to see which customer bought what item, you need an extra field to both tables (let's say nr integer not null) that will also be primary key. Otherwise, just remove it from the insert statements.

now...
in the uses list add classes, sysutils.
in the private section of your form add:

procedure transfer(path : string);
function parseLine(line : string) : TStringList;

then in implementation add the following functions:

the transfer

procedure TForm1.transfer(path : string);
var
sourceF : TextFile;
sr :  TSearchRec;
ff, custnum : integer;
line, CustID, CustName, Country, ItemCode, ItemDesc : string;
fields : TStringList;
begin

   form1.DataSource1.DataSet.Close;
   form1.Query1.SQL.Clear;
   form1.Query1.SQL.Add('select max(nr) as max from customer');
   form1.DataSource1.DataSet.Open;
   custnum := strToInt('0' + form1.DataSource1.DataSet.FieldByName('MAX').AsString);
   custnum := custnum + 1;
  
   ff := findfirst(path, faanyfile,sr);
   findclose(sr);
   if ff=0 then
   begin
     assignfile(sourceF, path);
     reset(sourceF);
     form1.DataSource1.DataSet.Close;
     while not eof(sourceF) do
     begin
       Readln(sourceF,line);
       fields := TStringList.Create;
       fields.Clear;
       fields.AddStrings(Form1.ParseLine(line));
       CustID := fields.Strings[0];
       CustName := fields.Strings[1];
       Country := fields.Strings[2];
       ItemCode := fields.Strings[3];
       ItemDesc := fields.Strings[4];

       {apply modifications here}

       form1.Query1.SQL.Clear;
       form1.Query1.SQL.Add('insert into customer(nr,CustID,CustName,Country) values("' + intTostr(custnum) + '", "' + CustID + '", "' + CustName + '", "' + Country + '");');
       form1.Query1.SQL.Add('insert into item(nr,ItemCode ItemDesc) values("' + intTostr(custnum) + '", "' + ItemCode + '", "' + ItemDesc + '");');
       try form1.Query1.ExecSQL;
       except on E : Exception do
         begin
            Showmessage('An error(' + E.Message + ') occured while executing the following statements: ' + #13#10 + form1.Query1.SQL.Strings[0] + #13#10 + form1.Query1.SQL.Strings[1] + #13#10 + 'Record not added to the DB');
         end;
       end;
       inc(custnum);
     end;
     form1.DataSource1.DataSet.Open;
     closefile(sourceF);
   end;
end;

the parsing

function TForm1.parseLine(line:string):TStringList;
var
list : TStringList;
i : integer;
begin
   i := 0;
   list := TStringList.Create;
   list.Clear;
   while i < 4 do
   begin
     list.Add(StringReplace(trim(copy(line, 0, pos(',', line)-1)), '&quot;', '', [rfReplaceAll]));
     line := trim(copy(line, pos(',', line)+1, length(line)));
     inc(i);
   end;
   list.Add(StringReplace(trim(copy(line, 0, length(line))), '&quot;', '', [rfReplaceAll]));
   result := list;
end;

I haven't tested it but it should work ok. make sure you call procedure transfer with a valid path (a TOpenFile component will do the trick) and you're on your way.
if you have any problems, just post here.

 

[beginner81]

Ohhh.. it's seems so complicated for me... ok .. forget bout the import data from txt file... and now i'm using the following DB Componenet to connect to the server:
1) TSQLConnection
2) TSQLQuery
3) TDataSetProvider
4) TClientDataset


i set the ProviderName property for TClientDataSet to TDataProvider... Dataset property for TDataSetProvider to TSQLQuery, and link TSQLQuery to SQLConnection ...
the problem here... now in TForm1.. i hav alot of fields which corresponded to user input... and now i store each of the data that user enter to TClientDataSet... how am i going to Updates them to different table in DBServer ??
As u say, i'd skip the following part(as u ask me to) :
With ClientDataSet.FieldDefs do

begin
Add('CustID', ftString,10);
Add('CustName',ftString,25);
Add('Country',ftString,15);
Add('ItemCode',ftString,10);
Add('ItemDesc',ftString, 10);
end;


So what i'm doing now is i'd assign each of the field in TForm to TClientdataset as following:

With TClientDataSet do
Begin
CreateDaset
Insert;
FindField('CustID').AsString:= TEdit1.Text;
FindField('CustName').AsString := TEdit2.Text;
FindField('Country').AsString := TEdit3.Text;
FindField('ItemCode').AsInteger := TEdit4.Text;
FindField('ItemDesc').AsDateTime := TEdit5.Text;
End;

FindField('XXX').AsString = (XXX is the field name that i'd assign to each table.. )

My main problem now is how to updates it to dbserver from TClientDataSet...

P/S: Sorry for that.. but i'm really need help now.. thx in advance.. ur help is appreciated.. by the way, where r u from ?? India??

 

[beginner81]

something to add here... the Tform1 hav 2 button.. once click at button1, all the data will only temporary store in Tdataclientdataset.. and click at button 2 will store the data to the DBServer permanently..

 

[DaZZleD]

for what you want to do now, use the table component.

and no, I'm not from India.

 

[beginner81]

oh ok mates.. i'll try my best.. thx alot ..