You may see some SQLExceptions while trying to perform add , delete or update operation using the DataGridView control in .net framework.
This post will speak about Adding and Deleting rows to the relation tables using windows application using DataGridView control in .net Framework 4.0 using Visual Studio 2010.
I have created 2 tables called Parent table and Child table in SQL 2008 R2 with a foreign key constraint between tables and then create Windows application with controls to demonstrate the functionality.
Create Parent Table
USE [Test]
GO
/****** Object: Table [dbo].[ParentTable] Script Date: 01/25/2011 03:00:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ParentTable](
[Parentid] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nchar](10) NULL,
CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED
(
[Parentid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Child Table
USE [Test]
GO
/****** Object: Table [dbo].[ChildTable] Script Date: 01/25/2011 02:59:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChildTable](
[ChildId] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED
(
[ChildId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChildTable] WITH CHECK ADD CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY([ParentId])
REFERENCES [dbo].[ParentTable] ([Parentid])
ON DELETE CASCADE
GO
Create New Windows application in Visual studio 2010 .Net Framework 4.0 and add New Data Source as shown in figure below
After selecting Databases and click next until you encounter the below page, then select or create the connection string to the database server and again keep clicking next until you see the wizard with shows Choose your Database objects .
Select the Tables, for which you want to create data source in the wizard.
Click Finish. This creates the Data source for 2 relation tables.Once done, go to the dataset in designer mode and verify that Hierarchal Update property is set to True .
From the Data Source Windows drag the Columns of parent table and then drag child named column from parent table to Windows which creates labels , textboxes and DataGridView control as shown below
Without doing any modifications to the Code- behind file , when we try to run the application we get the SQLException as below .
Designer generated code in VB.Net
Public Class RelationTables
Private Sub ParentTableBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ParentTableBindingNavigatorSaveItem.Click
Me.Validate()
Me.ParentTableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.TestDataSet)
End Sub
Private Sub RelationTables_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestDataSet.ParentTable' table. You can move, or remove it, as needed.
Me.ParentTableTableAdapter.Fill(Me.TestDataSet.ParentTable)
'TODO: This line of code loads data into the 'TestDataSet.ChildTable' table. You can move, or remove it, as needed.
Me.ChildTableTableAdapter.Fill(Me.TestDataSet.ChildTable)
End Sub
End Class
Execute Application and try Adding or deleting records from application.
Error with SqlException is shown below
This exception says “The Insert statement conflicted with the foreign key constraint…” is because of constraint relationship between tables.
Let’s alter the code to add rows in the code-behind class of form.
Add AddingNew event in code-behind class of the form.
In VB.Net
Public Class RelationTables
Private Sub ParentTableBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ParentTableBindingNavigatorSaveItem.Click
Me.Validate()
Me.ParentTableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.TestDataSet)
End Sub
Private Sub RelationTables_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestDataSet.ParentTable' table. You can move, or remove it, as needed.
Me.ParentTableTableAdapter.Fill(Me.TestDataSet.ParentTable)
'TODO: This line of code loads data into the 'TestDataSet.ChildTable' table. You can move, or remove it, as needed.
Me.ChildTableTableAdapter.Fill(Me.TestDataSet.ChildTable)
End Sub
Private Sub ChildTableBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles ChildTableBindingSource.AddingNew
Me.ParentTableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.TestDataSet)
End Sub
End Class
In C#.Net
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication2
{
public partial class RelationTables : Form
{
public RelationTables()
{
InitializeComponent();
}
private void parentTableBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.parentTableBindingSource.EndEdit();
this.childTableBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.testDataSet1);
}
private void RelationTables_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'testDataSet1.ChildTable' table. You can move, or remove it, as needed.
this.childTableTableAdapter.Fill(this.testDataSet1.ChildTable);
// TODO: This line of code loads data into the 'testDataSet1.ParentTable' table. You can move, or remove it, as needed.
this.parentTableTableAdapter.Fill(this.testDataSet1.ParentTable);
}
private void childTableBindingSource_AddingNew(object sender, AddingNewEventArgs e)
{
this.parentTableBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.testDataSet1);
}
}
}
With this code change , we can add rows successfully but still it will fail to deleted rows using windows form application.
To get the delete operation also to be working we need to do changes in Table design , go to Properties of Parent Table , select relationships change delete and Update Rule to cascade.
With these modifications we can now add the rows and delete rows from the Windows application using DataGridView control for relation tables in .Net framework.
Author : Archana , SQL Developer Engineer , Microsoft
Reviewed by : Naresh , SQL Developer Technical lead , Microsoft