How to handle exceptions with a SqlDataSource

Etienne picture Etienne · Apr 1, 2009 · Viewed 12.3k times · Source

I have a SqlDataSource that is supplying data to my GridView. Thats all i am using on my form, thus i have NO code behind at all. But somewhere i need a TRY CATCH block just in case my connection get's lost. What code must i place where?

If i get a error i want my lblMessage Text to be "No connection".

Edit

My GridView in my Machine.aspx

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" 
    Height="209px" PageSize="7" Width="331px" AllowSorting="True" 
                DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="Total" HeaderText="Total" ReadOnly="True" 
            SortExpression="Total" DataFormatString="{0:R#,###,###}" >
            <HeaderStyle HorizontalAlign="Left" />
        </asp:BoundField>
        <asp:BoundField DataField="b134_rmcid" HeaderText="Machine"  ReadOnly="True" 
            SortExpression="b134_rmcid" >
            <HeaderStyle HorizontalAlign="Left" />
        </asp:BoundField>
        <asp:BoundField DataField="b134_recdate" DataFormatString="{0:d/MM/yyyy}" 
            HeaderText="Date" ReadOnly="True" SortExpression="b134_recdate" >
            <HeaderStyle HorizontalAlign="Left" />
        </asp:BoundField>
    </Columns>
</asp:GridView>

My Connection right under my Gridview in my Machine.aspx

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ODBC_ConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:ODBC_ConnectionString.ProviderName %>" 

    SelectCommand="SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate FROM B134HRE" 
    onselected="SqlDataSource1_Selected">

</asp:SqlDataSource>

My Code in my Code Behind file in my Machine.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    lblError.Text = "hello there";
    SqlDataSource1.Selected += new SqlDataSourceStatusEventHandler(SqlDataSource1_Selected);


}


protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{

  if (e.ExceptionHandled)
   {

       lblError.Text = "There is a problem";  

   }

}

And still for some ready when i place a BreakPoint in my Selected Event it does not even get to it???

Why?

Answer

Paul Suart picture Paul Suart · Apr 1, 2009

The SqlDataSource has an Selected event. Add a handler to this event like so, and handle any errors (show an informative message etc) in this handler.

void GridView1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    if (e.ExceptionHandled)
    {
        //Show error message
    }
}

Sorry, but you're going to have to have some code in the code-behind!

Edit

Looking at your code, I don't think you're ever binding your GridView, so your SqlDataSource is never trying to select the data from your database.

In your Page_Load method, add the following code:

    if (!IsPostBack)
    {
        GridView1.DataBind();
    }

Further edit

Try changing "onselected" to "OnSelected" on your SqlDataSource and remove the line to bind your event handler in the code behind.

I'm stumped if that doesn't work as you've basically got the simplest-possible example.

Even further edit

Try this instead

void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    if (e.Exception != null)
    {
        //Show error message
        lblError.Text = "There is a problem"; 

        //Set the exception handled property so it doesn't bubble-up
        e.ExceptionHandled = true;
    }
}