Order Number
Customer
DVDName
DateOut
DateDue
DateIn
<%
// 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
%>
<%
}
}
// 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
%>
<%=orderId%>
<%=customerName%>
<%=dvdName%>
<%=dateOutPrint%>
<%=dateDuePrint%>
<%=dateInPrint%>
<%
}
odbcDataReader.Close();
if(odbcCommand != null)
odbcCommand.Dispose();
if(odbcConnection != null)
odbcConnection.Dispose();
}
catch(Exception ex)
{
throw ex;
}
%>