February 20, 2016
I have been experimenting a bit with ASP.Net, having used classic ASP (with VBScript) for generating various web-based reports somewhat extensively more than a decade ago. While before I used little more than Microsoft’s Notepad application and occasionally Microsoft FrontPage for HTML syntax verification, the development environment that I have been using for ASP.Net is the free Visual Studio Express 2015 for Web development environment. To say that the development approach is different is a bit of an understatement, of course some credit is due to the transition from VBScript to C# (or its close cousin VB.Net).
I created a sample project using C#, ASP.Net, and an Oracle Database connection to demonstrate some of the functionality, reproducing a date selector control format that I originally created for use in Visual Basic 6.0 in early 2002, as shown at the top-right of the picture below:
First, we will create a table in the database using a modified script from another article that I posted six years ago:
CREATE TABLE EMPLOYEE_ATTEND_TEST AS SELECT DECODE(TRUNC(DBMS_RANDOM.VALUE(0,16)), 0,'MIKE', 1,'ROB', 2,'SAM', 3,'JOE', 4,'ERIC', 5,'BILL', 6,'DENNIS', 7,'PATRICK', 8,'JIMMY', 9,'JOHN', 10,'SARAH', 11,'JILL', 12,'PHILLIS', 13,'STEPHANY', 14,'DENISE', 15,'MARTHA') EMPLOYEE_ID, TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE, DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)), 0,'VAC', 1,'HOL', 2,'BEREAVE', 3,'JURY', 4,'ABS', 5,'EXCUSE', 6,'MIL', 'OTHER') INDIRECT_ID FROM DUAL CONNECT BY LEVEL<=3000;
The above script creates a table with 3,000 rows, randomly assigning employee names and different attendance absence codes to each of those rows. The goal of the sample project is to allow a user of the web form to make a date and/or employee name selection, and then have the matching rows from the database table displayed in a HTML table on the web page using alternating colors for each table row. There are of course potential areas of improvement, leaving open the possibility of a follow up article (and an opportunity for code improvement suggestions from readers of this article).
To start, we need to create a new project in Visual Studio Express 2015 for Web (the directions should be similar for the full version of Visual Studio). From the File menu, select New Project… Under the Templates heading, expand Visual C#, then select Web. Give the project a useful name (I named the project WebsiteTestingApplication), select an appropriate parent folder for the project (I selected C:\Projects\CodeCSharp\), make certain that there is a checkmark next to Create directory for solution, then click the OK button.
Next, select the Empty template (if not already selected), make certain that Host in the cloud is not selected, then click the OK button.
In the Visual Studio IDE (integrated development environment), right-click the project in the Solution Explorer, select Add, and then click New Item…
In the Add New Item dialog, under the Visual C# heading at the left make certain that Web is selected, then select Web Form in the center pane. Give the form an appropriate name (I selected DatabaseQueryTest.aspx – this name will be shown in the web browser address bar), then click the Add button:
The starting pre-generated HTML code for the page should then appear in the IDE. Feel free to specify a table for the page between the <title> </title> elements (I specified C# Website Database Test – hoopercharles.wordpress.com), and change the form name (id) attribute (I specified frmWebsiteDatabaseTest):
Since my development computer is running a 64 bit version of Windows, it is best to specify that the Platform Target for the application is either 32 bit (x86) or 64 bit (x64). I will select x86 so that the project will use the 32 bit Oracle Client on the computer as well as the 32 bit Crystal Reports runtime drivers. From the Project menu, select WebsiteTestApplication Properties, click the Build heading at the left, then change the Platform target to x86:
Next, we will add references to the project, which will permit querying the Oracle database, and potentially displaying Crystal Report files. From the Project menu, select Add Reference…:
While this article will not use the Crystal Reports functionality, select the items as shown below by placing checkmarks to the left of the items (if these items are not in the list, installing the free Crystal Reports XI runtime should add these items to the list of selectable items) after clicking the Extensions option at the left:
Scroll down to the Oracle.DataAccess item and place a checkmark next to that item (note: the 64 bit driver may be selected even though we selected x86 as the Platform Target, as a result, we may need to fix this reference item later). Click the OK button:
Once the references are added, view the C# code that is associated with the DatabaseQueryTest.aspx page by right-clicking that page in the Solution Explorer and selecting View Code: (selecting View Markup will redisplay the HTML code that previously appeared on screen, while View Designer attempts to show an editable preview of the page):
We need to add a few using entries near the top of the C# code to simplify the process of using the items exposed by the add-ons that were just referenced, as shown below:
To the list of using entries, add:
using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider using Oracle.DataAccess.Types; // ODP.NET Oracle managed provider using CrystalDecisions.Shared; // Crystal Reports support using CrystalDecisions.CrystalReports.Engine; // Crystal Reports support //using CrystalDecisions.Windows.Forms; // Crystal Reports support, not needed for this project
(Note that there is a minor issue with the default list of using clauses when using database access techniques, but this problem will be addressed later.)
Below the opening bracket below the public partial class DatabaseQueryTest : System.Web.UI.Page line, add the following code, which will create variables for a connection to the Oracle database, create two variables for reading resultsets from the database, create four database command variables (for specifying the SQL statements and bind variable definitions), and a Boolean variable that tracks whether or not the database connection attempt was successful:
OracleConnection dbDatabase; OracleDataReader snpEmployeeID; OracleDataReader snpAttendance; OracleCommand comEmployeeID; OracleCommand comAttendanceSD; OracleCommand comAttendanceEmp; OracleCommand comAttendanceSDEmp; Boolean blConnected = false;
Save All of the project, then press the F5 key to run the web page. On my 64 bit development computer with x86 specified as the Platform Target, I immediately received an error stating that there was a mismatch between the processor architecture of the project and the Oracle.DataAccess dll that has a processor architecture of AMD64:
If you encounter this problem, expand References in the Solution Explorer window, right click Oracle.DataAccess and then select Remove from the menu. Next, from the Project menu select Add Reference…, click the Browse button, then manually locate and add the 32 bit version of the Oracle.DataAccess.dll file. On my development computer I have a 32 bit Oracle client with the necessary odp.net drivers installed in the folder C:\Oracle\product\11.2.0\client_1\ so in the Select the files to reference window I browsed to the folder: C:\Oracle\product\11.2.0\client_1\odp.net\bin\4, selected the Oracle.DataAccess.dll file, and then clicked the Add button:
Save All files in the project again, then press the F5 key to run the project (the current aspx page in the project). If errors such as “The name ‘CommandType’ does not exist in the current context” or “The name ‘ParameterDirection’ does not exist in the current context” appear (note that this error may not appear until the code is added to the Page_Load procedure near the end of this article):
There is a good chance that System.Data is not included in the using statement at the head of the code module. In such a case, add the following line before the using Oracle.DataAccess.Client; line:
using System.Data;
Press the F5 key again, and the blank web page should appear without complaint.
Next, we need to switch back to the Markup view for the DatabaseQueryTest.aspx page. In the Solution Explorer, right-click the DatabaseQueryTest.aspx file and select View Markup:
Between the div and /div elements below the <form id=”frmWebsiteDatabaseTest” runat=”server”> line we need to add HTML code to create the date selector region of the web page. In this case, I used a HTML table to control the positioning of the items that should appear in the date selector region:
(WordPress decided to steal and destroy the HTML code that I posted, refer to the picture above and type the code shown – the full style entry is shown below.)
style="position:absolute; max-width:275px; max-height:400px; left:5px; top:5px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #ffffff, #ccccdd); box-shadow: 3px 3px 12px #010101;"
Next, take a look at the preview of the page to see the results of adding the above code. In the Solution Explorer, right-click DatabaseQueryTest.aspx and select View Designer:
The Designer should show a window similar to what appears below:
The buttons appearing on the page do nothing yet, so we need to create the C# code that is associated with the buttons. Double-click the Today button to create the generic code that is associated with that button being clicked. When the Today button is double-clicked, a new tab named DatabaseQueryTest.aspx.cs will open next to the existing DatabaseQueryTest.aspx tab. Switch back to the DatabaseQueryTest.aspx tab and double-click the Yesterday button to create the generic code that is associated with that button being clicked. Repeat this process for the remaining buttons, working from left to right, top to bottom.
Below is the C# code that I created for each of the 14 buttons – copy this code into the appropriate location in the code found on the DatabaseQueryTest.aspx.cs tab (note that the Page.ClientScript.RegisterStartupScript code seems to be necessary, otherwise a date selector button will need to be clicked twice before the page is correctly updated):
protected void cmdToday_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.ToShortDateString();
cboEndDate.Text = DateTime.Today.ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmdYesterday_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-1).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(-1).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmdThisWeek_Click(object sender, EventArgs e)
{
Int32 i = 0;
DateTime varStartDate;
for (i = 0; i <= 6; i++)
{
if (DateTime.Today.AddDays(-i).DayOfWeek == DayOfWeek.Monday)
{
varStartDate = DateTime.Today.AddDays(-i);
cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
cboEndDate.Text = varStartDate.AddDays(6).ToShortDateString();
break; } } // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmdLastWeek_Click(object sender, EventArgs e)
{
Int32 i = 0;
DateTime varStartDate;
for (i = 0; i <= 6; i++)
{
if (DateTime.Today.AddDays(-i).DayOfWeek == DayOfWeek.Monday)
{
varStartDate = DateTime.Today.AddDays(-i - 7);
cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString();
cboEndDate.Text = varStartDate.AddDays(6).ToShortDateString();
break; } } // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmdThisMonth_Click(object sender, EventArgs e)
{
DateTime varStartDate;
varStartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString(); cboEndDate.Text = varStartDate.AddMonths(1).AddDays(-1).ToShortDateString(); // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmdLastMonth_Click(object sender, EventArgs e)
{
DateTime varStartDate;
varStartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddMonths(-1); cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString(); cboEndDate.Text = varStartDate.AddMonths(1).AddDays(-1).ToShortDateString(); // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmdYeartoDate_Click(object sender, EventArgs e)
{
DateTime varStartDate;
varStartDate = new DateTime(DateTime.Today.Year, 1, 1); cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString(); cboEndDate.Text = DateTime.Today.ToShortDateString(); // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmdLastYear_Click(object sender, EventArgs e)
{
DateTime varStartDate;
varStartDate = new DateTime(DateTime.Today.Year - 1, 1, 1); cboStartDate.Text = varStartDate.AddDays(0).ToShortDateString(); cboEndDate.Text = varStartDate.AddMonths(12).AddDays(-1).ToShortDateString(); // Force a page load so that the Repeater table is updated Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true); }
protected void cmd7Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-6).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmd30Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-29).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmd60Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-59).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmd90Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-89).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmd120Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-119).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
protected void cmd180Days_Click(object sender, EventArgs e)
{
cboStartDate.Text = DateTime.Today.AddDays(-179).ToShortDateString();
cboEndDate.Text = DateTime.Today.AddDays(0).ToShortDateString();
// Force a page load so that the Repeater table is updated
Page.ClientScript.RegisterStartupScript(typeof(Page), "autoPostback", Page.ClientScript.GetPostBackEventReference(this, String.Empty), true);
}
Save All of the project, then press the F5 key to run the web page. You should then see something similar to the below screen capture, and the buttons in the date selector region should almost work (we have not added any date entries into the DropDownList controls). We also have not yet added the Select Employee ID section of the page:
Switch back to the Markup view, and locate the /div line that follows the /table line. We will now add the HTML code that allows selecting an employee ID, the asp:Repeater code that will handle the display of the resultset that is returned from the Oracle database, and a simple area with an instruction for the end user of the web page. Directly below the /div line that follows the /table line add the code shown below that begins with the div style line:
The code (almost formatted, missing the leading spaces and blank lines:
<div style="position:absolute; max-width:260px; max-height:400px; left:300px; top:5px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #ffffff, #ccccdd); box-shadow: 3px 3px 12px #010101;"> <asp:Label ID="lblEmployeeID" Width="250px" Text="Select Employee ID" runat="server"></asp:Label> <asp:DropDownList ID="cboEmployeeID" style="position:relative;left:5px;" Width="250px" runat="server" AutoPostBack="true"></asp:DropDownList> </div> <div style="position:absolute; max-width:400px; max-height:500px; left:10px; top:260px; overflow:auto; box-shadow: 3px 3px 12px #010101;"> <asp:Repeater id="tblAttendance" runat="server"> <HeaderTemplate> <table border="1" style="border-collapse: collapse; min-width:400px;max-width:400px"> <tr style="background-color:#8080FF"> <th>Employee ID</th> <th>Shift Date</th> <th>Indirect Code</th> </tr> </HeaderTemplate> <ItemTemplate> <tr style="background-color:#ffffff"> <td><%# Eval("employee_id") %></td> <td style="text-align:right"><%# Eval("shift_date") %></td> <td><%# Eval("indirect_id") %></td> </tr> </ItemTemplate> <AlternatingItemTemplate> <tr style="background-color:#eeeeee"> <td><%# Eval("employee_id") %></td> <td style="text-align:right"><%# Eval("shift_date") %></td> <td><%# Eval("indirect_id") %></td> </tr> </AlternatingItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </div> <div style="position:absolute; max-width:260px; max-height:150px; left:300px; top:200px; border:inset; overflow:auto; background-color:#ffffff; background-image: linear-gradient(90deg, #fcfc08, #ffd800); box-shadow: 3px 3px 12px #010101;"> <asp:Label ID="lblInstructions" Width="250px" Text="" runat="server"></asp:Label> </div>
Switch back to the C# code (the DatabaseQueryTest.aspx.cs tab) and locate the Page_Load procedure, which automatically executes when the web page is rendered for the client browser. Change the Page_Load procedure so that it includes the code shown below (change MyUserName, MyPassword, and MyDBName to appropriate values to permit a connection to your database:
protected void Page_Load(object sender, EventArgs e)
{
String strSQL = "";
String strUserName = "";
String strPassword = "";
String strConnection = "";
Int32 i = 0;
// Connection Pooling article: http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46odp-097508.html // connection pooling documentation: https://docs.oracle.com/cd/E11882_01/win.112/e23174/featConnecting.htm#ODPNT169 // connection pooling is enabled by default, may be turned off by specifying Pooling=false in the strConnection variable // Connect to the database - connection pooling is used to minimize the number of actual new database connections required strUserName = "MyUserName"; strPassword = "MyPassword"; strConnection = "Data Source=MyDBName;User Id=" + strUserName + ";Password=" + strPassword +";"; strConnection = strConnection + "Pooling=true;Min Pool Size=10;Max Pool Size=50;Decr Pool Size=2;Connection Lifetime=1200;Connection Timeout=60;";
try
{
dbDatabase = new OracleConnection(strConnection);
dbDatabase.Open();
blConnected = true;
}
catch
{
blConnected = false;
}
if (!Page.IsPostBack)
{
if (blConnected == true)
{
lblInstructions.Text = "Select a date range and/or an employee ID from the list.";
strSQL = "SELECT DISTINCT" + "\n"; strSQL = strSQL + " EMPLOYEE_ID" + "\n"; strSQL = strSQL + "FROM" + "\n"; strSQL = strSQL + " EMPLOYEE_ATTEND_TEST" + "\n"; strSQL = strSQL + "ORDER BY" + "\n"; strSQL = strSQL + " EMPLOYEE_ID";
comEmployeeID = new OracleCommand(strSQL, dbDatabase); comEmployeeID.CommandType = CommandType.Text; comEmployeeID.CommandTimeout = 30; comEmployeeID.BindByName = false;
// Need to start at 30 days in the future so that cmdThisMonth does not generate an error
for (i = -30; i <= 1000; i++)
{
cboStartDate.Items.Add(DateTime.Today.AddDays(-i).ToShortDateString());
cboEndDate.Items.Add(DateTime.Today.AddDays(-i).ToShortDateString());
}
cboStartDate.Text = DateTime.Today.ToShortDateString();
cboEndDate.Text = DateTime.Today.ToShortDateString();
// Retrieve the employee ID list
cboEmployeeID.Items.Add("");
snpEmployeeID = comEmployeeID.ExecuteReader();
if (!snpEmployeeID.IsClosed)
{
while (snpEmployeeID.Read())
{
cboEmployeeID.Items.Add(snpEmployeeID["employee_id"].ToString());
}
snpEmployeeID.Close();
}
}
}
else
{
// Not the first time the page is displayed
if (blConnected == true)
{
strSQL = "SELECT" + "\n";
strSQL = strSQL + " EMPLOYEE_ID," + "\n";
strSQL = strSQL + " SHIFT_DATE," + "\n";
strSQL = strSQL + " INDIRECT_ID" + "\n";
strSQL = strSQL + "FROM" + "\n";
strSQL = strSQL + " EMPLOYEE_ATTEND_TEST" + "\n";
strSQL = strSQL + "WHERE" + "\n";
strSQL = strSQL + " SHIFT_DATE BETWEEN :1 AND :2" + "\n";
strSQL = strSQL + "ORDER BY" + "\n";
strSQL = strSQL + " EMPLOYEE_ID," + "\n";
strSQL = strSQL + " SHIFT_DATE";
comAttendanceSD = new OracleCommand(strSQL, dbDatabase);
comAttendanceSD.CommandType = CommandType.Text;
comAttendanceSD.CommandTimeout = 30;
comAttendanceSD.BindByName = false;
comAttendanceSD.Parameters.Add("start_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
comAttendanceSD.Parameters.Add("end_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
strSQL = "SELECT" + "\n"; strSQL = strSQL + " EMPLOYEE_ID," + "\n"; strSQL = strSQL + " SHIFT_DATE," + "\n"; strSQL = strSQL + " INDIRECT_ID" + "\n"; strSQL = strSQL + "FROM" + "\n"; strSQL = strSQL + " EMPLOYEE_ATTEND_TEST" + "\n"; strSQL = strSQL + "WHERE" + "\n"; strSQL = strSQL + " EMPLOYEE_ID = :1" + "\n"; strSQL = strSQL + "ORDER BY" + "\n"; strSQL = strSQL + " EMPLOYEE_ID," + "\n"; strSQL = strSQL + " SHIFT_DATE";
comAttendanceEmp = new OracleCommand(strSQL, dbDatabase);
comAttendanceEmp.CommandType = CommandType.Text;
comAttendanceEmp.CommandTimeout = 30;
comAttendanceEmp.BindByName = false;
comAttendanceEmp.Parameters.Add("employee_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input);
strSQL = "SELECT" + "\n"; strSQL = strSQL + " EMPLOYEE_ID," + "\n"; strSQL = strSQL + " SHIFT_DATE," + "\n"; strSQL = strSQL + " INDIRECT_ID" + "\n"; strSQL = strSQL + "FROM" + "\n"; strSQL = strSQL + " EMPLOYEE_ATTEND_TEST" + "\n"; strSQL = strSQL + "WHERE" + "\n"; strSQL = strSQL + " SHIFT_DATE BETWEEN :1 AND :2" + "\n"; strSQL = strSQL + " AND EMPLOYEE_ID = :3" + "\n"; strSQL = strSQL + "ORDER BY" + "\n"; strSQL = strSQL + " EMPLOYEE_ID," + "\n"; strSQL = strSQL + " SHIFT_DATE";
comAttendanceSDEmp = new OracleCommand(strSQL, dbDatabase);
comAttendanceSDEmp.CommandType = CommandType.Text;
comAttendanceSDEmp.CommandTimeout = 30;
comAttendanceSDEmp.BindByName = false;
comAttendanceSDEmp.Parameters.Add("start_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
comAttendanceSDEmp.Parameters.Add("end_date", OracleDbType.Date, 8, "", ParameterDirection.Input);
comAttendanceSDEmp.Parameters.Add("employee_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input);
if (cboEmployeeID.Text != "")
{
if (cboStartDate.Text != "")
{
// Employee ID and Start Date are specified
comAttendanceSDEmp.Parameters["start_date"].Value = DateTime.ParseExact(cboStartDate.Text, "d", ifpCulture);
comAttendanceSDEmp.Parameters["end_date"].Value = DateTime.ParseExact(cboEndDate.Text, "d", ifpCulture);
comAttendanceSDEmp.Parameters["employee_id"].Value = cboEmployeeID.Text;
snpAttendance = comAttendanceSDEmp.ExecuteReader();
snpAttendance.FetchSize = comAttendanceSDEmp.RowSize * 100;
}
else
{
// Employee ID is specified, Start Date is not specified
comAttendanceEmp.Parameters["employee_id"].Value = cboEmployeeID.Text;
snpAttendance = comAttendanceEmp.ExecuteReader();
snpAttendance.FetchSize = comAttendanceEmp.RowSize * 100;
}
}
else
{
// Employee ID is not specified, assume that the Start Date must be specified
comAttendanceSD.Parameters["start_date"].Value = DateTime.ParseExact(cboStartDate.Text, "d", ifpCulture);
comAttendanceSD.Parameters["end_date"].Value = DateTime.ParseExact(cboEndDate.Text, "d", ifpCulture);
snpAttendance = comAttendanceSD.ExecuteReader();
snpAttendance.FetchSize = comAttendanceSD.RowSize * 100;
}
if (!snpAttendance.IsClosed)
{
tblAttendance.DataSource = snpAttendance;
tblAttendance.DataBind();
// Web page does not show the correct information until the next postback? snpAttendance.Close(); } } }
if (blConnected == true)
{
// Disconnect from the database, because connection pooling is enabled, the connection might not actually close immediately
try
{
dbDatabase.Close();
}
catch { }
blConnected = false;
}
}
Save All of the project, then press the F5 key to run the web page. If the programming code is correct and complete, it should now be possible to select an Employee ID, click a button to select a date range, and then see a HTML rendered table in a scrollable viewport as shown below:
Note that while developing this article I had a couple of issues where the iisexpress.exe webserver stopped responding, resulting in a completely blank page as shown below, with the never ending message “Waiting for localhost” appearing:
There probably is a good method to recover from this problem, but as of yet I have not found that method. If you encounter this problem, find the IIS Express icon in the task tray, right-click that icon, and select Exit. I found that I then had to close and reopen the project in Visual Studio Express.
I will likely add additional information to this article in the next couple of days – it appears that I may have a way to post the HTML code that was removed by WordPress, and I will try to add additional explanation for the reasoning behind the C# code in the Page_Load procedure.
What is the challenge? How might you improve this sample project to do something interesting with the project?
Charles Hooper's Oracle Notes 
Leave a Reply