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.
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ChildTable] WITH CHECK ADD CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY([ParentId])
REFERENCES [dbo].[ParentTable] ([Parentid])
ON DELETE CASCADE
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
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
property is set to
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
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.