StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Identify fields - converting data

Identify fields - converting data

Tim Dol — Fri, 19 Jun 2009 08:33:03 GMT

I need to migrate some data from an old table to a new table. The old table and new table both have an auto incremented identity field as the primary key and I need to ensure the values remain the same in the new table.

I have a business object for the old and new and have written the conversion program but when I save the new business object I get a error message 'Cannot insert explicit value for identity column in table 'ContactNew' when IDENTITY_INSERT is set to OFF.'

I tried setting the IDENTITY_INDENTITY ON before the save and IDENTITY_INSERT OFF after the same but I still get the error.

loCommand.CommandText = "SET IDENTITY_INSERT " & Me.TableNameAndSchema & " ON"

Me.ExecuteNonQuery(loCommand)

Any ideas how to solve this issue?

Thanks,

Tim

RE: Identify fields - converting data

Trent L. Taylor — Fri, 19 Jun 2009 08:33:03 GMT

Greg is correct. The reason is this. When you call the ExecuteNonQuery, the connection session is only open for that one call. So when you set the IDENTITY_INSERT ON, execute, then execute the command. The IDENTITY_INSERT is back to off.

When I do this type of thing I generally like to create a stored procedure as it makes it a little easier.

RE: Identify fields - converting data

Greg McGuffey — Thu, 18 Jun 2009 10:58:29 GMT

you probably need to add the SET IDENTITY_INSERT ON and OFF to your main batch:

[codesnippet]Dim sqlBuilder As New System.Text.StringBuilder(512)
With sqlBuilder
.AppendFormat("SET IDENTITY_INSERT {0} ON", Me.TableNameAndSchema).Append(ControlChars.NewLine)

'-- baseSQL is the actual SQL that would be executed to copy values
.Append(baseSQL)

.AppendFormat("SET IDENTITY_INSERT {0} OFF", Me.TableNameAndSchema).Append(ControlChars.NewLine)
End With

loCommand.CommandText = sqlBuilder.ToString()
Me.ExecuteNonQuery(loCommand)[/codesnippet]

I haven't tried this, but it should work.