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,283 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 62

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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
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.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

943 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