ASP SQL Timesheet page allow Decimal Numbers
I have a timesheet that connects to a database.
The database allows the numbers to be decimal (e.g 7.5 hours). This field is set to double as opposed to integer.
When the timesheet gets this data it will only show the whole number (7). from what i have read i need to change the format of the field on the page (i have read it needs to be Cdbl rather than Cint) but i am not sure where this should go.
The database allows the numbers to be decimal (e.g 7.5 hours). This field is set to double as opposed to integer.
When the timesheet gets this data it will only show the whole number (7). from what i have read i need to change the format of the field on the page (i have read it needs to be Cdbl rather than Cint) but i am not sure where this should go.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%'The first thing that we have to do is load our ADOVBS.inc file, this is used in our Parameters, that you will see used below in all the statements that we will be using in this project%>
<!--#include file="ADOVBS.inc"-->
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Server.MapPath ("TimeSheets.accdb") & ";"
objConn.Open
' This code is being designed to cross-platform
' This code will work with both: ACCESS Database & SQL Server
' If your company ever decided to merge to SQL Server, the only that that will need to be changed is the Connection String.
' time to get the QueryString variables to use in our SELECT STATEMENTS!
getWeek = trim(Request.QueryString("WeekCh"))
getUser = trim(Request.QueryString("UserNameCH"))
Set sqltime = CreateObject("ADODB.Command") '
sqltime.ActiveConnection=objConn
sqltime.Prepared = true
sqltime.commandtext = "SELECT tID, tWeekBegin, tEmployeeNum, tProjNum, tStage, tTask, tMon, tTues, tWed, tThurs, tFri, tSat, tSun FROM tbl_TimeLog where tWeekBegin =? and tEmployeeNum=?"
sqltime.Parameters.Append sqltime.CreateParameter("@tWeekBegin", adVarChar,adParamInput, 255, getWeek)
sqltime.Parameters.Append sqltime.CreateParameter("@tEmployeeNum", adVarChar,adParamInput, 255, getUser)
set rsTime = sqltime.execute
Set sqlTotal = CreateObject("ADODB.Command") '
sqlTotal.ActiveConnection=objConn
sqlTotal.Prepared = true
sqlTotal.commandtext = "SELECT Count(tID), Count(tWeekBegin), Sum(tMon), Sum(tTues), Sum(tWed),Sum(tThurs), Sum(tFri), Sum(tSat), Sum(tSun) FROM tbl_TimeLog WHERE tWeekBegin = ? AND tEmployeeNum =?"
sqlTotal.Parameters.Append sqlTotal.CreateParameter("@tWeekBegin", adVarChar,adParamInput, 255, getWeek)
sqlTotal.Parameters.Append sqlTotal.CreateParameter("@tEmployeeNum", adVarChar,adParamInput, 255, getUser)
set rsTotal = sqlTotal.execute
Set sqlPass = CreateObject("ADODB.Command")
sqlPass.ActiveConnection=objConn
sqlPass.Prepared = true
sqlPass.commandtext = "SELECT tWeekBegin FROM tbl_TimeLog WHERE tWeekBegin =?"
sqlPass.Parameters.Append sqlPass.CreateParameter("@tWeekBegin", adVarChar,adParamInput, 255, getWeek)
set rsPass = sqlPass.execute
Set sqlLogin = CreateObject("ADODB.Command")
sqlLogin.ActiveConnection=objConn
sqlLogin.Prepared = true
sqlLogin.commandtext = "SELECT tEmployeeNum, Fname, JobTitle FROM qry_Login WHERE tEmployeeNum = ?"
sqlLogin.Parameters.Append sqlLogin.CreateParameter("@tEmployeeNum", adVarChar,adParamInput, 255, getUser)
set rsLogin = sqlLogin.execute
row=CInt(Request.Form("row"))
For n=1 to row
if trim(request.Form("Records")="Update") then ' This lets us ONLY load the UPDATE STATEMENT when it is called ONLY!
Set sqlUpdate=CreateObject("ADODB.Command")
' we are going to get of the Form variables to use in our Update Statement
multiID = int(request.Form("tID"))
tProjNum = trim(request.Form("tProjNum_" & n))
tStage = trim(request.Form("tStage_" & n))
tTask = trim(request.Form("tTask_" & n))
tMon = int(request.Form("tMon_" & n))
tTues = int(request.Form("tTues_" & n))
tWed = int(request.Form("tWed_" & n))
tThurs = int(request.Form("tThurs_" & n))
tFri = int(request.Form("tFri_" & n))
tSat = int(request.Form("tSat_" & n))
tSun = int(request.Form("tSun_" & n))
tID = int(request.Form("tID_" & n))
sqlUpdate.ActiveConnection=objConn
sqlUpdate.commandtext = "update tbl_TimeLog set "& _
"[tProjNum]=? ,"& _
"[tStage]=? ,"& _
"[tTask]=? ,"& _
"[tMon]=? ,"& _
"[tTues]=? ,"& _
"[tWed]=? ,"& _
"[tThurs]=? ,"& _
"[tFri]=? ,"& _
"[tSat]=? ,"& _
"[tSun]=? where tID=?"
'response.Write"tProjNum - "&tProjNum&" <br />tStage "&tStage&" <br />tTask "&tTask&" <br />tMon "&tMon&" <br />tTues "&tTues&" <br />tWed "&tWed&" <br />tThurs "&tThurs&" <br />tFri "&tFri&" <br />tSat "&tSat&" <br />tSun "&tSun&" <br /> tID"&tID&""
'response.End()
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tProjNum", adVarChar,adParamInput, 255, tProjNum)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tStage", adVarChar,adParamInput, 255, tStage)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tTask", adVarChar,adParamInput, 255, tTask)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tMon", adInteger,adParamInput, , tMon)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tTues", adInteger,adParamInput, , tTues)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tWed", adInteger,adParamInput, , tWed)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tThurs", adInteger,adParamInput, , tThurs)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tFri", adInteger,adParamInput, , tFri)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tSat", adInteger,adParamInput, , tSat)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tSun", adInteger,adParamInput, , tSun)
sqlUpdate.Parameters.Append sqlUpdate.CreateParameter("@tID", adInteger,adParamInput, , tID)
sqlUpdate.Execute
end if
next
if trim(request.Form("RecordsAdd")="Insert") then ' This lets us ONLY load the INSERT STATEMENT when it is called ONLY!
getWeek = trim(request.Form("WeekCh")) ' Have to get the Form Values to insert into the table
getUser = trim(request.form("UserNameCh")) ' same as above
set sqlInsert = Server.CreateObject("ADODB.Command")
sqlInsert.ActiveConnection = objConn
sqlInsert.CommandText = "INSERT INTO tbl_TimeLog (tWeekBegin, tEmployeeNum) VALUES (?,?)" ' Use Parameters ONLY here.
sqlInsert.Parameters.Append sqlInsert.CreateParameter("@tWeekBegin", adVarChar,adParamInput, 255, getWeek)
sqlInsert.Parameters.Append sqlInsert.CreateParameter("@tEmployeeNum", adVarChar,adParamInput, 255, getUser)
sqlInsert.Execute()
End If
' to read up on the CreateParameter, please navigate here: http://www.aspfree.com/c/a/Database/The-Command-Object/
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Q_24883096 - (Demo by: EE's very own Carrzkiss)</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../HMYWebStyle.css" rel="stylesheet" type="text/css" />
<%'To keep your page clean and tidy, always use CSS to specify your page elements.
' Never define the WIDTH attribute in your HTML element unless your dealing with <img> tags.
' The WIDTH attribute is longer supported by valid XHTML code. %>
<style type="text/css">
.tdHeaderTop{
font-size:small;
border:1px #333333 dotted; /*Do not want the border, remove it here*/
}
.tdHeader{
width:10px;
font-size:small;
border:1px #333333 dotted; /*Do not want the border, remove it here*/
}
</style>
</head>
<body>
<table style="width:100%; border:0px; text-align:center;" cellpadding="5" cellspacing="0">
<tr>
<td> All Fields must have a Value (Default = 0)<% If Not rsTime.EOF Or Not rsTime.BOF Then %>
<form action="Q_24883096.asp?WeekCh=<%=trim(request.QueryString("WeekCh"))%>&UserNameCH=<%=trim(request.QueryString("UserNameCH"))%>" method="post" id="TimeEd">
<div style="text-align:center;">
<table style="border:0px;" cellpadding="5" cellspacing="5">
<tr>
<td colspan="11">
<table style="width:100%; border:0px;" cellspacing="0" cellpadding="3">
<tr>
<td class="tdHeaderTop">
<input type="hidden" name="Records" value="Update" />
<input name="Update" type="submit" id="Update" value="Update" /></td>
<td class="tdHeaderTop">Week Beginning:
<input name="WeekCh" type="text" id="WeekCh" value="<%=rsTime("tWeekBegin")%>" readonly="readonly" />
</td>
<td class="tdHeaderTop">Employee:
<input name="UserNameCh" type="text" id="UserNameCh" value="<%=rsTime("tEmployeeNum")%>" />
</td>
</tr>
</table></td>
</tr>
<tr>
<td class="tdHeader">Project Number</td>
<td class="tdHeader">Stage</td>
<td class="tdHeader">Task</td>
<td class="tdHeader">Mon</td>
<td class="tdHeader">Tues</td>
<td class="tdHeader">Wed</td>
<td class="tdHeader">Thurs</td>
<td class="tdHeader">Fri</td>
<td class="tdHeader">Sat</td>
<td class="tdHeader">Sun</td>
<td class="tdHeader">Total</td>
</tr>
<%
row=0 ' Set the value of the Row Count to "0"
While NOT rsTime.EOF ' Start our loop
row=row + 1 ' Set the value of the Row Count to "1"%>
<tr>
<td class="tdHeader">
<input name="tProjNum_<%=row%>" type="text" id="tProjNum_<%=row%>" value="<%=rsTime("tProjNum")%>" size="5" /></td>
<td class="tdHeader">
<input name="tStage_<%=row%>" type="text" id="tStage_<%=row%>" value="<%=rsTime("tStage")%>" size="10" /></td>
<td class="tdHeader">
<input name="tTask_<%=row%>" type="text" id="tTask_<%=row%>" value="<%=rsTime("tTask")%>" size="35" /></td>
<td class="tdHeader">
<input name="tMon_<%=row%>" type="text" id="tMon_<%=row%>" value="<%=rsTime("tMon")%>" size="3" /></td>
<td class="tdHeader">
<input name="tTues_<%=row%>" type="text" id="tTues_<%=row%>" value="<%=rsTime("tTues")%>" size="3" /></td>
<td class="tdHeader">
<input name="tWed_<%=row%>" type="text" id="tWed_<%=row%>" value="<%=rsTime("tWed")%>" size="3" /></td>
<td class="tdHeader">
<input name="tThurs_<%=row%>" type="text" id="tThurs_<%=row%>" value="<%=rsTime("tThurs")%>" size="3" /></td>
<td class="tdHeader">
<input name="tFri_<%=row%>" type="text" id="tFri_<%=row%>" value="<%=rsTime("tFri")%>" size="3" /></td>
<td class="tdHeader">
<input name="tSat_<%=row%>" type="text" id="tSat_<%=row%>" value="<%=rsTime("tSat")%>" size="3" /></td>
<td class="tdHeader">
<input name="tSun_<%=row%>" type="text" id="tSun_<%=row%>" value="<%=rsTime("tSun")%>" size="3" /></td>
<td class="tdHeader">
<% Dim MyNum
Mynum =rsTime("tMon")+rsTime("tTues")+rsTime("tWed")+rsTime("tThurs")+rsTime("tFri")+rsTime("tSat")+rsTime("tSun")
Response.Write(Mynum)
%>
<input type="hidden" name="tID_<%=row%>" value="<%= rsTime("tID")%>" />
</td>
</tr>
<%
rsTime.movenext ' Move to then next record
wend
%>
<tr>
<td colspan="2"> </td>
<td class="tdHeader" style="text-align:right;">Total:</td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1002")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1003")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1004")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1005")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1006")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1007")%></td>
<td class="tdHeader" style="text-align:center;"><%=rsTotal("Expr1008")%></td>
<td class="tdHeader" style="text-align:center;">
<%
Dim MyTotal
MyTotal = rsTotal("Expr1002")+rsTotal("Expr1003")+rsTotal("Expr1004")+rsTotal("Expr1005")+rsTotal("Expr1006")+rsTotal("Expr1007")+rsTotal("Expr1008")
Response.Write(MyTotal)
%>
<input type="hidden" name="row" value="<%=row%>" /><%'this counts the rows that we have in the <form>%>
</td>
</tr>
</table>
</div>
</form>
<table style="width:100%; border:0px; text-align:left;" cellspacing="0" cellpadding="3">
<tr>
<td>
<%'The FORM for Inserting new ROWS%>
<form action="Q_24883096.asp?WeekCh=<%=trim(request.QueryString("WeekCh"))%>&UserNameCH=<%=trim(request.QueryString("UserNameCH"))%>" method="post" id="AddRow">
<input type="hidden" name="RecordsAdd" value ="Insert" />
<input name="Submit" type="submit" id="Submit" value="Add New Row">
<input name="WeekCh" type="hidden" id="WeekCh" value="<%=rsPass("tWeekBegin")%>">
<input name="UserNameCh" type="hidden" id="UserNameCh" value="<%=rsLogin("tEmployeeNum")%>">
</form>
</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table> </td>
</tr>
</table>
<%
rsTime.Close()
Set rsTime = Nothing
%>
<%
rsTotal.Close()
Set rsTotal = Nothing
%>
<%
rsPass.Close()
Set rsPass = Nothing
%>
<%
rsLogin.Close()
Set rsLogin = Nothing
objconn.close
set objconn = nothing
%>
<%end if%>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfectly with the post below.
But Carrzkiss takes the credit, he taught me everything i know.
But Carrzkiss takes the credit, he taught me everything i know.
I never thought about that one.
Good Going Geg, Good Going!!!!
Carrzkiss
Good Going Geg, Good Going!!!!
Carrzkiss
Totally cool.
I cannot beleive I was all over that code and was messing with the database.
And never once did I think to change that part of the code.
See, you taught me something today as well Geg.
Keep up the great work.
Carrzkiss
I cannot beleive I was all over that code and was messing with the database.
And never once did I think to change that part of the code.
See, you taught me something today as well Geg.
Keep up the great work.
Carrzkiss
Updated Demo for others to learn by as well
http://ee.cffcs.com/Q_2488
code
http://ee.cffcs.com/Q_2488
Carrzkiss
http://ee.cffcs.com/Q_2488
code
http://ee.cffcs.com/Q_2488
Carrzkiss
ASKER
Once you added the Cdbl i.e. tMon = CDbl(request.Form("tMon_" & n)), instead of Cint.
All i needed to to was change:
sqlUpdate.Parameters.Appen
sqlUpdate.Parameters.Appen
It works.
Must have had a great teacher.