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,327 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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