duplicate table record

[safra]

Hi,

What would be the fastest way to duplicate all fields of a record in a table (ADO) and then edit 2 of the fields manually?

Thanks,
Raoul

 

[VintageWine]

There may be a handy ADO facility, but I'm not aware of one.

I use the funtion below, which you're welcome to.

Have fun
Simon


type
TCopyRecordOption=(crReturnToOriginal,crReturnNewAutoincValue);
TCopyRecordOptions=set of TCopyRecordOption;
TAction=(crCopy,crIgnore,crSet,crAutoinc);

function CopyRecord(ADataset:TDataset; AIgnoreFields,ASetFields:array of string;
ASetValues:array of variant; AOptions:TCopyRecordOptions=[]):integer;
var
i,q:integer;
LAction:array of TAction;
LValues:array of variant;
LBookmark:TBookmark;
LAutoincFieldIndex:integer;
begin
Result:=0;
LAutoincFieldIndex:=-1;

with ADataset do begin
{Provide room in arrays}
SetLength(LAction,Fields.Count);
SetLength(LValues,Fields.Count);

if(crReturnToOriginal in AOptions)then
LBookmark:=GetBookmark
else
LBookmark:=nil;

try
{Note existing values and what to do with each field}
for i:=0 to Fields.Count-1 do
with Fields do
if(ClassType=TAutoIncField)then begin
if(crReturnNewAutoincValue in AOptions)and
(LAutoincFieldIndex=-1)then begin
LAutoincFieldIndex:=i;
LAction:=crAutoInc;
end else
LAction:=crIgnore;
end else
if(ReadOnly)or
(InTextList(FieldName,AIgnoreFields)<>-1)or
(ClassType=TDatasetField)then
LAction:=crIgnore
else begin
q:=InTextList(FieldName,ASetFields);
if(q<>-1)then begin
LAction:=crSet;
LValues:=ASetValues[q]; {Alternative is to find q later on}
end else begin
LAction:=crCopy;
LValues:=Value;
end;
end;

{Create new record}
Append;

{Put in values}
for i:=0 to Fields.Count-1 do
case LActionof
crCopy,crSet:Fields.Value:=LValues;
end;{case}

{Post; No post. Can hide record if filtered or a detail recordset}

{Note new autoindex field value if required}
if(crReturnNewAutoincValue in AOptions)and
(LAutoincFieldIndex>=0)then
Result:=Fields[LAutoincFieldIndex].asInteger;
finally
if(crReturnToOriginal in AOptions)then begin
GotoBookmark(LBookmark);
FreeBookmark(LBookmark);
end;
end;
end;
end;

 

[safra]

Yes, I was hoping for a simple solution.

But thanks a lot, your function looks very complete. It seems it uses another function 'InTextList'. Any chance of posting this function too?

regards,
Raoul

 

[LucieLastic]

hi

would something like this do? Modify the bit in the select to match your new values and make sure the fields match to the fields in the INSERT part:

Insert into MyTable(field1,field2,field3,field4)
select <edit1>,field2,field3,<edit2> from MyTable
where <condition>

eg with sample data

insert into MyTable (ID, Name, Co, NHSNr)
select 10, Name, Co, 'AB112233B', from MyTable
where ID = 9

lou

 

[LucieLastic]

...btw, you will have to list out all the fields. If it is a wide table (ie lots of columns), I may have some sql which will extract them for you.

 

[safra]

Nice LucieLastic! I didn't think of using sql for doing this which is rather stupid as my main experience with databases so far is MySQL / PHP.

The table is not that big so it is not a problem to manually enter the field names. However it would be interesting to know how to extract the field names through sql.

 

[LucieLastic]

hi Safra

I've no experience of MySQL, is it like SQLServer?

lou

 

[LucieLastic]

Also, if you're only adding 1 new record, you could just type it straight into Database Explorer, couldn't you?

 

[LucieLastic]

Oops, forget my last post as I forgot you mentioned ADO in your original message.

 

[safra]

It seems you cannot just go ahead with the sql query. It raises errors for fields with no value. So you still have to go through the source record and make sure only fields with a value are included in the insert query, correct?

I also wonder if you can return the ID of the new record from the query? In php you simple use the function mysql_insert_id(). Is there anything like that in Delphi?

 

[LucieLastic]

hi Safra

"It raises errors for fields with no value"
Why would it raise an error here, are you talking about NULL fields? If the field is null, it will write null to the new record. I've used this type of query quite a few times in the past, but I must admit never thru ADO, I've always done it straight on the database.

 

[safra]

Sorry, my mistake. The exact message was 'Field x has no default value' which was caused by a typo.