jmaresca2005
12-14-2004, 04:16 PM
I have created a drop down that populates with customers that is generated from the db. The drop down is on the search page where the user can search that customers info by selecting the customer. the drop down populates but i am uable to get the reults to display. here are the pages:
SEARCH.asp // page with drop down
<%
DIM objCmd
DIM objRS
DIM objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn
'******* STORED PROCEDURE CALL ********
objCmd.CommandText="_SP_Customer"
objCmd.CommandType=4
set objRS = objCmd.Execute
%>
<html>
<head>
<SCRIPT LANGUAGE="JavaScript"> <!--
function blockError(){return true;}
window.onerror = blockError;
// -->
</script>
<LINK REL="stylesheet" type="text/css" HREF="master.css">
</head>
<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()">
<table border="0" align="center" cellpadding="5" bordercolor="black" style="border: dashed 1px #000000;">
<tr >
<td bgcolor=black><font size="2" color="white"><b>Great Plains Item Number</b></font> </td>
</tr>
<tr>
<td><input type="text" name="GPITEM"></td>
</tr>
<tr >
<td bgcolor=black><font size="2" color="white"><b>Expected Ship Date</b></font> </td>
</tr>
<td><input type="text" name=""></td>
<tr >
<td bgcolor=black><select name="CUSTNMBR">
<option value="0">- Please Select a Customer -</option>
<%Do until objRS.EOF%>
<option value="">
<%=objRS.fields("CUSTNMBR")%>
</option>
<%objRS.MoveNext%>
<%Loop
%>
</select> </td>
</tr>
<!--
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Customer<input type="text" name="CUSTNMBR"></td>
</tr>-->
<!-- <select name="ShipToName">
<option value="0">- Please Select a Customer -</option>-->
<%
'Do until rs.EOF
' Response.Write "<option value=""" & rs("ShipToName") & """>" & rs("ShipToName") & "</option>" & vbCrLf
' rs.MoveNext
'Loop
'rs.Close
'Response.Write "</select>" & vbCrLf
'End If
'End If
'Set rs = nothing
'Set cmd = nothing
%>
</td>
</tr>
<tr>
<td>
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</td>
</tr>
</table>
<BR>
</FORM>
</body>
</html>
SEARCHRESULTS.ASP //page that displays the searched results.
<%
Const adVarChar = 200
Const adChar = 129
Const adDate = 7
Const adParamInput = &H0001
Dim objConn
Dim objCmd
Dim objRS
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
If len(trim(Request("GPITEM"))) = 0 then
GPITEM = "%"
Else
GPITEM = "%" & Request("GPITEM") & "%"
End if
'If len(trim(Request("EXPSHIP"))) = 0 then
'EXPSHIP = "%"
'Else
' EXPSHIP = "%" & Request("EXPSHIP") & "%"
'End if
'If len(trim(Request("CUSTNMBR"))) = 0 then
'CUSTNMBR = "%"
'Else
' CUSTNMBR = "%" & Request("CUSTNMBR") & "%"
'End if
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn
'Set objConn = CreateObject("ADODB.Connection")
'objConn.Open Application("Conn_ConnectionString2")
'Set objCmd=Server.CreateObject("ADODB.Command")
'objCmd.ActiveConnection=objConn
'******* STORED PROCEDURE CALL********
objCmd.CommandText="_SP_SearchInfo"
objCmd.CommandType=4
objCmd.Parameters.Append objCmd.CreateParameter ("@GPITEM",adChar,adParamInput,21,GPITEM)
'objCmd.Parameters.Append objCmd.CreateParameter ("@EXPSHIP",adVarChar,adParamInput,30,EXPSHIP)
'objCmd.Parameters.Append objCmd.CreateParameter ("@CUSTNMBR",adChar,adParamInput,15,CUSTNMBR)
set objRS = objCmd.Execute
%>
<html>
<title></title>
<head>
<script language="JavaScript">
<!-- Begin
function goToURL() { window.location = "http://dev-environ/Users/Search.asp"; }
// End -->
</script>
<style>
input{
font-family: "Verdana";
font-size: 8pt;
background-color: "white"
}
</style>
</head>
<body>
<TABLE border="0" width="70%" bordercolor="black" style="border: dashed 1px #000000;">
<tr>
<td width="4%" align="center" bgcolor="black"><font size="" color="white"><b>Great Plains Item Number</b></font><br><font size="1" color="white">Click Item Number to Update the Item</font></td>
<td width="3%" align="center" valign=top bgcolor="black"><font size="" color="white"><b>Expected Ship Date</b></font></td>
<td width="4%" align="center" valign=top bgcolor="black"><font size="" color="white"><b>Customer</b></font></td>
</tr>
<form method="post" action="download.asp?GPITEM=<%=objRS.fields("GPITEM")%>" target=_new>
<%Dim counter
counter = 1
Do until objRS.EOF
if counter mod 2 = 0 then
response.write("<TR bgcolor='white'>")
else
response.Write("<TR bgcolor='white'>")
end if%>
<td align=center>
<a href="update.asp?GPITEM=<%=objRS.fields("GPITEM")%>"><font size=1 color="black" ><%=objRS.fields("GPITEM")%></a></font>
</td>
<td align=center><font size=1><%'=objRs.fields("EXPSHIP")%> </font></td>
<td align=center><font size=1><%'=objRS.fields("CUSTNMBR")%></font></td>
</tr>
<%counter = counter + 1
objRS.MoveNext
Loop
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>
</table>
<input type="submit" value="Export to an Excel File" ><input type="button" value=" Back to Search Page " onClick="goToURL()">
<!--
<script language="Javascript1.2">
<!--
var message = " Print this Page ";
function printpage() {
window.print();
}
document.write("<form><input type=button "
+"value=\""+message+"\" onClick=\"printpage()\"></form>");
</script>//-->
</form>
</BODY>
</HTML>
_SP_Customer // stored procedure that populates the drop down/ this sp works.
CREATE PROCEDURE _SP_Customer --@CUSTNMBR char(15)
AS
select distinct INTRANETVIEW.CUSTNMBR
from INTRANETVIEW
--WHERE INTRANETVIEW.CUSTNMBR LIKE @CUSTNMBR
GO
_SP_SearchInfo// stored procedure that displays the results
CREATE PROCEDURE _SP_SearchInfo @GPITEM char(21)
--@EXPSHIP varchar(40)
--@CUSTNMBR char(15)
AS
SELECT INTRANETVIEW.GPITEM--, INTRANETVIEW.EXPSHIP --, INTRANETVIEW.CUSTNMBR
FROM INTRANETVIEW
WHERE INTRANETVIEW.GPITEM LIKE @GPITEM
--and INTRANETVIEW.EXPSHIP LIKE @EXPSHIP
--and INTRANETVIEW.CUSTNMBR LIKE @CUSTNMBR
GO
SEARCH.asp // page with drop down
<%
DIM objCmd
DIM objRS
DIM objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn
'******* STORED PROCEDURE CALL ********
objCmd.CommandText="_SP_Customer"
objCmd.CommandType=4
set objRS = objCmd.Execute
%>
<html>
<head>
<SCRIPT LANGUAGE="JavaScript"> <!--
function blockError(){return true;}
window.onerror = blockError;
// -->
</script>
<LINK REL="stylesheet" type="text/css" HREF="master.css">
</head>
<body>
<FORM name="form" action="SearchResults.asp" method="post" onSubmit="return check()">
<table border="0" align="center" cellpadding="5" bordercolor="black" style="border: dashed 1px #000000;">
<tr >
<td bgcolor=black><font size="2" color="white"><b>Great Plains Item Number</b></font> </td>
</tr>
<tr>
<td><input type="text" name="GPITEM"></td>
</tr>
<tr >
<td bgcolor=black><font size="2" color="white"><b>Expected Ship Date</b></font> </td>
</tr>
<td><input type="text" name=""></td>
<tr >
<td bgcolor=black><select name="CUSTNMBR">
<option value="0">- Please Select a Customer -</option>
<%Do until objRS.EOF%>
<option value="">
<%=objRS.fields("CUSTNMBR")%>
</option>
<%objRS.MoveNext%>
<%Loop
%>
</select> </td>
</tr>
<!--
<tr bgcolor=Thistle>
<td align=right>Expected Ship Date mm/dd/yyyy<input type="text" name="EXPSHIP"></td>
</tr>
<tr bgcolor=Thistle>
<td align=right>Customer<input type="text" name="CUSTNMBR"></td>
</tr>-->
<!-- <select name="ShipToName">
<option value="0">- Please Select a Customer -</option>-->
<%
'Do until rs.EOF
' Response.Write "<option value=""" & rs("ShipToName") & """>" & rs("ShipToName") & "</option>" & vbCrLf
' rs.MoveNext
'Loop
'rs.Close
'Response.Write "</select>" & vbCrLf
'End If
'End If
'Set rs = nothing
'Set cmd = nothing
%>
</td>
</tr>
<tr>
<td>
<INPUT TYPE="submit" value="Search" ><input type="reset" value=" Clear ">
</td>
</tr>
</table>
<BR>
</FORM>
</body>
</html>
SEARCHRESULTS.ASP //page that displays the searched results.
<%
Const adVarChar = 200
Const adChar = 129
Const adDate = 7
Const adParamInput = &H0001
Dim objConn
Dim objCmd
Dim objRS
Dim GPITEM
Dim EXPSHIP
Dim CUSTNMBR
If len(trim(Request("GPITEM"))) = 0 then
GPITEM = "%"
Else
GPITEM = "%" & Request("GPITEM") & "%"
End if
'If len(trim(Request("EXPSHIP"))) = 0 then
'EXPSHIP = "%"
'Else
' EXPSHIP = "%" & Request("EXPSHIP") & "%"
'End if
'If len(trim(Request("CUSTNMBR"))) = 0 then
'CUSTNMBR = "%"
'Else
' CUSTNMBR = "%" & Request("CUSTNMBR") & "%"
'End if
Set objConn = CreateObject("ADODB.Connection")
objConn.Open Application("Conn_ConnectionString")
Set objCmd=Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection=objConn
'Set objConn = CreateObject("ADODB.Connection")
'objConn.Open Application("Conn_ConnectionString2")
'Set objCmd=Server.CreateObject("ADODB.Command")
'objCmd.ActiveConnection=objConn
'******* STORED PROCEDURE CALL********
objCmd.CommandText="_SP_SearchInfo"
objCmd.CommandType=4
objCmd.Parameters.Append objCmd.CreateParameter ("@GPITEM",adChar,adParamInput,21,GPITEM)
'objCmd.Parameters.Append objCmd.CreateParameter ("@EXPSHIP",adVarChar,adParamInput,30,EXPSHIP)
'objCmd.Parameters.Append objCmd.CreateParameter ("@CUSTNMBR",adChar,adParamInput,15,CUSTNMBR)
set objRS = objCmd.Execute
%>
<html>
<title></title>
<head>
<script language="JavaScript">
<!-- Begin
function goToURL() { window.location = "http://dev-environ/Users/Search.asp"; }
// End -->
</script>
<style>
input{
font-family: "Verdana";
font-size: 8pt;
background-color: "white"
}
</style>
</head>
<body>
<TABLE border="0" width="70%" bordercolor="black" style="border: dashed 1px #000000;">
<tr>
<td width="4%" align="center" bgcolor="black"><font size="" color="white"><b>Great Plains Item Number</b></font><br><font size="1" color="white">Click Item Number to Update the Item</font></td>
<td width="3%" align="center" valign=top bgcolor="black"><font size="" color="white"><b>Expected Ship Date</b></font></td>
<td width="4%" align="center" valign=top bgcolor="black"><font size="" color="white"><b>Customer</b></font></td>
</tr>
<form method="post" action="download.asp?GPITEM=<%=objRS.fields("GPITEM")%>" target=_new>
<%Dim counter
counter = 1
Do until objRS.EOF
if counter mod 2 = 0 then
response.write("<TR bgcolor='white'>")
else
response.Write("<TR bgcolor='white'>")
end if%>
<td align=center>
<a href="update.asp?GPITEM=<%=objRS.fields("GPITEM")%>"><font size=1 color="black" ><%=objRS.fields("GPITEM")%></a></font>
</td>
<td align=center><font size=1><%'=objRs.fields("EXPSHIP")%> </font></td>
<td align=center><font size=1><%'=objRS.fields("CUSTNMBR")%></font></td>
</tr>
<%counter = counter + 1
objRS.MoveNext
Loop
Set objRS = nothing
objConn.Close
Set objConn = nothing
%>
</table>
<input type="submit" value="Export to an Excel File" ><input type="button" value=" Back to Search Page " onClick="goToURL()">
<!--
<script language="Javascript1.2">
<!--
var message = " Print this Page ";
function printpage() {
window.print();
}
document.write("<form><input type=button "
+"value=\""+message+"\" onClick=\"printpage()\"></form>");
</script>//-->
</form>
</BODY>
</HTML>
_SP_Customer // stored procedure that populates the drop down/ this sp works.
CREATE PROCEDURE _SP_Customer --@CUSTNMBR char(15)
AS
select distinct INTRANETVIEW.CUSTNMBR
from INTRANETVIEW
--WHERE INTRANETVIEW.CUSTNMBR LIKE @CUSTNMBR
GO
_SP_SearchInfo// stored procedure that displays the results
CREATE PROCEDURE _SP_SearchInfo @GPITEM char(21)
--@EXPSHIP varchar(40)
--@CUSTNMBR char(15)
AS
SELECT INTRANETVIEW.GPITEM--, INTRANETVIEW.EXPSHIP --, INTRANETVIEW.CUSTNMBR
FROM INTRANETVIEW
WHERE INTRANETVIEW.GPITEM LIKE @GPITEM
--and INTRANETVIEW.EXPSHIP LIKE @EXPSHIP
--and INTRANETVIEW.CUSTNMBR LIKE @CUSTNMBR
GO