David Smith

David Smith

  • NA
  • 2k
  • 0

C# MS Access Setting last using Last Inserted ID as parentID

Jan 11 2013 4:42 AM
What is the best way to do this logic. Basically I creating a series of color events, the first record inserted or created of the color events
transaction is the parent id. After retrieving the last inserted id from the first inserted record, i wanted to set as the parent id.

What is the best way to design this logic. Do I have recall an update query to store parentID query for the parent color record.I am going to demostrate below what I am trying to accomplish. How to right an sql query to take care of this in one step to set the get and set the parent id.
I am using Access 2007
ColorEventTable

ID      COLOR    ParentID

1        Red        1
2        Blue       1
3        Green     1
4        Black      1



Method I am using below


    public static bool Insert_CreateColorEvent(int Color,int ParentID)
        {


                string sql = "INSERT INTO tblService " +
                        "(Color, ParentID ) " + 
                        "VALUES        (?, ?) ";


                using (OleDbConnection sqlConnection = new OleDbConnection(Properties.Settings.Default.colorConnectionString))
                {
                    sqlConnection.Open();

                    OleDbDataAdapter sqlCommandAdapter = new OleDbDataAdapter();

                    using (OleDbCommand sqlCommand = new OleDbCommand(sql, sqlConnection))
                    {

                        if (Color!= null)
                        {
                            sqlCommand.Parameters.AddWithValue("Color", Color);
                        }
                        else
                        {
                            sqlCommand.Parameters.AddWithValue("Color", DBNull.Value);
                        }

                        if (ParentID > 0)
                        {
                            sqlCommand.Parameters.AddWithValue("ParentID", ParentID);
                        }
                        else
                        {
                            sqlCommand.Parameters.AddWithValue("ParentID", DBNull.Value);
                        }

                        sqlCommandAdapter.InsertCommand = sqlCommand;

                        int rowsAffected = 0;
                        if ((rowsAffected = sqlCommandAdapter.InsertCommand.ExecuteNonQuery()) > 0)
                        {
 
                            sqlCommand.CommandText = "Select @@Identity";

                            int ID = (int)sqlCommand.ExecuteScalar();

                            if (ID > 0)
                            {
                                if (DataAccess.IsParentIDValidated)
                                {
                                    DataAccess.IsParentIDValidated = false;
                                    DataAccess.ParentID = ID;
                                }

                                DataAccess.LastInsertedId = ID;
                            }

                        }
                    }
                }