Copying from grid to Adotable
Thomas Willms
Hello,
I am using the procedure below to copy data from a stringgrid (beginning with the column (2fixed +) i3, line variable Min2) to an ACCESS Database (shown in a DBGrid).
1. I never did this before and I have some problems and I am not sure
about the sense of Updatebatch and especially updaterecord because
'updaterecord' didn't update the dataset when used alone.
Another special problem is that I am copying strings and in the
database the format is sometimes DateTime, boolean ,
numeric or currencey.
In this case he refuses to copy saying it is not possible to convert.
I have problems to adapt the copying process to the format and
sometimes it seems that it doesn't work:
procedure AbspeichernSET(i3,Min2:Integer) ;
var j3,k : integer; tabellenname2, FN ,dateiname2, dateiname, resultat,iw: String;
begin
j3:= 0;
Form1.ADOTable2.Append;
While (j3 < Form1.DBAdvGrid1.colcount) do
begin
FN:= Form1.DBAdvGrid1.Cells[j3,0];
if not (FN = 'DBDateTime') then
begin
try
Form1.ADOTable2.FieldbyName(FN).asString:=Form3.AdvStringGrid1.Cells[2+i3,Min2 + j3];
except
try
Form1.ADOTable2.FieldbyName(FN).value:=Form3.AdvStringGrid1.Cells[2+i3,Min2 + j3];
except end;
end;
end;
j3:=j3+1;
end;
Form1.ADOTable2.UpdateRecord;Form1.ADOTable2.UpdateBatch; Form1.ADOTable2.UpdateCursorPos;
end;
In another case I am doing the same from a DBGrid1 (showing data retrieved from Internet PostgreSQL) to a DBGrid2 (in an ADOTable).
54 little tables work well but 3 big tables not:
In one case nothing is copied (because of an error at the end of the
first line although it works in all other cases) , in the two other cases only the last 8 or ten entries are appearing. After these entries it is flickering very much without anything more added at the end....
As I am copying a lot of tables, I change each time the tablename to load the new table from the database and to copy afterwards the data to the Accessfile. I found that it takes much time inthese cases
but I suppose that the loading should be finished before the copying begins..
The code doing the copying is the following
ADOTable1.Active:=false;ADOTable1.TableName:=tabelleNrX;
ADOTable1.Active:=true;
j:= 0;
Form1.ADOTable1.first;
while (j < Frm_SQL_Query.DBAdvGrid1.rowCount-1 ) do
begin
Form1.ADOTable1.last; Form1.ADOTable1.Append;
SpAnzahl:=Frm_SQL_Query.DBAdvGrid1.ColCount-1 ;
For f:= 0 to SpAnzahl do
begin
fn:=Frm_SQL_Query.DBAdvGrid1.cells[f,0]; //Showmessage(FN);
w:=Frm_SQL_Query.DBAdvGrid1.cells[f,1+j]; //Showmessage(w);
Form1.ADOTable1.FieldbyName(FN).AsString:=w;
Form1.DBAdvGrid2.AutoSizeColumns(true,j);
end;
Form1.ADOTable1.Post; Form1.ADOTable1.UpdateBatch;
Form1.ADOTable1.FindLast ; Form1.ADOTable1.UpdateCursorPos;
j:=j+1;
Do you need other info ? Can you help me ? Any ideas to improve; What makes more sense : putting FindFirst, First, Last, FindLast, Insert ?
Does "Append" not simply mean to put it at the end ???
Thanks very much
Thomas Willms
yannis
> Hello,
> I am using the procedure below to copy data from a stringgrid (beginning with
> the column (2fixed +) i3, line variable Min2) to an ACCESS Database (shown in
> a DBGrid).
>
Ok lets put thing in to perspective first.
1) Coping values from a string grid to any Dataset requires that
1) the formating of dates, float numbers and time are persistent
through all the grid it self.
2) The formating machies the Localization settings on the computer.
You can easelly mach the locale settings with the formating on the grid
by changing the global variables DecimalSeparator, CurrencyDecimals,
DateSeparator, ShortDateFormat etc which are in the SysUtils unit. take
a look on the help for more info.
2) You do not need to use first, last find in the loop just to insert
new records, you need to use locate or find only to update the data for
an existing record. So calling Append or insert will be enough but you
always need to call post after you have finished editing a record/row.
3) There is no need to use the table in batch mode if you are not
indead to send the new data in multiple rows to the server. If you just
keep calling the update batch in the end of each record appended then
just change the way the ADOTable is connected and returns data making
use a server side cursor instead you will gain in speed.
After all this have been taken care and steel have problems inserting
new rows to the server ask again.
Regards
Yannis.