Cannot insert explict value for identity column in table 'Employees' when IDENTITY

17 May 2006 12:25 PM

Christopher Lusardi

How can I fix this? When I do the below 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 the 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 methods I used are below.

Thanks,
Chris Lusardi

    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)
        UpdateDisplay()
    End Sub

17 May 2006 1:36 PM

Christopher Lusardi

When I look at the properties of the data adapter for daEmployees
nothing jumps out at me?

Chris Lusardi

17 May 2006 1:43 PM

Cor Ligthert [MVP]

Christopher,

I would look in the database for the description of the key column

Just my thought,

Cor

Show quoteHide quote

"Christopher Lusardi" <clusard***@aol.com> schreef in bericht
news:1147868722.368275.298830@i39g2000cwa.googlegroups.com...
> How can I fix this? When I do the below 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 the 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 methods I used are below.
>
> Thanks,
> Chris Lusardi
>
>    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)
>        UpdateDisplay()
>    End Sub
>

17 May 2006 2:25 PM

Christopher Lusardi

Cor Ligthert [MVP] wrote:

> I would look in the database for the description of the key column

How do I do this? 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.

Chris Lusardi

17 May 2006 2:37 PM

Christopher Lusardi

Christopher Lusardi wrote:

> Cor Ligthert [MVP] wrote:
> > I would look in the database for the description of the key column
>
> How do I do this? When I start vb and view "Server Explorer", I see:
>
> - Data Connections
>    - chrislusardi\sqlexpress.AdoStepByStep.dbo
>    + Database Diagrams
>    - Tables
>      - Employees
>           LastName
>           FirstName
>           ...

With a I double click on Employees, I see a yellow light bulb next to a
column indicating it's the primary key column. When I click on that
yellow bulb, nothing in the properties jump out and say here's the
error.

Chris Lusardi

17 May 2006 3:05 PM

Cor Ligthert [MVP]

Christopher,

Can you download the SQLServer Express beta management tool. I have no
expirience with that but it is for sure better than the server explorer.

Cor

Show quoteHide quote

"Christopher Lusardi" <clusard***@aol.com> schreef in bericht
news:1147876649.116943.320140@y43g2000cwc.googlegroups.com...
>
> Christopher Lusardi wrote:
>> Cor Ligthert [MVP] wrote:
>> > I would look in the database for the description of the key column
>>
>> How do I do this? When I start vb and view "Server Explorer", I see:
>>
>> - Data Connections
>>    - chrislusardi\sqlexpress.AdoStepByStep.dbo
>>    + Database Diagrams
>>    - Tables
>>      - Employees
>>           LastName
>>           FirstName
>>           ...
>
> With a I double click on Employees, I see a yellow light bulb next to a
> column indicating it's the primary key column. When I click on that
> yellow bulb, nothing in the properties jump out and say here's the
> error.
>
> Chris Lusardi
>

1 Jun 2006 4:28 AM

ewok66

I would modify your data adapter to ensure the identity column is not part of
your insert statement.

The button click event is adding a row to your dataset, not the database.
So calling the 'Update' method is actually doing an insert (not an update) on
the row you added.

--
--ewok
MCSA+M,MCSE, MCSD, MCDBA,MCITP

Show quoteHide quote

"Christopher Lusardi" wrote:

> How can I fix this? When I do the below 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 the 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 methods I used are below.
>
> Thanks,
> Chris Lusardi
>
>     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)
>         UpdateDisplay()
>     End Sub
>
>

Cannot insert explict value for identity column in table 'Employees' when IDENTITY_INSERT is set to