none
Hierarchical Update problem : newbie question - cash reward offered! RRS feed

  • Question

  • Hi,  [1 of 4 posts--since it's long]

    This simple hierarchical table of a simple parent and child, where the child has a foreign key comprising the parent’s primary key, cannot add a value to the child without an runtime exception, for the column Animal ID in the child table, “Cannot insert value NULL into column ‘AnimalID’, column does not allow nulls.  Insert fails”.

    Yet I followed to the best of my abilities what should have worked.

    Never done this before—any help appreciated.
    Note in my Stored Procedure for INSERT I do have an OUT variable comprising the ParentID, see:

    param = new SqlParameter("@AnimalID", SqlDbType.Int, 4, "AnimalID");
                    param.Direction = ParameterDirection.Output; //note @AnimalID used as OUTPUT variable in Stored Procedure


    so why isn’t it going into the child, in particular after this statement?:

                    // // NOTE! P. 341 Malik
                    param = new SqlParameter("@AnimalID", SqlDbType.Int, 4, "AnimalID");
                    param.Direction = ParameterDirection.Input; //from p. 468 top ADO.NET cookbook, it appears no output req'd here (only req'd for .INSERT)
                    insertPetsCommand.Parameters.Add(param);

    BTW, I will offer (gladly) a $20 reward / donation payable by money order if somebody can walk me through this… hope it doesn’t violate house rules, but this is frustrating!

    Please let me know if you need more info. 

    My speculation:  is my UPDATE, SELECT stored procedures OK?  They were ‘Wizard’ generated, and I’m not sure you even need them?  Then again, another example I have uses them. 

    Thank you,

    RC

    Tables (two): 

    Animals--
    “AnimalID” as Primary Key (Int)
    “AnimalType” as VarChar (50) (Null is OK)
    “TimeStamp” as timestamp

    Pets—
    “PetID” as Primary Key
    “FirstName” as VarChar(50) (null is ok)
    “AnimalID” as Foreign Key (linked to Animals table)
    “TimeStamp” as timestamp

    • Edited by RonConger09 Tuesday, April 28, 2009 10:47 PM
    Tuesday, April 28, 2009 10:44 PM

Answers

  • Turns out Wizards may actually be the solution to this problem.  I found a book by Karli Watson "Beginning C#2005 Databases" which walks you through how to use parent - child tables in DataGridView controls, and how you can update parent and child, and in what order to do so, using drag-and-drop functionality.  At least his examples work.  The only drawback, says the author, is that for true "hierarchical" relationships, where a key refers to the same table it resides in, you cannot use this drag-and-drop technique easily, though I rarely encounter such tables.

    RC
    • Marked as answer by RonConger09 Friday, May 1, 2009 1:18 PM
    Friday, May 1, 2009 1:17 PM

All replies

  •  


    //////


    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Data.Sql;


    namespace AnimalPetWinApp1
    {
        public partial class Form1 : Form
        {
           
             //DataSet myDataSetNonTyped1

            DataSetAnimalsPets myDataSetAnimalsPets;

            public Form1()
            {
                InitializeComponent();
               

                this.FillData();
            }

            private void FillData()
            {
                string s = sqlConnection1Animal.State.ToString();
                if (s == "Closed") sqlConnection1Animal.Open();

                #region working_region_region
                //
                //// promising, works
                /////////////////////////////////////////////!!!!!!!!!!!!!!?////////////


                SqlDataAdapter sDA = new SqlDataAdapter("SELECT * from [Animals]; SELECT * from [PETS]", sqlConnection1Animal.ConnectionString);
                sDA.TableMappings.Add("Table", "Animals");
                sDA.TableMappings.Add("Table1", "Pets");

                sDA.Fill(dataSetAnimalsPets1);

                dataGridView1.DataSource = dataSetAnimalsPets1.Tables["Animals"];
                this.dataGridView1.Columns[2].Visible = false;  //hide the timestamp to avoid exception error

                dataGridView2.DataSource = dataSetAnimalsPets1.Tables["Pets"];
                this.dataGridView2.Columns[3].Visible = false;
                //
                ////set autoincrement, etc
                dataSetAnimalsPets1.Tables["Animals"].Columns["AnimalID"].AutoIncrement = true;
                dataSetAnimalsPets1.Tables["Animals"].Columns["AnimalID"].AutoIncrementSeed = -1;
                dataSetAnimalsPets1.Tables["Animals"].Columns["AnimalID"].AutoIncrementStep = -1;

                dataSetAnimalsPets1.Tables["Pets"].Columns["PetID"].AutoIncrement = true;
                dataSetAnimalsPets1.Tables["Pets"].Columns["PetID"].AutoIncrementSeed = -1;
                dataSetAnimalsPets1.Tables["Pets"].Columns["PetID"].AutoIncrementStep = -1;

                ////diagnostics

                DataTable myTestDT = dataSetAnimalsPets1.Tables["Animals"];

                foreach (DataColumn dCol in myTestDT.Columns)
                {
                    Console.WriteLine("?!AI: {0}, AIC: {1}, AIS: {2}, Caption: {3}, ColName: {4} ", dCol.AutoIncrement, dCol.AutoIncrementSeed, dCol.AutoIncrementStep, dCol.Caption, dCol.ColumnName);
                }

                ///////

                #endregion


               

                #endregion

                string s2 = sqlConnection1Animal.State.ToString();
                if (s2 == "Open")  sqlConnection1Animal.Close();
              
              
            }
    /////////////////////////////

            private void button1_Click(object sender, EventArgs e)
            {
                using (SqlConnection testConnection = new SqlConnection(sqlConnection1Animal.ConnectionString))
                {
                    Console.WriteLine("test");
                    SqlDataAdapter sqlDa;
                    DataSetAnimalsPets myDataSetAnimalsPets; // = new DataSetAnimalsPets();

                    testConnection.Open();
                    SqlTransaction trans = testConnection.BeginTransaction();
                    SqlParameter param;

                    #region insert_the_Commands_Animal
                    ///
                    // Animal Insert
                    ///
                    SqlCommand insertAnimalCommand = new SqlCommand();
                    insertAnimalCommand.Connection = testConnection;
                    insertAnimalCommand = new SqlCommand("UP_ANIMALINSERT"); //stored procedure
                    insertAnimalCommand.CommandType = CommandType.StoredProcedure;
                    /////
                    param = new SqlParameter("@AnimalID", SqlDbType.Int, 4, "AnimalID");
                    param.Direction = ParameterDirection.Output; //note @AnimalID used as OUTPUT variable in Stored Procedure
                    insertAnimalCommand.Parameters.Add(param);
                   
                    //////
                    param = new SqlParameter("@AnimalType", SqlDbType.VarChar, 50, "AnimalType");
                    param.Direction = ParameterDirection.Input;
                    insertAnimalCommand.Parameters.Add(param);

                    /////
                    ///
                    // Animal SELECT
                    ///
                    SqlCommand selectAnimalCommand = new SqlCommand();
                    selectAnimalCommand.Connection = testConnection;
                    selectAnimalCommand = new SqlCommand("A2SelectCommand"); //p. 473 ADO.NET - does not require as much as the other commands
                    selectAnimalCommand.CommandType = CommandType.StoredProcedure;
                    //
                    //Animal UPDATE
                    ///
                    SqlCommand updateAnimalCommand = new SqlCommand();
                    updateAnimalCommand.Connection = testConnection;
                    updateAnimalCommand = new SqlCommand("A2NewUpdateCommand");
                    updateAnimalCommand.CommandType = CommandType.StoredProcedure;
                    ////
                    param = new SqlParameter("@AnimalID", SqlDbType.Int, 4, "AnimalID");
                    param.Direction = ParameterDirection.Input; //from p. 468 top ADO.NET cookbook, it appears no output req'd here (only req'd for .INSERT)
                    updateAnimalCommand.Parameters.Add(param);
                    //////
                    param = new SqlParameter("@AnimalType", SqlDbType.VarChar, 50, "AnimalType");
                    param.Direction = ParameterDirection.Input;
                    updateAnimalCommand.Parameters.Add(param);
                    ////
                    //
                    // Animal DELETE
                    ///
                    SqlCommand deleteAnimalCommand = new SqlCommand();
                    deleteAnimalCommand.Connection = testConnection;
                    deleteAnimalCommand = new SqlCommand("A2NewDeleteCommand");
                    deleteAnimalCommand.CommandType = CommandType.StoredProcedure;
                    ////
                    insertAnimalCommand.Transaction = trans;
                    #endregion

                    #region insert_the_commands_Pets
                    //
                    // Pets Insert
                    ///
                    SqlCommand insertPetsCommand = new SqlCommand();
                    insertPetsCommand.Connection = testConnection;
                    insertPetsCommand = new SqlCommand("UP_PETSINSERT"); //stored procedure
                    insertPetsCommand.CommandType = CommandType.StoredProcedure;
                    /////
                    param = new SqlParameter("@PetID", SqlDbType.Int, 4, "PetID");
                    param.Direction = ParameterDirection.Output; //note @PetID used as OUTPUT variable in Stored Procedure--but is this needed?  apparently yes, note "Pet Belonging"
                    insertPetsCommand.Parameters.Add(param);

                    //////
                    param = new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName");
                    param.Direction = ParameterDirection.Input;
                    insertPetsCommand.Parameters.Add(param);

                    // // NOTE! P. 341 Malik
                    param = new SqlParameter("@AnimalID", SqlDbType.Int, 4, "AnimalID");
                    param.Direction = ParameterDirection.Input; //from p. 468 top ADO.NET cookbook, it appears no output req'd here (only req'd for .INSERT)
                    insertPetsCommand.Parameters.Add(param);
                    /////
                    ///
                    //// Pets SELECT
                    ///
                    SqlCommand selectPetsCommand = new SqlCommand();
                    selectPetsCommand.Connection = testConnection;
                    selectPetsCommand = new SqlCommand("P2SelectCommand"); //p. 473 ADO.NET - does not require as much as the other commands
                    selectPetsCommand.CommandType = CommandType.StoredProcedure;
                    //
                    //Pets UPDATE
                    ///
                    SqlCommand updatePetsCommand = new SqlCommand();
                    updatePetsCommand.Connection = testConnection;
                    updatePetsCommand = new SqlCommand("P2UpdateCommand");
                    updatePetsCommand.CommandType = CommandType.StoredProcedure;
                    ////
                    param = new SqlParameter("@PetID", SqlDbType.Int, 4, "PetID");
                    param.Direction = ParameterDirection.Input;
                    updateAnimalCommand.Parameters.Add(param);
                    //////
                    param = new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName");
                    param.Direction = ParameterDirection.Input;
                    updateAnimalCommand.Parameters.Add(param);
                    ////
                    //
                    // Pets DELETE
                    ///
                    SqlCommand deletePetsCommand = new SqlCommand();
                    deletePetsCommand.Connection = testConnection;
                    deletePetsCommand = new SqlCommand("P2DeleteCommand");
                    deletePetsCommand.CommandType = CommandType.StoredProcedure;
                    ////
                    insertPetsCommand.Transaction = trans;
                    #endregion
                    
                    //...............
                   
                    //.. start the work
                    /*
                     *
                     Protocol (p. 480 ADO.NET Cookbook):
                     *
                     - Delete child rows
                     - delete parent rows
                     - update parent rows
                       - insert parent rows
                     - update child rows
                       - insert child rows
                     *
                     * */
                    // ... start the work
                    try
                    {
                       // myDataSetAnimalsPets = new DataSetAnimalsPets();//not needed
                        // delete, down-top order, child
                        sqlDa = new SqlDataAdapter("SELECT * from [Pets]", testConnection);
                        sqlDa.DeleteCommand = deletePetsCommand;
                        sqlDa.DeleteCommand.Connection = testConnection;
                        //sqlDa.Update(dataSetAnimalsPets1.P2SelectCommand.Select("", "", DataViewRowState.Deleted)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Pets"].Select("", "", DataViewRowState.Deleted)); //__#__2
                       
                        //
                        // delete, down-top order, parent
                        sqlDa = new SqlDataAdapter("SELECT * from [Animals]", testConnection);
                        sqlDa.DeleteCommand = deleteAnimalCommand;
                        sqlDa.DeleteCommand.Connection = testConnection;
                       // sqlDa.Update(dataSetAnimalsPets1.A2SelectCommand.Select("", "", DataViewRowState.Deleted)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Animals"].Select("", "", DataViewRowState.Deleted)); //__#__2
                        //
                        //--
                      //  // update parent rows 1 of 2
                        sqlDa = new SqlDataAdapter("SELECT * from [Animals]", testConnection);
                        sqlDa.UpdateCommand = updateAnimalCommand;
                        sqlDa.UpdateCommand.Connection = testConnection;
                        //  sqlDa.Update(dataSetAnimalsPets1.A2SelectCommand.Select("", "", DataViewRowState.ModifiedCurrent)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Animals"].Select("", "", DataViewRowState.ModifiedCurrent)); //__#__2
                   
                        //  //// insert parent rows 1 of 2
                        sqlDa = new SqlDataAdapter("SELECT * from [Animals]", testConnection);
                        sqlDa.InsertCommand = insertAnimalCommand;
                        sqlDa.InsertCommand.Connection = testConnection;
                       // sqlDa.Update(dataSetAnimalsPets1.A2SelectCommand.Select("", "", DataViewRowState.Added)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Animals"].Select("", "", DataViewRowState.Added)); //__#__2
                        //
                      //  // update child rows 2 of 2
                        sqlDa = new SqlDataAdapter("SELECT * from [Pets]", testConnection);
                        sqlDa.UpdateCommand = updatePetsCommand;
                        sqlDa.UpdateCommand.Connection = testConnection;
                        // sqlDa.Update(dataSetAnimalsPets1.P2SelectCommand.Select("", "", DataViewRowState.ModifiedCurrent)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Pets"].Select("", "", DataViewRowState.ModifiedCurrent)); //__#__2
                        //
                        //// insert parent rows 2 of 2
                        sqlDa = new SqlDataAdapter("SELECT * from [Pets]", testConnection);
                        sqlDa.InsertCommand = insertPetsCommand;
                        sqlDa.InsertCommand.Connection = testConnection;
                      //  sqlDa.Update(dataSetAnimalsPets1.P2SelectCommand.Select("", "", DataViewRowState.Added)); //__#__
                        sqlDa.Update(dataSetAnimalsPets1.Tables["Pets"].Select("", "", DataViewRowState.Added)); //__#__2

                        //
                        Console.WriteLine("stop here");
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                    }

                    finally
                    {
                        this.FillData();
                    }

                }
            }

            private void button2_Click(object sender, EventArgs e)
            {
                string s = sqlConnection1Animal.State.ToString();
                if (s == "Closed") sqlConnection1Animal.Open();

                //
                //// promising, works
                /////////////////////////////////////////////!!!!!!!!!!!!!!?////////////

                myDataSetAnimalsPets = new DataSetAnimalsPets();

                SqlDataAdapter sDA = new SqlDataAdapter("SELECT * from [Animals]; SELECT * from [PETS]", sqlConnection1Animal.ConnectionString);
                sDA.TableMappings.Add("Table", "Animals");
                sDA.TableMappings.Add("Table1", "Pets");

                sDA.Fill(dataSetAnimalsPets1);

                dataGridView1.DataSource = dataSetAnimalsPets1.Tables["Animals"];
                this.dataGridView1.Columns[2].Visible = false;  //hide the timestamp to avoid exception error

                dataGridView2.DataSource = dataSetAnimalsPets1.Tables["Pets"];
                this.dataGridView2.Columns[3].Visible = false;
     
     

            }
        }
    }
    /////////

    Tuesday, April 28, 2009 10:46 PM
  • 3 of 3--the stored procedures here...

    // Stored Procedures here:

    /////////////////////////
    PROCEDURE dbo.UP_ANIMALINSERT
    /*  comment p. 340 Malik. */

    (
     @AnimalID INT OUTPUT, @AnimalType varchar(50)
    )
    AS
     SET NOCOUNT OFF;
    INSERT INTO [Animals] ([AnimalType]) VALUES (@AnimalType);
     
    SELECT AnimalID, AnimalType, TimeStamp FROM Animals WHERE (AnimalID = SCOPE_IDENTITY())

     RETURN

    ///////////////////////////////

    PROCEDURE dbo.UP_PETSINSERT
    /*  malik pets insert..note PetID still needs to be OUTPUT despite not having a dependent dB */
    (
     @PetID INT OUTPUT, @FirstName varchar(50), @AnimalID int
    )
    AS
     SET NOCOUNT OFF;
    INSERT INTO [Pets] ([FirstName], [AnimalID]) VALUES (@FirstName, @AnimalID);
     
    SELECT PetID, FirstName, AnimalID, TimeStamp FROM Pets WHERE (PetID = SCOPE_IDENTITY())
    RETURN
    ///////////////////////////////


    PROCEDURE dbo.A2NewDeleteCommand
    (
     @Original_AnimalID int,
     @IsNull_TimeStamp Int,
     @Original_TimeStamp timestamp
    )
    AS
     SET NOCOUNT OFF;
    DELETE FROM [Animals] WHERE (([AnimalID] = @Original_AnimalID) AND ((@IsNull_TimeStamp = 1 AND [TimeStamp] IS NULL) OR ([TimeStamp] = @Original_TimeStamp)))

    ///////////////////////
    PROCEDURE dbo.A2NewUpdateCommand
    (
     @AnimalType varchar(50),
     @Original_AnimalID int,
     @IsNull_TimeStamp Int,
     @Original_TimeStamp timestamp,
     @AnimalID int
    )
    AS
     SET NOCOUNT OFF;
    UPDATE [Animals] SET [AnimalType] = @AnimalType WHERE (([AnimalID] = @Original_AnimalID) AND ((@IsNull_TimeStamp = 1 AND [TimeStamp] IS NULL) OR ([TimeStamp] = @Original_TimeStamp)));
     
    SELECT AnimalID, AnimalType, TimeStamp FROM Animals WHERE (AnimalID = @AnimalID)
    ////////////////
    PROCEDURE dbo.A2SelectCommand
    AS
     SET NOCOUNT ON;
    SELECT        AnimalID, AnimalType, TimeStamp
    FROM            Animals
    ////////////////

    PROCEDURE dbo.P2SelectCommand
    AS
     SET NOCOUNT ON;
    SELECT        PetID, FirstName, AnimalID, TimeStamp
    FROM            Pets

    //////////////
    PROCEDURE dbo.P2DeleteCommand
    (
     @Original_PetID int,
     @IsNull_TimeStamp Int,
     @Original_TimeStamp timestamp
    )
    AS
     SET NOCOUNT OFF;
    DELETE FROM [Pets] WHERE (([PetID] = @Original_PetID) AND ((@IsNull_TimeStamp = 1 AND [TimeStamp] IS NULL) OR ([TimeStamp] = @Original_TimeStamp)))
    ////////////
    PROCEDURE dbo.P2UpdateCommand
    (
     @FirstName varchar(50),
     @AnimalID int,
     @Original_PetID int,
     @IsNull_TimeStamp Int,
     @Original_TimeStamp timestamp,
     @PetID int
    )
    AS
     SET NOCOUNT OFF;
    UPDATE [Pets] SET [FirstName] = @FirstName, [AnimalID] = @AnimalID WHERE (([PetID] = @Original_PetID) AND ((@IsNull_TimeStamp = 1 AND [TimeStamp] IS NULL) OR ([TimeStamp] = @Original_TimeStamp)));
     
    SELECT PetID, FirstName, AnimalID, TimeStamp FROM Pets WHERE (PetID = @PetID)

     

    Tuesday, April 28, 2009 10:46 PM
  • Hi...I'm still looking for an answer...and sample code...but in the meantime I've concluded the Wizard generated datasets may be the problem for C# Forms using Visual Studio 2008 Express...see link below.

    If anybody has code please send it my way; I can pay a modest amount.  A template for a simple hierarchical table that uses the datagridView controls is wanted, for C#.  Thanks.

    RC

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fcd894e1-2bd9-4a5d-8cbb-9971a4be90b4 
    Wednesday, April 29, 2009 6:06 PM
  • Turns out Wizards may actually be the solution to this problem.  I found a book by Karli Watson "Beginning C#2005 Databases" which walks you through how to use parent - child tables in DataGridView controls, and how you can update parent and child, and in what order to do so, using drag-and-drop functionality.  At least his examples work.  The only drawback, says the author, is that for true "hierarchical" relationships, where a key refers to the same table it resides in, you cannot use this drag-and-drop technique easily, though I rarely encounter such tables.

    RC
    • Marked as answer by RonConger09 Friday, May 1, 2009 1:18 PM
    Friday, May 1, 2009 1:17 PM