Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

can you help revamp this?

Status
Not open for further replies.
chidi

chidi

Technical User
Mar 24, 2003
42
0
0
NG
sorry I am too stressed out to find the thread I sent earlier today.
Here is what I am trying to do:
1, per my boss, we want to give our users to ability to do a search based on either firstname, lastname, posnumber, jobtitle, jobgrade, hiredate, and training and posNumber in one table must match posnumber in all other tables selected in the join.
The select and join parts, I believe are working.
However, in my whereclause, we want to give our users various textboxes to search to entire a search criteria.
For instance, search by posnumber------- search by lastname---- etc.
I am having problem with that.
We also want to say since a required course (courseType) has a yes or no answer, we want to say if the required course has been taken by an employee, automatically put a checkmark on that checkbox.
Here is what I have so.
It is not even close to doing what we want.
A kind help will be greatly appreciated.
GaryC123 started to help on this last night but I am completely off track due to beginner experience.

<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<html>
<head>
<title>Search Results!</title>
</head>
<body bgcolor=&quot;steelBLUE&quot;>
<%
Dim SqlStmt

Set dbGlobalWeb = Server.CreateObject(&quot;ADODB.Connection&quot;)
dbGlobalWeb.Open &quot;DSN=aTrac&quot;
SqlStmt = &quot;SELECT Training.TrainingID, Training.Courses,&quot; &_
&quot;Training.CourseType, Training.PosNumber,Training.CourseStatus,&quot; &_
&quot;Reason.Reasons,Personnell.LastName,Personnell.FirstName,&quot; &_
&quot;Personnell.MiddleInitial,Personnell.HireDate,Personnell.JobTitle,&quot; &_
&quot;Personnell.DeptAssignedTo,Personnell.JobGrade,Personnell.AcctFund &quot; &_
&quot;FROM Personnell, Training,AccountingFund,Grading,Reason &quot;

If Request.Form(&quot;TypeSearch&quot;) = &quot;LastName &quot; Then
SqlStmt = SqlStmt & &quot; WHERE LastName LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; &_
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

If Request.Form(&quot;TypeSearch&quot;) = &quot;FirstName &quot; Then
SqlStmt = SqlStmt & &quot; WHERE FirstName LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; & _
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

If Request.Form(&quot;TypeSearch&quot;) = &quot;JobTitle &quot; Then
SqlStmt = SqlStmt & &quot; WHERE JobTitle LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; & _
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

If Request.Form(&quot;TypeSearch&quot;) = &quot;JobGrade &quot; Then
SqlStmt = SqlStmt & &quot; WHERE JobGrade LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; & _
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

If Request.Form(&quot;TypeSearch&quot;) = &quot;TrainingID &quot; Then
SqlStmt = SqlStmt & &quot; WHERE TrainingID LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; & _
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

If Request.Form(&quot;TypeSearch&quot;) = &quot;PosNumber &quot; Then
SqlStmt = SqlStmt & &quot; WHERE Personnell.PosNumber LIKE '%&quot; & Replace(strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot; &_
SqlStmt = SqlStmt & &quot;AND Personnell.PosNumber = Training.PosNumber &quot; &_
SqlStmt = SqlStmt & &quot;AND Training.PosNumber = Reason.PosNumber &quot; &_
SqlStmt = SqlStmt & &quot;AND Reason.PosNumber = AccountingFund.PosNbr &quot; &_
SqlStmt = SqlStmt & &quot;AND AccountingFund.PosNbr = Grading.PosNbr &quot; &_
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;
End If

Set rsGlobalWeb = Server.CreateObject(&quot;ADODB.Recordset&quot;)
rsGlobalWeb.Open SqlStmt, dbGlobalWeb, 3,3
%>

<% If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then %>
<h2 align=&quot;center&quot;>We did not find a match!</h2>
<%Else%>
<%If Not rsGlobalWeb.BOF Then%>
<h2>Here are the results of your search:</h2>

<table BORDER=&quot;0&quot; width=&quot;100%&quot; cellpadding=&quot;3&quot;>

<tr>

<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Last Name </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>First Name </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Middle Initial </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Position Number </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Job Title </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Job Grade </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Accounting Fund </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Hire Date </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Course Number </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Course Description </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Required? </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Course Status </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Reason Training Not Taken </font></th>
<th bgcolor=&quot;#800000&quot;><font face=&quot;Arial&quot; color=&quot;#FFFFFF&quot;>Dept Assigned To </font></th>
</tr>
<% Do While Not rsGlobalWeb.EOF %>
<tr>

<td><%=rsGlobalWeb(&quot;LastName&quot;)%></td>
<td><%=rsGlobalWeb(&quot;FirstName&quot;)%><td>
<td><%=rsGlobalWeb(&quot;MiddleInitial&quot;)%><td>
<td><%=rsGlobalWeb(&quot;posNumber&quot;)%><td>
<td><%=rsGlobalWeb(&quot;JobTitle&quot;)%></td>
<td><%=rsGlobalWeb(&quot;JobGrade&quot;)%></td>
<td><%=rsGlobalWeb(&quot;acctFund&quot;)%></td>
<td><%=rsGlobalWeb(&quot;HireDate&quot;)%></td>
<td><%=rsGlobalWeb(&quot;TrainingID&quot;)%></td>
<td><%=rsGlobalWeb(&quot;Courses&quot;)%></td>
<td><%=rsGlobalWeb(&quot;CourseType&quot;)%></td>
<td><%=rsGlobalWeb(&quot;CourseStatus&quot;)%></td>
<td><%=rsGlobalWeb(&quot;reasons&quot;)%></td>
<td><%=rsGlobalWeb(&quot;DeptAssignedTo&quot;)%></td>



</tr>

<%
rsGlobalWeb.MoveNext
Loop
%>

</table>

<%End If%>

<%End If%>


<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>
</body>
</html>
 
Sort by date Sort by votes
I would change the type search part to this:

Code: 
Dim strTypeSearch, strSearchHere

strTypeSearch = Request.Form(&quot;TypeSearch&quot;)

  '# call function with type name
  BuildSQL(strTypeSearch)

  '# Function to build SQL statment depending on select..
  Function BuildSQL(strSearchHere)
   
  SqlStmt = SqlStmt & &quot; WHERE &quot; & strSearchHere & &quot; LIKE '%&quot; & Replace (strSearch, &quot;'&quot;, &quot;''&quot;) & &quot;%' &quot;       &_
Request.Form(&quot;DaInBox&quot;) & &quot;%'&quot;

  End Function
www.vzio.com
ASP WEB DEVELOPMENT



 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
74
DiDYRO_
DiDYRO_
Ambientguitar
  • Locked
  • Question
Issue with this not calling the Sub in Classic ASP page
Replies
5
Views
120
MarkSweetland
MarkSweetland
Sai259
  • Locked
  • Question
Hello, Could you please provide any
Replies
2
Views
67
ChrisHirst
ChrisHirst
RonMc
  • Locked
  • Question
classic asp - getting dropdown selection and reposting - help
Replies
1
Views
89
ChrisHirst
ChrisHirst
leifoet
  • Locked
  • Question
How can I migrate a certain FP code to ASP Classic
Replies
6
Views
74
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top