Display Search by Network (ex. Cisco) or All Projects in the MS Access Table

[hotflash]

Hi All,

Below is a GOOD working script that I use to search for the record either by the MOP ID for example 1, 2, etc... (record on the database) or by Network for example when you type Cisco.

What I would like to do and is having problem is to either Search by Network or All MOP IDs on the database (show all records). Let's say if folks type in ALL, it will show all of the MOP IDs (Records) in the database. Thanks advance for your help.

<%
Dim strNetwork
Dim strMOPID
Dim Conn
Dim strSQL
Dim RS
 
strNetwork = trim(Request.Form("Network"))
strMOPID = trim(Request.Form("MOPID"))
If len(strNetwork)=0 then
    strNetwork=0
End if
 
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("MOPsCentral.mdb")
 
If isNumeric(strNetwork) then
    strSQL = "Select * FROM MOPs WHERE MOPID=" & strNetwork
Else
    strSQL = "Select * FROM MOPs WHERE Network='" & strNetwork & "' ORDER BY MOPID"
End If
 
RS.Open strSQL, Conn
 
If RS.EOF Then
   Response.Redirect("SearchMOPs.asp?MOPError=Sorry ... Your MOP ID or Network does not exist in our database.  Please try again.")
End If
%>

[CroCrew]

Hello hotflash,

Give this a try:

<%
Dim strNetwork
Dim strMOPID
Dim Conn
Dim strSQL
Dim RS
 
strNetwork = trim(Request.Form("Network"))
strMOPID = trim(Request.Form("MOPID"))
If len(strNetwork)=0 then
strNetwork=0
End if
 
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("MOPsCentral.mdb")
 
If (strNetwork = "All") then
    strSQL = "Select * FROM MOPs ORDER BY MOPID"
Else
    If isNumeric(strNetwork) then
        strSQL = "Select * FROM MOPs WHERE MOPID=" & strNetwork
    Else
        strSQL = "Select * FROM MOPs WHERE Network='" & strNetwork & "' ORDER BY MOPID"
    End If
End IF
 
RS.Open strSQL, Conn
 
If RS.EOF Then
Response.Redirect("SearchMOPs.asp?MOPError=Sorry ... Your MOP ID or Network does not exist in our database. Please try again.")
End If
%>
 
 

[hotflash]

Hi Crocrew,

It works fine. Thanks for your help.

[hotflash]

Hi CroCrew,

I have another question to see if you can help me to expand the search function here. Below is the search script that I used to search by ProjectID, ALL OPENED PROJECTS, and by Engineer Name. In the MS Access table, there is a field called RequestorsName as well. How can I modified this nested IF statement to make it also search for RequestorName for exampe CroCrew. I have tried around but does not have any luck.

Thanks for your help.

<%
Dim strGDNIEngineer
Dim strCompleteDate
Dim Conn
Dim strSQL
Dim RS
 
strGDNIEngineer = trim(Request.Form("GDNIEngineer"))
strCompleteDate = trim(Request.Form("CompleteDate"))
If len(strGDNIEngineer)=0 then
    strGDNIEngineer=0
End if
 
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")
 
If isNumeric(strGDNIEngineer) then
    strSQL = "Select * FROM PTSProjects WHERE ProjectID=" & strGDNIEngineer 
Else
  if strGDNIEngineer="ALL OPENED PROJECTS" then
    strSQL = "Select * FROM PTSProjects WHERE CompleteDate IS NULL"' ORDER BY ProjectID'
  else
      strSQL = "Select * FROM PTSProjects WHERE GDNIEngineer='" & strGDNIEngineer & "' AND CompleteDate IS NULL"' ORDER BY ProjectID'
  end if
End If
 
RS.Open strSQL, Conn
 
If RS.EOF Then
   Response.Redirect("SearchProject.asp?error=Sorry ... Your Project ID does not exist in our database.  Please try again.")
End If
%>

[CroCrew]

Hello hotflash,

It sounds like you need to look into using a CASE statement. Pass in from your <form> the type of search that the user want to do via a radio button or dropdown then use the CASE to determine what SQL syntax to use.

Example:

select case Request.Form("TypeOfSearch")
    case "UserName"
        strSQL = "SELECT * FROM YourTable WHERE FieldOne = '"Request.Form("UserName") & "'"
    case "ProjectID"
        strSQL = "SELECT * FROM YourTable WHERE FieldTwo = '"Request.Form("ProjectID") & "'"
    case "ProjectName"
        strSQL = "SELECT * FROM YourTable WHERE FieldThree = '"Request.Form("ProjectName") & "'"
    case Else
        Response.Write("Please select a search type!")
        Response.End
end select
 
 

Hope that helps~

[hotflash]

Hi CroCrew,

Thanks for your replying. You are SO GOOD at this stuff and you are going OVER MY HEAD on this one.

Please see if you can take my hand thru with this one. Below is the input box for user to put in either "Project ID, ALL OPENED RECORDS, or Username".

<TABLE ALIGN=CENTER BORDER=0 CELLSPACING=2 CELLPADDING=2 BGCOLOR="#FFFFFF">
                <TR>
                        <TH ALIGN=LEFT> <FONT FACE="HELVETICA,HELV,ARIAL" SIZE=3>Project ID: </FONT></TH>
                        <TD><INPUT CLASS="bluebox" NAME="GDNIEngineer" TYPE="TYPE" SIZE="35" ID="GDNIEngineer"></TD>
                </TR>
                &nbsp;&nbsp;&nbsp;&nbsp;
                <TR>
                    <TD ALIGN="CENTER" COLSPAN=2> <INPUT CLASS="Table_Blue" TYPE="submit" NAME="Submit" VALUE="SEARCH YOUR PROJECT"></TD>
                </TR>
    </TABLE>

How can you modify this using the RADIO button to make it work with the CASE statement that you provided earlier? The search items that we need are ProjectID, Username, and RequestorName. I also would like an option to search for ALL projects as well. Thanks for your outstanding support.

[CroCrew]

Hello hotflash,

Here is a crude example using two pages. Let me know if you need me to explain more on it.


PageOne.asp

<html>
    <head>
        <title>PageOne.asp</title>
    </head>
    <body>
        <form method="post" action="PageTwo.asp" name="xForm" id="xForm">
            Search on: 
            <input type="radio" name="SearchType" value="1" checked> Frist Name
            <input type="radio" name="SearchType" value="2"> Last Name
            <input type="radio" name="SearchType" value="3"> State
            <br>
            Using this: <input type="text" name="SearchWith">
            <br>
            <input type="submit" name="Submit">
        </form>
    </body>
</html>
 
 

PageTwo.asp

<%
select case Request.Form("SearchType")
    case "1"
        strSQL = "SELECT * FROM YourTable WHERE FieldOne = '"Request.Form("SearchWith") & "'"
    case "2"
        strSQL = "SELECT * FROM YourTable WHERE FieldTwo = '"Request.Form("SearchWith") & "'"
    case "3"
        strSQL = "SELECT * FROM YourTable WHERE FieldThree = '"Request.Form("SearchWith") & "'"
    case Else
        Response.Write("Please select a search type!")
        Response.End
end select
%>
 

Hope that helps out~

[hotflash]

Hi CroCrew,

I have transformed your code into the real environment but it does not show anything. Please help and thanks for your outstanding support.

Below is the SearchProject.a sp (Equivalent to your PageOne.asp):

<FORM ID="xForm" NAME="xForm" METHOD="post" ACTION="DisplayProject.asp">
 
  <TABLE ALIGN=CENTER BORDER=0 CELLSPACING=2 CELLPADDING=2 BGCOLOR="#FFFFFF">
                <TR>
                    <TD>
                                <FONT FACE="HELVETICA,HELV,ARIAL" SIZE=2>Please Select Your Search: </FONT><BR>
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT FACE="HELVETICA,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchType" VALUE="1"> Project ID </FONT><BR>
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT FACE="HELVETICA,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchType" VALUE="2"> GDNI Engineer </FONT><BR>
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT FACE="HELVETICA,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchType" VALUE="3"> Requestor's Name </FONT><BR>
                    <BR>
                  <INPUT TYPE="TEXT" SIZE="39" NAME="SearchWith">
            </TD>
            <BR>
                </TR>
                &nbsp;&nbsp;&nbsp;&nbsp;
                <TR>
                    <TD ALIGN="CENTER" COLSPAN=2> <INPUT CLASS="Table_Blue" TYPE="submit" NAME="Submit" VALUE="SEARCH YOUR PROJECT"></TD>
                </TR>
    </TABLE>
</FORM>

Here is the code for DisplayProject. asp (Equivalent to your PageTwo.asp):

<%
Dim strGDNIEngineer
Dim strCompleteDate
Dim Conn
Dim strSQL
Dim RS
 
strProjectID = trim(Request.Form("ProjectID"))
strGDNIEngineer = trim(Request.Form("GDNIEngineer"))
strRequestorName = trim(Request.Form("RequestorName"))
strCompleteDate = trim(Request.Form("CompleteDate"))
 
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("PTSystem.mdb")
select case Request.Form("SearchType")
    case "1"
        strSQL = "SELECT * FROM PTSProjects WHERE ProjectID= '"Request.Form("SearchWith") & "'"
    case "2"
        strSQL = "SELECT * FROM PTSProjects WHERE GDNIEngineer = '"Request.Form("SearchWith") & "'"
    case "3"
        strSQL = "SELECT * FROM PTSProjects WHERE RequestorName = '"Request.Form("SearchWith") & "'"
    case Else
        Response.Write("Please select a search type!")
        Response.End
end select
 
RS.Open strSQL, Conn
 
If RS.EOF Then
   Response.Redirect("SearchProject.asp?error=Sorry ... Your Project ID does not exist in our database.  Please try again.")
End If
%>

..... SOME HTML CODE .... Below is the portion of the code that goes in the middle of the HTML code of the DisplayProject. asp file. If you can give me your email, I can send the two files to you.

<%
 
WHILE NOT RS.EOF 
 
 
Response.Write("<TR>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("ProjectID") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("RequestorName") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("GDNIEngineer") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4""><A HREF=ViewProject.asp?ProjectID=" & RS("ProjectID") & ">View</A></FONT></TD>")
If Request.Cookies("AccessLevel")= "Other" Then
    Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4"">&nbsp;</FONT></TD>")
    Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4"">&nbsp;</FONT></TD>")
Elseif Request.Cookies("AccessLevel")= "Engineer" Then
    Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4""><A HREF=EditProject.asp?ProjectID=" & RS("ProjectID") & ">Edit</A></FONT></TD>")
    Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4"">&nbsp;</FONT></TD>")
Elseif Request.Cookies("AccessLevel")= "Admin" Then
    Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4""><A HREF=EditProject.asp?ProjectID=" & RS("ProjectID") & ">Edit</A></FONT></TD>")
  Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""4""><A onclick='return ConfirmDelete()'HREF=DeleteProject.asp?ProjectID=" & RS("ProjectID") & ">Delete</A></FONT></TD>")
End If
 
Response.Write("</TR>")
 
RS.MoveNext
Wend
 
 
RS.Close
Conn.Close
set Conn = nothing
set RS = Nothing
%>

[hotflash]

Hi Master CroCrew,

Can you help me here? Thanks once again for your help.

[CroCrew]

Hi CroCrew,

I have transformed your code into the real environment but it does not show anything. Please help and thanks for your outstanding support.

Below is the SearchProject.a sp (Equivalent to your PageOne.asp):<F ORM ID="xForm" NAME="xForm" METHOD="post" ACTION="Display Project.asp">

<TABLE ALIGN=CENTER BORDER=0 CELLSPACING=2 CELLPADDING=2 BGCOLOR="#FFFFF F">
<TR>
<TD>
<FONT FACE="HELVETICA ,HELV,ARIAL" SIZE=2>Please Select Your Search: </FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;<FONT FACE="HELVETICA ,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchTyp e" VALUE="1"> Project ID </FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;<FONT FACE="HELVETICA ,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchTyp e" VALUE="2"> GDNI Engineer </FONT><BR>
&nbsp;&nbsp;&nb sp;&nbsp;&nbsp; &nbsp;<FONT FACE="HELVETICA ,HELV,ARIAL" SIZE=2><INPUT TYPE="radio" NAME="SearchTyp e" VALUE="3"> Requestor's Name </FONT><BR>
<BR>
<INPUT TYPE="TEXT" SIZE="39" NAME="SearchWit h">
</TD>
<BR>
</TR>
&nbsp;&nbsp;&nb sp;&nbsp;
<TR>
<TD ALIGN="CENTER" COLSPAN=2> <INPUT CLASS="Table_Bl ue" TYPE="submit" NAME="Submit" VALUE="SEARCH YOUR PROJECT"></TD>
</TR>
</TABLE>
</FORM>

Here is the code for DisplayProject. asp (Equivalent to your PageTwo.asp):


<%
Dim strGDNIEngineer
Dim strCompleteDate
Dim Conn
Dim strSQL
Dim RS

strProjectID = trim(Request.Fo rm("ProjectID") )
strGDNIEngineer = trim(Request.Fo rm("GDNIEnginee r"))
strRequestorNam e = trim(Request.Fo rm("RequestorNa me"))
strCompleteDate = trim(Request.Fo rm("CompleteDat e"))

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Set RS = Server.CreateOb ject("ADODB.Rec ordset")

Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & Server.MapPath( "PTSystem.m db")
select case Request.Form("S earchType")
case "1"
strSQL = "SELECT * FROM PTSProjects WHERE ProjectID= '"Request.Form( "SearchWith ") & "'"
case "2"
strSQL = "SELECT * FROM PTSProjects WHERE GDNIEngineer = '"Request.Form( "SearchWith ") & "'"
case "3"
strSQL = "SELECT * FROM PTSProjects WHERE RequestorName = '"Request.Form( "SearchWith ") & "'"
case Else
Response.Write( "Please select a search type!")
Response.End
end select

RS.Open strSQL, Conn

If RS.EOF Then
Response.Redire ct("SearchProje ct.asp?error=So rry ... Your Project ID does not exist in our database. Please try again.")
End If
%>

..... SOME HTML CODE .... Below is the portion of the code that goes in the middle of the HTML code of the DisplayProject. asp file. If you can give me your email, I can send the two files to you.

<%

WHILE NOT RS.EOF


Response.Write( "<TR>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""2"">" & RS("ProjectID" ) & "&nbsp;</FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""2"">" & RS("RequestorNa me") & "&nbsp;</FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""2"">" & RS("GDNIEnginee r") & "&nbsp;</FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4""><A HREF=ViewProjec t.asp?ProjectID =" & RS("ProjectID" ) & ">View</A></FONT></TD>")
If Request.Cookies ("AccessLevel") = "Other" Then
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4"">&nbs p;</FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4"">&nbs p;</FONT></TD>")
Elseif Request.Cookies ("AccessLevel") = "Engineer" Then
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4""><A HREF=EditProjec t.asp?ProjectID =" & RS("ProjectID" ) & ">Edit</A></FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4"">&nbs p;</FONT></TD>")
Elseif Request.Cookies ("AccessLevel") = "Admin" Then
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4""><A HREF=EditProjec t.asp?ProjectID =" & RS("ProjectID" ) & ">Edit</A></FONT></TD>")
Response.Write( "<TD><FONT FACE=""HELVETIC A,HELV,ARIAL"" COLOR=""#000000 "" SIZE=""4""><A onclick='return ConfirmDelete() 'HREF=DeletePro ject.asp?Projec tID=" & RS("ProjectID" ) & ">Delete</A></FONT></TD>")
End If

Response.Write( "</TR>")

RS.MoveNext
Wend


RS.Close
Conn.Close
set Conn = nothing
set RS = Nothing
%>

Check your PMs (Private Message).