Hello all.
I have inherited a web app for an intranet site and was asked to point the query to a different database. I am fairly new to this, so I copied the old code and changed the table/database references where appropriate.
My database connect string is valid (tested it on its own to eliminate possibilities) and the SQL select statement is valid (checked it in Query Analyzer). I am assuming the error lies in the code to assemble the WHERE statement, but I am just not sure. Would someone here be willing to take a look at the code and see if they can fin any obvious problems?
Thank you!
===========================================================
Here is the code, copied into notepad and pasted:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/uniqueidNew.asp" -->
<%
bUsed=false
If request.form("uniqueid") <> "" or request.form("firstname") <> "" or request.form("lastname") <> "" then
sSQL = "Select EMPOWER.U_EMPL_POSITIONS.position_code, EMPOWER.U_EMPL_POSITIONS.id_code, EMPOWER.U_EMPL_POSITIONS.POS_EFF_DT, EMPOWER.U_EMPL_POSITIONS.POS_EXP_DT, EMPOWER.U_EMPL.loan_center, EMPOWER.U_EMPL.bus_phone, EMPOWER.ESS_USERS.EMPLOYEE_FIRST, EMPOWER.ESS_USERS.EMPLOYEE_LAST From EMPOWER.U_EMPL_POSITIONS JOIN EMPOWER.U_EMPL ON EMPOWER.U_EMPL_POSITIONS.userid = EMPOWER.U_EMPL.userid JOIN EMPOWER.ESS_USERS ON EMPOWER.U_EMPL.userid = EMPOWER.ESS_USERS.userid where "
if request.form("uniqueid") <> "" then
sID =request.form("uniqueid")
sSQL = sSQL & " (id_code = " & sID & ")"
bUsed=true
end if
if request.form("firstname") <> "" then
sSQL = sSQL & " EMPLOYEE_FIRST like '" & request.form("firstname") & "%'"
bUsed=true
end if
if request.form("lastname") <> "" then
if bUsed then
sSQL = sSQL & " and EMPLOYEE_LAST like '" & request.form("lastname") & "%'"
else
sSQL = sSQL & " EMPLOYEE_LAST like '" & request.form("lastname") & "%'"
bUsed=true
end if
end if
sSQL =sSQL & " order by EMPLOYEE_LAST, EMPLOYEE_FIRST, position_code"
' response.write ssql
set rsUniqueID = Server.CreateObject("ADODB.Recordset")
rsUniqueID.ActiveConnection = MM_uniqueidNew_STRING
rsUniqueID.Source = sSQL
rsUniqueID.CursorType = 0
rsUniqueID.CursorLocation = 2
rsUniqueID.LockType = 3
rsUniqueID.Open()
else
end if
%>
<html>
<head>
<title>UNIQUE ID LOOKUP</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_openBrWindow(theURL,winName,features) { //v2.0
window.open(theURL,winName,features);
}
//-->
</script>
<link href="styles/framework_style.css" rel="stylesheet" type="text/css">
<link href="styles/stylehome.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#0000FF" alink="#0000FF" leftmargin="0" topmargin="0">
<IFRAME SRC=" WIDTH="770px" HEIGHT="47" FRAMEBORDER=0; MARGINWIDTH=0; MARGINHEIGHT=0; SCROLLING="no";></IFRAME>
<table width="769" class="Breadcrumbs">
<tr>
<td width="673"> You are at: <img src="images/icons/home_icon.gif" width="9" height="8"><a href=" Home</a> >> <a href=" href=" ALPS</a> >> <span class="LocationRed">Unique Id Lookup</span></td>
</tr>
</table>
<table width="82%" border="0" align="left" cellpadding="0" cellspacing="0" id="maintable" height="401">
<tr>
<td valign="top" height="251" align="left" width="99%"><div align="left"></div>
<p align="center"><font color="#660066"><a href="instructions.htm" target="_blank"><br>
Lookup Instructions</a> <br>
</font> </p>
<form name="form1" method="post" action="">
<table width="32%" border="0" align="center">
<tr>
<td width="33%" height="20">Unique ID:</td>
<td width="42%" height="20"> <input type="integer" name="uniqueid">
</td>
</tr>
<tr>
<td width="33%">First Name:</td>
<td width="42%"> <input type="text" name="firstname"> </td>
</tr>
<tr>
<td width="33%">Last Name:</td>
<td width="42%"> <input type="text" name="lastname"> </td>
</tr>
<tr>
<td height="35" colspan="2" align="center"> <input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table>
</form>
<%
if bUsed = true then
if not rsUniqueid.eof then
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0" align="center">
<tr bgcolor="#996699">
<td><font color="#FFFFFF"><strong>Unique ID</strong></font></td>
<td><font color="#FFFFFF"><strong>First Name</strong></font></td>
<td><font color="#FFFFFF"><strong>Last Name</strong></font></td>
<td><font color="#FFFFFF"><strong><a href="javascript:" onClick="MM_openBrWindow('posn.asp','chart','scrollbars=yes,width=250,height=600')">
Position Code </a> </strong></font></td>
<td><font color="#FFFFFF"><strong>Location</strong></font></td>
<td><font color="#FFFFFF"><strong>Phone #</strong></font></td>
<td><font color="#FFFFFF"><strong>Position Expired Date</strong></font></td>
</tr>
<%
do while not rsUniqueID.eof
%>
<tr>
<td><%=(rsUniqueID.Fields.Item("id_code").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("EMPLOYEE_FIRST").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("EMPLOYEE_LAST").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("position_code").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("Loan_center").Value)%></td>
<td><%=left((rsUniqueID.Fields.Item("bus_phone").Value),3)%>-<%=mid((rsUniqueID.Fields.Item("bus_phone").Value),4,3)%>-<%=right((rsUniqueID.Fields.Item("bus_phone").Value),4)%></td>
<td><%=(rsUniqueID.Fields.Item("POS_EXP_DT").Value)%></td>
</tr>
<%
rsUniqueID.movenext
loop
end if
%>
</table>
<p>
<%
else
response.write "<center>No Records were found</center>"
end if
%>
</p>
<font color="#660066"> </font></td>
<td valign="top" align="left" width="1%"> </td>
</tr>
<tr>
<td height="120" colspan="2"> <center>
<div align="left">
<IFRAME SRC=" WIDTH="770px" HEIGHT="15" FRAMEBORDER=0; MARGINWIDTH=0; MARGINHEIGHT=0; SCROLLING="no";></IFRAME>
</div>
</center></td>
</tr>
</table>
<p><br>
<font color="#660066"><br>
</font><font color="#660066"><br>
<br>
</font> </p>
<p> </p>
</body>
</html>
I have inherited a web app for an intranet site and was asked to point the query to a different database. I am fairly new to this, so I copied the old code and changed the table/database references where appropriate.
My database connect string is valid (tested it on its own to eliminate possibilities) and the SQL select statement is valid (checked it in Query Analyzer). I am assuming the error lies in the code to assemble the WHERE statement, but I am just not sure. Would someone here be willing to take a look at the code and see if they can fin any obvious problems?
Thank you!
===========================================================
Here is the code, copied into notepad and pasted:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/uniqueidNew.asp" -->
<%
bUsed=false
If request.form("uniqueid") <> "" or request.form("firstname") <> "" or request.form("lastname") <> "" then
sSQL = "Select EMPOWER.U_EMPL_POSITIONS.position_code, EMPOWER.U_EMPL_POSITIONS.id_code, EMPOWER.U_EMPL_POSITIONS.POS_EFF_DT, EMPOWER.U_EMPL_POSITIONS.POS_EXP_DT, EMPOWER.U_EMPL.loan_center, EMPOWER.U_EMPL.bus_phone, EMPOWER.ESS_USERS.EMPLOYEE_FIRST, EMPOWER.ESS_USERS.EMPLOYEE_LAST From EMPOWER.U_EMPL_POSITIONS JOIN EMPOWER.U_EMPL ON EMPOWER.U_EMPL_POSITIONS.userid = EMPOWER.U_EMPL.userid JOIN EMPOWER.ESS_USERS ON EMPOWER.U_EMPL.userid = EMPOWER.ESS_USERS.userid where "
if request.form("uniqueid") <> "" then
sID =request.form("uniqueid")
sSQL = sSQL & " (id_code = " & sID & ")"
bUsed=true
end if
if request.form("firstname") <> "" then
sSQL = sSQL & " EMPLOYEE_FIRST like '" & request.form("firstname") & "%'"
bUsed=true
end if
if request.form("lastname") <> "" then
if bUsed then
sSQL = sSQL & " and EMPLOYEE_LAST like '" & request.form("lastname") & "%'"
else
sSQL = sSQL & " EMPLOYEE_LAST like '" & request.form("lastname") & "%'"
bUsed=true
end if
end if
sSQL =sSQL & " order by EMPLOYEE_LAST, EMPLOYEE_FIRST, position_code"
' response.write ssql
set rsUniqueID = Server.CreateObject("ADODB.Recordset")
rsUniqueID.ActiveConnection = MM_uniqueidNew_STRING
rsUniqueID.Source = sSQL
rsUniqueID.CursorType = 0
rsUniqueID.CursorLocation = 2
rsUniqueID.LockType = 3
rsUniqueID.Open()
else
end if
%>
<html>
<head>
<title>UNIQUE ID LOOKUP</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_openBrWindow(theURL,winName,features) { //v2.0
window.open(theURL,winName,features);
}
//-->
</script>
<link href="styles/framework_style.css" rel="stylesheet" type="text/css">
<link href="styles/stylehome.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#0000FF" alink="#0000FF" leftmargin="0" topmargin="0">
<IFRAME SRC=" WIDTH="770px" HEIGHT="47" FRAMEBORDER=0; MARGINWIDTH=0; MARGINHEIGHT=0; SCROLLING="no";></IFRAME>
<table width="769" class="Breadcrumbs">
<tr>
<td width="673"> You are at: <img src="images/icons/home_icon.gif" width="9" height="8"><a href=" Home</a> >> <a href=" href=" ALPS</a> >> <span class="LocationRed">Unique Id Lookup</span></td>
</tr>
</table>
<table width="82%" border="0" align="left" cellpadding="0" cellspacing="0" id="maintable" height="401">
<tr>
<td valign="top" height="251" align="left" width="99%"><div align="left"></div>
<p align="center"><font color="#660066"><a href="instructions.htm" target="_blank"><br>
Lookup Instructions</a> <br>
</font> </p>
<form name="form1" method="post" action="">
<table width="32%" border="0" align="center">
<tr>
<td width="33%" height="20">Unique ID:</td>
<td width="42%" height="20"> <input type="integer" name="uniqueid">
</td>
</tr>
<tr>
<td width="33%">First Name:</td>
<td width="42%"> <input type="text" name="firstname"> </td>
</tr>
<tr>
<td width="33%">Last Name:</td>
<td width="42%"> <input type="text" name="lastname"> </td>
</tr>
<tr>
<td height="35" colspan="2" align="center"> <input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table>
</form>
<%
if bUsed = true then
if not rsUniqueid.eof then
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0" align="center">
<tr bgcolor="#996699">
<td><font color="#FFFFFF"><strong>Unique ID</strong></font></td>
<td><font color="#FFFFFF"><strong>First Name</strong></font></td>
<td><font color="#FFFFFF"><strong>Last Name</strong></font></td>
<td><font color="#FFFFFF"><strong><a href="javascript:" onClick="MM_openBrWindow('posn.asp','chart','scrollbars=yes,width=250,height=600')">
Position Code </a> </strong></font></td>
<td><font color="#FFFFFF"><strong>Location</strong></font></td>
<td><font color="#FFFFFF"><strong>Phone #</strong></font></td>
<td><font color="#FFFFFF"><strong>Position Expired Date</strong></font></td>
</tr>
<%
do while not rsUniqueID.eof
%>
<tr>
<td><%=(rsUniqueID.Fields.Item("id_code").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("EMPLOYEE_FIRST").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("EMPLOYEE_LAST").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("position_code").Value)%></td>
<td><%=(rsUniqueID.Fields.Item("Loan_center").Value)%></td>
<td><%=left((rsUniqueID.Fields.Item("bus_phone").Value),3)%>-<%=mid((rsUniqueID.Fields.Item("bus_phone").Value),4,3)%>-<%=right((rsUniqueID.Fields.Item("bus_phone").Value),4)%></td>
<td><%=(rsUniqueID.Fields.Item("POS_EXP_DT").Value)%></td>
</tr>
<%
rsUniqueID.movenext
loop
end if
%>
</table>
<p>
<%
else
response.write "<center>No Records were found</center>"
end if
%>
</p>
<font color="#660066"> </font></td>
<td valign="top" align="left" width="1%"> </td>
</tr>
<tr>
<td height="120" colspan="2"> <center>
<div align="left">
<IFRAME SRC=" WIDTH="770px" HEIGHT="15" FRAMEBORDER=0; MARGINWIDTH=0; MARGINHEIGHT=0; SCROLLING="no";></IFRAME>
</div>
</center></td>
</tr>
</table>
<p><br>
<font color="#660066"><br>
</font><font color="#660066"><br>
<br>
</font> </p>
<p> </p>
</body>
</html>