I'm doing a project which is using asp.net and access.
Just wondering if anyone has a clue how I'd connect them as I'm having trouble with it.
Thanks a million,
Clare

Clare Hennessy

1. Create a WebForm
2. Add the following using directive to the top of the page to reference the OleDb versions of ADO.NET objects: using System.Data.OleDb
3. Add the following to the WebForms Page_Load event:

(I use C# but the VB.NET code is virtually identical)

private void Page_Load(object sender, System.EventArgs e)
        {
            //Set connection string.
            string connectString = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=C:\\Northwind.mdb";

            //Pass connection string to OleDbConnection object.
            OleDbConnection cn = new OleDbConnection(connectString);

            //Open connection.
            cn.Open();

            //Pass SQL statement to OleDbCOmmand object.
            string selectString = "SELECT CategoryID, CategoryName FROM Categories";
            OleDbCommand cmd = new OleDbCommand(selectString,cn);

            //Execute an OleDbDataReader
            OleDbDataReader reader = cmd.ExecuteReader();

            //Bind DataReader to DataGrid
            DataGrid1.DataSource = reader;
            DataGrid1.DataBind();

            //Close reader and connection.
            reader.Close();
            cn.Close();
        }

That's the bare bones. Just binds a forward-only, read-only recordset to a DataGrid web control.

HTH,

Bob

Should probably mention that you want to avoid hard-coding connection strings in individual WebForms as I did above. Its pretty much standard practice to maintain them in your apps Web.config file. To do that, first add another using directive to the code-behind class of your WebForm: using System.Configuration. Then in the <configuration> section of your Web.config file, create the followiing key:

<configuration>
    <appSettings>
        <add key="ConnectionString"
             value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb"/>
    </appSettings>
 <configuration>

Now you can change the line in your WebForms Page_Load event that sets the connection string from:

//Set connection string.
string connectString = "Provider=Microsoft.Jet.OLEDB.4.0;"
    + "Data Source=C:\\Northwind.mdb";

to:

//Set connection string.
string connectString = ConfigurationSettings.AppSettings["ConnectionString"];

Hi there.
Thanks a million for that Bob!

I've tried using that code but I'm getting an error when running it in asp.net like the following:

The type or namespace 'OleDbConnection' could not be found ( are you missing a using directive or an assembly ref?)

Any ideas?
Thanks alot,
Clare.

Clare Hennessy

Hi Clare,

First be sure the Page_Load event code I posted is added to the code-behind class of your WebForm. Don't use it as in-line script in your asp.net page.

Second, ASP.NET Web Apps add the following default using directives to the top of the code-behind class:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

You need to add the following to this list:

using System.Data.OleDb

This is the namespace where the OleDbConnection object lives.

There is an outside chance that you are missing an assembly reference to the System.Data.dll assembly where ADO.NET lives. Open your project's References folder and look for a reference to System.Data. If it isn't there, add it by right-clicking on the folder and selecting System.Data.dll from the Add References dialog.

HTH,

Bob

Hi, I am very new to programming world.Doing a project work based on Visual C# and ASP.NET. I had been trying to connect asp.net with microsoft access 2000 for last week itself.Thanks to the Wrox team for providing the free support in the net.

somissac