We help IT Professionals succeed at work.

ASP Timesheet Form

805 Views
Last Modified: 2012-06-21
I have a timesheet form that Updates Multiple rows.
I now need to be able to insert a new row.
2 Things seem to be happening.
1 is it inserts the row but not the initial values i'm expecting (i.e WeekBegin date and tEmployeeNum.)
2 is both the update button and insert button create a new row
This is the insert statement, Full code is attached:

if trim(request.Form("RecordsAdd")="Insert") then ' This lets us ONLY load the INSERT STATEMENT when it is called ONLY!
set sqlInsert = Server.CreateObject("ADODB.Command")
sqlInsert.ActiveConnection = objConn
sqlInsert.CommandText = "INSERT INTO tbl_TimeLog (tWeekBegin, tEmployeeNum) VALUES ("& getWeek &","& getUser &")"
sqlInsert.Execute()
End If
next
<%@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 ("TimeSheet.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
 
if trim(request.Form("RecordsAdd")="Insert") then ' This lets us ONLY load the INSERT STATEMENT when it is called ONLY! 
set sqlInsert = Server.CreateObject("ADODB.Command")
sqlInsert.ActiveConnection = objConn
sqlInsert.CommandText = "INSERT INTO tbl_TimeLog (tWeekBegin, tEmployeeNum) VALUES ("& getWeek &","& getUser &")"
sqlInsert.Execute()
End If
next
' 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_24847818 - (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_24847818.asp?WeekCh=<%=trim(request.QueryString("WeekCh"))%>&amp;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">&nbsp;</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>
          <p> 
            <input name="Insert" type="submit" id="Insert" value="Insert" />
            <input type="hidden" name="RecordsAdd" value="Insert" />
          </p>
        </div>
      </form>	            
      <table style="width:100%; border:0px; text-align:left;" cellspacing="0" cellpadding="3">
        <tr>
          <td>
          <%'add your Insert Button here%>
          </td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</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>

Open in new window

Comment
Watch Question

Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Here you go again.
http://ee.cffcs.com/Q_24883096/Q_24883096.asp?WeekCh=19/10/2009&UserNameCH=Howells

code
http://ee.cffcs.com/Q_24883096/Q_24883096.zip

Added in the <FORM> and the  information for INSERT.
Please test in the link above. And then test code on your own server.

Good Luck
Carrzkiss

Author

Commented:
Carrzkiss,
I was hoping you'd pick that one up.
I have tested it and i works great. I wasn't too far off, not too bad for my first sql coding??
How can i get the new 'Add Row' form to show even when no records are returned and then requery the main form? The reason for this is that on Monday morning there will be no records for anyone and so they will get a blank page.

Author

Commented:
Carrzkiss,
Sorry, one more question and hopefully i will be out of your hair.
If i wanted the numbers to have a decimal place, where and how would i change the setting?
Author, Web Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Fixed a problem with the [Update]
It would create new rows on Every Update.
Some how there was a hidden duplicate INSERT value that was being triggered.
It is removed and updated on the site, please read the [Insert New Row] Section.

Take Care
Carrzkiss

Author

Commented:
Thankyou Carrzkiss, once i get the decimal thing worked out and add drop downs for stages and tasks out i think i'm ready to go. (Through your teaching i even managed to create a drop down and a new connection to get the data from).
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Good Going.
It is always nice to be complemented on my work.

Also:
I tried searching high and low for the Decimal issue.
The only thing that I found out is: CDbl

Re-download the code here
http://ee.cffcs.com/Q_24883096/Q_24883096.zip

Now, you will notice between to 2 codes (This new code and the old code)
Is the UPDATE Statement

Original    tMon = int(request.Form("tMon_" & n))

New          tMon = CDbl(request.Form("tMon_" & n))

The CDbl converts the string to a double (decimal)
The only thing that needs to be done now, is to find some way to get it to get
This new Decimal Format when it is inserted into the Database.

I tried researching and could not find anything on it.
So:
Create a new Question on it, And show your code for the entire UPDATE statement.
And see if someone can get it to insert for you.

Please post the link in here so I can keep track of it as well.
As I would like to know myself how to get it to insert and keep it's decimal format.

Have a good one.
Carrzkiss

Author

Commented:
Cheers.
I just added the post at https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24886304.html

I found the Cdbl thing as well, just not sure of where to put it. I kept on getting errors when putting it in the body, will give yours a go now.
I found the Cdbl thing here:
http://www.aspwebpro.com/tutorials/asp/formattingspecdata.asp
Geg
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
I added in the missing information to your new post.
As well as the Demo link page, so that those that would like to assist will be able to
Test the code and see that we do at least have the string -to- Doulbe CDbl added in properly.

Have a good one Geg.
It is 4:45am EST here in N.C. , USA.
Getting ready to get my boy up for school, then set down and write out a business mailing.
(That is a week over-due, just having a little writers block, I hate it when it happens)

Carrzkiss

Author

Commented:
Carrzkiss
You're in North Carolina? I know it well. My sister lives in Winston Salem.
Do you ever sleep?
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
I use to work in Winston Salem back in the late 90's
Did the "Cable TV Upgrade there, so tell you sister that one of your EE buddy's
Is the reason she has: Digital TV, Highspeed Internet and Digital Phone though the Cable Company, which if I am not mistaken, is: Time Warner Cable /Roadrunner
(Small world aint it?)

Sleep, yep, just the last few nights I have not slept that much.
Too many thoughts in my head and other mess that keeps my mind accupied when I WANT to go to sleep.  :=)

Author

Commented:
Will do, It is Roadrunner, i will be over there in April on my way to Emigrate to New Zealand.
I seem to have accidently stopped monitoring the other post, any ideas how i get it back.
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
At the top of the page (Where your original post is at)
You should see:
Save
Monitor
Print
Email
Share

Click on Monitor

New Zealand, To live or work or school?

Author

Commented:
To Live and Work. Hopefully work, i don't have a job yet.
My wife is a Kiwi and it's time she moved home.
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Your lucky.
Pride her with everything you have.
Been single here for going on 14yrs now.
Lonely, BUT I look at it this way.
If I can make my dreams come true within the next year, then being single has been worth it, IF my dreams do not come true, then it has all been a waste.

So. Pride your wife with everything you have my friend, you are lucky to have a wife.

Carrzkiss (I love writen my name, do not know why :) )

Author

Commented:
I do, i know.
She is pretty wonderful. Moved to the uk to be with me 11 years ago. and i feel it's her turn now.
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Yep, it is payment time now.
Good Luck with the move, I know that you will all enjoy it.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.