Solved

Cannot insert explicit value for identity column in table 'patient' when IDENTITY_INSERT is set to OFF

Posted on 2014-01-24
8
1,323 Views
Last Modified: 2014-01-25
i get this error when i insert data in sql.
  using( patientDataContext db = new patientDataContext())
            {

                tblpatient Tblpatient = new tblpatient();
                Tblpatient.firstname = textfirstname.Text;
                Tblpatient.lastname = textlastname.Text;
                Tblpatient.bdate =Convert.ToDateTime(bdate.Text);
                Tblpatient.gender = cmbgender.Text;
                Tblpatient.SSN = txtssn.Text;
                Tblpatient.address = txtaddress.Text;
                Tblpatient.country = cmbcountry.Text;
                Tblpatient.state = cmbstate.Text;
                Tblpatient.city = cmbcity.Text;
                Tblpatient.mobileno =Convert.ToInt32 (txtcnno.Text);
                db.tblpatients.InsertOnSubmit (Tblpatient);
              
                db.SubmitChanges();
                MessageBox.Show("New Patient Create");
            

            }

Open in new window



patientid columns is identity =yes
0
Comment
Question by:loveuajay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39806130
INDENTITY INSERT must be ON for the table you want to insert an explicit value, not OFF.

It can only be ON for one table. So the usual way to handle it is to set it OFF, then ON for the table needed.

Bye, Olaf.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39806134
Strange - you don't actually set the identity column from your code according to the code you provide.
Have you edited anything out from the code you pasted ?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39806460
You state, "i get this error when i insert data in sql.", Please post the exception message and the inner exception message.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 14

Expert Comment

by:dejaanbu
ID: 39808135
I agree with Olaf...

SET IDENTITY_INSERT dbo.tbl_patient ON

 Run this Query on SSRS , the sqlserver query editor

Make sure your table name is correct.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39808332
@dejaanbu, I agree with Olaf...
Please read the question

Identity column is: patientid
              Tblpatient.firstname = textfirstname.Text;
                Tblpatient.lastname = textlastname.Text;
                Tblpatient.bdate =Convert.ToDateTime(bdate.Text);
                Tblpatient.gender = cmbgender.Text;
                Tblpatient.SSN = txtssn.Text;
                Tblpatient.address = txtaddress.Text;
                Tblpatient.country = cmbcountry.Text;
                Tblpatient.state = cmbstate.Text;
                Tblpatient.city = cmbcity.Text;
                Tblpatient.mobileno =Convert.ToInt32 (txtcnno.Text);


Where is it being set in that code ?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39808387
Good question, Andy. Patientid is not set in that code, so indeed the submit/insert should work with IDENTITY_INSERT set OFF. Somehow the mapping of the datacontext has not set patientid as a value not written in the insert. That would cause such an error, no matter what default value that column would have on the  DotNet tblPatient object.

Bye, Olaf.
0
 

Author Comment

by:loveuajay
ID: 39808639
so how set Patientid
in this code

so that work in my sql query

please take a look at my code again it is same that i post it
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 39808657
Do you really want to set the patientid? As it's defined as a identity in the database, it's generated (incremented) by the database. Your code isn't wrong. What is wrong is the mapping. You have to find code specifying patientid, it should be specified in c# in a way to not put this column into the generated insert sql statement, which the SubmitChanges() causes.

Bye, Olaf.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question