topical media & game development
basic-mysql-19-ASPApp-index.aspx / aspx
<%
*****************************************************************************
Author: Geoff Moes and Robert Sheldon
Project: Begining MySQL - Chapter 19
Module: index.aspx
Date: 12/10/04
Description:
This is the main listing page for the DVDRentals transaction application.
This page allows the user to list the current transactions and select
individual transactions for editing and deletion.
*****************************************************************************
%>
<%@ Page language="c#" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>
<html>
<head>
<title>DVD - Listing</title>
<link rel="stylesheet" href="dvdstyle.css" type="text/css">
<script language="JavaScript" src="dvdrentals.js"></script>
</script>
</head>
<body>
<form name="mainForm" method="post" action="index.aspx">
<input type="hidden" name="command" value="view">
<input type="hidden" name="transaction_id" value="">
<p></p>
<table cellSpacing=0 cellPadding=0 width=619 border=0>
<tr>
<td>
<table height=20 cellSpacing=0 cellPadding=0 width=619 bgcolor=#bed8e1 border=0>
<tr align=left>
<td valign="bottom" width="400" class="title">
DVD Transaction Listing
</td>
<td align="right" width="219" class="title">
<input type="button" value="New Transaction" class="add" onclick="doAdd(this)">
</td>
</tr>
</table>
<br>
<table cellSpacing="2" cellPadding="2" width="619" border="0">
<tr>
<td width="250" class="heading">Order Number</td>
<td width="250" class="heading">Customer</td>
<td width="250" class="heading">DVDName</td>
<td width="185" class="heading">DateOut</td>
<td width="185" class="heading">DateDue</td>
<td width="185" class="heading">DateIn</td>
<td width="99" class="heading"> </td>
<td width="99" class="heading"> </td>
</tr>
<%
// Declare and initialize variables with parameters retrieved from the form
String command = Request.Form["command"];
String transactionIdString = Request.Form["transaction_id"];
// Declare and initialize variables for database operations
OdbcConnection odbcConnection = null;
OdbcCommand odbcCommand = null;
// Wrap database-related code in a try/catch block to handle errors
try
{
// Create and open the connection
String strConnection = "driver={MySQL ODBC 3.51 Driver};" +
"server=localhost;" +
"database=DVDRentals;" +
"uid=mysqlapp;" +
"password=pw1";
odbcConnection = new OdbcConnection(strConnection);
odbcConnection.Open();
// Process the delete command
if(transactionIdString != null)
{
int transactionId = int.Parse(transactionIdString);
if("delete".Equals(command))
{
// Include the delete.aspx file
%>
<!-- #Include File="delete.aspx" -->
<%
}
}
// Construct the SQL statement
String selectSql = "SELECT " +
"Transactions.TransID, " +
"Transactions.OrderID, " +
"Transactions.DVDID, " +
"Transactions.DateOut, " +
"Transactions.DateDue, " +
"Transactions.DateIn, " +
"Customers.CustFN, " +
"Customers.CustLN, " +
"DVDs.DVDName " +
"FROM Transactions, Orders, Customers, DVDs " +
"WHERE Orders.OrderID = Transactions.OrderID " +
"AND Customers.CustID = Orders.CustID " +
"AND DVDs.DVDID = Transactions.DVDID " +
"ORDER BY Transactions.OrderID DESC, " +
"Customers.CustLN ASC, Customers.CustFN ASC, " +
"Transactions.DateDue DESC, DVDs.DVDName ASC";
odbcCommand = new OdbcCommand(selectSql, odbcConnection);
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader();
// Loop through the result set
while(odbcDataReader.Read())
{
// Retrive the columns from the result set into variables
int transId = (int) odbcDataReader["TransID"];
int orderId = (int) odbcDataReader["OrderID"];
int dvdId = (int) (short) odbcDataReader["DVDID"];
object obj;
String dateOutPrint = "";
String dateDuePrint = "";
String dateInPrint = "";
obj = odbcDataReader["DateOut"];
if(!obj.GetType().Equals(typeof(DBNull)))
{
DateTime dateOut = (DateTime) obj;
dateOutPrint = dateOut.ToString("MM-dd-yyyy");
}
obj = odbcDataReader["DateDue"];
if(!obj.GetType().Equals(typeof(DBNull)))
{
DateTime dateDue = (DateTime) obj;
dateDuePrint = dateDue.ToString("MM-dd-yyyy");
}
obj = odbcDataReader["DateIn"];
if(!obj.GetType().Equals(typeof(DBNull)))
{
DateTime dateIn = (DateTime) obj;
dateInPrint = dateIn.ToString("MM-dd-yyyy");
}
String custFirstName = (String) odbcDataReader["CustFN"];
String custLastName = (String) odbcDataReader["CustLN"];
String dvdName = (String) odbcDataReader["DVDName"];
// Format the result set into a readable form and assign variables
String customerName = "";
if(custFirstName != null)
customerName += custFirstName + " ";
if(custLastName != null)
customerName += custLastName;
if(dvdName == null)
dvdName = "";
// Print each value in each row in the HTML table
%>
<tr height="35" valign="top">
<td class="item">
<nobr>
<%=orderId%>
</nobr>
</td>
<td class="item">
<nobr>
<%=customerName%>
</nobr>
</td>
<td class="item">
<nobr>
<%=dvdName%>
</nobr>
</td>
<td class="item">
<nobr>
<%=dateOutPrint%>
</nobr>
</td>
<td class="item">
<nobr>
<%=dateDuePrint%>
</nobr>
</td>
<td class="item">
<nobr>
<%=dateInPrint%>
</nobr>
</td>
<td class="item" valign="center" align="center">
<input type="button" value="Edit" class="edit" onclick="doEdit(this, <%=transId%>)">
</td>
<td class="item" valign="center" align="center">
<input type="button" value="Delete" class="delete" onclick="doDelete(this, <%=transId%>)">
</td>
</tr>
<%
}
odbcDataReader.Close();
if(odbcCommand != null)
odbcCommand.Dispose();
if(odbcConnection != null)
odbcConnection.Dispose();
}
catch(Exception ex)
{
throw ex;
}
%>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>
(C) Æliens
20/2/2008
You may not copy or print any of this material without explicit permission of the author or the publisher.
In case of other copyright issues, contact the author.
