preventing error with sqldatasource in aspx file

Valerie Meunier

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

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted 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;
    }
}
Valerie Meunier5y ago
Thanks. This works.
Valerie Meunier5y ago
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 Singh5y ago
so, what's wrong with it @Meunier, it's a proper error, you can't insert a duplicate key if it is unique.
Rijwan Ansari5y ago
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 Meunier5y ago
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 Singh5y ago
you are doing it correctly, make sure you have the event name specified in the SqlDataSource in the aspx page
"SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="your select command'" OnSelected="SqlDataSource1_Selected">

"ID" QueryStringField="ID"/>
Valerie Meunier5y ago
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.");
}
}
}
Sachin Singh5y ago
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");
Jefferson S. Motta5y ago
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 < ex.Errors.Count; i++)
            {
                errorMessages.Append("Index #" + i + "\n" +
                    "Message: " + ex.Errors[i].Message + "\n" +
                    "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                    "Source: " + ex.Errors[i].Source + "\n" +
                    "Procedure: " + ex.Errors[i].Procedure + "\n");
            }
            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;
}