In this little project we will investigate how to show data from a Microsoft Access database on a WebForm in our ASP.net website.  Here we will take a look at retrieving data from Microsoft Access. As always we are using Visual Studio Community 2015 or 2017 and a file system web site on my computer.

If you would like to download my Access database for use with this lesson, download it here .

Always virus scan downloaded files! You don't need to have Access installed you your computer for this project to work.

 In other projects we will be using Microsoft SQL and an XML data source of our own. Here we'll use Microsoft Access and scale down to keep it simple. I have created a very small DB with only a single table and three fields: ID, Author, and Quote.  There are 7 quotes in my database. My idea is to have a "Quote of the Day" style widget for my website, that uses a different quote for each time the page is visited.

1. First, we'll save a copy of the database to play with. Save it as, Access 2000-2003 using the ".mdb" file extension, making it compatible with all versions of Access and easier to connect to in VS 2015.  Using a .ACCDB is a little more complicated. Save the database copy to the Visual Studio subdirectory of your web site in the App_Data folder.  You can also drag it from the Windows file system to that folder in the open Visual Studio project. If you used the File, Save As method, you will need to right click on the App_Data folder in VS and choose Refresh Folder.

2. Now that we have a database in place, right-click on your Solution Name in the Solution Explorer window and choose Add, Add New Item and add a Web Form, making sure to correctly choose your programming language, Code File choice and Master Page choice. For all our web projects we are using Visual Basic, separate code file and the default master page for our web site.

3. There's not going to be much style formatting here, until I get to the finished widget. This will be plain wrapper stuff. VS now allows adding visual controls even when in Source View, so either in Design or Source View drag or double-click a GridView from the Data section of the Toolbox.

4. Click on the GridView's Smart Tag (you'll need to be in Design View for this) and click Choose Data Source and from the drop-down, <New Data Source>. From the Configuration Wizard, click Database. I know it shows SQL, but it really means several different kinds of database types, including the one we need which is OLEDB.  Change the default ID to AccessDB or something meaningful for you and click OK.

5. Data Source Connection Wizard opens. Click New Connection. Choose Microsoft Access Database File and click Continue. Next to the Database File Name field, click Browse and browse to your Quotes.mdb file in your solution App_Data folderr.  When you actually upload this to your web host's server you will need to change this path accordingly.  For now this should be all we need to see it run on your local Visual Studio copy of Internet Information Server.  Click the Test Connection button and you should get a success message. Click Next and accept ConnectionString as the string name and click Next again and make sure to choose all three columns; ID, Quote, and Author. You can view your query or click Finish.

Now your GridView should show column headings from the actual database in Design View. Right-click anywhere in your page, choose View in Browser, and let's see what happens. Hopefully your browser opens with something like this. If your table opens and the table isn't wide enough to allow all the text on one line, with the GridView selected, go to Properties and remove the value of the Width property.

So now that we are able to see our database, let's see if we can just retrieve single quotes and authors for our project.

1. We will do all these steps in Design View. Drag a FormView from the Toolbox to your Web Form, as we did for the GridView, click the FormView Smart Tag and for Choose Data Source, use the drop-down and choose the AccessDB datasource we already created. If asked by VS to Refresh the Schema, choose No. Easy, smeasy!

2. This step is a little more involved.  We need to do three quick tasks. There are several templates that dictate how our FormView will look. We will modify these templates by removing some of the default items. The templates we will edit are the ItemTemplate, HeaderTemplate, and the FooterTemplate.

From the FormView Smart Tag Tasks, click Edit Templates (at the bottom). First, with ItemTemplate appearing in the Display field, move to the left in the actual FormView and remove the text, ID: [IDLabel] completely. We don't want the database unique ID number to appear in our widget. Second, cut (not copy) the entire author's line: Author: [AuthorLabel]. Click the drop-down for the Display Field again and choose FooterTemplate. Paste what you just copied into the footer and then remove the text "Author: " and replace it with 4 or 5 spaces, a dash, and one more space. Third, click the Display field again and choose the HeaderTemplate and type in "~ Quote of the Day ~".  From the Task pane click End Template Editing.

3. Save your project and run it. We should get a very plain quote of the day, like this.

~ Quote of the Day ~

You miss 100 percent of the shots you never take.

-  Wayne Gretzky

4. Now let's pretty it up. For this tutorial we will use whatever this ASP.net control has as its Properties and see what we can come up with. Click on the FromView control and go to the Properties window in Visual Studio. If it is not open, press F4 and you should see it tabbed beneath the Solution Explorer window.  See my side bar on the right of this page for a  link to MSDN and using the IDE for Visual Studio.

Once you see the Properties for this control, maybe you can see a few properties I have modified and make your version look even better. We'll leave that up to you! If you want to be lazy, you can always choose Auto Format from the Tasks window. The one that probably fits my web site the best would be Rainy Day.  Hint: Here's something I didn't do. Look at Source View and notice how the Item and Footer Templates use a label to display text, but the Header Template only uses plain text. Bet you could do something with that!

~ Quote of the Day ~

You miss 100 percent of the shots you never take.

-  Wayne Gretzky

Well, so far, so good! Now let's look at two methods of showing data at different times. We will attempt to do a single quote for each day and a new quote for each visit to the web page. I am sure you have probably added a few goodies to your web page as we have been working along. If you look at the page you have right now, it says a lot about Visual Studio as a development tool that we have not even written a single line of Visual Basic yet.

Actually we're going to see if we can get a random quote on every page visit and still do it without any Visual Basic programming. We'll see if we can get ASP.net and Visual Studio to come through for us again by using the built in query of the data connector.

1. Let's start with our Quotes.aspx page open in Design View and while holding down the control key, click and drag a copy of the pretty version of the FormView control to a new area of the page. I usually put a couple of empty paragraphs in between where I am typing and the control I am working with. Do you remember how to add the data source and connection string?  Here we'll make a couple of changes.

Since we are going to attempt to modify this data using the Data Source's built in query function. We will create a new Data Source and name it AccessDB2. The original AccessDB source will show up in the Data Source field since we copied the FormView control and will remain active for the other data controls on this page. But we will need to make sure we do not apply any settings involved in this new DataSource to any of our data controls including the new control. If you click on any of the copied FromView controls you will see they have been given a different name. This one is FormView3.

2. So click on the FormView3 Tasks, and create a new AccessDB2 Data Source and click Configure Data Source. Leave the default ConnectionString and click Next.

3. Here's where we will modify our SELECT statement to pull a random row from our database. Click the radial button for "Specify a custom SQL statement or stored procedure."  Click Next. For our SELECT statement we will use:

SELECT TOP 1 ID, Author, Quote FROM Quotes ORDER BY Rnd(-(100000*ID)*Time())

Copy everything on the line above and paste into the SELECT Statement area and click Next. Test your SELECT statement and it should return a different quote almost every time.  A random number generated by RND() function. Even though the same quote may come up quite often, that's because we only have 7 quotes. Now click Finish. Immediately you will have a pop-up asking to refresh the data grids and schema for FormView3. Be sure to say NO.

4. Now we can run our page. If you are using my database or one similar, you should see the first quote in your database (in mine it is Wayne Gretzky) in all the data controls on our page, except for FormView3 which will be a random quote. Or at least as random as possible with only seven items to our query to choose from.

~ Quote of the Day ~

To the man who only has a hammer, everything he encounters begins to look like a nail.

-  Abraham Maslow

So there is our little widget, if you will. It's really just some ASP.net controls retrieving data from a database. I hope to have some more lessons on data access and manipulation soon. Before we call it quits, I would like to look at one more thing that Visual Studio is capable of doing that will help us when dealing with databases.

Creating an Access database without Microsoft Access is possible through programming, but that is beyond the scope of this lesson. Our next lesson will deal with modifying a database through controls on a web page. So if you downloaded my Access database, because you don't even have Access installed on your computer, no worries. If you were able to build a usable connection string, you can use Visual Studio to retrieve and modify your database.

1. Go to your App_Data folder or where ever you have your Quotes.mdb file or database and double-click it to open. If you have Access installed on your computer, Visual Studio will open it. You can just close it immediately and use Visual Studio instead. You should see on the left, sharing the Toolbox area, a tabbed window for Server Explorer.

2. Under Data Connections click the nodes to drill down to your database tables and right-click the Quotes table and choose Retrieve Data and VS will run a simple query on your table and return the data.

3. Your table will open a Query tabbed window in the center pane of Visual Studio. Here you can edit existing records and add new records to your table by entering data into the NULL fields. If you have a lot of records in your database, you can use the Navigation bar at the bottom of the Query window.

We won't be adding tables or using more complicated query language on this Access database. Where we are headed in this series is towards presenting data from SQL on our ASP.net web site.  Stay tuned.

If you see any mistakes or have any comments about this lesson, please use my Contact page to send me a message. Thanks for viewing my lesson.