In Visual Basic and ADO.NET 2.0: How To Set IDENTITY_INSERT On

Hello,
From a Visual Basic program how would I turn on IDENTITY_INSERT?
Are there any warnings in doing this? What is the right time to do it?
How often should I do it? When should I turn it off?
Discussion started in microsoft.public.dotnet.languages.vb:
http://groups.google.com/group/micr...5def3c632f40314
Christopher Lusardi> From a Visual Basic program how would I turn on IDENTITY_INSERT?
> Are there any warnings in doing this? What is the right time to do it?
> How often should I do it? When should I turn it off?
If you really need to manually force a specific IDENTITY value, the proper
place to do this (imho) is as close to the database as possible (e.g. in a
stored procedure). This way you can have complete transactional and error
handling control over it. If your vb code exits before setting the property
back, you're going to leave the table in a bad state.
A|||Why would you want to do that? The purpose of the identity property is to ge
nerate a new value for
each new row you insert. Setting this option allow you to specify a value fo
r that column, but that
would defeat the purpose of having the identity attribute for the column in
the first place.
The setting can be useful for a dba to "repair" a lost row (need to get that
row back with the
original identity value), for instance.
How to set it? Execute below from ADO, and make sure it is on the same conne
ction as the later
INSERT statement (beware of connection pooling)
SET IDENTITY_INSERT tblname ON
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<clusardi2k@.aol.com> wrote in message news:1147879600.181591.192450@.i40g2000cwc.googlegroup
s.com...
> Hello,
> From a Visual Basic program how would I turn on IDENTITY_INSERT?
> Are there any warnings in doing this? What is the right time to do it?
> How often should I do it? When should I turn it off?
> Discussion started in microsoft.public.dotnet.languages.vb:
> http://groups.google.com/group/micr...5def3c632f40314
> Christopher Lusardi
>|||> The setting can be useful for a dba to "repair" a lost row (need to get
> that row back with the original identity value), for instance.
The only place I use it is in a specific case where we are migrating data
from an old system to a completely rewritten one and, ly, the previous
maintainers actually attached value to the identity values, placing it in
scripts, distributing details to clients, etc. So I needed an easy way to
create similar entities in the system that had the same identifier as the
old system. It sucks, and I wouldn't want it to be a normal operative
process.
A|||Tibor Karaszi wrote:
> Why would you want to do that? The purpose of the identity property is to
generate a new value for
> each new row you insert. Setting this option allow you to specify a value
for that column, but that
> would defeat the purpose of having the identity attribute for the column i
n the first place.
>
Summarizing from my above reference:
When I do the below within VB, I get the error message:
"Cannot insert explict value for identity column in table
'Employees' when IDENTITY_INSERT is set to OFF."
To get this message, I click my Add button to add a new row to the
database, and then I click the Update button to save the new database
to the external memory.
The functions I used are below.
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
Dim drNew As DataRow
drNew = dsAdoSbs.Employees.NewRow()
drNew.Item("FirstName") = "New First"
drNew.Item("LastName") = "New Last"
dsAdoSbs.Employees.Rows.Add(drNew)
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
daEmployees.Update(dsAdoSbs.Employees)
End Sub
When I look at the properties of the data adapter for daEmployees above
nothing jumps out at me?
When I start vb and view "Server Explorer", I see:
- Data Connections
- chrislusardi\sqlexpress.AdoStepByStep.dbo
+ Database Diagrams
- Tables
- Employees
LastName
FirstName
..
I can't find "sqlexpress.AdoStepByStep" on my PC with a search on the C
drive.
With a I double click on Employees, I see a small yellow light key next
to a
column indicating it's the primary key column etc. When I click on that
yellow key, nothing in the properties jump out and say here's the
error.
Chris Lusardi|||This is an ADO issue. For some reason, ADO doesn't realize that you have an
identity column, so ADO
generates an INSERT statement where it specifies a value for that identity c
olumn. Unless someone
with ADO knowledge jumps in here, I suggest you post this in an appropriate
ADO newsgroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<clusardi2k@.aol.com> wrote in message news:1147884319.815223.323170@.i40g2000cwc.googlegroup
s.com...
> Tibor Karaszi wrote:
> Summarizing from my above reference:
> When I do the below within VB, I get the error message:
> "Cannot insert explict value for identity column in table
> 'Employees' when IDENTITY_INSERT is set to OFF."
> To get this message, I click my Add button to add a new row to the
> database, and then I click the Update button to save the new database
> to the external memory.
> The functions I used are below.
> Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnAdd.Click
> Dim drNew As DataRow
> drNew = dsAdoSbs.Employees.NewRow()
> drNew.Item("FirstName") = "New First"
> drNew.Item("LastName") = "New Last"
> dsAdoSbs.Employees.Rows.Add(drNew)
> End Sub
>
> Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles btnUpdate.Click
> daEmployees.Update(dsAdoSbs.Employees)
> End Sub
> When I look at the properties of the data adapter for daEmployees above
> nothing jumps out at me?
> When I start vb and view "Server Explorer", I see:
> - Data Connections
> - chrislusardi\sqlexpress.AdoStepByStep.dbo
> + Database Diagrams
> - Tables
> - Employees
> LastName
> FirstName
> ...
> I can't find "sqlexpress.AdoStepByStep" on my PC with a search on the C
> drive.
> With a I double click on Employees, I see a small yellow light key next
> to a
> column indicating it's the primary key column etc. When I click on that
> yellow key, nothing in the properties jump out and say here's the
> error.
> Chris Lusardi
>|||> This is an ADO issue. For some reason, ADO doesn't realize that you have
> an identity column, so ADO generates an INSERT statement where it
> specifies a value for that identity column. Unless someone with ADO
> knowledge jumps in here, I suggest you post this in an appropriate ADO
> newsgroup.
Or call a stored procedure instead of all this built-in, double-click, drag
and drop stuff that is not as generic or useful as it would appear.