Solved

SQL query on ASP page to show records for multiple users with GET

Posted on 2009-07-07
135
607 Views
Last Modified: 2012-05-07
Hi all,
I have an ASP page with an SQL query that uses GET  to populate the UserName. The problem that I am having is running it for more than one user. If I select one Username & my date range it works perfectly, as soon as I select multiple people the page wont even display with the results. Does anyone have any ideas? I will post both pages below.

Thanks.
Jon
0
Comment
Question by:Jon DeVito
  • 67
  • 58
  • 4
  • +3
135 Comments
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24799480
Page to make selection:
(Right now I have it so they can only choose 1 just not to cause any issues on the site).


<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var Users_cmd = Server.CreateObject ("ADODB.Command");
Users_cmd.ActiveConnection = MM_TimeClock_STRING;
Users_cmd.CommandText = "SELECT DISTINCT UserName FROM dbo.Punch ORDER BY UserName ASC";
Users_cmd.Prepared = true;
 
var Users = Users_cmd.Execute();
var Users_numRows = 0;
%><!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" -->
<style type="text/css">
<!--
.style2 {font-size: 14px}
-->
</style>
<!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap"> </td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TC_Login.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	         <br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
        
        <form action="TimeClock_PP_Admin_Multiple_Date.asp" method="get" target="_blank" class="style2">
        
          <p class="style2">Name
            <select name="username2" id="username">
              <% 
while (!Users.EOF) {
%>
              <option value="<%=(Users.Fields.Item("UserName").Value)%>"><%=(Users.Fields.Item("UserName").Value)%></option>
              <%
  Users.MoveNext();
}
if (Users.CursorType > 0) {
  if (!Users.BOF) Users.MoveFirst();
} else {
  Users.Requery();
}
%>
              </select>
        </p>
          <p class="style2">Date From
            <select name="Month2" id="Month2">
              <option value="1" selected="selected">January (1)</option>
              <option value="2">February (2)</option>
              <option value="3">March (3)</option>
              <option value="4">April (4)</option>
              <option value="5">May (5)</option>
              <option value="6">June (6)</option>
              <option value="7">July (7)</option>
              <option value="8">August (8)</option>
              <option value="9">September (9)</option>
              <option value="10">October (10)</option>
              <option value="11">November (11)</option>
              <option value="12">December (12)</option>
            </select>
            <label>
            <select name="Day2" id="Day2">
              <option value="1" selected="selected">1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
            </select>
            </label>
            <select name="Year2" id="Year2">
              <option value="2009" selected="selected">2009</option>
              <option value="2010">2010</option>
              <option value="2011">2011</option>
              <option value="2012">2012</option>
              <option value="2013">2013</option>
              <option value="2014">2014</option>
              <option value="2015">2015</option>
              <option value="2016">2016</option>
              <option value="2017">2017</option>
              <option value="2018">2018</option>
              <option value="2019">2019</option>
              <option value="2020">2020</option>
            </select>
            <label> </label>
          </p>
          <p class="style2">Date To
            <select name="Month3" id="Month3">
              <option value="1" selected="selected">January (1)</option>
              <option value="2">February (2)</option>
              <option value="3">March (3)</option>
              <option value="4">April (4)</option>
              <option value="5">May (5)</option>
              <option value="6">June (6)</option>
              <option value="7">July (7)</option>
              <option value="8">August (8)</option>
              <option value="9">September (9)</option>
              <option value="10">October (10)</option>
              <option value="11">November (11)</option>
              <option value="12">December (12)</option>
            </select>
            <select name="Day3" id="Day3">
              <option value="1" selected="selected">1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
            </select>
            <select name="Year3" id="Year3">
              <option value="2009" selected="selected">2009</option>
              <option value="2010">2010</option>
              <option value="2011">2011</option>
              <option value="2012">2012</option>
              <option value="2013">2013</option>
              <option value="2014">2014</option>
              <option value="2015">2015</option>
              <option value="2016">2016</option>
              <option value="2017">2017</option>
              <option value="2018">2018</option>
              <option value="2019">2019</option>
              <option value="2020">2020</option>
            </select>
            <label> </label>
          </p>
          <p class="style2">
            <label>
            <input type="submit" name="Submit_Mutiplt" id="Submit_Mutiplt" value="Submit" />
            </label>
          </p>
        </form>
	    <!-- InstanceEndEditable --><br />
	    <br />
	 <br />	
	</div></td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22"> </td>
	<td width="317"> </td>
	<td width="50"> </td>
	<td width="558"> </td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
Users.Close();
%>

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24799489
Page showing results:


<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
  AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2"));
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING;
AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = ? AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+AdminMultipleDate__MMColParam+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";
AdminMultipleDate_cmd.Prepared = true;
AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
var AdminMultipleDate_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap"> </td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TC_Login.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	         <br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p> </p>
	    <p> </p>
	    <p> </p>
	    <p> </p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	 <br />	
	</div></td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22"> </td>
	<td width="317"> </td>
	<td width="50"> </td>
	<td width="558"> </td>
	<td width="4"> </td>
	<td width="675"> </td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 15

Expert Comment

by:mcmonap
ID: 24799551
Can you get the page to return the text of the SQL query you running?  I suspect you have an issue with your syntax but it will be easier to diagnose if you can show what is being sent to your SQL server
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 15

Expert Comment

by:mcmonap
ID: 24799565
Your query text look like the snippet below - queries are:
  • what goes in place of "?"
  • what goes in place of "AdminMultipleDate__MMColParam"
  • should "UserName = '"+AdminMultipleDate__MMColParam+"'" be "UserName IN '"+AdminMultipleDate__MMColParam+"'"
SELECT
	UserName
	, PunchInTime
	, ChangedPunchInFrom
	, PunchOutTime
	, ChangedPunchOutFrom
	, Reason
	, ComputerName
	, ChangedRecord
FROM
	( SELECT
		*
		,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum
		, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val
	FROM
		punch ) temp
WHERE
	UserName = ? 
	AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1)
	OR UserName = '"+AdminMultipleDate__MMColParam+"' 
	AND (PunchOutTime BETWEEN '"+Date_From+"' 
	AND Cast('"+Date_To+"' As datetime) + 1)
ORDER BY
	date_val DESC
	, rnum

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24800512
All of the codes I posted work perfectly fine. The problem is when I query multiple usernames. I basically need to loop this query for every username that is selected on the first page.

Thanks.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24800885
If you need to loop through, you can do something like this.
(Change to your own recordset)

   <%While Not objRs.EOF%>
PUT YOUR CODE HERE FOR THE USERS INFORMATION
   <%
    objRs.MoveNext
    Wend
%>

Carrzkiss
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 24801526
Why would you want to loop that query?  The database could return all the data in one go if you asked it to.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24803322
>>Why would you want to loop that query?
I totally got focused on something else and posted the wrong code in here.

Disreguard I post jondevito:

Also.
jondevito:

Reposted your code, use the [Attach Code Snippet] section below the Message Editor.

Post both pages in 1 Snippet.
Seperate them with

--------Page 1-------------

--------Page 2-------------

And mcmonap
Thinks for making me aware of my mistake.

Carrzkiss
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24804188
Sorry everybody, I dont think I'm explaining this very well.
I have a search criteria with Name, Date-From, & Date-To.
If I search for 1 name in a date range it works perfectly. If I search for more than one name the page wont even display. The URL looks like http://timeclock?username=user1&username=user2&username=user3&date-from=aRange&date-to=anotherRange. Thats why I thought a loop was necessary to get all of the username= out of the url. I have no idea how to get this to work so the loop idea may be totally off.

Thanks again.
--------------- Page 1 --------------
 
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var Users_cmd = Server.CreateObject ("ADODB.Command");
Users_cmd.ActiveConnection = MM_TimeClock_STRING;
Users_cmd.CommandText = "SELECT DISTINCT UserName FROM dbo.Punch ORDER BY UserName ASC";
Users_cmd.Prepared = true;
 
var Users = Users_cmd.Execute();
var Users_numRows = 0;
%><!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" -->
<style type="text/css">
<!--
.style2 {font-size: 14px}
-->
</style>
<!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TC_Login.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
        
        <form action="TimeClock_PP_Admin_Multiple_Date.asp" method="get" target="_blank" class="style2">
        
          <p class="style2">Name
            <select name="username2" id="username">
              <% 
while (!Users.EOF) {
%>
              <option value="<%=(Users.Fields.Item("UserName").Value)%>"><%=(Users.Fields.Item("UserName").Value)%></option>
              <%
  Users.MoveNext();
}
if (Users.CursorType > 0) {
  if (!Users.BOF) Users.MoveFirst();
} else {
  Users.Requery();
}
%>
              </select>
        </p>
          <p class="style2">Date From
            <select name="Month2" id="Month2">
              <option value="1" selected="selected">January (1)</option>
              <option value="2">February (2)</option>
              <option value="3">March (3)</option>
              <option value="4">April (4)</option>
              <option value="5">May (5)</option>
              <option value="6">June (6)</option>
              <option value="7">July (7)</option>
              <option value="8">August (8)</option>
              <option value="9">September (9)</option>
              <option value="10">October (10)</option>
              <option value="11">November (11)</option>
              <option value="12">December (12)</option>
            </select>
            <label>
            <select name="Day2" id="Day2">
              <option value="1" selected="selected">1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
            </select>
            </label>
            <select name="Year2" id="Year2">
              <option value="2009" selected="selected">2009</option>
              <option value="2010">2010</option>
              <option value="2011">2011</option>
              <option value="2012">2012</option>
              <option value="2013">2013</option>
              <option value="2014">2014</option>
              <option value="2015">2015</option>
              <option value="2016">2016</option>
              <option value="2017">2017</option>
              <option value="2018">2018</option>
              <option value="2019">2019</option>
              <option value="2020">2020</option>
            </select>
            <label> </label>
          </p>
          <p class="style2">Date To
            <select name="Month3" id="Month3">
              <option value="1" selected="selected">January (1)</option>
              <option value="2">February (2)</option>
              <option value="3">March (3)</option>
              <option value="4">April (4)</option>
              <option value="5">May (5)</option>
              <option value="6">June (6)</option>
              <option value="7">July (7)</option>
              <option value="8">August (8)</option>
              <option value="9">September (9)</option>
              <option value="10">October (10)</option>
              <option value="11">November (11)</option>
              <option value="12">December (12)</option>
            </select>
            <select name="Day3" id="Day3">
              <option value="1" selected="selected">1</option>
              <option value="2">2</option>
              <option value="3">3</option>
              <option value="4">4</option>
              <option value="5">5</option>
              <option value="6">6</option>
              <option value="7">7</option>
              <option value="8">8</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
              <option value="12">12</option>
              <option value="13">13</option>
              <option value="14">14</option>
              <option value="15">15</option>
              <option value="16">16</option>
              <option value="17">17</option>
              <option value="18">18</option>
              <option value="19">19</option>
              <option value="20">20</option>
              <option value="21">21</option>
              <option value="22">22</option>
              <option value="23">23</option>
              <option value="24">24</option>
              <option value="25">25</option>
              <option value="26">26</option>
              <option value="27">27</option>
              <option value="28">28</option>
              <option value="29">29</option>
              <option value="30">30</option>
              <option value="31">31</option>
            </select>
            <select name="Year3" id="Year3">
              <option value="2009" selected="selected">2009</option>
              <option value="2010">2010</option>
              <option value="2011">2011</option>
              <option value="2012">2012</option>
              <option value="2013">2013</option>
              <option value="2014">2014</option>
              <option value="2015">2015</option>
              <option value="2016">2016</option>
              <option value="2017">2017</option>
              <option value="2018">2018</option>
              <option value="2019">2019</option>
              <option value="2020">2020</option>
            </select>
            <label> </label>
          </p>
          <p class="style2">
            <label>
            <input type="submit" name="Submit_Mutiplt" id="Submit_Mutiplt" value="Submit" />
            </label>
          </p>
        </form>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
Users.Close();
%>
 
 
------------ Page 2 ----------------
 
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
  AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2"));
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING;
AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = ? AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+AdminMultipleDate__MMColParam+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";
AdminMultipleDate_cmd.Prepared = true;
AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
var AdminMultipleDate_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TC_Login.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 24804364
OK.
Do you have multple Fields for all the different Usernames that you are wanting to search for?
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24804377
What do you mean by multiple fields? There is a box that allows multiple selections on the first page & sends those to the second page. Thats working ok.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24804405
<form action="TimeClock_PP_Admin_Multiple_Date.asp" method="get" target="_blank" class="style2">

Remember GET has a limit to the lengh of the URL
http://support.microsoft.com/kb/q208427/

This includes the URL and all parameters passed
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24804458
I'm well within the limit. I'm only looking to do it for a few usernames.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24804544
mcmonap had it right  
WHERE UserName = ? being multiple parameters needs to be where username in
0
 
LVL 9

Expert Comment

by:rg20
ID: 24804565
mcmonap had it right

your statement for one username would be where username =
but for multiple usernames it would be where username in ()
0
 
LVL 3

Expert Comment

by:Kelevra
ID: 24805297
Hi,

Why don't you try using a record set to store your data (users) in it then use  a for each loop:

to display them.

Hope this help!
while ! RsUsers.EOF
 
for each x in RsUsers.Fields
'display logic here:
x.value
next
 
RsUsers.movenext
 
loop

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24805599
I just tried this but I get a page can't be displayed:

"SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ? AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in '"+AdminMultipleDate__MMColParam+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";


Kelevra, I would try that but I have no idea how to make that work.

Thanks.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24805618
in needs to be surrounded by ()
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 24805619
To elaborate on what I this means from the ASP side is this (I don't know how to do this [SQL is my bag baby] so perhaps someone else can step in...)
  • You need to parse your url/parameters to get a list of the usernames; This parsing must cope with:
    • 0 names "''" (that's a string containing two single quotes)
    • 1 name "'user1'" (that's single quote, username, single quote)
    • multiple names "'user1', 'user2', 'user3'" (that's single quote, username, single quote, comma, single quote, username...etc.)
  • Change your query to use the IN syntax for SQL server (http://msdn.microsoft.com/en-us/library/ms177682.aspx)
  • You pass this string into your sql query as a variable
  • Parse the results from SQL as required
This should mean that you have a single query which returns you all the info you want (efficient) with the minimum of code in your page (easy to maintain) - ideally you should put the query into a stored procedure but don't worry about that for now!
This is the approach I would advise with our web developers but you can obviously do it how you see fit.

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24806999
rg20, what exactly needs to be in (), I tried a few things but they didnt work.

mcmonap,
not sure what you mean, but the exact url I get using the GET is http://timeclock/TimeClock_PP_Admin_Multiple_Date.asp?username2=Angela&username2=Anthony&username2=Arlene&Month2=1&Day2=1&Year2=2009&Month3=8&Day3=1&Year3=2009&Submit_Mutiple=Submit
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807277
If I do this the page comes up but with no records:

"SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (?) AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";


PS - Because of the way that Dreamweaver handles var's ? & AdminMultipleDate__MMColParam have the same value which is the QueryString for UserName.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807402
The statement above still works for 1 username, just not when more than 1 are selected.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24807470
your where clause seems to contradict itself, you are selecting the username with a ? then with adminmulitpledate_mmcolparam.  Why 2 different

if those variables are the same you should be able to do something like

WHERE UserName in ('"+AdminMultipleDate__MMColParam+"')  AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1)
OR AND ' whichever works
(PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1)

ORDER BY date_val DESC, rnum";

I would personally remove all the date features, and just see if you can do a simple select * from punch where username in ().  print out the query and see if it is formed correctly, then integrate it.

If anyone has any other comments please feel free.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807511
Its definitely formatted correctly because it works exactly as its supposed to with 1 username. Its using 2 var's for the same thing because Dreamweaver uses the ? for the first statement but I cant use it a second time so I used the actual var name. If I change the ? to the var name than I have to remove all references to the ?, not worth all the work when it definitely works right.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24807568
agreed, but since you use the username in the where clause the first time, you don't need to do it again  

where username = (?) and (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1)
OR AND ' whichever works
(PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1)


you've already filtered on the username no need to do it twice in a where clause
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807589
I thought the same thing, but what it was doing was giving me all PunchIn records for the username & ALL PunchOut records. Once I added that param in there everything worked.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24807669
you could build your where clause as a separate string then concantinate

usernamestring = "username = (?)"

where "+usernamestring+" and (.....
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807714
True, I can work on fixing that but I'm sure this isn't the prob with the multiple usernames. I did a response.write(username) on the next page & it does show all of the names separated by a ",". It also gets an error if I add more that 4 or 5 usernames for some reason & its only 250 total characters, no where close to the limit.

0
 
LVL 9

Expert Comment

by:rg20
ID: 24807852
you need a response.write on before executing the SQL, then try running it in a console window.  If the code works for one and you get an error with many, odds are the SQL needs adjustment
can you show the SQL that is being generated if it doesn't work?
if the query works for multiple, do a view source and let see the error that comes at the bottom
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24807892
Sorry, dont mean to sound stupid but I'm not sure what you mean.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24807986
I would print out the SQL and execute it directly against the database and ensure that the query is returning the correct data.  Without that we can't fix the further problems.

If it is working, when you load the page, you can right click on the IE browser and "view source" and look for an asp error message in there
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808002
aaaah, now I gotcha. Let me try that.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808245
Totally works, this is the exact code that I used:

"SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('Amir', 'Jon', 'Imtiaz', 'Antenette', 'Grace', 'Erica', 'Arlene') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('Amir', 'Jon', 'Imtiaz', 'Antenette', 'Grace', 'Erica', 'Arlene') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";

When I do this it shows exactly what I need to to...but when I pass the usernames instead of statically assigning them it doesnt work. If I Response.Write the UserName parameter it shows exactly like this: AmyT, Angela, Arlene...but wont work for more than a few names at all.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808285
now what does view source show on the asp page where your trying to generate it?
before you execute the SQL, put a response.write statement so we can see exactly how it is different from what you put here.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808369
if it shows exactly like this, AmyT, Angela, Arlene  it appears your are missing quotes

you would need to do a concantination and replace

string = "'" + replace(usernamestring,",","','") + "'"

             single quote    replace , with ' , '              single quote
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808372

 
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css"> 
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        AmyT, Angela, Arlene
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          
            <tr>
              <td>Jon</td>
              <td>7/8/2009 12:25:09 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>WIN2008CTX                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Jon</td>
              <td>7/8/2009 9:48:55 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>WIN2008CTX                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Jon</td>
              <td>7/7/2009 10:22:24 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>WIN2008CTX                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Jon</td>
              <td></td>
              <td></td>
              <td>7/7/2009 7:31:25 PM</td>
              <td></td>
              <td>On Time</td>
              <td>WIN2008CTX                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Jon</td>
              <td>7/6/2009 10:06:44 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>Worked From Home</td>
              <td>NJ-JON                                            </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Jon</td>
              <td></td>
              <td></td>
              <td>7/6/2009 6:09:24 PM</td>
              <td></td>
              <td>On Time</td>
              <td>NJ-JON                                            </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>7/8/2009 8:41:00 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-IMTIAZ                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>7/7/2009 8:37:52 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-IMTIAZ                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>7/7/2009 5:12:11 PM</td>
              <td></td>
              <td>On Time</td>
              <td>NJ-IMTIAZ                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>7/6/2009 8:38:15 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>nj-imtiaz.amieelynn.local                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>7/6/2009 5:12:53 PM</td>
              <td></td>
              <td>On Time</td>
              <td>nj-imtiaz.amieelynn.local                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>7/2/2009 8:41:41 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>7/2/2009 3:28:40 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>7/1/2009 8:38:12 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>7/1/2009 5:28:29 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>6/30/2009 8:53:18 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>6/30/2009 5:40:40 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>6/29/2009 8:40:24 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>6/29/2009 5:23:02 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td>6/26/2009 8:36:37 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Imtiaz</td>
              <td></td>
              <td></td>
              <td>6/26/2009 5:28:29 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>7/8/2009 9:24:55 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-GRACEO                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>7/6/2009 9:00:23 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>nj-graceo.amieelynn.local                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td></td>
              <td></td>
              <td>7/6/2009 4:31:07 PM</td>
              <td></td>
              <td>On Time</td>
              <td>nj-graceo.amieelynn.local                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>7/2/2009 9:03:08 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td></td>
              <td></td>
              <td>7/2/2009 3:25:00 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>7/1/2009 9:13:57 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td></td>
              <td></td>
              <td>7/1/2009 5:26:19 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>6/30/2009 9:11:37 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td></td>
              <td></td>
              <td>6/30/2009 5:11:46 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td>6/26/2009 8:57:49 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Grace</td>
              <td></td>
              <td></td>
              <td>6/26/2009 4:52:59 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>7/8/2009 8:53:24 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>7/7/2009 8:52:43 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>7/7/2009 5:55:32 PM</td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>7/2/2009 8:50:12 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>7/2/2009 3:26:28 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>7/1/2009 8:49:10 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>7/1/2009 5:20:15 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>6/30/2009 8:34:38 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>6/30/2009 5:19:54 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>6/29/2009 9:18:40 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>Traffic</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>6/29/2009 5:19:22 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td>6/26/2009 9:03:31 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Erica</td>
              <td></td>
              <td></td>
              <td>6/26/2009 4:58:51 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>7/8/2009 8:39:58 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>WIN2008CTX                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>7/7/2009 9:08:26 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-ARLENEL                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>7/7/2009 5:42:41 PM</td>
              <td></td>
              <td>On Time</td>
              <td>NJ-ARLENEL                                        </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>7/6/2009 8:53:07 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>joeslapop.domain.local                            </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>7/6/2009 5:13:45 PM</td>
              <td></td>
              <td>On Time</td>
              <td>nj-confrm.amieelynn.local                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>7/2/2009 9:16:16 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>7/2/2009 3:26:49 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>7/1/2009 9:14:39 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>Traffic</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>7/1/2009 5:21:33 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>6/30/2009 9:01:47 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>6/30/2009 5:20:03 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>6/29/2009 9:04:16 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td>6/26/2009 9:02:47 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Arlene</td>
              <td></td>
              <td></td>
              <td>6/26/2009 4:56:11 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Antenette</td>
              <td>7/8/2009 8:52:54 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Antenette</td>
              <td>7/7/2009 8:53:52 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Antenette</td>
              <td></td>
              <td></td>
              <td>7/7/2009 5:11:21 PM</td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Antenette</td>
              <td>7/6/2009 8:55:00 AM</td>
              <td>7/6/2009 9:07:44 AM</td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>nj-antenette.amieelynn.local                      </td>
              <td>True</td>
            </tr>
            
            <tr>
              <td>Antenette</td>
              <td></td>
              <td></td>
              <td>7/6/2009 5:41:16 PM</td>
              <td></td>
              <td>On Time</td>
              <td>undefined                                         </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>7/8/2009 8:52:11 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-AMIRK                                          </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>7/7/2009 8:41:41 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>NJ-AMIRK                                          </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>7/7/2009 6:26:46 PM</td>
              <td></td>
              <td>On Time</td>
              <td>NJ-AMIRK                                          </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>7/6/2009 8:46:22 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td>nj-amirk.amieelynn.local                          </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>7/6/2009 5:29:55 PM</td>
              <td></td>
              <td>On Time</td>
              <td>NJ-AMIRK                                          </td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>7/2/2009 8:54:38 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>7/2/2009 3:35:42 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>7/1/2009 8:42:39 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>7/1/2009 5:57:14 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>6/30/2009 8:46:16 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>6/30/2009 6:03:39 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>6/29/2009 8:57:16 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td>6/26/2009 8:51:34 AM</td>
              <td></td>
              <td></td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
            <tr>
              <td>Amir</td>
              <td></td>
              <td></td>
              <td>6/26/2009 5:28:06 PM</td>
              <td></td>
              <td>On Time</td>
              <td></td>
              <td>False</td>
            </tr>
            
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808421
Very true, I do need the ' '. Ok if that is the case how would I do that?
If I do this: AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
I get: 'AmyT, Angela, Arlene'
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808482
AdminMultipleDate__MMColParam = "'" + replace(String(Request.QueryString("UserName2")), ", ", "','")  + "'";
                                                                                                             
                                                                                                                                             replace , with ' , '
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808516
But will that only work with 3 usernames because of the 3 ,'s?
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808532
why is that?  If I have a string that has 50 commas, it would do it for each one inside the string.  The ends would be handled with the code you currently have.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808550
This is not an individual command to replace each one

replace (string, what_to_replace, what_you_want)
replace (String(Request.QueryString("UserName2"))      ,              ","              ,         " ',' ")
                 in this string                                             replace        comma       with       ','
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808563
Ok, I'm just confused by the coma's.
So what you're saying is to make it like this?

AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
AdminMultipleDate__MMColParam = "'" + replace(String(Request.QueryString("UserName2")), ', ', ',')  + "'";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808588
didnt see ur last post.
So it should be:

AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
AdminMultipleDate__MMColParam = "'" + replace(String(Request.QueryString("UserName2")), ", ", "','")  + "'";
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808617
yes but you need to encapsulate each single quote in a double quote.

all were doing is finding each comma and replacing it with a single_quote_comma_single_quote
copy the statement as I have it here

AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808642
Totally understand now. Only question is, do I keep the

AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808675
I get this now:

Microsoft JScript runtime error '800a138f'

Object expected

/TimeClock_PP_Admin_Multiple_Date.asp, line 7

line 7 is AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808677
don't know if it will work on the question mark but heres a shot


SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ("'"+replace(?,",","','")+"'") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ("'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808721
I get this:

Microsoft JScript compilation error '800a03ec'

Expected ';'

/TimeClock_PP_Admin_Multiple_Date.asp, line 22

AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ("'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ("'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808739
sorry its pointing to the ' in the first "'"
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808777
my bad  the syntax might get me a few times

AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";

since were already in the string we don't need to close it yet
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808808
Trust me, I more than appreciate the help with this, it would take me more than a few times to get it, lol =)

I'm getting:

Microsoft JScript runtime error '800a138f'

Object expected

/TimeClock_PP_Admin_Multiple_Date.asp, line 22



0
 
LVL 9

Expert Comment

by:rg20
ID: 24808817
eliminate the username with the ? and see if it passes that point
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808827
Actually I did. I made it:

"SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808840
it works now??
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808849
Nope, I still get the error:

Microsoft JScript runtime error '800a138f'

Object expected

/TimeClock_PP_Admin_Multiple_Date.asp, line 22
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808864
above it assign the sql to a variable then response.write the variable
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808895
Not sure how to do that, & the page doesnt come up for me to even see the var.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808900
view source?  is this an external site?
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808908
Nope internal.
All I get when I view source is:

 <font face="Arial" size=2>
<p>Microsoft JScript runtime </font> <font face="Arial" size=2>error '800a138f'</font>
<p>
<font face="Arial" size=2>Object expected</font>
<p>
<font face="Arial" size=2>/TimeClock_PP_Admin_Multiple_Date.asp</font><font face="Arial" size=2>, line 22</font>
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808915
comment out the SQL and assign it to a variable above the commandtext function.  then response write it
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24808961
I cant get anything to come up when I comment out the SQL.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24808966
you should have something like this, then view source

teststring =  "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";

response.write teststring
response.end 'if you want
' AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809008
Ok, got it to come up, it shows 'AmyT, Angela, Arlene'
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809025
can I see the whole sql?  Is that the first one username or the second or both?
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809030
the asp version and the webpage rendition please
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809081

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
  AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
  
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
//<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING;
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
//%>
//<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        <%Response.Write(AdminMultipleDate__MMColParam)%>
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809096
View Source
 
//
//
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css"> 
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        'AmyT, Angela, Arlene'
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
           <font face="Arial" size=2>
<p>Microsoft JScript runtime </font> <font face="Arial" size=2>error '800a138f'</font>
<p>
<font face="Arial" size=2>Object expected</font>
<p>
<font face="Arial" size=2>/TimeClock_PP_Admin_Multiple_Date.asp</font><font face="Arial" size=2>, line 117</font> 

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24809134
Replace it with this
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
  AdminMultipleDate__MMColParam = "'" + String(Request.QueryString("UserName2")) + "'";
  
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
//<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING;
SQL = AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
 
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
//%>
//<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        <%Response.Write(SQL)%>
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24809166
show me the SQL that comes from the web page
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809192
Microsoft JScript runtime error '800a138f'

Object expected

/TimeClock_PP_Admin_Multiple_Date.asp, line 24
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809197
What do you mean by the sql that comes from the web page?
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809224
Line 24 has a syntax error, when you put this in place and run the script,

so we assign the query to a SQL statement
SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";

where you had
 response.write ("AdminMultipleDate__MMColParam")
was not showing properly because we don't have a replace() function in there and even if we did the SQL is not using that variable

I changed it to
response.write(SQL).  

This will tell us exactly what the server is seeing.  

Take the query and run it in the console or command line whatever it is to see if it works.

I will check this later in the evening please let me know
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809527
What did you come up with??
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809597
you still on ?
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809835
Sorry, was on my way home. I'm having trouble running that in SQL command because of the var's. Nothing is defined because its being passed. Not sure how to do it that way.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809861
I also posted this question: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24555119.html

I think once we get this to work everything will work correctly because if I type it exactly like that instead of using the param it works.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24809943
It will.  Ok heres what were going to do.

Line 24 has a syntax error, when you put this in place and run the script,

so we assign the query to a SQL statement
SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";

where you had
 response.write ("AdminMultipleDate__MMColParam")
was not showing properly because we don't have a replace() function in there and even if we did the SQL is not using that variable

I changed it to
response.write(SQL).  

This will tell us exactly what the server is seeing.  

PUt only the SQL that shows up on the asp page
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24809964
Gotcha. Page can't be displayed. Do I have everything correct below?
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
  AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
%>
<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = '"+Test+"' AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+Test+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
%>
<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        <%Response.Write(SQL)%>
  
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24810023
If your going to set the parameter like this

 AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";

then use
 AdminMultipleDate__MMColParam in the where clause for the list of your users


SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810030
I noticed that & took it out but still have the same prob
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810034
What does the SQL say
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810040
also just use Request.QueryString("UserName2") not string(Request.QueryString("UserName2")
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810046
Having trouble even just cutting it down to get the page to run...


var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (String(Request.QueryString("UserName2")))  AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (String(Request.QueryString("UserName2"))) AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810054
I need to see the output from the sql script not the asp side.  I need to examine what is happening when we make changes
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810061
and your still using the request("username2") not what I told you, did you save your changes?
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810069
var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810090
Sorry, I'm getting totally confused here with all of the changes. 2 comments above it says: also just use Request.QueryString("UserName2") not string(Request.QueryString("UserName2") so I changed it.

Is this the way you want it?

SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('" +replace (Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (Request.QueryString("UserName2")),"," , "','")  + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810094
didnt see ur last comment. Trying it now.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810122
no forget all the above

change this line

AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";

to
AdminMultipleDate__MMColParam = "'" +replace (Request.QueryString("UserName2"),"," , "','")  + "'";

change query to

var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810141
Still get Page Cant be Displayed with this code:
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
	AdminMultipleDate__MMColParam = "'" +replace (Request.QueryString("UserName2"),"," , "','")  + "'";
 
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
%>
<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = '"+Test+"' AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+Test+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
%>
<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        <%Response.Write(SQL)%>
  
 
		<table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24810152
excellent we got it right, now can you view source and tell me the SQL
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810198
This is what I get when I view source:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
 
    <head>
        <link rel="stylesheet" type="text/css" href="ErrorPageTemplate.css" >
 
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 
        <title>HTTP 500 Internal Server Error</title>
 
        <script src="errorPageStrings.js" language="javascript" type="text/javascript">
        </script>
        <script src="httpErrorPagesScripts.js" language="javascript" type="text/javascript">
        </script>
    </head>
 
    <body onLoad="javascript:expandCollapse('infoBlockID', true); initGoBack(); navCancelInit(); initMoreInfo('infoBlockID');">
 
        <table width="730" cellpadding="0" cellspacing="0" border="0">
 
        <!-- Error title -->
            <tr>
                <td id="infoIconAlign" width="60" align="left" valign="top" rowspan="2">
                    <img src="info_48.png" id="infoIcon" alt="Info icon">
                </td>
                <td id="mainTitleAlign" valign="middle" align="left" width="*">
                    <h1 id="mainTitle">The website cannot display the page</h1>
                </td>
            </tr>
 
 
 
            <tr>
                <!-- This row is for HTTP status code, as well as the divider-->
                <td id="http500Align" class="errorCodeAndDivider" align="right"><ID id="http500">&nbsp;HTTP 500</ID>
                    <div class="divider"></div>
                </td>
            </tr>
 
 
        <!-- Error Body -->
            <tr>
                <td>
                    &nbsp;
                </td>
                <td id="likelyCausesAlign" valign="top" align="left">
                    <h3 id="likelyCauses">Most likely causes:</h3>
                    <ul>
                        <li id="causeSiteMaintenance">The website is under maintenance.</li>
                        <li id="causeSiteError">The website has a programming error.</li>
                    </ul>
                </td>
            </tr>
 
        <!-- What you can do -->
            <tr>
                <td>
                    &nbsp;
                </td>
                <td id="whatToTryAlign" valign="top" align="left">
                    <h2 id="whatToTry">What you can try:</h2>
                </td>
            </tr>
 
        <!-- refresh page -->
            <tr>
                <td>
                    &nbsp;
                </td>
                <td id="refreshPageAlign" align="left" valign="middle">
                    <h4>
                        <table>
                          <tr>
                              <td valign="top">
                                  <img src="bullet.png" border="0" alt="" class="actionIcon">
                              </td>
                              <td valign="top">
                                  <span id="navCancelContainer"></span><noscript id="refreshPage">Refresh the page.</noscript>
                              </td>
                          </tr>
                        </table>
                    </h4>
                </td>
            </tr>
 
 
        <!-- back to previous page -->
            <tr>
                <td >
                    &nbsp;
                </td>
                <td id="goBackAlign" align="left" valign="middle">
                    <h4>
                        <table>
                          <tr>
                              <td valign="top">
                                  <img src="bullet.png" border="0" alt="" class="actionIcon">
                              </td>
                              <td valign="top">
                                  <span id="goBackContainer"></span><noscript id="goBack">Go back to the previous page.</noscript>
                              </td>
                          </tr>
                        </table>
                    </h4>
                </td>
            </tr>
 
 
        <!-- InfoBlock -->
            <tr>
                <td id="infoBlockAlign" align="right" valign="top">
                    &nbsp;
                </td>
                <td id="moreInfoAlign" align="left" valign="center">
                    <h4>
                        <table>
                            <tr>
                                <td valign="top">
                                    <a href="#" onclick="javascript:expandCollapse('infoBlockID', true); return false;"><img src="down.png" id="infoBlockIDImage" border="0" class="actionIcon" alt="More information"></a>
                                </td>
                                <td valign="top">
                                    <span id="moreInfoContainer"></span>
                                    <noscript><ID id="moreInformation">More information</ID></noscript>
                                </td>
                            </tr>
                        </table>
                    </h4>
 
                    <div id="infoBlockID" class="infoBlock">
                        <p id="errorExplanation">This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented  the webpage from displaying.</p>
                        <p id="moreInfoSeeHelp">For more information about HTTP errors, see Help.</p>
                    </div>
 
                </td>
            </tr>
 
 
        </table>
    </body>
</html>

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24810207
nice error page, ok now just after the SQL command put

response.write(SQL)
response.end
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810210
you don't have to go through all the selection processes, you just need to make the changes and refresh most of the time
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810213
Yeah, it didn't look like much help there, lol.

Same deal when I added those.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810226
you moved them to the top just after we create the SQL statment right?

If not do that, if you did then add a
response.write to the line after you put the replace function and a response.end after that.

if this is a dev server, I would change all the default pages to default not custom, that would help a bit
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810242
Yep, I moved them to right under the SQL. Here is the code I'm using. This isn't a dev server.
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
	AdminMultipleDate__MMColParam = "'" +replace (Request.QueryString("UserName2"),"," , "','")  + "'";
 
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColParam  + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
response.write(SQL)
response.end
 
%>
 
<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = '"+Test+"' AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+Test+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
%>
<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>
        
	    <table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 9

Expert Comment

by:rg20
ID: 24810254
line 8 add
response.write(AdminMultipleDate__MMColPara)
response.end

if that fails put response.end before the replace( AdminMultipleDate__MMColPara function, just to isolate the problem
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810277
Adding the 2 lines to line 8 didnt work. Did you mean this with the last suggestion?

<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") {
response.end
      AdminMultipleDate__MMColParam = "'" +replace (Request.QueryString("UserName2"),"," , "','")  + "'";
      response.write(AdminMultipleDate__MMColParam)
response.end
      

}
%>
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810286
ok you added response.end before the line which shows that the system to that point is working as no error page was displayed.  

Next remove the response.end before the replace function see if you get a page error
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810308
Nope, just the page not displayed again.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810317
change the
AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";
back to what you see above
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810336
I will change that but I did get it to display while screwing with it just now. This shows on the page:

SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('Angela, Antenette, Anthony') AND (PunchInTime BETWEEN '1/1/2009' AND Cast('8/1/2009' As datetime) + 1) OR UserName in ('Angela, Antenette, Anthony') AND (PunchOutTime BETWEEN '1/1/2009' AND Cast('8/1/2009' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum

With the following code:
So now if we play with the AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2")); to tweak it it should work.
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") { 
	AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2"));
 
}
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
 
%>
 
<%
//var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
//AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
//AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName = '"+Test+"' AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName = '"+Test+"' AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
//AdminMultipleDate_cmd.Prepared = true;
//AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
//var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
//var AdminMultipleDate_numRows = 0;
%>
<%
//var Repeat1__numRows = -1;
//var Repeat1__index = 0;
//AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>        
	    <%
		Response.Write(SQL)
		%>
        <table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810338
Changing that line made the page not display again.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810360
yes that is the only line we play with now

so you changed it from
AdminMultipleDate__MMColParam = "'" +replace (String(Request.QueryString("UserName2")),"," , "','")  + "'";
to

AdminMultipleDate__MMColParam =  String(Request.QueryString("UserName2"))

now add the replace function only

AdminMultipleDate__MMColParam =  replace(String(Request.QueryString("UserName2")),"," , "','")

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810374
Page not displayed.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810433
nothing in the view source?
or an error page?
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810445
Exact same as before with the view source. The error is a generic Page not Found.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810496
ok thats good we have a identifiable problem,
set

test =  'Angela, Antenette, Anthony'
AdminMultipleDate__MMColParam =replace(test,"," , "','")

 
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810501
Feels so close, lol.

Same thing with that one, Page not displayed.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810503
ok thats wrong let me research a bit more
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810512
Cool thanks. I'm doing the same on Google right now.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810531
try this,

temp = split(String(Request.QueryString("UserName2"))",")
then
AdminMultipleDate__MMColParam =  join(temp,"','")

time to game the system :)
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810553
Haha, the game has been kicking my ass all day =)

Tried this but got the same, page not displayed:

<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") {
var temp = split(String(Request.QueryString("UserName2"))",");
AdminMultipleDate__MMColParam =  join(temp,"','");
}
%>
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810561
comment out the join and see if you get anything different
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810567
Same.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810571
put this before the if statement
response.write(IsArray(Request.QueryString("UserName2")))

response.end

If the answer comes back true then
add
AdminMultipleDate__MMColParam =  join(temp,"','")

if it comes back false
add

AdminMultipleDate__MMColParam =  split(temp,",")
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810586
page not displayed again.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810590
page source?  I am looking to see is request.querystring("username2") is an array
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810599
Same source as before, totally generic
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810615
as the first lines on the page


response.write(IsArray(Request.QueryString("UserName2")))

response.end

what does view source say?

page should not crash
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810629
Still crashes.
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810636
try this conversion to string
response.write(IsArray(cStr(Request.QueryString("UserName2"))))
response.end

also copy and paste your url for me.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810640
I'm pretty sure that its not an array. Wouldnt I need to make it an array?
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810646
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810653
well I'm doing some research as well and Im not convinced that dreamweaver passes url parameters as strings hence the errors when we try to parse it.

your String() function around the request item is a curious thing to me, I am not sure that it does the job either.  I am sure that cStr does because I had to do it to make C# and asp classic talk to each other.

I would expect the url to contain some single quotes around the string objects, but not sure
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810662
Not sure, I'm really fumbling my way thru this. I have only created extremely basic pages before so this is totally new to me.
0
 
LVL 9

Accepted Solution

by:
rg20 earned 500 total points
ID: 24810676
remove this
response.write(IsArray(cStr(Request.QueryString("UserName2"))))
response.end

get back to where we were

<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") {
var temp = split(String(Request.QueryString("UserName2"))",");
AdminMultipleDate__MMColParam =  join(temp,"','");
}
%>
I seen a typo from before so try this again.

if not try this
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" && 
    String(Request.QueryString("UserName2")) != "") {
    AdminMultipleDate__MMColParam = replace(cStr(Request.QueryString("UserName2")),",","','");
}
%>
0
 
LVL 9

Expert Comment

by:rg20
ID: 24810677
If I had IIS set up on this machine we would have been done hours ago :(
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810693
Tried both & they both get page not displayed.

Luckily I have access to my work server from here because I dont have it installed either, lol.
0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810867
This is the code that worked. Thanks again Roger!!!!
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" &&
   String(Request.QueryString("UserName2")) != "") { 
   AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2")).replace(/, /g , "','"); 
   
   }
%>
 <%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
 <%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
//var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
 
%>
 
<%
var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName ASC, date_val DESC, rnum";
AdminMultipleDate_cmd.Prepared = true;
AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
 
var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
var AdminMultipleDate_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!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"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
  <tr bgcolor="#26354A">
	<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
	<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="22" nowrap="nowrap">&nbsp;</td>
	<td colspan="3" height="24">
	<table border="0" cellpadding="0" cellspacing="0" id="navigation">
        <tr>
          <td align="center" nowrap="nowrap" class="navText"><a href="TimeClock.asp">TIME CLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.html">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeClock_Admin.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="2" valign="top" bgcolor="#26354A"><div align="center"><img src="Images/FrontDoor.JPG" alt="Amiee Lynn Photo" width="339" height="288" border="0" /><br />
	  <table border="0" cellspacing="0" cellpadding="0" width="230">
	    <tr>
	      <td width="230" class="sidebarText" id="padding"><br />
	        
	        &nbsp;<br />		</td>
	      </tr>
        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="558" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p>        
	    <%
		%>
        <table border="2" cellpadding="2" cellspacing="2">
          <tr>
            <td>User Name</td>
            <td>Punch In Time</td>
            <td>Punch In Changed From</td>
            <td>Punch Out Time</td>
            <td>Punch Out Changed From</td>
            <td>Reason Late / Left Early</td>
            <td>Punched From Computer</td>
            <td>Changed Record</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!AdminMultipleDate.EOF)) { %>
            <tr>
              <td><%=(AdminMultipleDate.Fields.Item("UserName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchInTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchInFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("PunchOutTime").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedPunchOutFrom").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("Reason").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ComputerName").Value)%></td>
              <td><%=(AdminMultipleDate.Fields.Item("ChangedRecord").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  AdminMultipleDate.MoveNext();
}
%>
        </table>
        <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <p>&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="22">&nbsp;</td>
	<td width="317">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="558">&nbsp;</td>
	<td width="4">&nbsp;</td>
	<td width="675">&nbsp;</td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
AdminMultipleDate.Close();
%>

Open in new window

0
 
LVL 3

Author Comment

by:Jon DeVito
ID: 24810870
Sorry about that. I posted the resolution for everyone to see.
0
 
LVL 3

Author Closing Comment

by:Jon DeVito
ID: 31600890
Thanks again!!!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24813788
No problem and I appreciate you doing that.  This helps others in future who may have a similar problem.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question