none
DAtaset insert HAS a VERY Weired BUG RRS feed

  • Question

  • When I try to commit the dataset into Database , the records in the one of the master details Datatables has the records collabered while saving into DB, dut to autonumbering issue.
    Monday, July 20, 2009 7:50 PM

All replies

  • Do you mean "clobbered" as in not committed?
    Monday, July 20, 2009 8:11 PM
  • Yes,
    When I try to insert, the dataset has the consecutive row numbers, and when I try to save to DB the last two records get the same ID while saving into DB.
    Monday, July 20, 2009 11:04 PM
  • Not sure how to explain why you are getting a problem with the last two numbers.

    However, do make sure that there is no identity, generator, trigger, etc. in the database itself that is assigning the values.  This will take priority over what you are doing in the DataSet.

    Note that when assigning sequential numbers it is actually more common to let the database due it with an identity column, generator, trigger, or whatever technique is used by your particular database server and makes sense for your application.  In this case, the recommended approach is to set AutoNumberSeed and AutoNumberIncrement to -1 so that temporary identities in the DataSet are negative and easily distinguished from permanent identities assigned by the database.  When the commands are configured correctly (read through my answers on http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/65eea503-f709-4f50-9eac-9492bd1813bd), the DataSet will receive the permanent identities as part of the Update method.
    Tuesday, July 21, 2009 1:47 AM
  • I do have the autonumber column, and I am using RowupdatedEvent to take care of this autoidentity number.
    Somehow the temporary values are assigned correctly in the dataset but while commiting into the DB, the masterID gets reassigned to teh next masterID key from the next new record into the child table for it's foriegn key.
    I have certain number of records already existing DB henc ehte Autoincrement doesn't take effect even though I set while I initialize the varaible while initializing the dataset.
    BElow
    Clonetechniques copies the record from the TEchnique table to which in turn traverses the loop to create it own childrenm and grand children records all new records gets certain default value from the known record which is already exisiting i.e FromXXXID. But while saving the loop reassigns the PrimaryKeyID to the child table forieng key again eventhough it assigns correctly in the dataset.
    This happend while saving the dataset to DB.

    I have the realtion like this

    Surgeon - > Technique-> Pathology \ Case-> Fluidics etc..
                                      -> Phase     /

    SurgeonID is the FK for TechniqueTable
    TechID is the FK for the Pathology table and Phase table
    PhasedID and PathologyID are the FK for the case table
    CaseId is the FK for the fluidics table etc.

     

    public void CloneTechniquesOf(int fromSurgeonID, int toSurgeonID, string[] Techniques)

    {

     

    // What is the last TechniqueID?

     

    string theSelectQuery = "TechniqueID<>0";

     

    string sortkey = "TechniqueID";

    DataTable allTheTechs = NGXSelect(TableName.Technique, theSelectQuery, sortkey);

     

    int theLastTechID = Convert.ToInt32(allTheTechs.Rows[allTheTechs.Rows.Count - 1]["TechniqueID"]);

    DataRow rowTechnique;

     

    //object [][] theSelectedTech = new object [theseTechniques.GetLength(0)][];

     

    for (int i = 0; i < Techniques.GetLength(0); i++)

    {

     

    // Get this technique;

    theSelectQuery = "SurgeonID=" + fromSurgeonID.ToString() + " and TechniqueName='" + Techniques[i] + "'";

    DataTable theseTechniques = NGXSelect(TableName.Technique, theSelectQuery);

     

    if (theseTechniques.Rows.Count != 1)

    {

    BL.frmNGXErrorDisplay theErrorDisplay =

    new BL.frmNGXErrorDisplay(false); //Wipro-Sangeetha:28-Mar-2007

     

    //Removed HardCoded string:- Wipro-Sam:16-Mar-2007

     

    // theErrorDisplay.DisplayModalError ("Error in CloneTechniquesOf");

    theErrorDisplay.DisplayModalError(BL.NGXUtil.getStringFromRes("err_NGXDB_02"));

     

    return;

    }

     

    for (int j = 0; j < theseTechniques.Rows.Count; j++)

    {

     

    //theSelectedTech [i] = new object [theseTechniques.Rows [i].ItemArray.GetLength (0)];

     

    int fromTechID = Convert.ToInt32(theseTechniques.Rows[j][0]);

    rowTechnique = p_dataset.Tables["tblTechnique"].NewRow();

     

    foreach (DataColumn col in p_dataset.Tables["tblTechnique"].Columns)

    {

     

    if (col.AutoIncrement)

    {

     

    continue;

    }

     

    if (col.ColumnName == "SurgeonID")

    rowTechnique["SurgeonID"] = toSurgeonID;

     

    else if (col.ColumnName == "TechniqueSequenceNumber")

    rowTechnique["TechniqueSequenceNumber"] = i;

     

    else

    rowTechnique[col] = theseTechniques.Rows[j][col.ColumnName];

    }

    rowTechnique.SetParentRow(p_dataset.Tables["tblSurgeon"].Rows.Find(toSurgeonID));

    p_dataset.Tables["tblTechnique"].Rows.Add(rowTechnique);

    theSelectQuery = "TechniqueID<>0";

    sortkey = "TechniqueID";

    allTheTechs = NGXSelect(TableName.Technique, theSelectQuery, sortkey);

     

    int toTechID = Convert.ToInt32(allTheTechs.Rows[allTheTechs.Rows.Count - 1]["TechniqueID"]);

    ClonePathologiesOf(fromTechID, toTechID);

    ClonePhasesOf(fromTechID, toTechID);

    }

    }

    }






    ///

     

    <param name="toTechniqueID">New technique ID</param>

     

    public void ClonePhasesOf(int fromTechniqueID, int toTechniqueID)

    {

     

    // What are the phases associated with the "from" technique?

     

    string theSelectQuery = "TechniqueID=" + fromTechniqueID.ToString();

     

    string sortkey = "PhaseID";

    DataTable thesePhases = NGXSelect(TableName.Phase, theSelectQuery, sortkey);

    DataRow rowPhase;

    DataColumn idColumn;

     

    for (int i = 0; i < thesePhases.Rows.Count; i++)

    {

     

    int fromPhaseID = Convert.ToInt32(thesePhases.Rows[i][0]);

    rowPhase = p_dataset.Tables["tblPhase"].NewRow();

     

    foreach (DataColumn col in p_dataset.Tables["tblPhase"].Columns)

    {

     

    if (col.AutoIncrement)

    {

     

    continue;

    }

     

    if (col.ColumnName == "TechniqueID")

    rowPhase["TechniqueID"] = toTechniqueID;

     

    else

    rowPhase[col] = thesePhases.Rows[i][col.ColumnName];

    }

    rowPhase.SetParentRow(p_dataset.Tables["tblTechnique"].Rows.Find(toTechniqueID));

     

    try

    {

    rowPhase.SetParentRow(p_dataset.Tables["tblTechnique"].Rows.Find(toTechniqueID));

    p_dataset.Tables["tblPhase"].Rows.Add(rowPhase);

    }

     

    catch (Exception e)

    {

    BL.CNGXMessageBox.Show(e.ToString());

    }

    theSelectQuery = "PhaseID<>0";

    DataTable allThePhases = NGXSelect(TableName.Phase, theSelectQuery, "PhaseID");

     

    int theLastPhaseID = Convert.ToInt32(allThePhases.Rows[allThePhases.Rows.Count - 1]["PhaseID"]);

     

    int toPhaseID = theLastPhaseID;

    CloneCasesOf(fromPhaseID, toPhaseID);

    }

    BL.frmNGXProgrammingRoot.dataBaseHasChanged =

    true;

    }

     

    public void CloneCasesOf(int fromPhaseID, int toPhaseID)

    {

     

    // What are the cases associated with the "from" technique?

     

    string theSelectQuery = "PhaseID=" + fromPhaseID.ToString();

     

    string sortkey = "CaseID";

    DataTable theseCases = NGXSelect(TableName.Case, theSelectQuery, sortkey);

    DataRow rowCase;

    DataColumn idColumn;

    theSelectQuery = "TechniqueID=" + BL.frmNGXProgrammingRoot.theActiveTechniqueID.ToString() + " and PathologyName='Any'";

    DataTable thePaths = NGXSelect(TableName.Pathology, theSelectQuery);

     

    for (int i = 0; i < theseCases.Rows.Count; i++)

    {

     

    int oldPathID = Convert.ToInt32(theseCases.Rows[i]["PathologyID"]);

     

    int newPathID = -1;

     

    int pathDelta = 0;

     

    if (PathIDOfCloned != null)

    {

    pathDelta = BL.NGXGlobal.nGetIndex(PathIDOfCloned, oldPathID);

    newPathID = firstNewPathologyID + pathDelta;

    }

     

    else

    {

    newPathID = Convert.ToInt32(thePaths.Rows[0]["PathologyID"]);

    }

     

    int fromCaseID = Convert.ToInt32(theseCases.Rows[i][0]);

    rowCase = p_dataset.Tables["tblCase"].NewRow();

     

    foreach (DataColumn col in p_dataset.Tables["tblCase"].Columns)

    {

     

    if (col.AutoIncrement)

    {

     

    continue;

    }

     

    if (col.ColumnName == "PhaseID")

    rowCase["PhaseID"] = toPhaseID;

     

    else if (col.ColumnName == "PathologyID")

    rowCase["PathologyID"] = newPathID;

     

    else

    rowCase[col] = theseCases.Rows[i][col.ColumnName];

    }

    rowCase.SetParentRow(p_dataset.Tables["tblPhase"].Rows.Find(toPhaseID));

    p_dataset.Tables["tblCase"].Rows.Add(rowCase);

    DataTable allTheCases = NGXSelect(TableName.Case, "CaseID<>0", "CaseID");

     

    int theLastCaseID = Convert.ToInt32(allTheCases.Rows[allTheCases.Rows.Count - 1]["CaseID"]);

    CloneFluidicsOf(fromCaseID, theLastCaseID);

    CloneUSOf(fromCaseID, theLastCaseID);

    }

    BL.frmNGXProgrammingRoot.dataBaseHasChanged =

    true;

    }



    I have hte SaveDatasetTODB function as

    public

     

    void vSaveDatasetToDB()

    {

     

    // only do if changes - Do not for now since saving the values to the dataset

     

    // causes a change to be noted

     

    // check for errors ??

    DataSet addedDataSet = p_dataset.GetChanges(DataRowState.Added);

    p_dataset.EnforceConstraints =

    false;

    p_CaseDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_CaseDataAdapter_OnRowUpdated);

    p_PhaseDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_PhaseDataAdapter_OnRowUpdated);

    p_PathologyDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_PathologyDataAdapter_OnRowUpdated);

    p_FluidicsDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_FluidicsDataAdapter_OnRowUpdated);

    p_UltrasoundDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_UltrasoundDataAdapter_OnRowUpdated);

    p_TechniqueDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_TechniqueDataAdapter_OnRowUpdated);

    p_SurgeonDataAdapter.RowUpdated +=

    new OleDbRowUpdatedEventHandler(p_SurgeonDataAdapter_OnRowUpdated);

     

    if (p_dataset.HasChanges())

     

    //BL.frmNGXProgrammingRoot.dataBaseHasChanged)

    {

     

     

    try

    {

    p_SurgeonDataAdapter.Update(TableSurgeon.Select("", "", DataViewRowState.Added));

    }

     

    catch (Exception ex)

    {

    BL.CNGXMessageBox.Show(ex.ToString());

    }

     

    try

    {

    p_TechniqueDataAdapter.Update(TableTechnique.Select("", "", DataViewRowState.Added));

    }

     

    catch (Exception ex)

    {

    BL.CNGXMessageBox.Show(ex.ToString());

    }

     

    try

    {

    p_PhaseDataAdapter.Update(TablePhase.Select("", "", DataViewRowState.Added));

    }

     

    catch (Exception ex)

    {

    BL.CNGXMessageBox.Show(ex.ToString());

    }

     

    try

    {

    p_PathologyDataAdapter.Update(TablePathology.Select("", "", DataViewRowState.Added));

    }

     

    catch (Exception ex)

    {

    BL.CNGXMessageBox.Show(ex.ToString());

    }

     

    try

    {

    p_CaseDataAdapter.Update(TableCase.Select("", "", DataViewRowState.Added));

    }

    etc


    and the rowupdatedEvent as

    public

     

    void p_SurgeonDataAdapter_OnRowUpdated(object sender,

    OleDbRowUpdatedEventArgs e)

    {

     

    if (e.StatementType == StatementType.Insert)

    {

    OleDbCommand oCmd =

    new OleDbCommand("SELECT @@IDENTITY",

    e.Command.Connection);

    e.Row["SurgeonID"] = (

    int)oCmd.ExecuteScalar();

    currentSurgeonID = (

    int)e.Row["SurgeonID"];

    e.Status = UpdateStatus.SkipCurrentRow;

    e.Row.AcceptChanges();

    }

     

    //

    }

     

    public void p_TechniqueDataAdapter_OnRowUpdated(object sender,

    OleDbRowUpdatedEventArgs e)

    {

     

    if (e.StatementType == StatementType.Insert)

    {

    OleDbCommand oCmd =

    new OleDbCommand("SELECT @@IDENTITY",

    e.Command.Connection);

    e.Row["TechniqueID"] = (

    int)oCmd.ExecuteScalar();

    currentTechniqueID = (

    int)e.Row["TechniqueID"];

    e.Status = UpdateStatus.SkipCurrentRow;

    e.Row.AcceptChanges();

    }

    }

     

    public void p_FluidicsDataAdapter_OnRowUpdated(object sender,

    OleDbRowUpdatedEventArgs e)

    {

     

    if (e.StatementType == StatementType.Insert)

    {

    OleDbCommand oCmd =

    new OleDbCommand("SELECT @@IDENTITY",

    e.Command.Connection);

    e.Row["FluidicsID"] = (

    int)oCmd.ExecuteScalar();

    e.Status = UpdateStatus.SkipCurrentRow;

    currentFluidicsID = (

    int)e.Row["FluidicsID"];

    }

     

    if (e.RecordsAffected == 0)

    {

    e.Row.RowError = "Optimistic concurrency violation detected";

    e.Status = UpdateStatus.SkipCurrentRow;

    }

    e.Row.AcceptChanges();

    }

     

    public void p_PathologyDataAdapter_OnRowUpdated(object sender,

    OleDbRowUpdatedEventArgs e)

    {

     

    if (e.StatementType == StatementType.Insert)

    {

    OleDbCommand oCmd =

    new OleDbCommand("SELECT @@IDENTITY",

    e.Command.Connection);

    e.Row["PathologyID"] = (

    int)oCmd.ExecuteScalar();

    currentPathologyID = (

    int)e.Row["PathologyID"];

    e.Status = UpdateStatus.SkipCurrentRow;

    e.Row.AcceptChanges();

    }

    }

     

    public void p_UltrasoundDataAdapter_OnRowUpdated(object sender,

    OleDbRowUpdatedEventArgs e)



    etc.

    I am not sure where I am going wrong.
    Please let meknow if I can do something different inorder to not have overwritten value of PrimaryKeyId into the child table and hence avoid compalining that one of hte master record doesnt have child table.
    Thanks

    Tuesday, July 21, 2009 3:14 PM