<% /******************************************************************************* * Author: Geoff Moes and Robert Sheldon * Project: Begining MySQL - Chapter 19 * Module: edit.aspx * Date: 12/10/04 * * Description: * * This page lists the individual record. It is called from the main page. * If the page is called with an "add" command, it creates a blank record * and populates it with default values. If it is called with an "edit" * command, it displays an existing record. * *******************************************************************************/ %> <%@ 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" %> <% // Initialize variables with parameters retrieved from the form String command = Request.Form["command"]; String transactionIdString = Request.Form["transaction_id"]; String transIdString = Request.Form["TransID"]; String orderIdString = Request.Form["OrderID"]; String dvdIdString = Request.Form["DVDID"]; String dateOutString = Request.Form["DateOut"]; String dateDueString = Request.Form["DateDue"]; String dateInString = Request.Form["DateIn"]; // Declare and initialize variables with default values OdbcConnection odbcConnection = null; DateTime dateDue = DateTime.MinValue; DateTime dateOut = DateTime.MinValue; DateTime dateIn = DateTime.MinValue; int orderId = -1; int dvdId = -1; String error = ""; int transId = -1; String selectSql; OdbcCommand odbcCommand; OdbcDataReader odbcDataReader; // Wrap database-access code in 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 save and savenew commands if("save".Equals(command) || "savenew".Equals(command)) { // Check for missing parameters and reformat values for MySQL if(transIdString != null) transId = int.Parse(transIdString); if(orderIdString != null) orderId = int.Parse(orderIdString); if(orderId == -1) error += "Please select an \"Order\"
"; if(dvdIdString != null) dvdId = int.Parse(dvdIdString); if(dvdId == -1) error += "Please select a \"DVD\"
"; if((dateDueString != null) && (dateDueString.Length > 0)) dateDue = DateTime.Parse(dateDueString); else error += "Please enter a \"Date Due\"
"; if((dateOutString != null) && (dateOutString.Length > 0)) dateOut = DateTime.Parse(dateOutString); else error += "Please enter a \"Date Out\"
"; if((dateInString != null) && (dateInString.Length > 0)) dateIn = DateTime.Parse(dateInString); if(error.Length == 0) { if("save".Equals(command)) { // Run the update in update.aspx %> <% } else { // Run the insert in insert.aspx %> <% } // Redirect the application to the listing page Response.Redirect("index.aspx"); } } else { // If it is a new record, initialize the variables to default values if("add".Equals(command)) { transId = 0; orderId = 0; dvdId = 0; dateOutString = DateTime.Today.ToString("MM-dd-yyyy"); dateDueString = DateTime.Today.AddDays(3).ToString("MM-dd-yyyy"); dateInString = ""; } else { // If it is an existing record, read from database if(transactionIdString != null) { // Build query from transactionId value passed down from form transId = int.Parse(transactionIdString); selectSql = "SELECT " + "OrderID, " + "DVDID, " + "DateOut, " + "DateDue, " + "DateIn " + "FROM Transactions " + "WHERE TransID = ?"; // Execute query odbcCommand = new OdbcCommand(selectSql, odbcConnection); OdbcParameter odbcParameter = new OdbcParameter("", OdbcType.Int); odbcParameter.Value = transId; odbcCommand.Parameters.Add(odbcParameter); // Populate the variables for display into the form odbcDataReader = odbcCommand.ExecuteReader(); if(odbcDataReader.Read()) { orderId = (int) odbcDataReader["OrderID"]; dvdId = (int) (short) odbcDataReader["DVDID"]; object obj = odbcDataReader["DateOut"]; if(!obj.GetType().Equals(typeof(DBNull))) { dateOut = (DateTime) obj; dateOutString = dateOut.ToString("MM-dd-yyyy"); } else dateOutString = ""; obj = odbcDataReader["DateDue"]; if(!obj.GetType().Equals(typeof(DBNull))) { dateDue = (DateTime) obj; dateDueString = dateDue.ToString("MM-dd-yyyy"); } else dateDueString = ""; obj = odbcDataReader["DateIn"]; if(!obj.GetType().Equals(typeof(DBNull))) { dateIn = (DateTime) obj; dateInString = dateIn.ToString("MM-dd-yyyy"); } else dateInString = ""; } // Close objects odbcDataReader.Close(); if(odbcCommand != null) odbcCommand.Dispose(); } } } %>

DVD Transaction   <%if(error.Length > 0){%> <%=error%> <%}%> <%if((!"add".Equals(command)) && (!"savenew".Equals(command))){%> <%}%> Order Select Order <% // Retrieve data to populate drop-down list selectSql = "SELECT Orders.OrderID, Orders.CustID, " + "Customers.CustFN, Customers.CustLN " + "FROM Orders, Customers " + "WHERE Customers.CustID = Orders.CustID " + "ORDER BY Orders.OrderID DESC"; // Execute the query odbcCommand = new OdbcCommand(selectSql, odbcConnection); odbcDataReader = odbcCommand.ExecuteReader(); // Loop through the results while(odbcDataReader.Read()) { // Assigned returned values to the variables int orderId1 = (int) odbcDataReader["OrderID"];; String custFirstName = (String) odbcDataReader["CustFN"]; String custLastName = (String) odbcDataReader["CustLN"]; // Format the data for display String customerName = ""; if(custFirstName != null) customerName += custFirstName + " "; if(custLastName != null) customerName += custLastName; // If the order ID matches the existing value mark, it as selected if(orderId1 != orderId) { %> <%=orderId1%> - <%=customerName%> <% } else { %> <%=orderId1%> - <%=customerName%> <% } } // Close objects odbcDataReader.Close(); if(odbcCommand != null) odbcCommand.Dispose(); %> DVD Select DVD <% // Retrieve data to populate drop-down list selectSql = "SELECT DVDID, DVDName FROM DVDs ORDER BY DVDName"; odbcCommand = new OdbcCommand(selectSql, odbcConnection); odbcDataReader = odbcCommand.ExecuteReader(); // Loop through the result set while(odbcDataReader.Read()) { int dvdId1 = (int) (short) odbcDataReader["DVDID"]; String dvdName = (String) odbcDataReader["DVDName"]; if(dvdName == null) dvdName = ""; if(dvdId1 != dvdId) { %> <%=dvdName%> <% } else { %> <%=dvdName%> <% } } // Close objects odbcDataReader.Close(); if(odbcCommand != null) odbcCommand.Dispose(); if(odbcConnection != null) odbcConnection.Dispose(); %> Date Out Date Due Date In <%if(("add".Equals(command)) || ("savenew".Equals(command))){%> <%}else{%> <%}%>

<% } catch(Exception ex) { throw ex; } %>