Solved

ASP.NET, How to handle Gridview foreign key constraints when deleting ??

Posted on 2011-03-07
8
1,294 Views
Last Modified: 2012-05-11
Hi,

What is the beat way to intercept this SQLException, stop processing and display the error message when using the standard Gridview features. Currently the best I can see is by using something similar to:

Protected Sub gv_OnRowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)

Try

Catch ex As System.Data.SqlClient.SqlException

some delete code here... Would expect a reference to the standard Gridview delete process somehow...

If ex.Number = 547 Then 'handle foreign key violation(547)

lblMsg.Text = "Record cannot be deleted as it is being used by other tables."

End If

End Try

End Sub

Open in new window


I guess there must be a very standard way of achieving this.

I am using ASP.NET2 and SQL Server 2005.

Thanks in advance,

Sam

0
Comment
Question by:SamJolly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 

Author Comment

by:SamJolly
ID: 35055875
Further research produced this, but I get a stackoverflow error:

 
Protected Sub _gv_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles _gvFunds.RowDeleting
        Try

            _gvFunds.DeleteRow(e.RowIndex) (An unhandled exception of type 'System.StackOverflowException' occurred in System.dll error on this line)

        Catch ex As System.Data.SqlClient.SqlException

            If ex.Number = 547 Then 'handle foreign key violation(547)

                Me._lblError.Text = "record cannot be deleted as it is being used by another table."

            End If

        End Try


    End Sub

Open in new window


Thoughts?

Thanks,

Sam
0
 
LVL 1

Accepted Solution

by:
alagurajan_logica earned 500 total points
ID: 35055995
Hi,

Insted of doing like this, you can get the current row reference from the GV and using the store procedure to delete the row from database, in the storeprocedure you can easily find is there any DB error is coming, if so use the output parameter to stop the process and display the error message.

Correct me if i am wrong

Rajan
0
 

Author Comment

by:SamJolly
ID: 35056126
Hi Rajan,

Yes I could imagine how one could use stored procedures for this. However there must be a standard approach to do this in ASP.NET by capturing the SQLException Error Number. Obviously I would like to understand this approach since I can use it for other errors.

Further thoughts?

Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SamJolly
ID: 35056494
Sorted in SqlDataSource_Deleted event handler.

Thanks,

Sam
0
 
LVL 1

Expert Comment

by:alagurajan_logica
ID: 35056663
Hi,
If you want to do it using Exception handling then what you have used in the coding is the correct way of handling, but in other thoughts it is not recommended way of doing this may cause performance problem to your application

Thanks
Rajan
0
 

Author Comment

by:SamJolly
ID: 35056862
Ok Rajan, thanks for your help.

So in your view this error checking should be handled in the Stored Procedure?

For better or worse I am using a SQLDataSource at present which also contains the Delete DDL code. So perhaps if I need to put this back into a SP then I can also move the error code as well.

0
 
LVL 2

Expert Comment

by:alagurjan
ID: 35057007
ok that is fine :)
0
 

Author Closing Comment

by:SamJolly
ID: 35063654
thks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Complete Life Cycle for all the controls 2 44
Set arraylist as datatable 2 29
Read oracle BLOB in vb.net 2 24
vb.net searchandselect 12 15
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question