Jon DeVito
asked on
SQL query on ASP page to show records for multiple users with GET
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
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
ASKER
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();
%>
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
Your query text look like the snippet below - queries are:
- what goes in place of "?"
- what goes in place of "AdminMultipleDate__MMColP
aram" - should "UserName = '"+AdminMultipleDate__MMCo
lParam+"'" be "UserName IN '"+AdminMultipleDate__MMCo lParam+"'"
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
ASKER
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.
Thanks.
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
(Change to your own recordset)
<%While Not objRs.EOF%>
PUT YOUR CODE HERE FOR THE USERS INFORMATION
<%
objRs.MoveNext
Wend
%>
Carrzkiss
Why would you want to loop that query? The database could return all the data in one go if you asked it to.
>>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
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
ASKER
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.
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"> </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();
%>
------------ 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"> </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();
%>
OK.
Do you have multple Fields for all the different Usernames that you are wanting to search for?
Do you have multple Fields for all the different Usernames that you are wanting to search for?
ASKER
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.
<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
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
ASKER
I'm well within the limit. I'm only looking to do it for a few usernames.
mcmonap had it right
WHERE UserName = ? being multiple parameters needs to be where username in
WHERE UserName = ? being multiple parameters needs to be where username in
mcmonap had it right
your statement for one username would be where username =
but for multiple usernames it would be where username in ()
your statement for one username would be where username =
but for multiple usernames it would be where username in ()
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!
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
ASKER
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__MMCo lParam+"' 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.
"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__MMCo
Kelevra, I would try that but I have no idea how to make that work.
Thanks.
in needs to be surrounded by ()
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...)
This is the approach I would advise with our web developers but you can obviously do it how you see fit.
- 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/libr ary/ms1776 82.aspx) - You pass this string into your sql query as a variable
- Parse the results from SQL as required
This is the approach I would advise with our web developers but you can obviously do it how you see fit.
ASKER
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
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
ASKER
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__MMC olParam+"' ) 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__MMColPa ram have the same value which is the QueryString for UserName.
"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__MMC
PS - Because of the way that Dreamweaver handles var's ? & AdminMultipleDate__MMColPa
ASKER
The statement above still works for 1 username, just not when more than 1 are selected.
your where clause seems to contradict itself, you are selecting the username with a ? then with adminmulitpledate_mmcolpar am. Why 2 different
if those variables are the same you should be able to do something like
WHERE UserName in ('"+AdminMultipleDate__MMC olParam+"' ) 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.
if those variables are the same you should be able to do something like
WHERE UserName in ('"+AdminMultipleDate__MMC
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.
ASKER
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.
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
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
ASKER
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.
you could build your where clause as a separate string then concantinate
usernamestring = "username = (?)"
where "+usernamestring+" and (.....
usernamestring = "username = (?)"
where "+usernamestring+" and (.....
ASKER
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.
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
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
ASKER
Sorry, dont mean to sound stupid but I'm not sure what you mean.
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
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
ASKER
aaaah, now I gotcha. Let me try that.
ASKER
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.
"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.
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.
before you execute the SQL, put a response.write statement so we can see exactly how it is different from what you put here.
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
you would need to do a concantination and replace
string = "'" + replace(usernamestring,","
single quote replace , with ' , ' single quote
ASKER
<!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="TimeClock_Admin.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>
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> </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>
ASKER
Very true, I do need the ' '. Ok if that is the case how would I do that?
If I do this: AdminMultipleDate__MMColPa ram = "'" + String(Request.QueryString ("UserName 2")) + "'";
I get: 'AmyT, Angela, Arlene'
If I do this: AdminMultipleDate__MMColPa
I get: 'AmyT, Angela, Arlene'
AdminMultipleDate__MMColPa ram = "'" + replace(String(Request.Que ryString(" UserName2" )), ", ", "','") + "'";
replace , with ' , '
replace , with ' , '
ASKER
But will that only work with 3 usernames because of the 3 ,'s?
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.
This is not an individual command to replace each one
replace (string, what_to_replace, what_you_want)
replace (String(Request.QueryStrin g("UserNam e2")) , "," , " ',' ")
in this string replace comma with ','
replace (string, what_to_replace, what_you_want)
replace (String(Request.QueryStrin
in this string replace comma with ','
ASKER
Ok, I'm just confused by the coma's.
So what you're saying is to make it like this?
AdminMultipleDate__MMColPa ram = "'" + String(Request.QueryString ("UserName 2")) + "'";
AdminMultipleDate__MMColPa ram = "'" + replace(String(Request.Que ryString(" UserName2" )), ', ', ',') + "'";
So what you're saying is to make it like this?
AdminMultipleDate__MMColPa
AdminMultipleDate__MMColPa
ASKER
didnt see ur last post.
So it should be:
AdminMultipleDate__MMColPa ram = "'" + String(Request.QueryString ("UserName 2")) + "'";
AdminMultipleDate__MMColPa ram = "'" + replace(String(Request.Que ryString(" UserName2" )), ", ", "','") + "'";
So it should be:
AdminMultipleDate__MMColPa
AdminMultipleDate__MMColPa
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__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
all were doing is finding each comma and replacing it with a single_quote_comma_single_
copy the statement as I have it here
AdminMultipleDate__MMColPa
ASKER
Totally understand now. Only question is, do I keep the
AdminMultipleDate__MMColPa ram = "'" + String(Request.QueryString ("UserName 2")) + "'";
AdminMultipleDate__MMColPa
ASKER
I get this now:
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip le_Date.as p, line 7
line 7 is AdminMultipleDate__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip
line 7 is AdminMultipleDate__MMColPa
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.QueryStrin g("UserNam e2")),"," , "','") + "'") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY date_val DESC, rnum";
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(?,",","','")+
ASKER
I get this:
Microsoft JScript compilation error '800a03ec'
Expected ';'
/TimeClock_PP_Admin_Multip le_Date.as p, line 22
AdminMultipleDate_cmd.Comm andText = "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.QueryStrin g("UserNam e2")),"," , "','") + "'") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ("'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----^
Microsoft JScript compilation error '800a03ec'
Expected ';'
/TimeClock_PP_Admin_Multip
AdminMultipleDate_cmd.Comm
--------------------------
ASKER
sorry its pointing to the ' in the first "'"
my bad the syntax might get me a few times
AdminMultipleDate_cmd.Comm andText = "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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') 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
AdminMultipleDate_cmd.Comm
since were already in the string we don't need to close it yet
ASKER
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_Multip le_Date.as p, line 22
I'm getting:
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip
eliminate the username with the ? and see if it passes that point
ASKER
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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
"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.QueryStrin
it works now??
ASKER
Nope, I still get the error:
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip le_Date.as p, line 22
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip
above it assign the sql to a variable then response.write the variable
ASKER
Not sure how to do that, & the page doesnt come up for me to even see the var.
view source? is this an external site?
ASKER
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>
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
comment out the SQL and assign it to a variable above the commandtext function. then response write it
ASKER
I cant get anything to come up when I comment out the SQL.
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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') 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.Comm andText = "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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
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.QueryStrin
response.write teststring
response.end 'if you want
' AdminMultipleDate_cmd.Comm
ASKER
Ok, got it to come up, it shows 'AmyT, Angela, Arlene'
can I see the whole sql? Is that the first one username or the second or both?
the asp version and the webpage rendition please
ASKER
<%@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"> </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="TimeClock_Admin.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>
<%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> </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();
%>
ASKER
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"> </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="TimeClock_Admin.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>
'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>
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"> </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="TimeClock_Admin.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>
<%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> </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();
%>
show me the SQL that comes from the web page
ASKER
Microsoft JScript runtime error '800a138f'
Object expected
/TimeClock_PP_Admin_Multip le_Date.as p, line 24
Object expected
/TimeClock_PP_Admin_Multip
ASKER
What do you mean by the sql that comes from the web page?
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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') 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__MMCol Param")
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
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.QueryStrin
where you had
response.write ("AdminMultipleDate__MMCol
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
What did you come up with??
you still on ?
ASKER
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.
ASKER
I also posted this question: https://www.experts-exchange.com/questions/24555119/ASP-Replace-function-syntax.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.
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.
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.QueryStrin g("UserNam e2")),"," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "') 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__MMCol Param")
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
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.QueryStrin
where you had
response.write ("AdminMultipleDate__MMCol
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
ASKER
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"> </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="TimeClock_Admin.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>
<%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> </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();
%>
If your going to set the parameter like this
AdminMultipleDate__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
then use
AdminMultipleDate__MMColPa ram 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__MMColPa ram + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColPa ram + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
AdminMultipleDate__MMColPa
then use
AdminMultipleDate__MMColPa
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__MMColPa
ASKER
I noticed that & took it out but still have the same prob
What does the SQL say
also just use Request.QueryString("UserN ame2") not string(Request.QueryString ("UserName 2")
ASKER
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.QueryStrin g("UserNam e2"))) AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (String(Request.QueryStrin g("UserNam e2"))) AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
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.QueryStrin
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
and your still using the request("username2") not what I told you, did you save your changes?
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__MMColPa ram + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColPa ram + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
ASKER
Sorry, I'm getting totally confused here with all of the changes. 2 comments above it says: also just use Request.QueryString("UserN ame2") not string(Request.QueryString ("UserName 2") 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("User Name2"))," ," , "','") + "') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('" +replace (Request.QueryString("User Name2"))," ," , "','") + "') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
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("User
ASKER
didnt see ur last comment. Trying it now.
no forget all the above
change this line
AdminMultipleDate__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
to
AdminMultipleDate__MMColPa ram = "'" +replace (Request.QueryString("User Name2"),", " , "','") + "'";
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__MMColPa ram + ") AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in (" + AdminMultipleDate__MMColPa ram + ") AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
change this line
AdminMultipleDate__MMColPa
to
AdminMultipleDate__MMColPa
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__MMColPa
ASKER
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"> </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="TimeClock_Admin.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>
<%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> </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();
%>
excellent we got it right, now can you view source and tell me the SQL
ASKER
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"> HTTP 500</ID>
<div class="divider"></div>
</td>
</tr>
<!-- Error Body -->
<tr>
<td>
</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>
</td>
<td id="whatToTryAlign" valign="top" align="left">
<h2 id="whatToTry">What you can try:</h2>
</td>
</tr>
<!-- refresh page -->
<tr>
<td>
</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 >
</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">
</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>
nice error page, ok now just after the SQL command put
response.write(SQL)
response.end
response.write(SQL)
response.end
you don't have to go through all the selection processes, you just need to make the changes and refresh most of the time
ASKER
Yeah, it didn't look like much help there, lol.
Same deal when I added those.
Same deal when I added those.
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
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
ASKER
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"> </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="TimeClock_Admin.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();
%>
line 8 add
response.write(AdminMultip leDate__MM ColPara)
response.end
if that fails put response.end before the replace( AdminMultipleDate__MMColPa ra function, just to isolate the problem
response.write(AdminMultip
response.end
if that fails put response.end before the replace( AdminMultipleDate__MMColPa
ASKER
Adding the 2 lines to line 8 didnt work. Did you mean this with the last suggestion?
<%
var AdminMultipleDate__MMColPa ram = "1";
if (String(Request.QueryStrin g("UserNam e2")) != "undefined" &&
String(Request.QueryString ("UserName 2")) != "") {
response.end
AdminMultipleDate__MMColPa ram = "'" +replace (Request.QueryString("User Name2"),", " , "','") + "'";
response.write(AdminMultip leDate__MM ColParam)
response.end
}
%>
<%
var AdminMultipleDate__MMColPa
if (String(Request.QueryStrin
String(Request.QueryString
response.end
AdminMultipleDate__MMColPa
response.write(AdminMultip
response.end
}
%>
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
Next remove the response.end before the replace function see if you get a page error
ASKER
Nope, just the page not displayed again.
change the
AdminMultipleDate__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
back to what you see above
AdminMultipleDate__MMColPa
back to what you see above
ASKER
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__MMColPa ram = String(Request.QueryString ("UserName 2")); to tweak it it should work.
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__MMColPa
<%@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"> </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="TimeClock_Admin.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>
<%
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> </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();
%>
ASKER
Changing that line made the page not display again.
yes that is the only line we play with now
so you changed it from
AdminMultipleDate__MMColPa ram = "'" +replace (String(Request.QueryStrin g("UserNam e2")),"," , "','") + "'";
to
AdminMultipleDate__MMColPa ram = String(Request.QueryString ("UserName 2"))
now add the replace function only
AdminMultipleDate__MMColPa ram = replace(String(Request.Que ryString(" UserName2" )),"," , "','")
so you changed it from
AdminMultipleDate__MMColPa
to
AdminMultipleDate__MMColPa
now add the replace function only
AdminMultipleDate__MMColPa
ASKER
Page not displayed.
nothing in the view source?
or an error page?
or an error page?
ASKER
Exact same as before with the view source. The error is a generic Page not Found.
ok thats good we have a identifiable problem,
set
test = 'Angela, Antenette, Anthony'
AdminMultipleDate__MMColPa ram =replace(test,"," , "','")
set
test = 'Angela, Antenette, Anthony'
AdminMultipleDate__MMColPa
ASKER
Feels so close, lol.
Same thing with that one, Page not displayed.
Same thing with that one, Page not displayed.
ok thats wrong let me research a bit more
ASKER
Cool thanks. I'm doing the same on Google right now.
try this,
temp = split(String(Request.Query String("Us erName2")) ",")
then
AdminMultipleDate__MMColPa ram = join(temp,"','")
time to game the system :)
temp = split(String(Request.Query
then
AdminMultipleDate__MMColPa
time to game the system :)
ASKER
Haha, the game has been kicking my ass all day =)
Tried this but got the same, page not displayed:
<%
var AdminMultipleDate__MMColPa ram = "1";
if (String(Request.QueryStrin g("UserNam e2")) != "undefined" &&
String(Request.QueryString ("UserName 2")) != "") {
var temp = split(String(Request.Query String("Us erName2")) ",");
AdminMultipleDate__MMColPa ram = join(temp,"','");
}
%>
Tried this but got the same, page not displayed:
<%
var AdminMultipleDate__MMColPa
if (String(Request.QueryStrin
String(Request.QueryString
var temp = split(String(Request.Query
AdminMultipleDate__MMColPa
}
%>
comment out the join and see if you get anything different
ASKER
Same.
put this before the if statement
response.write(IsArray(Req uest.Query String("Us erName2")) )
response.end
If the answer comes back true then
add
AdminMultipleDate__MMColPa ram = join(temp,"','")
if it comes back false
add
AdminMultipleDate__MMColPa ram = split(temp,",")
response.write(IsArray(Req
response.end
If the answer comes back true then
add
AdminMultipleDate__MMColPa
if it comes back false
add
AdminMultipleDate__MMColPa
ASKER
page not displayed again.
page source? I am looking to see is request.querystring("usern ame2") is an array
ASKER
Same source as before, totally generic
as the first lines on the page
response.write(IsArray(Req uest.Query String("Us erName2")) )
response.end
what does view source say?
page should not crash
response.write(IsArray(Req
response.end
what does view source say?
page should not crash
ASKER
Still crashes.
try this conversion to string
response.write(IsArray(cSt r(Request. QueryStrin g("UserNam e2"))))
response.end
also copy and paste your url for me.
response.write(IsArray(cSt
response.end
also copy and paste your url for me.
ASKER
I'm pretty sure that its not an array. Wouldnt I need to make it an array?
well I'm doing some research as well and Im not convinced that dreamweaver passes url parameters as strings hence the errors when we try to parse it.
your String() function around the request item is a curious thing to me, I am not sure that it does the job either. I am sure that cStr does because I had to do it to make C# and asp classic talk to each other.
I would expect the url to contain some single quotes around the string objects, but not sure
your String() function around the request item is a curious thing to me, I am not sure that it does the job either. I am sure that cStr does because I had to do it to make C# and asp classic talk to each other.
I would expect the url to contain some single quotes around the string objects, but not sure
ASKER
Not sure, I'm really fumbling my way thru this. I have only created extremely basic pages before so this is totally new to me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If I had IIS set up on this machine we would have been done hours ago :(
ASKER
Tried both & they both get page not displayed.
Luckily I have access to my work server from here because I dont have it installed either, lol.
Luckily I have access to my work server from here because I dont have it installed either, lol.
ASKER
This is the code that worked. Thanks again Roger!!!!
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var AdminMultipleDate__MMColParam = "1";
if (String(Request.QueryString("UserName2")) != "undefined" &&
String(Request.QueryString("UserName2")) != "") {
AdminMultipleDate__MMColParam = String(Request.QueryString("UserName2")).replace(/, /g , "','");
}
%>
<%
var Date_From = "1";
Date_From = String(Request.QueryString("Month2")) + "/" + String(Request.QueryString("Day2")) + "/" + String(Request.QueryString("Year2"));
%>
<%
var Date_To = "1";
Date_To = String(Request.QueryString("Month3")) + "/" + String(Request.QueryString("Day3")) + "/" + String(Request.QueryString("Year3"));
%>
<%
//var SQL = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName DESC, date_val DESC, rnum";
%>
<%
var AdminMultipleDate_cmd = Server.CreateObject ("ADODB.Command");
AdminMultipleDate_cmd.ActiveConnection = MM_TimeClock_STRING
AdminMultipleDate_cmd.CommandText = "SELECT UserName, PunchInTime, ChangedPunchInFrom, PunchOutTime, ChangedPunchOutFrom, Reason, ComputerName, ChangedRecord FROM ( SELECT *,ROW_NUMBER() OVER ( ORDER BY ISNULL(punch.PunchInTime , Punch.PunchOutTime) ) rnum, CONVERT( CHAR(10), CONVERT(DATETIME, ISNULL(Punch.PunchInTime , Punch.PunchOutTime), 101),101) date_val FROM punch ) temp WHERE UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchInTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) OR UserName in ('"+AdminMultipleDate__MMColParam+"') AND (PunchOutTime BETWEEN '"+Date_From+"' AND Cast('"+Date_To+"' As datetime) + 1) ORDER BY UserName ASC, date_val DESC, rnum";
AdminMultipleDate_cmd.Prepared = true;
AdminMultipleDate_cmd.Parameters.Append(AdminMultipleDate_cmd.CreateParameter("param1", 200, 1, 50, AdminMultipleDate__MMColParam)); // adVarChar
var AdminMultipleDate = AdminMultipleDate_cmd.Execute();
var AdminMultipleDate_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
AdminMultipleDate_numRows += Repeat1__numRows;
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/Home.dwt" codeOutsideHTMLIsLocked="false" -->
<!-- DW6 -->
<head>
<!-- Copyright 2005 Macromedia, Inc. All rights reserved. -->
<!-- InstanceBeginEditable name="doctitle" -->
<title>Time Clock</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="mm_training.css" type="text/css" />
<style type="text/css">
<!--
.style1 {font-size: 12px}
-->
</style>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#64748B">
<table width="123%" border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#26354A">
<td width="22" nowrap="nowrap"><img src="mm_spacer.gif" alt="" width="15" height="1" border="0" /></td>
<td height="70" colspan="3" class="logo" nowrap="nowrap">Amiee Lynn, Inc. <span class="tagline">| Time Clock</span></td>
<td width="4"> </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="TimeClock_Admin.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();
%>
ASKER
Sorry about that. I posted the resolution for everyone to see.
ASKER
Thanks again!!!
No problem and I appreciate you doing that. This helps others in future who may have a similar problem.
ASKER
(Right now I have it so they can only choose 1 just not to cause any issues on the site).
Open in new window