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,274 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
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
You state, "i get this error when i insert data in sql.", Please post the exception message and the inner exception message.
0
 
LVL 14

Expert Comment

by:dejaanbu
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now