title: uploading xml file to sql, turn off primary key in command
body: <p>I've searched google and youtube and here of course and i can't find the answer. I need to turn the identity insert On and OFF but don't know how to do that in the sql command of the ADO.Net project. I'm sure it must be extremely easy for you highly experienced people.</p>
  
  <p>additional information I'm using c#, visual studio and sql server, i don't know if that helps narrow the problem down?</p>
  
  <p>here is sql command i tried in the ado.net project.</p>
  
  <pre><code>sql = "SET IDENTITY_INSERT HomeCareVisit ON insert into HomeCareVisit values(" 
         + VisitRefNo + ",'" + PatientNo + "','" + ScheduledDateTime + "','" 
         + TreatmentInstructions + "','" + MedicalStaffID + "','" + Priority + "','" 
         + ActualVisitDateTime + "','" + TreatmentProvided + "','" + Prescription 
         + "','" + AdvisoryNotes + "'," + (FurtherVisitRequired ? "1" : "0") + " );"; 
  </code></pre>
  
  <p>and it gave the error </p>
  
  <p>Violation of PRIMARY KEY constraint 'PK_VisitRefNo'. Cannot insert duplicate key in object 'dbo.HomeCareVisit'.
  The statement has been terminated.</p>
  
  <p>here is the code shows what data type the column are</p>
  
  <pre><code>VisitRefNo = ds.Tables[0].Rows[i].ItemArray[0].ToString();
  PatientNo = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[1]);
  ScheduledDateTime = Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray[2]);
  TreatmentInstructions = ds.Tables[0].Rows[i].ItemArray[3].ToString();
  MedicalStaffID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[4]);
  Priority = ds.Tables[0].Rows[i].ItemArray[5].ToString();
  ActualVisitDateTime = Convert.ToDateTime(ds.Tables[0].Rows[i].ItemArray[6]);
  TreatmentProvided = ds.Tables[0].Rows[i].ItemArray[7].ToString();
  Prescription = ds.Tables[0].Rows[i].ItemArray[8].ToString();
  AdvisoryNotes = ds.Tables[0].Rows[i].ItemArray[9].ToString();
  FurtherVisitRequired = Convert.ToBoolean(ds.Tables[0].Rows[i].ItemArray[10]);
  </code></pre>