ASP.NET database programming Access connection failed

Errors and failures will inevitably occur in the application. What happens if someone "updates" the server's directory tree by adding a new level of folders on top of the MDB directory? What happens if the MDB name is changed? What if the MDB file is corrupted? Each of the above problems will prevent a successful connection to the data and result in a failed page. Good programming practice shows that you need to be as careful as possible with any failures.

Before we get into the actual commands, we need to understand that the AccessDataSource control is derived from the SqlDataSource control. In most cases, this is just a background question. But when handling exceptions, you must use the object that actually exists in (and hence is named for) the SQL data source object.

The code used for the soft landing technique will be triggered when the AccessDataSource control experiences OnSelected event. This event will be triggered internally when GridView requests data from the data source control. The code that handles the connection error checks for the exception parameter passed from the data source control. The AccessDataSource control does not have its own name for this parameter, so you can only use the name SqlDataSourceStatus-EventArguments. If the exception parameter is null, then nothing has happened. If the exception parameter has a value, this value is checked. If the parameter is of type OLEDB Exception, the text of the warning label on the page will be prompted. Again, notice the use of terminology. It would be clearer if there were an AccessException type, but there is none. You can use the more generic OleDbException object and end the script with a command to handle the exception. This will allow GridView to continue rendering without data and prevent the regular ASP.NET 2.0 failure page from appearing on a light brown background. Because GridView does not get any data, it will display a replacement table with only one cell that displays the message in its EmptyDataText attribute.

If you are having trouble with these steps, don't be pessimistic; The next exercise will be demonstrated. Now, just cut and paste the code into the page. Later in the book, I'll discuss how to create a replacement page for GridView in the case of a connection failure and the details of handling error events.

try # 4 -- handle AccessDataSource connection failure

(1) in the ch02 folder, create a file named TIO-4-ConnectionFailure-CS.aspx. In the Design view, add an AccessDataSource control to the page that points to Northwind and selects all columns from the table.

(2) add GridView to display the information in the data source control. Again, add a label control and name it "Message".

(3) now switch to the Source view and make some changes to the markup, as shown in the highlighted code below. If there is a < columns > tag part, delete it. The code left behind should look like this:

The < html >
< head id="Headl" runat="server" >
< title > Chapter 2 TIO #4 Connection Failure to in C# < /title >
< / head >
The < body >
< h3 > Chapter 2 TIO #4 Connection Failure to Access in C# < /h3 > Chapter 2 TIO #4 Connection Failure to Access in C < form id="forml" runat="server" >
< asp:label ID="Message" runat="server"/ > < br/ > < br/ >
< asp: gridview id = "GridViewl runat" = "server
" datasourceid="AccessDataSourcel"
AutoGenerateColumns="true"
EmptyDataText="No data records were returned" / >
< asp: AccessDataSource ID = "AccessDataSourcel Runat" = "server
" selectcommand="Select * From Products"
datafile="~/App_Data/Northwind.mdb"
OnSelected="AccessDataSourcel_Selected"
/ >

< / form >
< /body > < /html >
(4) check the page; There should be no problems when viewing products sold by Northwind.

(5) code will now be added to handle the connection problem. Go to the top of the page and enter the following script. The first example is written in C# and the second in VB. Just enter one of them.

< %@ page language="C#" % >
< script runat="server" >

void AccessDataSourcel_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if (e Exception! =
null) The & # 123;
if (e. Exception. GetType () = = typeof (System. Data. OleDb. OleDbException))
The & # 123;
Text = "There was a problem opening a connection connection the
Please contact the system administrator for this site ";

//Optionally set GridViewl.Visible = false;

e. ExceptionHandled = true;
The & # 125;
The & # 125;
}

< / script >
The < html >...
The following is a script written with VB.

< %@ Page Language="VB" % >
"http: / / www. w3. org/TR/xhtmlll/DTD/xhtmlll dtd" >

< script runat="server" >
Sub AccessDataSourcel_Selected(ByVal sender As Object, ByVal e As
SqlDataSourceStatusEventArgs)
If (Not e.Exception Is Nothing) Then
If TypeOf e. Exception Is System. Data. OleDb. OleDbException Then
Message. Text = "There was problem opening a
'Optionally set GridViewl.Visible = false
e ExceptionHandled = True
End If
End If
End Sub

< / script >
The < html >...

(6) save and run the page. Since our actual connection is still intact, we don't have any problems yet. Close the browser.

(7) now move the MDB file of Northwind from \App_Data to the C:\Temp folder. The connection will fail. Alternatively, you can modify the code to try to connect to Southwind.mdb. Run the page and note that the browser will display a decent failure message.

(8) if Northwind.mdb has been moved, then move it back to C:\BegAspNetDb\ App_ Data folder.

Example # 4 -- handling AccessDataSource connection failure

First, remember that the AccessDataSource control is a descendant of SqlDataSource and USES a series of exceptions that apply to OLEDB data sources, so don't be surprised if you reference an object with an SQL or OLEDB name instead of an Access name.

Observe the three changes made on the page to handle the connection failure. First, add a property of the GridView data source control that displays a message when GridView does not get any data from the data source control. Second, add a property of the data source control that calls the Data_Selected event handler when an OnSelected event occurs. Please note that this is in the DataSource event. Although the user does not directly select the AccessDataSource control (nor is it presented to the user), the selection occurs internally when GridView requests data from the data source control. Third, you wrote the script.

The script will receive several parameters, one of which is an exception. There is no object called AccessDataSourceStatusEventArgs. However, you can get a base object that internally derives Access-DataSource: SQL DataSource. The SqlDataSource object has state parameters that can be submitted to the AccessDataSource control. If there is no problem, the exception list is empty. If there is an exception, the code will test to see what type of exception is thrown. Likewise, there are no objects like Access Exception. However, AccessDataSource puts the exception into a more common object called OleDb Exception. Assume that all exceptions in this set are caused by a connection failure. Our code will respond with some friendly failure announcements to the tag Message.

The biggest trick in this code is to always have the object use three different names. An Access file (MDB) is used as the data source and the AccessDataSource control is used. But the underlying SqlDataSource is used as an event parameter. Finally, the normal OLEDB exception set is used. Many errors arise from using an ASP.NET 2.0 object named Access in all cases.