473,701 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to delete a SQL Server record (descending from another record of the same table) with a C# application

Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generation s" (in which there is only one table, called
"Generation s"), and it allows the user to add, edit and delete the
various records of the table.
"Generation s" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint):

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generation s" database doesn't exist yet)
--GO (insert this statement only if "Generation s" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDP erson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerso n", "txtNamePerson" ,
"txtAgePers on" and "txtIDParen t" TextBox objects, and a
"btnPrevoiusPer son", "btnNextPerson" , "btnAddPers on",
"btnEditPerson" , "btnDeletePerso n", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generatio_ _IDPar_7D78A4E7 ". The conflict has occurred in
"dbo.Generation s" table, column 'IDParent' of "Generation s"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generation s" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generation s" with the T-SQL code, so you have to
customize the cn.ConnectionSt ring C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collecti ons;
using System.Data;
using System.Data.Ole Db;
using System.Data.Com mon;
using System.Data.Sql Client;
using System.Drawing;
using System.Windows. Forms;

class SeparateMain
{
public static void Main()
{
Application.Run (new Generations());
}
}
class Generations: Form
{
/*************** *************** *************** *************** **************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/*************** *************** *************** *************** **************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent;
public Button btnPreviousPers on, btnNextPerson, btnAddPerson,
btnEditPerson, btnDeletePerson , btnOk, btnCancel, btnClose;
public Generations()
{
// Form dimension and position
Size = new System.Drawing. Size(570, 300);
Text = "Generation s";
CenterToScreen( );

// SQL Server objects istantiation and initialization
cn = new SqlConnection() ;
daGenerations = new SqlDataAdapter( );
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.Dat aSetName = "TablesSet" ;

// Table "Generation s"
dtGenerations.T ableName = "Generation s";

dtGenerations.C olumns.Add("IDP erson", typeof(int));
dtGenerations.C olumns["IDPerson"].AutoIncrement = true;
dtGenerations.C olumns["IDPerson"].AutoIncrementS eed = 1;
dtGenerations.C olumns["IDPerson"].AutoIncrementS tep = 1;

dtGenerations.C olumns.Add("Nam ePerson", typeof(string)) ;
dtGenerations.C olumns["NamePerson "].AllowDBNull = false;
dtGenerations.C olumns["NamePerson "].DefaultValue = "";

dtGenerations.C olumns.Add("Age Person", typeof(int));
dtGenerations.C olumns["AgePerson"].AllowDBNull = false;

dtGenerations.C olumns.Add("IDP arent", typeof(int));

dtGenerations.P rimaryKey = new DataColumn[] {
dtGenerations.C olumns["IDPerson"] };

dsTablesSet.Tab les.Add(dtGener ations);

// "Generation s" inner relation
dsTablesSet.Rel ations.Add("Gen erations_Parent Son",
dtGenerations.C olumns["IDPerson"], dtGenerations.C olumns["IDParent"]);

cn.ConnectionSt ring = "Persist Security Info=False;Inte grated
Security=SSPI;d atabase=Generat ions;server=(lo cal)\\TECLOGICA ;Connect
Timeout=10";

string strSQL;
SqlParameterCol lection pc;
SqlParameter param;

// "Generation s" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.S electCommand = new SqlCommand(strS QL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New ,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Orig" ;
daGenerations.U pdateCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.U pdateCommand.Pa rameters;

pc.Add("@NamePe rson_New", SqlDbType.NVarC har, 50,
"NamePerson ");
pc.Add("@AgePer son_New", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt_New", SqlDbType.Int, 0, "IDParent") ;

param = pc.Add("@IDPers on_Orig", SqlDbType.Int, 0, "IDPerson") ;
param.SourceVer sion = DataRowVersion. Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.I nsertCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.I nsertCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;
pc.Add("@NamePe rson", SqlDbType.NVarC har, 50, "NamePerson ");
pc.Add("@AgePer son", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt", SqlDbType.Int, 0, "IDParent") ;

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.D eleteCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.D eleteCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;

daGenerations.F ill(dsTablesSet , "Generation s");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManage r)BindingContex t[dsTablesSet,
"Generation s"];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Par ent = this;
lblIDPerson.Siz e = new Size(120, 25);
lblIDPerson.Loc ation = new Point(10, 10);
lblIDPerson.Tex t = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Par ent = this;
txtIDPerson.Siz e = new Size(120, 25);
txtIDPerson.Loc ation = new Point(lblIDPers on.Left,
lblIDPerson.Bot tom);
txtIDPerson.Ena bled = false;
txtIDPerson.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Person");

lblNamePerson = new Label();
lblNamePerson.P arent = this;
lblNamePerson.S ize = new Size(120, 25);
lblNamePerson.L ocation = new Point(lblIDPers on.Right + 20,
lblIDPerson.Top );
lblNamePerson.T ext = "Name";

txtNamePerson = new TextBox();
txtNamePerson.P arent = this;
txtNamePerson.S ize = new Size(120, 25);
txtNamePerson.L ocation = new Point(lblNamePe rson.Left,
lblNamePerson.B ottom);
txtNamePerson.E nabled = false;
txtNamePerson.D ataBindings.Add ("Text", dsTablesSet,
"Generations.Na mePerson");

lblAgePerson = new Label();
lblAgePerson.Pa rent = this;
lblAgePerson.Si ze = new Size(120, 25);
lblAgePerson.Lo cation = new Point(lblNamePe rson.Right + 20,
lblNamePerson.T op);
lblAgePerson.Te xt = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Pa rent = this;
txtAgePerson.Si ze = new Size(120, 25);
txtAgePerson.Lo cation = new Point(lblAgePer son.Left,
lblAgePerson.Bo ttom);
txtAgePerson.En abled = false;
txtAgePerson.Da taBindings.Add( "Text", dsTablesSet,
"Generations.Ag ePerson");

lblIDParent = new Label();
lblIDParent.Par ent = this;
lblIDParent.Siz e = new Size(120, 25);
lblIDParent.Loc ation = new Point(lblAgePer son.Right + 20,
lblAgePerson.To p);
lblIDParent.Tex t = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Par ent = this;
txtIDParent.Siz e = new Size(120, 25);
txtIDParent.Loc ation = new Point(lblIDPare nt.Left,
lblIDParent.Bot tom);
txtIDParent.Ena bled = false;
txtIDParent.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Parent");

btnPreviousPers on = new Button();
btnPreviousPers on.Parent = this;
btnPreviousPers on.Size = new Size(120, 25);
btnPreviousPers on.Location = new Point(150, 100);
btnPreviousPers on.Text = "Previous";
btnPreviousPers on.Click += new
EventHandler(bt nPreviousPerson _Click);

btnNextPerson = new Button();
btnNextPerson.P arent = this;
btnNextPerson.S ize = new Size(120, 25);
btnNextPerson.L ocation = new Point(btnPrevio usPerson.Right +
20, btnPreviousPers on.Top);
btnNextPerson.T ext = "Next";
btnNextPerson.C lick += new EventHandler(bt nNextPerson_Cli ck);

btnAddPerson = new Button();
btnAddPerson.Pa rent = this;
btnAddPerson.Si ze = new Size(120, 25);
btnAddPerson.Lo cation = new Point(80, 160);
btnAddPerson.Te xt = "Add";
btnAddPerson.Cl ick += new EventHandler(bt nAddPerson_Clic k);

btnEditPerson = new Button();
btnEditPerson.P arent = this;
btnEditPerson.S ize = new Size(120, 25);
btnEditPerson.L ocation = new Point(btnAddPer son.Right + 20,
btnAddPerson.To p);
btnEditPerson.T ext = "Edit";
btnEditPerson.C lick += new EventHandler(bt nEditPerson_Cli ck);

btnDeletePerson = new Button();
btnDeletePerson .Parent = this;
btnDeletePerson .Size = new Size(120, 25);
btnDeletePerson .Location = new Point(btnEditPe rson.Right + 20,
btnEditPerson.T op);
btnDeletePerson .Text = "Delete";
btnDeletePerson .Click += new
EventHandler(bt nDeletePerson_C lick);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(bt nOk_Click);

btnCancel = new Button();
btnCancel.Paren t = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Locat ion = new Point(btnOk.Rig ht + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visib le = false;
btnCancel.Click += new EventHandler(bt nCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Locati on = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(bt nClose_Click);
}

void btnAddPerson_Cl ick(object sender, EventArgs e)
{
cmGenerations.A ddNew();

txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnEditPerson_C lick(object sender, EventArgs e)
{
txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnDeletePerson _Click(object sender, EventArgs e)
{
cmGenerations.R emoveAt(cmGener ations.Position );
daGenerations.U pdate(dtGenerat ions);
}

void btnPreviousPers on_Click(object sender, EventArgs e)
{
if (cmGenerations. Position 0)
cmGenerations.P osition--;
}

void btnNextPerson_C lick(object sender, EventArgs e)
{
if (cmGenerations. Position < cmGenerations.C ount-1)
cmGenerations.P osition++;
}

void btnOk_Click(obj ect sender, EventArgs e)
{
cmGenerations.E ndCurrentEdit() ;
daGenerations.U pdate(dtGenerat ions);

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnCancel_Click (object sender, EventArgs e)
{
cmGenerations.C ancelCurrentEdi t();

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnClose_Click( object sender, EventArgs e)
{
Close();
}
}

Jul 21 '06 #1
6 3856
Hi,

DO not post the entire code , nobody will run it in the first place :)

It's clear what is happening, you cannot delete a row if it has another row
that makes reference to it.

First you ahve to decide what makes the most sense in y our situation. You
could change the FK to allow it to accept null, or you could add a "dead"
person record.

In any case you will have to change the parentID column in the children of
the row you want to delete BEFORE actually delete it.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"polocar" <po*****@tin.it wrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generation s" (in which there is only one table, called
"Generation s"), and it allows the user to add, edit and delete the
various records of the table.
"Generation s" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint):

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generation s" database doesn't exist yet)
--GO (insert this statement only if "Generation s" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDP erson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerso n", "txtNamePerson" ,
"txtAgePers on" and "txtIDParen t" TextBox objects, and a
"btnPrevoiusPer son", "btnNextPerson" , "btnAddPers on",
"btnEditPerson" , "btnDeletePerso n", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generatio_ _IDPar_7D78A4E7 ". The conflict has occurred in
"dbo.Generation s" table, column 'IDParent' of "Generation s"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generation s" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generation s" with the T-SQL code, so you have to
customize the cn.ConnectionSt ring C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collecti ons;
using System.Data;
using System.Data.Ole Db;
using System.Data.Com mon;
using System.Data.Sql Client;
using System.Drawing;
using System.Windows. Forms;

class SeparateMain
{
public static void Main()
{
Application.Run (new Generations());
}
}
class Generations: Form
{
/*************** *************** *************** *************** **************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/*************** *************** *************** *************** **************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent;
public Button btnPreviousPers on, btnNextPerson, btnAddPerson,
btnEditPerson, btnDeletePerson , btnOk, btnCancel, btnClose;
public Generations()
{
// Form dimension and position
Size = new System.Drawing. Size(570, 300);
Text = "Generation s";
CenterToScreen( );

// SQL Server objects istantiation and initialization
cn = new SqlConnection() ;
daGenerations = new SqlDataAdapter( );
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.Dat aSetName = "TablesSet" ;

// Table "Generation s"
dtGenerations.T ableName = "Generation s";

dtGenerations.C olumns.Add("IDP erson", typeof(int));
dtGenerations.C olumns["IDPerson"].AutoIncrement = true;
dtGenerations.C olumns["IDPerson"].AutoIncrementS eed = 1;
dtGenerations.C olumns["IDPerson"].AutoIncrementS tep = 1;

dtGenerations.C olumns.Add("Nam ePerson", typeof(string)) ;
dtGenerations.C olumns["NamePerson "].AllowDBNull = false;
dtGenerations.C olumns["NamePerson "].DefaultValue = "";

dtGenerations.C olumns.Add("Age Person", typeof(int));
dtGenerations.C olumns["AgePerson"].AllowDBNull = false;

dtGenerations.C olumns.Add("IDP arent", typeof(int));

dtGenerations.P rimaryKey = new DataColumn[] {
dtGenerations.C olumns["IDPerson"] };

dsTablesSet.Tab les.Add(dtGener ations);

// "Generation s" inner relation
dsTablesSet.Rel ations.Add("Gen erations_Parent Son",
dtGenerations.C olumns["IDPerson"], dtGenerations.C olumns["IDParent"]);

cn.ConnectionSt ring = "Persist Security Info=False;Inte grated
Security=SSPI;d atabase=Generat ions;server=(lo cal)\\TECLOGICA ;Connect
Timeout=10";

string strSQL;
SqlParameterCol lection pc;
SqlParameter param;

// "Generation s" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.S electCommand = new SqlCommand(strS QL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New ,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Orig" ;
daGenerations.U pdateCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.U pdateCommand.Pa rameters;

pc.Add("@NamePe rson_New", SqlDbType.NVarC har, 50,
"NamePerson ");
pc.Add("@AgePer son_New", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt_New", SqlDbType.Int, 0, "IDParent") ;

param = pc.Add("@IDPers on_Orig", SqlDbType.Int, 0, "IDPerson") ;
param.SourceVer sion = DataRowVersion. Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.I nsertCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.I nsertCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;
pc.Add("@NamePe rson", SqlDbType.NVarC har, 50, "NamePerson ");
pc.Add("@AgePer son", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt", SqlDbType.Int, 0, "IDParent") ;

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.D eleteCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.D eleteCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;

daGenerations.F ill(dsTablesSet , "Generation s");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManage r)BindingContex t[dsTablesSet,
"Generation s"];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Par ent = this;
lblIDPerson.Siz e = new Size(120, 25);
lblIDPerson.Loc ation = new Point(10, 10);
lblIDPerson.Tex t = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Par ent = this;
txtIDPerson.Siz e = new Size(120, 25);
txtIDPerson.Loc ation = new Point(lblIDPers on.Left,
lblIDPerson.Bot tom);
txtIDPerson.Ena bled = false;
txtIDPerson.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Person");

lblNamePerson = new Label();
lblNamePerson.P arent = this;
lblNamePerson.S ize = new Size(120, 25);
lblNamePerson.L ocation = new Point(lblIDPers on.Right + 20,
lblIDPerson.Top );
lblNamePerson.T ext = "Name";

txtNamePerson = new TextBox();
txtNamePerson.P arent = this;
txtNamePerson.S ize = new Size(120, 25);
txtNamePerson.L ocation = new Point(lblNamePe rson.Left,
lblNamePerson.B ottom);
txtNamePerson.E nabled = false;
txtNamePerson.D ataBindings.Add ("Text", dsTablesSet,
"Generations.Na mePerson");

lblAgePerson = new Label();
lblAgePerson.Pa rent = this;
lblAgePerson.Si ze = new Size(120, 25);
lblAgePerson.Lo cation = new Point(lblNamePe rson.Right + 20,
lblNamePerson.T op);
lblAgePerson.Te xt = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Pa rent = this;
txtAgePerson.Si ze = new Size(120, 25);
txtAgePerson.Lo cation = new Point(lblAgePer son.Left,
lblAgePerson.Bo ttom);
txtAgePerson.En abled = false;
txtAgePerson.Da taBindings.Add( "Text", dsTablesSet,
"Generations.Ag ePerson");

lblIDParent = new Label();
lblIDParent.Par ent = this;
lblIDParent.Siz e = new Size(120, 25);
lblIDParent.Loc ation = new Point(lblAgePer son.Right + 20,
lblAgePerson.To p);
lblIDParent.Tex t = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Par ent = this;
txtIDParent.Siz e = new Size(120, 25);
txtIDParent.Loc ation = new Point(lblIDPare nt.Left,
lblIDParent.Bot tom);
txtIDParent.Ena bled = false;
txtIDParent.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Parent");

btnPreviousPers on = new Button();
btnPreviousPers on.Parent = this;
btnPreviousPers on.Size = new Size(120, 25);
btnPreviousPers on.Location = new Point(150, 100);
btnPreviousPers on.Text = "Previous";
btnPreviousPers on.Click += new
EventHandler(bt nPreviousPerson _Click);

btnNextPerson = new Button();
btnNextPerson.P arent = this;
btnNextPerson.S ize = new Size(120, 25);
btnNextPerson.L ocation = new Point(btnPrevio usPerson.Right +
20, btnPreviousPers on.Top);
btnNextPerson.T ext = "Next";
btnNextPerson.C lick += new EventHandler(bt nNextPerson_Cli ck);

btnAddPerson = new Button();
btnAddPerson.Pa rent = this;
btnAddPerson.Si ze = new Size(120, 25);
btnAddPerson.Lo cation = new Point(80, 160);
btnAddPerson.Te xt = "Add";
btnAddPerson.Cl ick += new EventHandler(bt nAddPerson_Clic k);

btnEditPerson = new Button();
btnEditPerson.P arent = this;
btnEditPerson.S ize = new Size(120, 25);
btnEditPerson.L ocation = new Point(btnAddPer son.Right + 20,
btnAddPerson.To p);
btnEditPerson.T ext = "Edit";
btnEditPerson.C lick += new EventHandler(bt nEditPerson_Cli ck);

btnDeletePerson = new Button();
btnDeletePerson .Parent = this;
btnDeletePerson .Size = new Size(120, 25);
btnDeletePerson .Location = new Point(btnEditPe rson.Right + 20,
btnEditPerson.T op);
btnDeletePerson .Text = "Delete";
btnDeletePerson .Click += new
EventHandler(bt nDeletePerson_C lick);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(bt nOk_Click);

btnCancel = new Button();
btnCancel.Paren t = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Locat ion = new Point(btnOk.Rig ht + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visib le = false;
btnCancel.Click += new EventHandler(bt nCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Locati on = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(bt nClose_Click);
}

void btnAddPerson_Cl ick(object sender, EventArgs e)
{
cmGenerations.A ddNew();

txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnEditPerson_C lick(object sender, EventArgs e)
{
txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnDeletePerson _Click(object sender, EventArgs e)
{
cmGenerations.R emoveAt(cmGener ations.Position );
daGenerations.U pdate(dtGenerat ions);
}

void btnPreviousPers on_Click(object sender, EventArgs e)
{
if (cmGenerations. Position 0)
cmGenerations.P osition--;
}

void btnNextPerson_C lick(object sender, EventArgs e)
{
if (cmGenerations. Position < cmGenerations.C ount-1)
cmGenerations.P osition++;
}

void btnOk_Click(obj ect sender, EventArgs e)
{
cmGenerations.E ndCurrentEdit() ;
daGenerations.U pdate(dtGenerat ions);

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnCancel_Click (object sender, EventArgs e)
{
cmGenerations.C ancelCurrentEdi t();

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnClose_Click( object sender, EventArgs e)
{
Close();
}
}

Jul 21 '06 #2
MSSQL 2005 supports a type of referential integrity that will delete rows in
related tables but I don't know how to use it yet. Ordinarily our code is
responsible for the CRUD operations on the row(s) in other tables related
with Foreign Keys.
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee. com
URL http://www.metromilwaukee.com/clintongallagher/

"polocar" <po*****@tin.it wrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generation s" (in which there is only one table, called
"Generation s"), and it allows the user to add, edit and delete the
various records of the table.
"Generation s" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint):

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generation s" database doesn't exist yet)
--GO (insert this statement only if "Generation s" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDP erson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerso n", "txtNamePerson" ,
"txtAgePers on" and "txtIDParen t" TextBox objects, and a
"btnPrevoiusPer son", "btnNextPerson" , "btnAddPers on",
"btnEditPerson" , "btnDeletePerso n", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generatio_ _IDPar_7D78A4E7 ". The conflict has occurred in
"dbo.Generation s" table, column 'IDParent' of "Generation s"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generation s" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generation s" with the T-SQL code, so you have to
customize the cn.ConnectionSt ring C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collecti ons;
using System.Data;
using System.Data.Ole Db;
using System.Data.Com mon;
using System.Data.Sql Client;
using System.Drawing;
using System.Windows. Forms;

class SeparateMain
{
public static void Main()
{
Application.Run (new Generations());
}
}
class Generations: Form
{
/*************** *************** *************** *************** **************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/*************** *************** *************** *************** **************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent;
public Button btnPreviousPers on, btnNextPerson, btnAddPerson,
btnEditPerson, btnDeletePerson , btnOk, btnCancel, btnClose;
public Generations()
{
// Form dimension and position
Size = new System.Drawing. Size(570, 300);
Text = "Generation s";
CenterToScreen( );

// SQL Server objects istantiation and initialization
cn = new SqlConnection() ;
daGenerations = new SqlDataAdapter( );
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.Dat aSetName = "TablesSet" ;

// Table "Generation s"
dtGenerations.T ableName = "Generation s";

dtGenerations.C olumns.Add("IDP erson", typeof(int));
dtGenerations.C olumns["IDPerson"].AutoIncrement = true;
dtGenerations.C olumns["IDPerson"].AutoIncrementS eed = 1;
dtGenerations.C olumns["IDPerson"].AutoIncrementS tep = 1;

dtGenerations.C olumns.Add("Nam ePerson", typeof(string)) ;
dtGenerations.C olumns["NamePerson "].AllowDBNull = false;
dtGenerations.C olumns["NamePerson "].DefaultValue = "";

dtGenerations.C olumns.Add("Age Person", typeof(int));
dtGenerations.C olumns["AgePerson"].AllowDBNull = false;

dtGenerations.C olumns.Add("IDP arent", typeof(int));

dtGenerations.P rimaryKey = new DataColumn[] {
dtGenerations.C olumns["IDPerson"] };

dsTablesSet.Tab les.Add(dtGener ations);

// "Generation s" inner relation
dsTablesSet.Rel ations.Add("Gen erations_Parent Son",
dtGenerations.C olumns["IDPerson"], dtGenerations.C olumns["IDParent"]);

cn.ConnectionSt ring = "Persist Security Info=False;Inte grated
Security=SSPI;d atabase=Generat ions;server=(lo cal)\\TECLOGICA ;Connect
Timeout=10";

string strSQL;
SqlParameterCol lection pc;
SqlParameter param;

// "Generation s" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.S electCommand = new SqlCommand(strS QL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New ,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Orig" ;
daGenerations.U pdateCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.U pdateCommand.Pa rameters;

pc.Add("@NamePe rson_New", SqlDbType.NVarC har, 50,
"NamePerson ");
pc.Add("@AgePer son_New", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt_New", SqlDbType.Int, 0, "IDParent") ;

param = pc.Add("@IDPers on_Orig", SqlDbType.Int, 0, "IDPerson") ;
param.SourceVer sion = DataRowVersion. Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.I nsertCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.I nsertCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;
pc.Add("@NamePe rson", SqlDbType.NVarC har, 50, "NamePerson ");
pc.Add("@AgePer son", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt", SqlDbType.Int, 0, "IDParent") ;

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.D eleteCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.D eleteCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;

daGenerations.F ill(dsTablesSet , "Generation s");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManage r)BindingContex t[dsTablesSet,
"Generation s"];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Par ent = this;
lblIDPerson.Siz e = new Size(120, 25);
lblIDPerson.Loc ation = new Point(10, 10);
lblIDPerson.Tex t = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Par ent = this;
txtIDPerson.Siz e = new Size(120, 25);
txtIDPerson.Loc ation = new Point(lblIDPers on.Left,
lblIDPerson.Bot tom);
txtIDPerson.Ena bled = false;
txtIDPerson.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Person");

lblNamePerson = new Label();
lblNamePerson.P arent = this;
lblNamePerson.S ize = new Size(120, 25);
lblNamePerson.L ocation = new Point(lblIDPers on.Right + 20,
lblIDPerson.Top );
lblNamePerson.T ext = "Name";

txtNamePerson = new TextBox();
txtNamePerson.P arent = this;
txtNamePerson.S ize = new Size(120, 25);
txtNamePerson.L ocation = new Point(lblNamePe rson.Left,
lblNamePerson.B ottom);
txtNamePerson.E nabled = false;
txtNamePerson.D ataBindings.Add ("Text", dsTablesSet,
"Generations.Na mePerson");

lblAgePerson = new Label();
lblAgePerson.Pa rent = this;
lblAgePerson.Si ze = new Size(120, 25);
lblAgePerson.Lo cation = new Point(lblNamePe rson.Right + 20,
lblNamePerson.T op);
lblAgePerson.Te xt = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Pa rent = this;
txtAgePerson.Si ze = new Size(120, 25);
txtAgePerson.Lo cation = new Point(lblAgePer son.Left,
lblAgePerson.Bo ttom);
txtAgePerson.En abled = false;
txtAgePerson.Da taBindings.Add( "Text", dsTablesSet,
"Generations.Ag ePerson");

lblIDParent = new Label();
lblIDParent.Par ent = this;
lblIDParent.Siz e = new Size(120, 25);
lblIDParent.Loc ation = new Point(lblAgePer son.Right + 20,
lblAgePerson.To p);
lblIDParent.Tex t = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Par ent = this;
txtIDParent.Siz e = new Size(120, 25);
txtIDParent.Loc ation = new Point(lblIDPare nt.Left,
lblIDParent.Bot tom);
txtIDParent.Ena bled = false;
txtIDParent.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.ID Parent");

btnPreviousPers on = new Button();
btnPreviousPers on.Parent = this;
btnPreviousPers on.Size = new Size(120, 25);
btnPreviousPers on.Location = new Point(150, 100);
btnPreviousPers on.Text = "Previous";
btnPreviousPers on.Click += new
EventHandler(bt nPreviousPerson _Click);

btnNextPerson = new Button();
btnNextPerson.P arent = this;
btnNextPerson.S ize = new Size(120, 25);
btnNextPerson.L ocation = new Point(btnPrevio usPerson.Right +
20, btnPreviousPers on.Top);
btnNextPerson.T ext = "Next";
btnNextPerson.C lick += new EventHandler(bt nNextPerson_Cli ck);

btnAddPerson = new Button();
btnAddPerson.Pa rent = this;
btnAddPerson.Si ze = new Size(120, 25);
btnAddPerson.Lo cation = new Point(80, 160);
btnAddPerson.Te xt = "Add";
btnAddPerson.Cl ick += new EventHandler(bt nAddPerson_Clic k);

btnEditPerson = new Button();
btnEditPerson.P arent = this;
btnEditPerson.S ize = new Size(120, 25);
btnEditPerson.L ocation = new Point(btnAddPer son.Right + 20,
btnAddPerson.To p);
btnEditPerson.T ext = "Edit";
btnEditPerson.C lick += new EventHandler(bt nEditPerson_Cli ck);

btnDeletePerson = new Button();
btnDeletePerson .Parent = this;
btnDeletePerson .Size = new Size(120, 25);
btnDeletePerson .Location = new Point(btnEditPe rson.Right + 20,
btnEditPerson.T op);
btnDeletePerson .Text = "Delete";
btnDeletePerson .Click += new
EventHandler(bt nDeletePerson_C lick);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(bt nOk_Click);

btnCancel = new Button();
btnCancel.Paren t = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Locat ion = new Point(btnOk.Rig ht + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visib le = false;
btnCancel.Click += new EventHandler(bt nCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Locati on = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(bt nClose_Click);
}

void btnAddPerson_Cl ick(object sender, EventArgs e)
{
cmGenerations.A ddNew();

txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnEditPerson_C lick(object sender, EventArgs e)
{
txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnDeletePerson _Click(object sender, EventArgs e)
{
cmGenerations.R emoveAt(cmGener ations.Position );
daGenerations.U pdate(dtGenerat ions);
}

void btnPreviousPers on_Click(object sender, EventArgs e)
{
if (cmGenerations. Position 0)
cmGenerations.P osition--;
}

void btnNextPerson_C lick(object sender, EventArgs e)
{
if (cmGenerations. Position < cmGenerations.C ount-1)
cmGenerations.P osition++;
}

void btnOk_Click(obj ect sender, EventArgs e)
{
cmGenerations.E ndCurrentEdit() ;
daGenerations.U pdate(dtGenerat ions);

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnCancel_Click (object sender, EventArgs e)
{
cmGenerations.C ancelCurrentEdi t();

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnClose_Click( object sender, EventArgs e)
{
Close();
}
}

Jul 22 '06 #3
mac
I'm a C# newbie, so I can't offer much on your code, but I'm pretty familiar
with databases.

The behavior you are describing is normal for a Foreign Key (FK) constraint.
An FK is a relational implementation of a subset constraint. If you remove
the parent row, but there are still child rows, you have violated the subset
constraint, and the database engine (propely) won't let you do that.
Depending on the particular database engine, FK's can be defined to do one
of three things upon attempted deletes:
1. RESTRICT (also called NO ACTION by some databses). This is the default
for a foreign key.
2. CASCADE DELETE
3. SET NULL
4. CASCADE UPDATE

Restrict FK's do what you have encountered. They disallow deletes. With a
RESTRICT key, you have to write your code to delete from the "bottom up".
You start deleting at the bottom of the generational hierarchy and work your
way up to the highest level.

With CASCADE DELETE foreign keys (which MS SQL Server supports), you can
just delete the parent, and the children are automatically deleted by the
engine. This can be useful, but you need to make sure you want to delete
that particular parent, because the engine won't stop you from hurting
yourself. Can't speak to SQL Server on this particular issue, but Oracle
generally does a lot better if you index the foreign key column sot aht the
engine can delete the correct rows quickly.

Don't know if SQL Server supports SET NULL. A SET NULL key doesn't delete
the child row, but nulls out the foreign key. This means you would be left
with "child" rows that cannot be identifed with any particular parent.

CASCADE UPDATE propogates updated primary keys to the child rows. However,
if you find the need to update primary keys, I think the database has bigger
problems. I have never used this.

By far the most common type of foreign key is RESTRICT. I've only seen
CASCADE used a few times, and have never heard of anyone actually using SET
NULL.

Thanks,

Mac

"clintonG" <cs*********@RE MOVETHISTEXTmet romilwaukee.com wrote in message
news:u4******** ********@TK2MSF TNGP02.phx.gbl. ..
MSSQL 2005 supports a type of referential integrity that will delete rows
in related tables but I don't know how to use it yet. Ordinarily our code
is responsible for the CRUD operations on the row(s) in other tables
related with Foreign Keys.
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee. com
URL http://www.metromilwaukee.com/clintongallagher/

"polocar" <po*****@tin.it wrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .
>Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations " (in which there is only one table, called
"Generations") , and it allows the user to add, edit and delete the
various records of the table.
"Generations " table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint):

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generations " database doesn't exist yet)
--GO (insert this statement only if "Generation s" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDP erson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerso n", "txtNamePerson" ,
"txtAgePerso n" and "txtIDParen t" TextBox objects, and a
"btnPrevoiusPe rson", "btnNextPerson" , "btnAddPers on",
"btnEditPerson ", "btnDeletePerso n", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generatio __IDPar_7D78A4E 7". The conflict has occurred in
"dbo.Generatio ns" table, column 'IDParent' of "Generation s"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generation s" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generation s" with the T-SQL code, so you have to
customize the cn.ConnectionSt ring C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collecti ons;
using System.Data;
using System.Data.Ole Db;
using System.Data.Com mon;
using System.Data.Sql Client;
using System.Drawing;
using System.Windows. Forms;

class SeparateMain
{
public static void Main()
{
Application.Run (new Generations());
}
}
class Generations: Form
{
/*************** *************** *************** *************** **************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/*************** *************** *************** *************** **************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent;
public Button btnPreviousPers on, btnNextPerson, btnAddPerson,
btnEditPerso n, btnDeletePerson , btnOk, btnCancel, btnClose;
public Generations()
{
// Form dimension and position
Size = new System.Drawing. Size(570, 300);
Text = "Generation s";
CenterToScreen( );

// SQL Server objects istantiation and initialization
cn = new SqlConnection() ;
daGenerations = new SqlDataAdapter( );
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.Dat aSetName = "TablesSet" ;

// Table "Generation s"
dtGenerations.T ableName = "Generation s";

dtGenerations.C olumns.Add("IDP erson", typeof(int));
dtGenerations.C olumns["IDPerson"].AutoIncrement = true;
dtGenerations.C olumns["IDPerson"].AutoIncrementS eed = 1;
dtGenerations.C olumns["IDPerson"].AutoIncrementS tep = 1;

dtGenerations.C olumns.Add("Nam ePerson", typeof(string)) ;
dtGenerations.C olumns["NamePerson "].AllowDBNull = false;
dtGenerations.C olumns["NamePerson "].DefaultValue = "";

dtGenerations.C olumns.Add("Age Person", typeof(int));
dtGenerations.C olumns["AgePerson"].AllowDBNull = false;

dtGenerations.C olumns.Add("IDP arent", typeof(int));

dtGenerations.P rimaryKey = new DataColumn[] {
dtGenerations. Columns["IDPerson"] };

dsTablesSet.Tab les.Add(dtGener ations);

// "Generation s" inner relation
dsTablesSet.Rel ations.Add("Gen erations_Parent Son",
dtGenerations. Columns["IDPerson"], dtGenerations.C olumns["IDParent"]);

cn.ConnectionSt ring = "Persist Security Info=False;Inte grated
Security=SSPI; database=Genera tions;server=(l ocal)\\TECLOGIC A;Connect
Timeout=10";

string strSQL;
SqlParameterCol lection pc;
SqlParameter param;

// "Generation s" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.S electCommand = new SqlCommand(strS QL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New ,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Orig ";
daGenerations.U pdateCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.U pdateCommand.Pa rameters;

pc.Add("@NamePe rson_New", SqlDbType.NVarC har, 50,
"NamePerson" );
pc.Add("@AgePer son_New", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt_New", SqlDbType.Int, 0, "IDParent") ;

param = pc.Add("@IDPers on_Orig", SqlDbType.Int, 0, "IDPerson") ;
param.SourceVer sion = DataRowVersion. Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.I nsertCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.I nsertCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;
pc.Add("@NamePe rson", SqlDbType.NVarC har, 50, "NamePerson ");
pc.Add("@AgePer son", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt", SqlDbType.Int, 0, "IDParent") ;

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.D eleteCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.D eleteCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;

daGenerations.F ill(dsTablesSet , "Generation s");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManage r)BindingContex t[dsTablesSet,
"Generations "];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Par ent = this;
lblIDPerson.Siz e = new Size(120, 25);
lblIDPerson.Loc ation = new Point(10, 10);
lblIDPerson.Tex t = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Par ent = this;
txtIDPerson.Siz e = new Size(120, 25);
txtIDPerson.Loc ation = new Point(lblIDPers on.Left,
lblIDPerson.Bo ttom);
txtIDPerson.Ena bled = false;
txtIDPerson.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.I DPerson");

lblNamePerson = new Label();
lblNamePerson.P arent = this;
lblNamePerson.S ize = new Size(120, 25);
lblNamePerson.L ocation = new Point(lblIDPers on.Right + 20,
lblIDPerson.To p);
lblNamePerson.T ext = "Name";

txtNamePerson = new TextBox();
txtNamePerson.P arent = this;
txtNamePerson.S ize = new Size(120, 25);
txtNamePerson.L ocation = new Point(lblNamePe rson.Left,
lblNamePerson. Bottom);
txtNamePerson.E nabled = false;
txtNamePerson.D ataBindings.Add ("Text", dsTablesSet,
"Generations.N amePerson");

lblAgePerson = new Label();
lblAgePerson.Pa rent = this;
lblAgePerson.Si ze = new Size(120, 25);
lblAgePerson.Lo cation = new Point(lblNamePe rson.Right + 20,
lblNamePerson. Top);
lblAgePerson.Te xt = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Pa rent = this;
txtAgePerson.Si ze = new Size(120, 25);
txtAgePerson.Lo cation = new Point(lblAgePer son.Left,
lblAgePerson.B ottom);
txtAgePerson.En abled = false;
txtAgePerson.Da taBindings.Add( "Text", dsTablesSet,
"Generations.A gePerson");

lblIDParent = new Label();
lblIDParent.Par ent = this;
lblIDParent.Siz e = new Size(120, 25);
lblIDParent.Loc ation = new Point(lblAgePer son.Right + 20,
lblAgePerson.T op);
lblIDParent.Tex t = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Par ent = this;
txtIDParent.Siz e = new Size(120, 25);
txtIDParent.Loc ation = new Point(lblIDPare nt.Left,
lblIDParent.Bo ttom);
txtIDParent.Ena bled = false;
txtIDParent.Dat aBindings.Add(" Text", dsTablesSet,
"Generations.I DParent");

btnPreviousPers on = new Button();
btnPreviousPers on.Parent = this;
btnPreviousPers on.Size = new Size(120, 25);
btnPreviousPers on.Location = new Point(150, 100);
btnPreviousPers on.Text = "Previous";
btnPreviousPers on.Click += new
EventHandler(b tnPreviousPerso n_Click);

btnNextPerson = new Button();
btnNextPerson.P arent = this;
btnNextPerson.S ize = new Size(120, 25);
btnNextPerson.L ocation = new Point(btnPrevio usPerson.Right +
20, btnPreviousPers on.Top);
btnNextPerson.T ext = "Next";
btnNextPerson.C lick += new EventHandler(bt nNextPerson_Cli ck);

btnAddPerson = new Button();
btnAddPerson.Pa rent = this;
btnAddPerson.Si ze = new Size(120, 25);
btnAddPerson.Lo cation = new Point(80, 160);
btnAddPerson.Te xt = "Add";
btnAddPerson.Cl ick += new EventHandler(bt nAddPerson_Clic k);

btnEditPerson = new Button();
btnEditPerson.P arent = this;
btnEditPerson.S ize = new Size(120, 25);
btnEditPerson.L ocation = new Point(btnAddPer son.Right + 20,
btnAddPerson.T op);
btnEditPerson.T ext = "Edit";
btnEditPerson.C lick += new EventHandler(bt nEditPerson_Cli ck);

btnDeletePerson = new Button();
btnDeletePerson .Parent = this;
btnDeletePerson .Size = new Size(120, 25);
btnDeletePerson .Location = new Point(btnEditPe rson.Right + 20,
btnEditPerson. Top);
btnDeletePerson .Text = "Delete";
btnDeletePerson .Click += new
EventHandler(b tnDeletePerson_ Click);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(bt nOk_Click);

btnCancel = new Button();
btnCancel.Paren t = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Locat ion = new Point(btnOk.Rig ht + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visib le = false;
btnCancel.Click += new EventHandler(bt nCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Locati on = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(bt nClose_Click);
}

void btnAddPerson_Cl ick(object sender, EventArgs e)
{
cmGenerations.A ddNew();

txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnEditPerson_C lick(object sender, EventArgs e)
{
txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnDeletePerson _Click(object sender, EventArgs e)
{
cmGenerations.R emoveAt(cmGener ations.Position );
daGenerations.U pdate(dtGenerat ions);
}

void btnPreviousPers on_Click(object sender, EventArgs e)
{
if (cmGenerations. Position 0)
cmGenerations.P osition--;
}

void btnNextPerson_C lick(object sender, EventArgs e)
{
if (cmGenerations. Position < cmGenerations.C ount-1)
cmGenerations.P osition++;
}

void btnOk_Click(obj ect sender, EventArgs e)
{
cmGenerations.E ndCurrentEdit() ;
daGenerations.U pdate(dtGenerat ions);

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnCancel_Click (object sender, EventArgs e)
{
cmGenerations.C ancelCurrentEdi t();

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnClose_Click( object sender, EventArgs e)
{
Close();
}
}


Jul 22 '06 #4
Very helpful mac. I found this [1] while rooting around which briefly
describes and links to the enhanced 2005 feature set noting cascading has
been updated and may be relative to this news article.

I don't know better at the moment but I'd rather use CASCADE DELETE and let
the database clean unwanted rows from related tables. Were you inferring the
FK in the related table should be indexed when using MSSQL?

<%= Clinton Gallagher

[1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx
"mac" <no**@none.comw rote in message
news:1ujwg.1517 2$Nv.10393@fed1 read10...
I'm a C# newbie, so I can't offer much on your code, but I'm pretty
familiar with databases.

The behavior you are describing is normal for a Foreign Key (FK)
constraint. An FK is a relational implementation of a subset constraint.
If you remove the parent row, but there are still child rows, you have
violated the subset constraint, and the database engine (propely) won't
let you do that. Depending on the particular database engine, FK's can be
defined to do one of three things upon attempted deletes:
1. RESTRICT (also called NO ACTION by some databses). This is the default
for a foreign key.
2. CASCADE DELETE
3. SET NULL
4. CASCADE UPDATE

Restrict FK's do what you have encountered. They disallow deletes. With
a RESTRICT key, you have to write your code to delete from the "bottom
up". You start deleting at the bottom of the generational hierarchy and
work your way up to the highest level.

With CASCADE DELETE foreign keys (which MS SQL Server supports), you can
just delete the parent, and the children are automatically deleted by the
engine. This can be useful, but you need to make sure you want to delete
that particular parent, because the engine won't stop you from hurting
yourself. Can't speak to SQL Server on this particular issue, but Oracle
generally does a lot better if you index the foreign key column sot aht
the engine can delete the correct rows quickly.

Don't know if SQL Server supports SET NULL. A SET NULL key doesn't delete
the child row, but nulls out the foreign key. This means you would be
left with "child" rows that cannot be identifed with any particular
parent.

CASCADE UPDATE propogates updated primary keys to the child rows.
However, if you find the need to update primary keys, I think the database
has bigger problems. I have never used this.

By far the most common type of foreign key is RESTRICT. I've only seen
CASCADE used a few times, and have never heard of anyone actually using
SET NULL.

Thanks,

Mac

"clintonG" <cs*********@RE MOVETHISTEXTmet romilwaukee.com wrote in message
news:u4******** ********@TK2MSF TNGP02.phx.gbl. ..
>MSSQL 2005 supports a type of referential integrity that will delete rows
in related tables but I don't know how to use it yet. Ordinarily our code
is responsible for the CRUD operations on the row(s) in other tables
related with Foreign Keys.
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee. com
URL http://www.metromilwaukee.com/clintongallagher/

"polocar" <po*****@tin.it wrote in message
news:11******* *************** @b28g2000cwb.go oglegroups.com. ..
>>Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generation s" (in which there is only one table, called
"Generations" ), and it allows the user to add, edit and delete the
various records of the table.
"Generation s" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his age and
the ID of his parent).
The "IDPerson" and "IDParent" fields are connected with a
PRIMARY - FOREIGN KEY constraint (I attach below the T-SQL Query that
creates the database with the table, the fields and the inner
constraint) :

USE master
GO
--DROP Database Generations; (insert this statement only if
"Generation s" database doesn't exist yet)
--GO (insert this statement only if "Generation s" database
doesn't exist yet)
CREATE DATABASE Generations
GO
USE Generations
GO
CREATE TABLE Generations (IDPerson int IDENTITY (1,1) PRIMARY KEY,
NamePerson nvarchar(50) NOT NULL, AgePerson int NOT NULL,IDParent int
NULL FOREIGN KEY REFERENCES Generations(IDP erson));
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Paul', 97, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Henry', 74, 1);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Bob', 51, 2);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Mike', 25, 3);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('John', 78, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Peter', 47, 5);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Patrick', 25, 6);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Michael', 2, 7);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Jim', 65, NULL);
INSERT INTO Generations (NamePerson, AgePerson, IDParent) VALUES
('Justin', 40, 9);

At this point I have created a very simple C# program to view the
fields' values of every record in a Form.
So, I have inserted a "txtIDPerso n", "txtNamePerson" ,
"txtAgePerson " and "txtIDParen t" TextBox objects, and a
"btnPrevoiusP erson", "btnNextPerson" , "btnAddPers on",
"btnEditPerso n", "btnDeletePerso n", "btnOk" and
"btnCancel" Button objects (I think the names explain clearly their
function).
I have written the code that imports the database structure and data in
the offline ADO.NET objects (included the relationship between
"IDPerson" and "IDParent" fields).

The issue that I don't solve is an error that raises when I try to
delete a person that has sons (in other words, a record with one or
more records descending).
For example, if I try to delete "Paul" (who has Henry as son, Bob
as grandchild and Mike as great-grandchild), the compiler gives the
following error:

"The DELETE statement is in conflict with SAME TABLE REFERENCE
"FK__Generati o__IDPar_7D78A4 E7". The conflict has occurred in
"dbo.Generati ons" table, column 'IDParent' of "Generation s"
database".

If I try to delete Mike (who has no sons), there are no problems.

The last thing I have noticed is that, if in the T-SQL statement I
create the "Generation s" table without the "IDPerson" -
"IDParent" constraint (but I leave it unchanged in the C# code),
everything functions correctly: if I try to delete Paul, the program
deletes Paul, Henry, Bob and Mike.

As I need to keep the constraint in the SQL Server database too (not
only in the C# code), do you know if there is a way to make it
function? (is it a bug of SQL Server 2005, or the bug is me?)

I attach the C# code below, if there is anyone who wants to test it (of
course you must connect to your own SQL Server 2005 instance where you
have created "Generation s" with the T-SQL code, so you have to
customize the cn.ConnectionSt ring C# statement. The T-SQL code for the
database creation and population is attached above).

Thank you very much

using System;
using System.Collecti ons;
using System.Data;
using System.Data.Ole Db;
using System.Data.Com mon;
using System.Data.Sql Client;
using System.Drawing;
using System.Windows. Forms;

class SeparateMain
{
public static void Main()
{
Application.Run (new Generations());
}
}
class Generations: Form
{
/*************** *************** *************** *************** **************/
/* CREAZIONE CONTROLLI PRESENTI NEL FORM
*/
/*************** *************** *************** *************** **************/

// SQL Server database objects
public SqlConnection cn;
public SqlDataAdapter daGenerations;
public DataSet dsTablesSet;
public DataTable dtGenerations;
public CurrencyManager cmGenerations;

// Graphical objects (Labels, TextBoxes and Buttons)
public Label lblIDPerson, lblNamePerson, lblAgePerson, lblIDParent;
public TextBox txtIDPerson, txtNamePerson, txtAgePerson,
txtIDParent ;
public Button btnPreviousPers on, btnNextPerson, btnAddPerson,
btnEditPerson , btnDeletePerson , btnOk, btnCancel, btnClose;
public Generations()
{
// Form dimension and position
Size = new System.Drawing. Size(570, 300);
Text = "Generation s";
CenterToScreen( );

// SQL Server objects istantiation and initialization
cn = new SqlConnection() ;
daGenerations = new SqlDataAdapter( );
dsTablesSet = new DataSet();
dtGenerations = new DataTable();

dsTablesSet.Dat aSetName = "TablesSet" ;

// Table "Generation s"
dtGenerations.T ableName = "Generation s";

dtGenerations.C olumns.Add("IDP erson", typeof(int));
dtGenerations.C olumns["IDPerson"].AutoIncrement = true;
dtGenerations.C olumns["IDPerson"].AutoIncrementS eed = 1;
dtGenerations.C olumns["IDPerson"].AutoIncrementS tep = 1;

dtGenerations.C olumns.Add("Nam ePerson", typeof(string)) ;
dtGenerations.C olumns["NamePerson "].AllowDBNull = false;
dtGenerations.C olumns["NamePerson "].DefaultValue = "";

dtGenerations.C olumns.Add("Age Person", typeof(int));
dtGenerations.C olumns["AgePerson"].AllowDBNull = false;

dtGenerations.C olumns.Add("IDP arent", typeof(int));

dtGenerations.P rimaryKey = new DataColumn[] {
dtGenerations .Columns["IDPerson"] };

dsTablesSet.Tab les.Add(dtGener ations);

// "Generation s" inner relation
dsTablesSet.Rel ations.Add("Gen erations_Parent Son",
dtGenerations .Columns["IDPerson"], dtGenerations.C olumns["IDParent"]);

cn.ConnectionSt ring = "Persist Security Info=False;Inte grated
Security=SSPI ;database=Gener ations;server=( local)\\TECLOGI CA;Connect
Timeout=10" ;

string strSQL;
SqlParameterCol lection pc;
SqlParameter param;

// "Generation s" UPDATE, INSERT, DELETE logic
strSQL = "SELECT IDPerson, NamePerson, AgePerson, IDParent FROM
Generations ORDER BY IDPerson";
daGenerations.S electCommand = new SqlCommand(strS QL, cn);

strSQL = "UPDATE Generations SET NamePerson = @NamePerson_New ,
AgePerson = @AgePerson_New, IDParent = @IDParent_New WHERE IDPerson =
@IDPerson_Ori g";
daGenerations.U pdateCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.U pdateCommand.Pa rameters;

pc.Add("@NamePe rson_New", SqlDbType.NVarC har, 50,
"NamePerson") ;
pc.Add("@AgePer son_New", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt_New", SqlDbType.Int, 0, "IDParent") ;

param = pc.Add("@IDPers on_Orig", SqlDbType.Int, 0, "IDPerson") ;
param.SourceVer sion = DataRowVersion. Original;

strSQL = "SET IDENTITY_INSERT Generations ON; INSERT INTO
Generations (IDPerson, NamePerson, AgePerson, IDParent) VALUES
(@IDPerson, @NamePerson, @IDParent); SET IDENTITY_INSERT Generations
OFF";
daGenerations.I nsertCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.I nsertCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;
pc.Add("@NamePe rson", SqlDbType.NVarC har, 50, "NamePerson ");
pc.Add("@AgePer son", SqlDbType.Int, 0, "AgePerson" );
pc.Add("@IDPare nt", SqlDbType.Int, 0, "IDParent") ;

strSQL = "DELETE FROM Generations WHERE IDPerson = @IDPerson";
daGenerations.D eleteCommand = new SqlCommand(strS QL, cn);
pc = daGenerations.D eleteCommand.Pa rameters;
pc.Add("@IDPers on", SqlDbType.Int, 0, "IDPerson") ;

daGenerations.F ill(dsTablesSet , "Generation s");

// Initialize CurrencyManager object
cmGenerations = (CurrencyManage r)BindingContex t[dsTablesSet,
"Generation s"];

// Graphical objects istantiation and initialization
lblIDPerson = new Label();
lblIDPerson.Par ent = this;
lblIDPerson.Siz e = new Size(120, 25);
lblIDPerson.Loc ation = new Point(10, 10);
lblIDPerson.Tex t = "ID";

txtIDPerson = new TextBox();
txtIDPerson.Par ent = this;
txtIDPerson.Siz e = new Size(120, 25);
txtIDPerson.Loc ation = new Point(lblIDPers on.Left,
lblIDPerson.B ottom);
txtIDPerson.Ena bled = false;
txtIDPerson.Dat aBindings.Add(" Text", dsTablesSet,
"Generations. IDPerson");

lblNamePerson = new Label();
lblNamePerson.P arent = this;
lblNamePerson.S ize = new Size(120, 25);
lblNamePerson.L ocation = new Point(lblIDPers on.Right + 20,
lblIDPerson.T op);
lblNamePerson.T ext = "Name";

txtNamePerson = new TextBox();
txtNamePerson.P arent = this;
txtNamePerson.S ize = new Size(120, 25);
txtNamePerson.L ocation = new Point(lblNamePe rson.Left,
lblNamePerson .Bottom);
txtNamePerson.E nabled = false;
txtNamePerson.D ataBindings.Add ("Text", dsTablesSet,
"Generations. NamePerson");

lblAgePerson = new Label();
lblAgePerson.Pa rent = this;
lblAgePerson.Si ze = new Size(120, 25);
lblAgePerson.Lo cation = new Point(lblNamePe rson.Right + 20,
lblNamePerson .Top);
lblAgePerson.Te xt = "Age";

txtAgePerson = new TextBox();
txtAgePerson.Pa rent = this;
txtAgePerson.Si ze = new Size(120, 25);
txtAgePerson.Lo cation = new Point(lblAgePer son.Left,
lblAgePerson. Bottom);
txtAgePerson.En abled = false;
txtAgePerson.Da taBindings.Add( "Text", dsTablesSet,
"Generations. AgePerson");

lblIDParent = new Label();
lblIDParent.Par ent = this;
lblIDParent.Siz e = new Size(120, 25);
lblIDParent.Loc ation = new Point(lblAgePer son.Right + 20,
lblAgePerson. Top);
lblIDParent.Tex t = "IDParent";

txtIDParent = new TextBox();
txtIDParent.Par ent = this;
txtIDParent.Siz e = new Size(120, 25);
txtIDParent.Loc ation = new Point(lblIDPare nt.Left,
lblIDParent.B ottom);
txtIDParent.Ena bled = false;
txtIDParent.Dat aBindings.Add(" Text", dsTablesSet,
"Generations. IDParent");

btnPreviousPers on = new Button();
btnPreviousPers on.Parent = this;
btnPreviousPers on.Size = new Size(120, 25);
btnPreviousPers on.Location = new Point(150, 100);
btnPreviousPers on.Text = "Previous";
btnPreviousPers on.Click += new
EventHandler( btnPreviousPers on_Click);

btnNextPerson = new Button();
btnNextPerson.P arent = this;
btnNextPerson.S ize = new Size(120, 25);
btnNextPerson.L ocation = new Point(btnPrevio usPerson.Right +
20, btnPreviousPers on.Top);
btnNextPerson.T ext = "Next";
btnNextPerson.C lick += new EventHandler(bt nNextPerson_Cli ck);

btnAddPerson = new Button();
btnAddPerson.Pa rent = this;
btnAddPerson.Si ze = new Size(120, 25);
btnAddPerson.Lo cation = new Point(80, 160);
btnAddPerson.Te xt = "Add";
btnAddPerson.Cl ick += new EventHandler(bt nAddPerson_Clic k);

btnEditPerson = new Button();
btnEditPerson.P arent = this;
btnEditPerson.S ize = new Size(120, 25);
btnEditPerson.L ocation = new Point(btnAddPer son.Right + 20,
btnAddPerson. Top);
btnEditPerson.T ext = "Edit";
btnEditPerson.C lick += new EventHandler(bt nEditPerson_Cli ck);

btnDeletePerson = new Button();
btnDeletePerson .Parent = this;
btnDeletePerson .Size = new Size(120, 25);
btnDeletePerson .Location = new Point(btnEditPe rson.Right + 20,
btnEditPerson .Top);
btnDeletePerson .Text = "Delete";
btnDeletePerson .Click += new
EventHandler( btnDeletePerson _Click);

btnOk = new Button();
btnOk.Parent = this;
btnOk.Size = new Size(120, 25);
btnOk.Location = new Point(150, 220);
btnOk.Text = "OK";
btnOk.Visible = false;
btnOk.Click += new EventHandler(bt nOk_Click);

btnCancel = new Button();
btnCancel.Paren t = this;
btnCancel.Size = new Size(120, 25);
btnCancel.Locat ion = new Point(btnOk.Rig ht + 20, btnOk.Top);
btnCancel.Text = "Cancel";
btnCancel.Visib le = false;
btnCancel.Click += new EventHandler(bt nCancel_Click);

btnClose = new Button();
btnClose.Parent = this;
btnClose.Size = new Size(120, 25);
btnClose.Locati on = new Point(420, 220);
btnClose.Text = "Close";
btnClose.Click += new EventHandler(bt nClose_Click);
}

void btnAddPerson_Cl ick(object sender, EventArgs e)
{
cmGenerations.A ddNew();

txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnEditPerson_C lick(object sender, EventArgs e)
{
txtNamePerson.E nabled = true;
txtIDParent.Ena bled = true;

btnPreviousPers on.Visible = false;
btnNextPerson.V isible = false;
btnAddPerson.Vi sible = false;
btnEditPerson.V isible = false;
btnDeletePerson .Visible = false;

btnOk.Visible = true;
btnCancel.Visib le = true;
}

void btnDeletePerson _Click(object sender, EventArgs e)
{
cmGenerations.R emoveAt(cmGener ations.Position );
daGenerations.U pdate(dtGenerat ions);
}

void btnPreviousPers on_Click(object sender, EventArgs e)
{
if (cmGenerations. Position 0)
cmGenerations.P osition--;
}

void btnNextPerson_C lick(object sender, EventArgs e)
{
if (cmGenerations. Position < cmGenerations.C ount-1)
cmGenerations.P osition++;
}

void btnOk_Click(obj ect sender, EventArgs e)
{
cmGenerations.E ndCurrentEdit() ;
daGenerations.U pdate(dtGenerat ions);

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnCancel_Click (object sender, EventArgs e)
{
cmGenerations.C ancelCurrentEdi t();

txtNamePerson.E nabled = false;
txtIDParent.Ena bled = false;

btnOk.Visible = false;
btnCancel.Visib le = false;

btnPreviousPers on.Visible = true;
btnNextPerson.V isible = true;
btnAddPerson.Vi sible = true;
btnEditPerson.V isible = true;
btnDeletePerson .Visible = true;
}

void btnClose_Click( object sender, EventArgs e)
{
Close();
}
}



Jul 22 '06 #5
mac
Hi Clinton,

I don't know if indexing the FK in SQL Server is a good idea or not. At
work, most of my database designs end up in Oracle. About six months ago,
we had occassion to implement CASCADE DELETE foreign keys, and the DBA (who
is a sharp guy) insisted that I put an index on the foreign key column.

He had seen cases where top level deletes became quite slow because of all
the additional deletes that were happening through the foreign keys.. Keep
in mind that this was a rather unusual situation, because we were talking
about a reporting instancethat was programatically updated (including
deletes) from another source. The code could specify a ton of deletes in a
very short time.

In the case of users specifying a deletes, I can't see that many deletes
happening at once.

Cascade Deletes frequently make me a little queasy, because often people try
to delete a parent without thinking of all the historical information they
are going to delete. A NO ACTION (RESTRICT) foreign key will prevent this,
and force the person to explicitly delete the child rows first. A CASCADE
key will allow the user to charge ahead.

Thanks,

Mac
"clintonG" <cs*********@RE MOVETHISTEXTmet romilwaukee.com wrote in message
news:um******** ******@TK2MSFTN GP03.phx.gbl...
Very helpful mac. I found this [1] while rooting around which briefly
describes and links to the enhanced 2005 feature set noting cascading has
been updated and may be relative to this news article.

I don't know better at the moment but I'd rather use CASCADE DELETE and
let the database clean unwanted rows from related tables. Were you
inferring the FK in the related table should be indexed when using MSSQL?

<%= Clinton Gallagher

[1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx

Jul 23 '06 #6
Good points to ponder. Not knowing a better strategy, in the past I've put a
flag in the top level table and simply changed the value of the flag and
referenced the value as a business rule to indicate the "authorizat ion" to
enable or disable access to the parent row and all related tables. Contrary
to convention it did allow me to retain historical data in the database.

<%= Clinton

"mac" <no**@none.comw rote in message
news:Onzwg.1519 6$Nv.6324@fed1r ead10...
Hi Clinton,

I don't know if indexing the FK in SQL Server is a good idea or not. At
work, most of my database designs end up in Oracle. About six months ago,
we had occassion to implement CASCADE DELETE foreign keys, and the DBA
(who is a sharp guy) insisted that I put an index on the foreign key
column.

He had seen cases where top level deletes became quite slow because of all
the additional deletes that were happening through the foreign keys..
Keep in mind that this was a rather unusual situation, because we were
talking about a reporting instancethat was programatically updated
(including deletes) from another source. The code could specify a ton of
deletes in a very short time.

In the case of users specifying a deletes, I can't see that many deletes
happening at once.

Cascade Deletes frequently make me a little queasy, because often people
try to delete a parent without thinking of all the historical information
they are going to delete. A NO ACTION (RESTRICT) foreign key will prevent
this, and force the person to explicitly delete the child rows first. A
CASCADE key will allow the user to charge ahead.

Thanks,

Mac
"clintonG" <cs*********@RE MOVETHISTEXTmet romilwaukee.com wrote in message
news:um******** ******@TK2MSFTN GP03.phx.gbl...
>Very helpful mac. I found this [1] while rooting around which briefly
describes and links to the enhanced 2005 feature set noting cascading has
been updated and may be relative to this news article.

I don't know better at the moment but I'd rather use CASCADE DELETE and
let the database clean unwanted rows from related tables. Were you
inferring the FK in the related table should be indexed when using MSSQL?

<%= Clinton Gallagher

[1] http://msdn2.microsoft.com/en-us/library/ms170355.aspx


Jul 23 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3606
by: M Wells | last post by:
Hi All, I have a table in SQL Server 2000 that contains several million member ids. Some of these member ids are duplicated in the table, and each record is tagged with a 1 or a 2 in to indicate where they came from. I want to remove all member ids records from the table that have a recsrc of 1 where the same member id also exists in the table with a recsrc of 2.
7
2981
by: Philip Mette | last post by:
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns acct_num,activity_date,and pay_amt and I want to delete one instance of a record in table 1 for every instance of that record in table 2 how could I do that. For example. Table 1 ----------- acct activity_date pay_amt
3
2102
by: John Rivers | last post by:
Hello, I think this will apply to alot of web applications: users want the ability to delete a record in table x this record is related to records in other tables and those to others in other tables etc. in other words we must use cascade delete to do
1
1424
by: Prabhat | last post by:
Hi All, Sorry that I am Asking this question in this Group. I have one doubt in SQL Server, Regarding how DELETE Works? What is the Actual Thing that SQL Server Do when we delete any record from the Database Table? Does it maintain any Falg Internally or actually Delete the Record from Database?
5
4201
by: Peggy Wu | last post by:
dear all, I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390. As below, for the performance issue, Does anyone know which one is better ?? To get a record that field A has a maximum value, either I could declare a cursor to select the qualified records order by field A descending, then fetch the 1st record. (exec-sql
2
3700
by: =?Utf-8?B?QmV0bw==?= | last post by:
I am developing a ASP.NET application, with C# code-behind classes; with SQL Server 2000. All database access is made through stored procedures. I can insert, select and update in all tables from C# through stored procedures. The problem I am having is with delete operation. When a delete-purposed stored procedure executes, the row is gone when I do a select on that same table. But if I check table contents from SQL Server's Enterprise...
1
2454
by: Bobby | last post by:
Hi I am using Access 2003 mdb as a front end to an application which uses SQL Server 2000 as the backend. The two are connected using ODBC. On one particular table (the Stock table), I have a simple form which updates stock. When the user presses the delete key, he gets the usual warning about not being able to undo this command, and the record disappears from the screen. However, on some records, when he goes back into the form, the...
11
4075
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved, CrtdUser and Date And Edit and Delete buttons
0
8733
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8649
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9223
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8934
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7822
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4410
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4662
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3102
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2035
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.