preventing error with sqldatasource in aspx file

Question

Hi,

how to prevent error caused by a sql statement in sqldatsource in aspx file e.g. "Cannot insert duplicate key row in object 'dbo.table1'"? I would replcace that ugly message by my own message.

Thanks

V

Sachin Singh · Answer

You are handling it correctly, for custom messages use this void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) { if (e.Exception != null) { lblError.Text = "There is a problem in sql statement" ; e.ExceptionHandled = true ; } }

Rijwan Ansari · Answer

I can add few more suggestions. - Always validate the data before entring into SQL like duplicate check, data type validations, length and so on. - You can capture exceptions/errors into log file. Create generic function for to handle that. - Redirect to error page for unhandle error/exception with generic message like something went wrong. Regards Rijwan

Valerie Meunier · Answer

Of course that i can't insert a duplicate key if it's unique. That's not the point. The point is how to avoid the ugly message from aspx file "cannot insert etc ...". I don't think it's possible to do that with try ... catch because there is no code in try. The code which causes the error is the insert sql statement in the aspx file. The detailsview is in insert mode, so i think this must be present in detailsview OnItemInserted="DetailsView1_ItemInserted".But with try/catch, it doesn't work because try is empty.

Sachin Singh · Answer

so, what's wrong with it @Meunier, it's a proper error, you can't insert a duplicate key if it is unique.

Valerie Meunier · Answer

This is the aspx code. It's about a detailsview in insert mode. When typing a duplicate record, i still get the aspx message "Cannot insert duplicate keyr etc ..." (because of the unique index in sql server) Otherwise, there is no error. Thanks " DeleteCommand="DELETE FROM [Inschrijvingen] WHERE [Inschr_nr] = @Inschr_nr" InsertCommand="INSERT INTO [Inschrijvingen] ([Lidnr], [Taknr] VALUES (@Lidnr, @Taknr)" UpdateCommand="UPDATE [Inschrijvingen] SET [Lidnr] = @Lidnr, [Taknr] = @Taknr WHERE [Inschr_nr] = @Inschr_nr" SelectCommand="SELECT * from [inschrijvingen]" OnSelected="SqlDataSource1_Selected">

Sachin Singh · Answer

you are doing it correctly, make sure you have the event name specified in the SqlDataSource in the aspx page "SqlDataSource1" runat= "server" ConnectionString= " " SelectCommand= "your select command'" OnSelected= "SqlDataSource1_Selected" > "ID" QueryStringField= "ID" />

Sachin Singh · Answer

One way try { // code here } catch (SqlException sqlerror) { Server.Transfer( "SqlErrorPage.aspx" , true ); } catch (Exception ex) { // Handle generic ones here. } second way try { // code here } catch (Exception ex) { if (ex is SqlException) { Server.Transfer( "SqlError.aspx" , true ); } else { // Handle generic ones here. } } I have used Server.Transfer() instead of Response.Redirect(), so that you could use, previous page properties if you wish. TextBox txt = (TextBox)Page.PreviousPage.FindControl( "serverNameText" );

Valerie Meunier · Answer

Thanks. This works.

Valerie Meunier · Answer

Thanks, but i tried this without succes. Maybe i 'm using the wrong event? I also tried with _Selecting and _DataBibnding ... Nothing works. using System; using System.Web.UI.WebControls; using System.Data.SqlClient; public partial class abonn_new : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DetailsView1.Focus(); } protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) { try { // you can check if Select Command is blank etc., etc., } catch ( SqlException ex) { Console.WriteLine(ex.Message); Console.WriteLine("My own message."); } } }

Jefferson S. Motta · Answer

SqlException Class (System.Data.SqlClient) | Microsoft Docs public static void ShowSqlException(string connectionString) { string queryString = "EXECUTE NonExistantStoredProcedure" ; StringBuilder errorMessages = new StringBuilder(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); try { command.Connection.Open(); command.ExecuteNonQuery(); } catch (SqlException ex) { for ( int i = 0; i { errorMessages.Append( "Index #" + i + "
" + "Message: " + ex.Errors[i].Message + "
" + "LineNumber: " + ex.Errors[i].LineNumber + "
" + "Source: " + ex.Errors[i].Source + "
" + "Procedure: " + ex.Errors[i].Procedure + "
" ); } Console.WriteLine(errorMessages.ToString()); } } } c# - How to Identify the primary key duplication from a SQL Server 2008 error code? - Stack Overflow try { // insertion code } catch (SqlException ex) { if (ex.Number == 2627) { //Violation of primary key. Handle Exception } else throw ; }