StrataFrame Forum

Identify fields - converting data

http://forum.strataframe.net/Topic23581.aspx

By Tim Dol - 6/18/2009

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

 
By Greg McGuffey - 6/18/2009

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



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)




I haven't tried this, but it should work.
By Trent L. Taylor - 6/19/2009

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.