CIS 430 Assignment 4 -- Database Connectivity and Catalog Pages

This assignment requires that your operating system is capable of supporting Visual Studio.Net 2003 and that you have sufficient privileges to download and install dll files.  If you do not have Visual Studio.Net 2003 at home, then you will need to use the computers in the lab, and you will need to follow the directions below if the lab machines prohibit installation of software.

Create a new page for your eCommerce site that will serve as a catalog page.  This page will be populated with some product information stored in a SQLServer site as well as some data stored in a MySQL site.

Much of the code will be provided for this exercise, although you will later set up your own database with product information that you will then have to access instead of the sample database.

Step 1:  Download the ADO.Net driver for MySQL:

1. Go to http://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-1.0.4.zip/from/pick
2. Select a mirror, and save the zip file somewhere that you can find it (like your desktop)
3. Unzip the file, again to a location where you can find it.
4. Double click the .msi file to install it (complete install, don't change the path or any other options)

Step 2: Create an ASP.Net page:

1. Start Visual Studio .NET 2003
2. Select "New Project"
3. In the New Project dialog box select "ASP.NET Web Application" and....
   In the Location field enter "http://classweb.students.cob.isu.edu/cis430/<username>/dotnet (this will create an application sub folder that any asp.net pages can run in, asp.net pages will not run in their existing folder).  Be sure to replace <username> with your cobclasses username.

Note #1: If you get an error at this point regarding the ASP versions, simply hit Cancel and do it again.

Note #2: If you get a message box stating (in the title bar) that Web Access Failed then click on "Try to open the project with FrontPage Server Extensions."

4. Go to Project->Add Reference
5. Click the Browse button
6. Assuming you are running Windows XP, find C:\Program Files\MySQL\MySQL Connector Net 1.0.4\bin\.NET 1.1\MySQL.Data.dll and select it. Then hit Open.
7. Click OK on the Add Reference dialog box
8. To start entering code, right click on Page and select View Code
9. Put in the proper Imports statements (see below) and write the VB code (see details below) to connect to and retrieve data from the databases.  Use the code in the SQL Server presentation for an example.
10. When you complete code entry, do NOT hit Run, but instead click File, View in Browser.
     

You may want to go into the web.config file and change the custom errors from "RemoteOnly" to "Off", and yes it is case sensitive.  That way if there are any errors it will display them on the web page in the browser rather than tell you that you can't see the errors.  The web.config file will be located in the same directory as your aspx page.  This is not a required step, but may make things easier.

A quick example created by Mike Bradley can be found at http://classweb.students.cob.isu.edu/CIS430/parkerkr/DB_Test/WebForm1.aspx

The VB code segments for the page follow:

Imports Statements:

• Imports System.Data

• Imports System.Data.SqlClient

• Imports MySql.Data.MySqlClient

SQL Server details:

• server=134.50.5.137

• database=cis430sp05

• user=iusr_cis430

• password=microsoftisgood

MySQL details:

• server=134.50.5.137
• database=cis430sp05
• userid=iusr_cis430
• password=opensourceisbad

Database table details (same in both database tables):

• Table name: Products

• Field1: productID (use ToString in Response.Write)

• Field2: productName

• Field3: productDesc

• Field4: productpicurl (sample code: Response.Write("<img src='" + MySqlDataReaderVariable("productpicurl") + "' /><br><br><br>"))

To determine the URL of your page, click Project and then the Properties option at the bottom of the drop-down menu.  You can type that URL into a browser to view your page.  That is the URL that you will link to the Index page.

Note:  If you attempt to cut from a browser and paste into Frontpage you will get additional formatting characters.  Instead paste into Notepad, then cut from Notepad and paste into Frontpage.  That removes the extraneous formatting.

Due one week after lecture on SQL Server.

Directions for Lab Computers

If you do not have privileges to install software (for example, on the lab machines) then instead of Step 1 above you will need to go to http://dev.mysql.com/downloads/connector/net/1.0.html and click the link to download the Windows Source and Binaries, no installer (ZIP).  Unzip it in a new folder that you can easily locate.

In Step 2f, you will have to browse to the subfolder in your new folder to locate MySql.Data.dll.  For example, when I created the folder on my machine I called it mysqlcon and stored it on the desktop.  The dll file was then stored in the C:\Documents and Settings\Kevin\Desktop\mysqlcon\bin\net-1.1\release folder.

The rest of the steps listed above should be the same.