Question

ASP Access Database UPDATE Multiple Rows/Columns

Asked by: GegH

I am trying to create a timesheet for our intranet.
I have a form to search for a particular Week Begining date and return a list of entries.
Each Project and Project Taks has a row with an area to enter a number for hours for each day.
Someone logging in would enter a certain task/Project number then through the week add the hours for that task.
Nobody will do this as they complete the task, they will wait to the end of the day or week to fill out the timesheet and so need to update multiple rows at once.
my code returns an error that states the update syntax is wrong. The page has an error saying the current record has been deleted, can you help.
Code is below. This was created in Dreamweaver so maybe lots of errors.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/Timesheet.asp" -->
<%
' *** Edit Operations: declare variables
 
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
 
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
 
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i
 
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If
 
' boolean to abort record edit
MM_abortEdit = false
 
' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables
 
If (CStr(Request("MM_update")) = "form2" And CStr(Request("MM_recordId")) <> "") Then
 
  MM_editConnection = MM_Timesheet_STRING
  MM_editTable = "tbl_TimeLog"
  MM_editColumn = "ID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "TimesheetEd.asp"
  MM_fieldsStr  = "tMon|value"
  MM_columnsStr = "tMon|none,none,NULL"
 
  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next
 
  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If
 
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
 
If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then
 
  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId
 
  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
 
    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If
 
End If
%>
<%
' *** Edit Operations: declare variables
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If
 
' boolean to abort record edit
MM_abortEdit = false
 
' query string to execute
MM_editQuery = ""
%>
<%
Dim rsTimesheet__MMColParam
rsTimesheet__MMColParam = "1"
If (Request.Form("WeekChoice") <> "") Then 
  rsTimesheet__MMColParam = Request.Form("WeekChoice")
End If
%>
<%
Dim rsDate
Dim rsDate_numRows
 
Set rsDate = Server.CreateObject("ADODB.Recordset")
rsDate.ActiveConnection = MM_Timesheet_STRING
rsDate.Source = "SELECT * FROM qry_TimeLog_Week"
rsDate.CursorType = 0
rsDate.CursorLocation = 2
rsDate.LockType = 1
rsDate.Open()
 
rsDate_numRows = 0
%>
<%
Dim rsTime__MMColParam
rsTime__MMColParam = "1"
If (Request.Form("WeekChoice") <> "") Then 
  rsTime__MMColParam = Request.Form("WeekChoice")
End If
%>
 
<%
Dim rsTime
Dim rsTime_numRows
 
Set rsTime = Server.CreateObject("ADODB.Recordset")
rsTime.ActiveConnection = MM_Timesheet_STRING
rsTime.Source = "SELECT * FROM tbl_TimeLog WHERE tWeekBegin = '" + Replace(rsTime__MMColParam, "'", "''") + "'"
rsTime.CursorType = 0
rsTime.CursorLocation = 2
rsTime.LockType = 1
rsTime.Open()
 
rsTime_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = -1
Repeat1__index = 0
rsTime_numRows = rsTime_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index
 
Repeat2__numRows = 10
Repeat2__index = 0
rsTime_numRows = rsTime_numRows + Repeat2__numRows
%>
<head>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-10-27 at 09:27:19ID24847818
Topics: Active Server Pages (ASP)
,; Scripting Languages
,; Web Languages/Standards
Participating Experts: 1
Points: 500
Comments: 24

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: GegHPosted on 2009-10-29 at 13:31:41ID: 25697931

I have managed to get rid of the error but it still doesn't update the database or update on the page.
Any ideas?

by: carrzkissPosted on 2009-10-30 at 03:29:12ID: 25701585

gegH
Please have a look at this example that I did for another user.
This demostrates how to do SO MUCH with better coding and less aggrivation.
And gets you away from DW coding that you are currently using, which is considered by myself and many others as: Crap Coding.

To do this properly, please try this out.
Example Page
http://ee.cffcs.com/Q_24798150/BilletScanDefects4.asp
code
http://ee.cffcs.com/Q_24798150/Q_24798150.zip
=====================================
If you would like for me to make up an example for you for your database
Please provide me with:
A demo copy of your database, with some records in it, and i will make up one for you with details on how to use the code, just like I have in the code(s) in the link(s) above.
=====================================
Have a good one.
Carrzkiss

by: GegHPosted on 2009-10-30 at 07:21:24ID: 25703189

Thanks Carrzkiss,
Attached is the database.
I had a go at the addition thing as well, but i couldn't quite get it to work.
I managed to add the row together (that was the easy bit), the problem is adding the filtered column together. It's the code to identify which records are showing that is letting me down.
i Change the "." of the database extension for a "-" to allow it to be uploaded

TimeSheet-accdb.zip
- 24 KB
Timesheet database

by: carrzkissPosted on 2009-11-03 at 11:44:02ID: 25732703

I am at a loss with the way your project is suppose to work. As the only thing that I have is just the DW code, I need some forms, I need a working page.
Can you send me over a working example.
I have your database, I just need to see how the page works, and how you are doing your page, in order for me to work it up on my end.

by: GegHPosted on 2009-11-03 at 14:52:17ID: 25734781

Carrzkiss.
Sorry too many things going on at once. redundancies happening at work, hopefully avoided this one for now.
I managed to sort the maths issue, querying a recordset, creating the sum of the relevant columns.
It is the same page as the following question where i will post the complete page and database.
I'm pretty new to this so if there is a better, more efficient way to do it please let me know (i am persistent though).

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24851862.html

I think this string should be closed so as not to confuse?

by: carrzkissPosted on 2009-11-03 at 15:44:16ID: 25735140

Why [B] for?
What I showed actually does what you need.
So it should have been an [A] grading.

I will continue to assist you once you post a working example of your code.
And should not take that long to produce the example for you, using
Straight ASP/SQL Coding the proper way.

But, this should have been an [A] grading not a [B]
As it DOES do what you need, you just do not understand it enough to do it yourself from my example.

Carrzkiss

by: GegHPosted on 2009-11-04 at 01:09:19ID: 25737553

carrzkiss,
Sorry, the intention was to put an A grade. It was getting late and i was getting confused about which question i was on. The intention wasn't to close this question. After i closed it i realised my error and was hoping you would still be monitoring it.
Attached is the full page.

The idea is to input the data and update the database as you go really.
First problem was to add a row at will then return to the same filtered, updated dataset to fill out the form.
As the week moves on you will add times to existing rows.
We have 50 employees and each employee should only be able to see their own timesheet, but choose the week they are filling in.
But that week date has to be generated at the start of the week.
For a novice this is incredibly complex, probably made more so when using Dreamweaver, that i guess throws up false errors every now and then.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/Timesheet.asp" -->
<%
' *** Edit Operations: declare variables
 
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
 
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
 
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i
 
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If
 
' boolean to abort record edit
MM_abortEdit = false
 
' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables
 
If (CStr(Request("MM_update")) = "TimeEd" And CStr(Request("MM_recordId")) <> "") Then
 
  MM_editConnection = MM_Timesheet_STRING
  MM_editTable = "tbl_TimeLog"
  MM_editColumn = "tID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "TimesheetEd3.asp"
  MM_fieldsStr  = "tProjNum|value|tStage|value|tTask|value|tMon|value|tTues|value|tWed|value|tThurs|value|tFri|value|tSat|value|tSun|value"
  MM_columnsStr = "tProjNum|',none,''|tStage|',none,''|tTask|',none,''|tMon|none,none,NULL|tTues|none,none,NULL|tWed|none,none,NULL|tThurs|none,none,NULL|tFri|none,none,NULL|tSat|none,none,NULL|tSun|none,none,NULL"
 
  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next
 
  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If
 
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
 
If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then
 
  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId
 
  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
 
    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If
 
End If
%>
<%
Dim rsTime__MMColParam
rsTime__MMColParam = "1"
If (Request.QueryString("WeekCh") <> "") Then 
  rsTime__MMColParam = Request.QueryString("WeekCh")
End If
%>
<%
Dim rsTime__MMColParam2
rsTime__MMColParam2 = "1"
If (Request.QueryString("UserNameCH")   <> "") Then 
  rsTime__MMColParam2 = Request.QueryString("UserNameCH")  
End If
%>
<%
Dim rsTime
Dim rsTime_numRows
 
Set rsTime = Server.CreateObject("ADODB.Recordset")
rsTime.ActiveConnection = MM_Timesheet_STRING
rsTime.Source = "SELECT *  FROM tbl_TimeLog  WHERE tWeekBegin = '" + Replace(rsTime__MMColParam, "'", "''") + "' AND tEmployeeNum = '" + Replace(rsTime__MMColParam2, "'", "''") + "'"
rsTime.CursorType = 0
rsTime.CursorLocation = 2
rsTime.LockType = 1
rsTime.Open()
 
rsTime_numRows = 0
%>
<%
Dim rsTotal__MMColParam
rsTotal__MMColParam = "1"
If (Request.QueryString("WeekCH") <> "") Then 
  rsTotal__MMColParam = Request.QueryString("WeekCH")
End If
%>
<%
Dim rsTotal__MMColParam2
rsTotal__MMColParam2 = "1"
If (Request.QueryString("UserNameCH") <> "") Then 
  rsTotal__MMColParam2 = Request.QueryString("UserNameCH")
End If
%>
<%
Dim rsTotal
Dim rsTotal_numRows
 
Set rsTotal = Server.CreateObject("ADODB.Recordset")
rsTotal.ActiveConnection = MM_Timesheet_STRING
rsTotal.Source = "SELECT Count(tID), Count(tWeekBegin), Sum(tMon), Sum(tTues), Sum(tWed),Sum(tThurs), Sum(tFri), Sum(tSat), Sum(tSun)  FROM tbl_TimeLog  WHERE tWeekBegin = '" + Replace(rsTotal__MMColParam, "'", "''") + "' AND tEmployeeNum = '" + Replace(rsTotal__MMColParam2, "'", "''") + "'"
rsTotal.CursorType = 0
rsTotal.CursorLocation = 2
rsTotal.LockType = 1
rsTotal.Open()
 
rsTotal_numRows = 0
%>
<%
Dim rsPass__MMColParam
rsPass__MMColParam = "1"
If (Request.QueryString("WeekCh") <> "") Then 
  rsPass__MMColParam = Request.QueryString("WeekCh")
End If
%>
<%
Dim rsPass
Dim rsPass_numRows
 
Set rsPass = Server.CreateObject("ADODB.Recordset")
rsPass.ActiveConnection = MM_Timesheet_STRING
rsPass.Source = "SELECT tWeekBegin FROM tbl_TimeLog WHERE tWeekBegin = '" + Replace(rsPass__MMColParam, "'", "''") + "'"
rsPass.CursorType = 0
rsPass.CursorLocation = 2
rsPass.LockType = 1
rsPass.Open()
 
rsPass_numRows = 0
%>
<%
Dim rsLogin__MMColParam
rsLogin__MMColParam = "1"
If (Request.QueryString("UserNameCh") <> "") Then 
  rsLogin__MMColParam = Request.QueryString("UserNameCh")
End If
%>
<%
Dim rsLogin
Dim rsLogin_numRows
 
Set rsLogin = Server.CreateObject("ADODB.Recordset")
rsLogin.ActiveConnection = MM_Timesheet_STRING
rsLogin.Source = "SELECT * FROM qry_Login WHERE tEmployeeNum = '" + Replace(rsLogin__MMColParam, "'", "''") + "'"
rsLogin.CursorType = 0
rsLogin.CursorLocation = 2
rsLogin.LockType = 1
rsLogin.Open()
 
rsLogin_numRows = 0
%>
 
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = 10
Repeat1__index = 0
rsTime_numRows = rsTime_numRows + Repeat1__numRows
%>
 
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../HMYWebStyle.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div align="center"> 
  <table width="90%" height="50" border="0" cellpadding="5" cellspacing="0" >
    <tr valign="top"> 
      <td width="444" height="40" valign="middle"> <form name="Google" id="GoogleSearch" method="Get" action="http://www.google.co.uk/search">
          <div align="left"> 
            <input type="text"   valign="middle" name="q" height="25" size="40" maxlength="255" value="" />
            <input name=meta type=hidden id=cty value="cr=countryUK|countryGB">
            <input type="submit" valign="middle" value="Google UK Search" />
          </div>
        </form></td>
      <td width="65" valign="middle"><form action="http://www.ihsti.com/scripts/ti_logon/ti_logon.asp" method="post" name="form1" target="_blank" id="form1">
          <label> 
          <input name="imageField" type="image" src="/Images/IHS_login_2.png" align="middle" width="50" height="50" />
          </label>
          <input name="send"     type="hidden" id="send" value="Submit" />
          <input name="Welcome"  type="hidden" id="Welcome" />
          <input name="Username" type="hidden" id="Username" value="HAZELMY" />
          <input name="Password" type="hidden" id="Password" value="pantiles" />
        </form></td>
      <td width="175" height="40" valign="middle"> <a href="https://secure.projectminder.com/login.aspx" target="_blank"><img src="/Images/ProjMindText.gif" alt="Project Mider Login" width="174" height="30" border="0" /></a></h5></td>
      <td width="116" height="40" valign="middle"><div align="right"> <a href="http://www.hmy.uk.com" target="_blank"><img src="/Images/HMY-ID.gif" alt="HMY Website" width="116" height="42" border="0" /></a></div></td>
    </tr>
  </table>
  <table width="90%" height="30" border="0" cellpadding="5" cellspacing="0" >
    <tr> 
      <td width="92" height="40"><h5><he><a href="../News.asp">News/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="/GenTemplates.asp">Templates/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../EmpHandbook.asp">Employee 
          Handbook/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../QA.asp">QA 
          Procedures/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../ProjData.asp">Project 
          Database/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../TechGuide.asp">Technical 
          Guidance</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../training.asp">Training/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../StaffRes.asp">Staff 
          Resources/</a></he></h5></td>
      <td width="92" height="40"><div align="right"> 
          <h5><a href="../ExterLink.asp">Useful 
            Links/</a></h5>
        </div></td>
    </tr>
  </table>
  <p1></p1> 
  <table width="90%" height="50" border="0" cellpadding="5" cellspacing="0" bgcolor="#FFFFFF">
    <tr> 
      <td width="92" height="40"><h5><he>Archive//</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><div align="right"></div></td>
    </tr>
  </table>
  <p>&nbsp;</p>
</div>
<p align="left"> 
<table width="90%" border="0" align="center" cellpadding="5" cellspacing="0">
  <tr> 
    <td> <% If Not rsTime.EOF Or Not rsTime.BOF Then %>
      <form action="<%=MM_editAction%>" method="POST" name="TimeEd" id="TimeEd">
        <div align="center"> 
          <table border="0" cellpadding="5" cellspacing="5">
            <tr> 
              <td colspan="11"><table width="100%" border="0" cellspacing="0" cellpadding="3">
                  <tr> 
                    <td><input name="Update" type="submit" id="Update" value="Update"></td>
                    <td>Week Beginning: 
                      <input name="WeekCh" type="text" id="WeekCh" value="<%=(rsTime.Fields.Item("tWeekBegin").Value)%>" readonly="true"> 
                    </td>
                    <td>Employee: 
                      <input name="UserNameCh" type="text" id="UserNameCh" value="<%=(rsTime.Fields.Item("tEmployeeNum").Value)%>"> 
                    </td>
                  </tr>
                </table></td>
            </tr>
            <tr> 
              <td width="20"><font size="-1">Project Number</font></td>
              <td><font size="-1">Stage</font></td>
              <td><font size="-1">Task</font></td>
              <td width="10"><font size="-1">Mon</font></td>
              <td width="10"><font size="-1">Tues</font></td>
              <td width="10"><font size="-1">Wed</font></td>
              <td width="10"><font size="-1">Thurs</font></td>
              <td width="10"><font size="-1">Fri</font></td>
              <td width="10"><font size="-1">Sat</font></td>
              <td width="10"><font size="-1">Sun</font></td>
              <td><font size="-1">Total</font></td>
            </tr>
            <% While ((Repeat1__numRows <> 0) AND (NOT rsTime.EOF)) %>
            <tr> 
              <td> 
                <input name="tProjNum" type="text" id="tProjNum" value="<%=(rsTime.Fields.Item("tProjNum").Value)%>" size="5"></td>
              <td> 
                <input name="tStage" type="text" id="tStage" value="<%=(rsTime.Fields.Item("tStage").Value)%>" size="10"></td>
              <td> 
                <input name="tTask" type="text" id="tTask" value="<%=(rsTime.Fields.Item("tTask").Value)%>" size="35"></td>
              <td> 
                <input name="tMon" type="text" id="tMon" value="<%=(rsTime.Fields.Item("tMon").Value)%>" size="3"></td>
              <td> 
                <input name="tTues" type="text" id="tTues" value="<%=(rsTime.Fields.Item("tTues").Value)%>" size="3"></td>
              <td> 
                <input name="tWed" type="text" id="tWed" value="<%=(rsTime.Fields.Item("tWed").Value)%>" size="3"></td>
              <td> 
                <input name="tThurs" type="text" id="tThurs" value="<%=(rsTime.Fields.Item("tThurs").Value)%>" size="3"></td>
              <td> 
                <input name="tFri" type="text" id="tFri" value="<%=(rsTime.Fields.Item("tFri").Value)%>" size="3"></td>
              <td> 
                <input name="tSat" type="text" id="tSat" value="<%=(rsTime.Fields.Item("tSat").Value)%>" size="3"></td>
              <td> 
                <input name="tSun" type="text" id="tSun" value="<%=(rsTime.Fields.Item("tSun").Value)%>" size="3"></td>
              <td> 
                <% 
	Dim MyNum
	Mynum = (rsTime.Fields.Item("tMon").Value)+(rsTime.Fields.Item("tTues").Value)+(rsTime.Fields.Item("tWed").Value)+(rsTime.Fields.Item("tThurs").Value)+(rsTime.Fields.Item("tFri").Value)+(rsTime.Fields.Item("tSat").Value)+(rsTime.Fields.Item("tSun").Value)
	Response.Write(Mynum)
	%> </td>
            </tr>
            <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsTime.MoveNext()
Wend
%>
            <tr> 
              <td colspan="2"><p> <br>
                </p></td>
              <td><p align="right"><font size="-1">Total:</font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1002").Value)%> </font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1003").Value)%></font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1004").Value)%></font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1005").Value)%></font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1006").Value)%></font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1007").Value)%></font></p></td>
              <td><p align="center"><font size="-1"><%=(rsTotal.Fields.Item("Expr1008").Value)%></font></p></td>
              <td> <% 
	Dim MyTotal
	MyTotal = (rsTotal.Fields.Item("Expr1002").Value)+(rsTotal.Fields.Item("Expr1003").Value)+(rsTotal.Fields.Item("Expr1004").Value)+(rsTotal.Fields.Item("Expr1005").Value)+(rsTotal.Fields.Item("Expr1006").Value)+(rsTotal.Fields.Item("Expr1007").Value)+(rsTotal.Fields.Item("Expr1008").Value)
	Response.Write(MyTotal)
	%> </td>
            </tr>
          </table>
          <% Do Until rsTime.EOF = True %>
          <input type="hidden" name="MM_update" value="TimeEd">
          <input type="hidden" name="MM_recordId" value="<%= rsTime.Fields.Item("tID").Value %>">
          <% Loop %>
        </div>
      </form>
      <% End If ' end Not rsTime.EOF Or NOT rsTime.BOF %>
      <table width="100%" border="0" cellspacing="0" cellpadding="3">
        <tr>
          <td>
<form action="TimesheetInsert.asp" method="get" name="AddRow" id="AddRow">
              <input name="Submit" type="submit" id="Submit" value="Add New Row">
              <input name="WeekCh" type="hidden" id="WeekCh" value="<%=(rsPass.Fields.Item("tWeekBegin").Value)%>">
              <input name="UserNameCh" type="hidden" id="UserNameCh" value="<%=(rsLogin.Fields.Item("tEmployeeNum").Value)%>">
            </form>
            
          </td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table> </td>
  </tr>
</table>
</p>
</body>
</html>
<%
rsTime.Close()
Set rsTime = Nothing
%>
<%
rsTotal.Close()
Set rsTotal = Nothing
%>
<%
rsPass.Close()
Set rsPass = Nothing
%>
<%
rsLogin.Close()
Set rsLogin = Nothing
%>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:

Select all Open in new window

TimeSheet-accdb
- 512 KB
Timesheet Database accdb extension

by: carrzkissPosted on 2009-11-04 at 03:52:35ID: 25738382

Due to the fact that you have an outside database "login"
I cannot test this script out to see what you are doing.
Your Database has so goodies in it, that trying to look at it
To determin what you are trying to do is going to be a nitemare.

I get passed 1 error and am faced with another Error.
And now your html coding is crashing my DWCS3.

Can you please put together a page that will run with the querystring of:

?WeekCh=26/10/2009&UserNameCh=1

Or whatever it is suppose to be.
I created a login table with the following fields:
tEmployeeNum
username

I know that the "tEmployeeNum" is correct, but that is all that I know.
Due to the fact that you are using * wildcards in place of your column names.
This is one reason why I am against the use of * wildcards being used in place of the column names, as it makes it so difficult to troubleshot someone else work when you do not have the proper database to go with the columns.

Give me a good working example, that does not have me "Having" to login
And I will see about helping you out on this project.
Right now, I am at a stand still.

Now, I have to figure out "why" your page is crashing DW for.

Carrzkiss

by: GegHPosted on 2009-11-04 at 06:04:15ID: 25739360

Carrzkiss
Attached is a database that has the login table inserted.
I've tested it this side and it seems to work. The page code attached is the one i use to get to the timesheet page.
Thankyou for all your help, i feel like i'm so close yet can't go any further.

<%@LANGUAGE="VBSCRIPT"%> 
<!--#include file="../Connections/Timesheet.asp" -->
<%
Dim rsSecurity
Dim rsSecurity_numRows
 
Set rsSecurity = Server.CreateObject("ADODB.Recordset")
rsSecurity.ActiveConnection = MM_Timesheet_STRING
rsSecurity.Source = "SELECT *  FROM qry_Login"
rsSecurity.CursorType = 0
rsSecurity.CursorLocation = 2
rsSecurity.LockType = 1
rsSecurity.Open()
 
rsSecurity_numRows = 0
%>
<%
Dim rsWeek
Dim rsWeek_numRows
 
Set rsWeek = Server.CreateObject("ADODB.Recordset")
rsWeek.ActiveConnection = MM_Timesheet_STRING
rsWeek.Source = "SELECT * FROM qry_TimeLog_Week"
rsWeek.CursorType = 0
rsWeek.CursorLocation = 2
rsWeek.LockType = 1
rsWeek.Open()
 
rsWeek_numRows = 0
%>
<%
Dim rsTime
Dim rsTime_numRows
 
Set rsTime = Server.CreateObject("ADODB.Recordset")
rsTime.ActiveConnection = MM_Timesheet_STRING
rsTime.Source = "SELECT tWeekBegin, tEmployeeNum FROM tbl_TimeLog"
rsTime.CursorType = 0
rsTime.CursorLocation = 2
rsTime.LockType = 1
rsTime.Open()
 
rsTime_numRows = 0
%>
 
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../HMYWebStyle.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div align="center"> 
  <table width="90%" height="50" border="0" cellpadding="5" cellspacing="0" >
    <tr valign="top"> 
      <td width="444" height="40" valign="middle"> <form name="Google" id="GoogleSearch" method="Get" action="http://www.google.co.uk/search">
          <div align="left"> 
            <input type="text"   valign="middle" name="q" height="25" size="40" maxlength="255" value="" />
            <input name=meta type=hidden id=cty value="cr=countryUK|countryGB">
            <input type="submit" valign="middle" value="Google UK Search" />
          </div>
        </form></td>
      <td width="65" valign="middle"><form action="http://www.ihsti.com/scripts/ti_logon/ti_logon.asp" method="post" name="form1" target="_blank" id="form1">
          <label> 
          <input name="imageField" type="image" src="/Images/IHS_login_2.png" align="middle" width="50" height="50" />
          </label>
          <input name="send"     type="hidden" id="send" value="Submit" />
          <input name="Welcome"  type="hidden" id="Welcome" />
          <input name="Username" type="hidden" id="Username" value="HAZELMY" />
          <input name="Password" type="hidden" id="Password" value="pantiles" />
        </form></td>
      <td width="175" height="40" valign="middle"> <a href="https://secure.projectminder.com/login.aspx" target="_blank"><img src="/Images/ProjMindText.gif" alt="Project Mider Login" width="174" height="30" border="0" /></a></h5></td>
      <td width="116" height="40" valign="middle"><div align="right"> <a href="http://www.hmy.uk.com" target="_blank"><img src="/Images/HMY-ID.gif" alt="HMY Website" width="116" height="42" border="0" /></a></div></td>
    </tr>
  </table>
  <table width="90%" height="30" border="0" cellpadding="5" cellspacing="0" >
    <tr> 
      <td width="92" height="40"><h5><he><a href="../News.asp">News/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="/GenTemplates.asp">Templates/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../EmpHandbook.asp">Employee 
          Handbook/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../QA.asp">QA 
          Procedures/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../ProjData.asp">Project 
          Database/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../TechGuide.asp">Technical 
          Guidance</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../training.asp">Training/</a></he></h5></td>
      <td width="92" height="40"><h5><he><a href="../StaffRes.asp">Staff 
          Resources/</a></he></h5></td>
      <td width="92" height="40"><div align="right"> 
          <h5><a href="../ExterLink.asp">Useful 
            Links/</a></h5>
        </div></td>
    </tr>
  </table>
  <p1></p1> 
  <table width="90%" height="50" border="0" cellpadding="5" cellspacing="0" bgcolor="#FFFFFF">
    <tr> 
      <td width="92" height="40"><h5><he>Archive//</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><h5><he>&nbsp;</he></h5></td>
      <td width="92" height="40"><div align="right"></div></td>
    </tr>
  </table>
  <p>&nbsp;</p>
</div>
<p align="left"> <table width="90%" border="0" align="center" cellpadding="5" cellspacing="0">
  <tr> 
    <td><table width="100%" border="0" cellspacing="0" cellpadding="3">
        <tr>
          <td>
<form action="TimesheetEd3.asp" method="get" name="WeekChoice" id="WeekChoice">
              <select name="WeekCh" id="WeekCh">
                <%
While (NOT rsWeek.EOF)
%>
                <option value="<%=(rsWeek.Fields.Item("tWeekBegin").Value)%>"><%=(rsWeek.Fields.Item("tWeekBegin").Value)%></option>
                <%
  rsWeek.MoveNext()
Wend
If (rsWeek.CursorType > 0) Then
  rsWeek.MoveFirst
Else
  rsWeek.Requery
End If
%>
              </select>
              <select name="UserNameCh" id="UserNameCh">
                <%
While (NOT rsSecurity.EOF)
%>
                <option value="<%=(rsSecurity.Fields.Item("tEmployeeNum").Value)%>"><%=(rsSecurity.Fields.Item("tEmployeeNum").Value)%></option>
                <%
  rsSecurity.MoveNext()
Wend
If (rsSecurity.CursorType > 0) Then
  rsSecurity.MoveFirst
Else
  rsSecurity.Requery
End If
%>
              </select>
              <input type="submit" name="Submit" value="Submit">
            </form></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
      </table>
      <p>&nbsp;</p></td>
  </tr>
</table></p>
</body>
</html>
<%
rsSecurity.Close()
Set rsSecurity = Nothing
%>
<%
rsWeek.Close()
Set rsWeek = Nothing
%>
<%
rsTime.Close()
Set rsTime = Nothing
%>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:

Select all Open in new window

TimeSheet-accdb.zip
- 36 KB
Timesheet DB

by: carrzkissPosted on 2009-11-04 at 09:59:26ID: 25741982

I am having to do a re-pair on my DWCS3.
It is messed up, so once I have it working I will test your code here.
Right now priority is DW.

What is the link + querystring for this to work?
page.asp?id=1&this=that

by: GegHPosted on 2009-11-04 at 11:22:54ID: 25742869

Not quite sure of the question?
The first page passes "WeekCh" and "UserNameCh" to the main page as a URL Parameter (WeekBegin and tEmployeeNum).
The second page filters the dataset "rsTime" by these parameters.

by: carrzkissPosted on 2009-11-04 at 11:37:00ID: 25743007

A repair got me going again, DW is fixed.

I got the original page to run, so I am good to go.
I will get back with you later on when I have a working example for you.

Have a good one.
Carrzkiss

by: carrzkissPosted on 2009-11-04 at 14:37:05ID: 25744825

In you form, you have the following

<% While ((Repeat1__numRows <> 0) AND (NOT rsTime.EOF)) %>
<form> here
</form>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsTime.MoveNext()
Wend
%>

Why is this here for?
You only have a single form, but yet, you are trying to load multiple records.

Please explain this.
I have removed it as it is not needed.

ALSO.
What is this for:

===================================
<% Do Until rsTime.EOF = True %>
<input type="hidden" name="MM_update" value="TimeEd">
<input type="hidden" name="MM_recordId" value="<%= rsTime.Fields.Item("tID").Value %>">
<% Loop %>
===================================

You are looping for no reason? (This also crashes in my design)
Please explain these 2 to me.

I am getting ready to design the Update statement now.
And will have it done within the hour.

Carrzkiss

layout.jpg
- 143 KB
Your Layout VS. My layout

by: GegHPosted on 2009-11-04 at 15:12:17ID: 25745138

Carrzkiss,
The multiple row part is because in any given week a user will work on multiple projects and multiple stages of a given project. This may equate to different costings further down the road.
So in the database each row has 2 extra columns, the user and the week in which the task was completed.
If on your form you add a new row it will pass the date and user to the database to create the new row then essentially requery to add it to the form giving multiple rows. I wasn't sure how else to add multiple rows to a form.

when the form had multiple rows to edit/add data to it kept on getting an error. The only way i could stop the error was to create the loop. Thinking about it now it may be the loop that was stopping the update work. When it was a single row it worked fine, but we need to see all tasks for any given week and any given user. That is where i got lost.

by: GegHPosted on 2009-11-04 at 15:17:14ID: 25745185

Sorry, just thought of something.
I tried a version where the row was contained within a single form, and then the form was repeated depending on how many records should be shown on the page. However, i couldn't find anything that would allow me to submit multiple forms at once, and it was a nightmare to line up columns and titles making it very difficult to use and a bit tempramental.

by: carrzkissPosted on 2009-11-04 at 15:23:29ID: 25745246

OK.
That will give your multi-forms.
That is a different issue all together.

Give me a demostration Query of a user that will have multiple items to work on.
So that I can take a look at that, as I have the update done, but not as you have just specified.

Give me a user in your database that you have supplied to me, that will show multiple rows: WeekCh=19/10/2009&UserNameCH=Howells
This user only shows "1" weeks worth of work.
Give me one that shows multiple weeks (or) multiple jobs, so I can see what you are trying to do here.

by: carrzkissPosted on 2009-11-04 at 21:51:55ID: 25747042

I have the other user with multiple actions
WeekCh=26/10/2009&UserNameCh=Best

Are you wanting to allow the user to be able to update multiple
[Projects] at the same time? (There is more coding invlolved)
(or) (This is easier)
Have different <form>'s with their own [Submit] button for each [Project]

Which one do you want to do? I prefer the 2nd, as it is easier.
The 1st is more advance, and will take a little more time, and well.
[Experts Exchange] is not the place that I want to tackle it on.

Let me know?

by: GegHPosted on 2009-11-05 at 01:00:09ID: 25747700

The single submit button was really what i was looking for. The multiple submit buttons for each row/projects would do the job though.
I just worry that someone entering a time on a particular project will forget to press the submit button on that particular row.
Is there a way to set off a chain reaction, that pushing one submit button will run the 'submit' on each form on the page?

by: carrzkissPosted on 2009-11-05 at 04:47:50ID: 25748829

OK.
1 Button, submits multiple projects

http://ee.cffcs.com/Q_24847818/Q_24847818.asp?WeekCh=26/10/2009&UserNameCH=Best

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

Please read the liner notes within the code.
This is NOT, I repeat: THIS IS NOT
Dreamweaver "DW" generated Code.
This is ASP/SQL Hand Coded.
Look through the code and understand it.
I have a few links within the code to information that I feel you should read
Up on in order to truely understand what the code will do for you.

Files included:
Q_24847818.asp => The entire project rest in this file, the forms and asp updates.
ADOVBS.inc => These are the Parameters used in the SQL.
TimeSheets.accdb => Your database
Q_24847818.zip => The zip archive which holds all files

I have also added in information about setting and using CSS properly.
I have also validated the entire page to XHTML Standard.

This should get your going in the right direction.

Carrzkiss

by: GegHPosted on 2009-11-05 at 06:22:19ID: 25749707

Absolutely awesome. Works perfectly.
I will go to the other message and close it down, if i could add extra points i would.
I have one question (for the moment anyway), If i need to add a row, for example, someone logs in on Monday morning after doing one task, they will need a button to add a row to start the timesheet, they will then log in monday evening and add a row after doing a second task in the afternoon. Is this best to do by adding a form to the bottom of the page which will jump to a second page to add a row, entering the username and date automatically (using a URL Parameter or something). Then on pressing the insert button it jumps back to the original form with the inserted row or is there a better way?
I read somewhere that you cant have an insert and update command on the same page?

by: carrzkissPosted on 2009-11-05 at 10:10:11ID: 25752193

#1: Try to stay away from pages that tell you that you CANNOT do something.
As there is always a way to do it, (OR) they could not do it themselves.

You will recognize in my code that there is an IF STATEMENT right before the
Update Statement begins.
All you need to do is add in your own IF STATEMENT.

Example:
if trim(request.form("Records")="Insert")) then' Create the hidden field in your form.
INSERT STATEMENT GOES HERE
end if

You can have this button created at the bottom of the page.

------
You also need to look at the person who is logged in and make sure that they only deal with their own and not someone jumping into someone elses.

This is done through the logon credentials, and needs to be tended to soon.
=================
This question is suppose to be re-opened soon, to be re-graded.
Once it is, just accept my post on the code I provided.

Have a good one.
Carrzkiss

by: GegHPosted on 2009-11-06 at 00:58:07ID: 31646544

I have searched for hours for a solution to this problem and found nothing half as good as what i have received from Carrzkiss.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

ASP Access Database UPDATE Multiple Rows/Columns

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Active Server Pages (ASP)

Scripting Languages

Web Languages/Standards

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press