Solved

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

Posted on 2009-07-07
135
605 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 15

Expert Comment

by:mcmonap
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
<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
Comment Utility
I'm well within the limit. I'm only looking to do it for a few usernames.
0
 
LVL 9

Expert Comment

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

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
in needs to be surrounded by ()
0
 
LVL 15

Expert Comment

by:mcmonap
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The statement above still works for 1 username, just not when more than 1 are selected.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, dont mean to sound stupid but I'm not sure what you mean.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
aaaah, now I gotcha. Let me try that.
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

 

<!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
Comment Utility
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
Comment Utility
AdminMultipleDate__MMColParam = "'" + replace(String(Request.QueryString("UserName2")), ", ", "','")  + "'";
                                                                                                             
                                                                                                                                             replace , with ' , '
0
 
LVL 3

Author Comment

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

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Totally understand now. Only question is, do I keep the

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

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sorry its pointing to the ' in the first "'"
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
eliminate the username with the ? and see if it passes that point
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
it works now??
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
above it assign the sql to a variable then response.write the variable
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
view source?  is this an external site?
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
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
Comment Utility
I cant get anything to come up when I comment out the SQL.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
Ok, got it to come up, it shows 'AmyT, Angela, Arlene'
0
 
LVL 9

Expert Comment

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

Expert Comment

by:rg20
Comment Utility
the asp version and the webpage rendition please
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility

<%@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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
show me the SQL that comes from the web page
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
Microsoft JScript runtime error '800a138f'

Object expected

/TimeClock_PP_Admin_Multiple_Date.asp, line 24
0
 
LVL 3

Author Comment

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

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
What did you come up with??
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
you still on ?
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I noticed that & took it out but still have the same prob
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
What does the SQL say
0
 
LVL 9

Expert Comment

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

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
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
Comment Utility
and your still using the request("username2") not what I told you, did you save your changes?
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
didnt see ur last comment. Trying it now.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
excellent we got it right, now can you view source and tell me the SQL
0
 
LVL 3

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
nice error page, ok now just after the SQL command put

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

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
Yeah, it didn't look like much help there, lol.

Same deal when I added those.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Nope, just the page not displayed again.
0
 
LVL 9

Expert Comment

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

Author Comment

by:Jon DeVito
Comment Utility
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
Comment Utility
Changing that line made the page not display again.
0
 
LVL 9

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
Page not displayed.
0
 
LVL 9

Expert Comment

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

Author Comment

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

Expert Comment

by:rg20
Comment Utility
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
Comment Utility
Feels so close, lol.

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

Expert Comment

by:rg20
Comment Utility