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,296 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HttpPostedFile 1 26
HTML - Color not displaying correctly in EMAIL. 6 41
SQL Help 27 45
How to seperate number and letters from a string 3 11
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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