Solved

SQL SELECT statement on ASP page using prepopulated hidden form field value

Posted on 2009-06-29
2
397 Views
Last Modified: 2012-05-07
Hi all, I'm having a problem on an ASP page getting a SQL SELECT statement to work. Basically what I'm trying to accomplish is this...I have a hidden form field on this page that is prepopulated with the users name. I need the SQL SELECT statement to use the value of the hidden field in the WHERE UserName =. I used Javascript to get the username to populate without the DOMAIN\ in the front, but I'm not stuck on using Javascript, if there is another way I can do this I am more than willing but it is a must that I get just the username without the DOMAIN\. I've tried every suggestion I can find to do this but havent come up with a solution. Any help would be appreciated. I will post the code from the asp page. Thanks in advance.

PS - I'm using Dreamweaver CS3 if it make a difference.

Jon
<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/TimeClock.asp" -->
<%
var PrevPunches__MMColParam = "1";
if (String(Request.Form("hiddenField")) != "undefined" && 
    String(Request.Form("hiddenField")) != "") { 
  PrevPunches__MMColParam = String(Request.Form("hiddenField"));
}
%>
<%
var PrevPunches_cmd = Server.CreateObject ("ADODB.Command");
PrevPunches_cmd.ActiveConnection = MM_TimeClock_STRING;
PrevPunches_cmd.CommandText = "SELECT UserName, PunchTime, LateComment FROM dbo.Punch WHERE UserName = hiddenField ORDER BY PunchTime DESC";
PrevPunches_cmd.Prepared = true;
PrevPunches_cmd.Parameters.Append(PrevPunches_cmd.CreateParameter("param1", 200, 1, 50, PrevPunches__MMColParam)); // adVarChar
 
var PrevPunches = PrevPunches_cmd.Execute();
var PrevPunches_numRows = 0;
%>
<%
var Repeat1__numRows = 20;
var Repeat1__index = 0;
PrevPunches_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>Company Portal</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="100%" border="0" cellspacing="0" cellpadding="0">
	<tr bgcolor="#26354A">
	<td width="15" 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">| Company Portal</span></td>
	<td width="40">&nbsp;</td>
	<td width="100%">&nbsp;</td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FFCC00">
	<td width="15" nowrap="nowrap">&nbsp;</td>
	<td width="705" 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">TIMECLOCK</a></td>          
          <td class="navText" align="center" nowrap="nowrap"><a href="TimeOff.asp">TIME OFF</a></td>
          <td class="navText" align="center" nowrap="nowrap"><a href="TC_Login.asp">ADMIN</a></td>
        </tr>
      </table>	</td>
	<td width="40">&nbsp;</td>
	<td width="100%">&nbsp;</td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr bgcolor="#FF6600">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="4" border="0" /></td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td width="230" 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"><a href="javascript:;">Next Event &gt;</a><br />
	        Market week 6-?-2008<br />
	        
	        &nbsp;<br />		</td>
	      </tr>
	        </table>
	  </div></td>
	<td width="50" valign="top"><div align="center"><img src="mm_spacer.gif" alt="" width="50" height="1" border="0" /></div></td>
	<td width="440" valign="top"><div align="center"><br />
	    <!-- InstanceBeginEditable name="EditRegion3" -->
	    <p class="style2 style2">You punched the clock as</p>
        <span class="style2">
        <script type="text/javascript">
var user;
user = "<%=Request.ServerVariables("AUTH_USER")%>".substr(9);
        </script>
        </span>
        <p class="style2">
<script type="text/javascript">document.write(user);</script>
 
<script type="text/javascript">
document.writeln('<input type="hidden" name="hiddenField" value="'+ user +'">');
</script>
	    <p class="style2 style2">&nbsp;</p>
	    
        <table border="1" cellpadding="1" cellspacing="1">
          <tr>
            <td>UserName</td>
            <td>PunchTime</td>
            <td>LateComment</td>
          </tr>
          <% while ((Repeat1__numRows-- != 0) && (!PrevPunches.EOF)) { %>
            <tr>
              <td><%=(PrevPunches.Fields.Item("UserName").Value)%></td>
              <td><%=(PrevPunches.Fields.Item("PunchTime").Value)%></td>
              <td><%=(PrevPunches.Fields.Item("LateComment").Value)%></td>
            </tr>
            <%
  Repeat1__index++;
  PrevPunches.MoveNext();
}
%>
        </table>
        <p class="style2 style2">&nbsp;</p>
	    <p class="style2 style2">&nbsp;</p>
	    <!-- InstanceEndEditable --><br />
	    <br />
	&nbsp;<br />	
	</div></td>
	<td width="40">&nbsp;</td>
	<td width="100%">&nbsp;</td>
	</tr>
 
	<tr bgcolor="#D3DCE6">
	<td colspan="6"><img src="mm_spacer.gif" alt="" width="1" height="1" border="0" /></td>
	</tr>
 
	<tr>
	<td width="15">&nbsp;</td>
	<td width="215">&nbsp;</td>
	<td width="50">&nbsp;</td>
	<td width="440">&nbsp;</td>
	<td width="40">&nbsp;</td>
	<td width="100%">&nbsp;</td>
	</tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
PrevPunches.Close();
%>

Open in new window

0
Comment
Question by:Jon DeVito
2 Comments
 
LVL 5

Expert Comment

by:boowhup
ID: 24742669
Looking at your code , this is what I would do

Replace the following line

PrevPunches_cmd.CommandText = "SELECT UserName, PunchTime, LateComment FROM dbo.Punch WHERE UserName = hiddenField ORDER BY PunchTime DESC";

with

PrevPunches_cmd.CommandText = "SELECT UserName, PunchTime, LateComment FROM dbo.Punch WHERE UserName = REPLACE(hiddenfield 'DOMAIN\','') ORDER BY PunchTime DESC";

========================================================
But that original line looks wrong to me in the 1st place -  the hiddenfield part doesn't look like it's referencing the asp/javascript variable. I would have said the original line should be something like ...

1) PrevPunches_cmd.CommandText = "SELECT UserName, PunchTime, LateComment FROM dbo.Punch WHERE UserName = '" & String(Request.Form("hiddenField")) & "' ORDER BY PunchTime DESC";

or maybe

2) PrevPunches_cmd.CommandText = "SELECT UserName, PunchTime, LateComment FROM dbo.Punch WHERE UserName = '" & PrevPunches__MMColParam & "' ORDER BY PunchTime DESC";

And from there you could make it Replace(String(Request.Form("hiddenField")),"DOMAIN\","") or Replace(String(Request.Form("hiddenField")),"DOMAIN\","") etc.  Or even use a split command.

0
 
LVL 3

Accepted Solution

by:
Jon DeVito earned 0 total points
ID: 24742869
I was going about this totally the wrong way. I needed to use GET instead of POST to be able to use the variable. Thanks for trying to help though. Much appreciated.

Jon
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pass SQL to JSON. Page is in classic ASP and using Jquery 4 40
SQL Select Query help 1 38
StoredProcedure to JSON query faulty syntax 2 29
UPDATE JOIN multiple tables 5 23
When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

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

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

Join & Ask a Question