Using Identity and TMSTable

kurtbilde · Post by **kurtbilde** » Thu 14 Apr 2011 05:57

Hi,

Trying to convert data from paradox to MS SQL. The ID Column is a Identity column on MS SQL. I'm trying to use a TMSSQL to set the identity_insert on:

Code: Select all

SET IDENTITY_INSERT TestApp.dbo.TableName ON

Then using a TMSTable to insert data:

Code: Select all

taMSTable.insert;
taMSTable.FieldByName('ID').value := taPDoxTable.FieldByName('OLDID').value;
taMSTable.FieldByName('VALUE1').value := taPDoxTable.FieldByName('OLDVALUE1').value;
ect...
taMSTable.post;

Then I would use TMSSQL to do a:

Code: Select all

SET IDENTITY_INSERT TestApp.dbo.TableName OFF

This doesn't seems to work as I get "the field ID cannot be modified" right after the .insert;

The reason that I'm using the TMSTable is that I would like to be able to do field convertion as I'm moving from Paradox to MS SQL...

Any ideas?

-Kurt

AndreyZ · Post by **AndreyZ** » Thu 14 Apr 2011 08:15

Hello,

To solve the problem, you should set the taMSTable.Options.SetFieldsReadOnly option to False.

kurtbilde · Post by **kurtbilde** » Thu 14 Apr 2011 15:35

Hello,

That didn't do the trick

Is there any other options that needs to be set?

-Kurt

kurtbilde · Post by **kurtbilde** » Thu 14 Apr 2011 15:45

Seems like the given ID field also was set to readonly :-O

So two setting was needed to be changed on the table level and on the field level of the table!

-Kurt

AndreyZ · Post by **AndreyZ** » Fri 15 Apr 2011 09:37

If you are using persistent fields, you can use two solutions:
1) remove them and add again; after this all fields will have the ReadOnly property set to False;
2) manually set the ReadOnly property of persistent fields to False.