I have two text boxes on my form that are going to be submitted to a sql server. They are both datetime sql db types and what I'm looking to do is to calculate the time difference between the boxes and present that to the user as duration. I know that this can be done with datediff on the sql side so what I was thinking would be the best manner to handle this would be just to add this
datediff (minute, starttime,endtime) to my sql query, store the return value as a variable to present in a label or should I do something different?
Here is the default.aspx.vb code that I have so far:
Code:
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
InsertRow()
End Sub
Public Sub ClearFields()
OperatorDropdown.SelectedIndex = -1
exceptiondateInput.Text = ""
starttimeInput.Text = ""
endtimeInput.Text = ""
durationInput.Text = ""
code.SelectedIndex = -1
Approvedby.SelectedIndex = -1
End Sub
Public Sub InsertRow()
Dim myConnectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxxx;"
Dim myConnection As New SqlConnection(myConnectionString)
Dim commandText As String = _
"insert into Exceptions (employeenumber, exceptiondate, starttime, endtime, duration, code, approvedby) " & _
"values(@employeenumber, @exceptiondate, @starttime, @endtime, @duration, @code, @approved)"
Using connection As New SqlConnection(myConnectionString)
Dim command As New SqlCommand(commandText, connection)
command.Parameters.Add("@employeenumber", SqlDbType.VarChar)
command.Parameters("@employeenumber").Value = OperatorDropdown.SelectedItem.Text
command.Parameters.Add("@exceptiondate", SqlDbType.DateTime)
command.Parameters("@exceptiondate").Value = exceptiondateInput.Text
command.Parameters.Add("@starttime", SqlDbType.DateTime)
command.Parameters("@starttime").Value = starttimeInput.Text
command.Parameters.Add("@endtime", SqlDbType.DateTime)
command.Parameters("@endtime").Value = endtimeInput.Text
command.Parameters.Add("@duration", SqlDbType.VarChar)
command.Parameters("@duration").Value = durationInput.Text
command.Parameters.Add("@code", SqlDbType.VarChar)
command.Parameters("@code").Value = code.SelectedItem.Text
command.Parameters.Add("@approved", SqlDbType.VarChar)
command.Parameters("@approved").Value = Approvedby.SelectedItem.Text
Try
connection.Open()
Dim rowsAffected As Integer = command.ExecuteNonQuery()
Catch ex As Exception
myErrorMessageLabel.Text = ex.Message
End Try
thankyouLabel.Text = "Your Data Has Been Submitted"
ClearFields()
End Using
End Sub
End Class
I also know that currently I have a textbox for duration, but that is going to be a label to present the value of the difference between the starttime and endtime before the user presses the submit button.
I've seen a few varieties of articles describing ways to do this. The part that confuses me is because I'm running both a regularexpressionvalidator and a requiredfieldvalidator on both the starttime and endtime check boxes, I just want to make sure that I'm putting my code in the right place. Here's my default.aspx page with the text boxes:
Code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
#Submit1
{
width: 131px;
}
.style1
{
color: #FF0000;
}
</style>
<script language="javascript" type="text/javascript">
// <!CDATA[
// ]]>
</script>
</head>
<body>
<br />
<form id="form1" runat="server">
<div align="left">
<asp:DropDownList ID="OperatorDropdown" runat="server"
DataSourceID="SqlDataSource1" DataTextField="employeenumber"
DataValueField="employeenumber" Height="23px" Width="130px">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MDRConnectionString %>"
SelectCommand="SELECT [Employeenumber] FROM [Employees]">
</asp:SqlDataSource>
Choose an Operator
<br />
<br />
<br />
<asp:TextBox ID="exceptiondateInput" runat="server" Width="130px" Height="23px"></asp:TextBox>
<span class="style1"></span> Exception Date <asp:RegularExpressionValidator
ID="DateValidator" runat="server"
ControlToValidate="exceptiondateInput"
ValidationExpression="^\d{1,2}\/\d{1,2}\/\d{4}$"
Text="*"
ErrorMessage="Please Enter the Date Correctly">
</asp:RegularExpressionValidator>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
Text="*"
ErrorMessage="You Must Supply A Date"
ControlToValidate="exceptiondateInput"></asp:RequiredFieldValidator>
<br />
<br />
<br />
<asp:TextBox ID="starttimeInput" runat="server" Width="130px" Height="23px"></asp:TextBox>
<span class="style1"></span> Start Time <asp:RegularExpressionValidator
ID="StartTimeValidator" runat="server"
ValidationExpression="^((([0]?[1-9]|1[0-2])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?( )?(AM|am|aM|Am|PM|pm|pM|Pm))|(([0]?[0-9]|1[0-9]|2[0-3])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?))$"
Text="*"
ErrorMessage="Start Time Incorrectly Formatted"
ControlToValidate="starttimeInput"></asp:RegularExpressionValidator>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
Text="*"
ErrorMessage="This field cannot be empty"
ControlToValidate="starttimeInput">*</asp:RequiredFieldValidator>
<br />
<br />
<br />
<asp:TextBox ID="endtimeInput" runat="server" Width="130px" Height="23px"></asp:TextBox>
<span class="style1"></span> End Time <asp:RegularExpressionValidator
ID="EndTimeValidator" runat="server"
ValidationExpression="^((([0]?[1-9]|1[0-2])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?( )?(AM|am|aM|Am|PM|pm|pM|Pm))|(([0]?[0-9]|1[0-9]|2[0-3])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?))$"
Text="*"
ErrorMessage="You Must Supply an End Time"
ControlToValidate="endtimeInput"></asp:RegularExpressionValidator>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
Text="*"
ErrorMessage="This field cannot be empty" ControlToValidate="endtimeInput"></asp:RequiredFieldValidator>
<br />
<br />
<br />
<asp:TextBox ID="durationInput" runat="server" Width="130px" Height="23px"></asp:TextBox>
<span class="style1"></span> Duration <asp:RequiredFieldValidator
ID="DurationValidator" runat="server"
Text="*"
ErrorMessage="Exception Duration Must Be Entered"
ControlToValidate="durationInput"></asp:RequiredFieldValidator>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
Text="*"
ErrorMessage="This field cannot be empty" ControlToValidate="durationInput"></asp:RequiredFieldValidator>
<br />
<br />
<br />
<asp:DropDownList ID="code" runat="server" Height="23px" Width="206px">
<asp:ListItem Value="NoSelection">--- No Selection --</asp:ListItem>
<asp:ListItem>Approved Technical Reason</asp:ListItem>
<asp:ListItem>Coaching Session</asp:ListItem>
<asp:ListItem>ETO</asp:ListItem>
<asp:ListItem>Sick Leave</asp:ListItem>
<asp:ListItem>Special Project</asp:ListItem>
<asp:ListItem>Supervisor Meeting</asp:ListItem>
<asp:ListItem>Vacation</asp:ListItem>
</asp:DropDownList>
Choose a reason for exception <asp:compareValidator id="codeValidator"
runat="server" ControlToValidate="code"
ValueToCompare="NoSelection" Operator="NotEqual"
Text="*"
ErrorMessage="Please Select a Reason" >*</asp:CompareValidator>
<br />
<br />
<br />
<asp:DropDownList ID="Approvedby" runat="server" Width="130px" Height="23px">
<asp:ListItem Value="NoSelection">--- No Selection --</asp:ListItem>
<asp:ListItem>Patrice Paul</asp:ListItem>
<asp:ListItem>Zach Cochran</asp:ListItem>
</asp:DropDownList>
Approved By <asp:compareValidator id="CompareValidator1"
runat="server" ControlToValidate="Approvedby"
ValueToCompare="NoSelection" Operator="NotEqual"
Text="*"
ErrorMessage="Please Select either Patrice or Zach" >*</asp:CompareValidator>
<br />
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Submit" />
<br />
<br />
<br />
<span class="style1">* all fields must be filled out</span><br />
<span class="style1">* times should have am or pm after them</span><br />
<br />
<asp:Label ID="myErrorMessageLabel" runat="server"></asp:Label>
<br />
<asp:Label ID="thankyouLabel" runat="server"></asp:Label>
<br />
<asp:ValidationSummary ID="ValidationSummary1" runat="server" Height="150px" />
<br />
<br />
</div>
</form>
</body>
</html>
Granted at this point, the "duration" field is still a textbox, but that's going to be replaced with a label to show my results. Oh and I also want to pass the duration variable to the sql query on my default.aspx.vb page. I'm assuming that once I know what code to put into the page, that passing that variable won't be difficult.
Looks like everything is in the right place. Only thing I would advise is to break out your insert action into a separate class, then create an instance of that object in the page, that way, you abstract the functionality of db operations, instead of programming to the concrete class, which will eventually become a design problem.
Reply With Quote
Bookmarks