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

Published Jan 15 2019 01:37 PM 54 Views
First published on MSDN on Feb 23, 2011

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

Version history
Last update:
‎Jan 15 2019 01:37 PM
Updated by: