SQLExceptions while trying to perform add , delete or update operation using the DataGridView control

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 withshows  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