Problem with MSSQL 2005 and my app.

[Robert Johnson]

Hi all. My datasource is a MSSQL 2005 database with several tables all
visable in my datasources tab. I can preview data in the Designer OK so my
connection is fine. Now, I add a form, drag from my datasouce the table and
let VS2005 create a nav bar and grid. Build and run. I can see the data
fine but when I try to add data and press the save Icon I get the folowing
Exception and no data is saved. What am I doing wrong? I must have
something setup incorectly in the database but I can't see what. It's jsut
a simple lookup table with three fields, ID which is an Int that is the PK
and is Identity column, BarrelLgtn a VarChar(50), Comments a VarChar(50).
Thats it, very simple. If you add a row the Identity works as the next value
is indicated but it just will not save and the error is thrown. HELP!!!

Best Regards,

Robert
Me.Validate()

Me.TblBarrelLgthBindingSource.EndEdit()

Me.TblBarrelLgthTableAdapter.Update(Me.Gibbons_SQLDataSet.tblBarrelLgth)

SQL Exception was unhandled came from the last line of code above. and below
is the error:

{"Cannot insert explicit value for identity column in table 'tblBarrelLgth'
when IDENTITY_INSERT is set to OFF."}

 

[Mihai]

Your program tries to insert a value in an identity field.
SQL Server automaticaly insert a value in a an identity field , so you have
to change the insert command in dataadapter
dataadapter.insertCommand = "Insert into yourTable ( BarrelLgtn , Comments )
values (field1,field2)"

dataadapter.Update()
Don't try to insert values in identity columns.

Regards,
Mihai

 

[Robert Johnson]

Yes, but this is all generated code. I didn't write any of it. I just draged
the datatable unto the form and VS created the navbar and grid. Are you
indicating that VS2005 does not know enough to create code with an itentity
field in mind? Strange.

Robert

 

[Mihai]

You have to avoid to manually insert values in identity columns.Try to do
this in SQL server environment by opening a table and insert manually values
in identity columns.You will have the same error message.
If you want in your application to show the value of identity column when
you create a new record, is a function in SQL server that "reserve" a value
for you.I don't remember the function name but you can search in SQL Server
Help.So you can display only this reserved value. And in multiuser
environment if another user tries to insert in the same time a new record
this function gives him another values so the conflicts are avoided.
In a multiuser environment for example if each user inserts manualy values
what could happen ?Conflicts, of course. So you have to avoid this.And try
to not use automaticaly generated code because you don't have a good control
of what is happening.

regards,
Mihai