asked on

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

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

Olaf Doschke

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.

AndyAinscow

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 ?

Fernando Soto

You state, "i get this error when i insert data in sql.", Please post the exception message and the inner exception message.

Deja Anbu

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.

AndyAinscow

@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 ?

Olaf Doschke

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.

loveuajay

ASKER

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

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial