Using Identity and TMSTable

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
kurtbilde
Posts: 113
Joined: Wed 16 Mar 2005 16:02
Location: Odense, Denmark

Using Identity and TMSTable

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
Posts: 113
Joined: Wed 16 Mar 2005 16:02
Location: Odense, Denmark

Post by kurtbilde » Thu 14 Apr 2011 15:35

Hello,

That didn't do the trick :cry:

Is there any other options that needs to be set?

-Kurt

kurtbilde
Posts: 113
Joined: Wed 16 Mar 2005 16:02
Location: Odense, Denmark

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.

Post Reply