need help with this search page

needhelp26 · 07-08-2004, 12:17 AM

I have a search page and the values searched are loc and title. I have a dept table which has these entries and when users search, they search the dept table for results. The titles in dept table are:

Receptionist
Customer Service Assistant
Front Desk Clerk
Data Entry Clerk
Computer Operator
Data Entry

when the users select loc as 'North Carolina' and title as 'Receptionist', I am trying to bring all the results related to 'Receptionist' such as :

Customer Service Assistant
Front Desk Clerk

and for 'Data Entry Clerk':

Computer Operator
Data Entry

etc.,etc.,

I am trying to use a Select Case to implement this search such as:

Code:

Select Case title 
Case "A" 
A = "Receptionist" 
A = "Front Desk Clerk" 
A = "Customer Service Assistant" 
Case "B" 
B = "Data Entry Clerk" 
B = "Data Entry" 
B = "Computer Operator" 
End Select

And my code doesn't seem to work. Can someone please let me know whether my approach to the search is right, if not what would be the best way to implement this search? Thanks in advance for any help provided.

Bullschmidt · 07-08-2004, 04:46 AM

Example on a page receiving a post of a SQL statement based on two listboxes (Rep and Customer) that might each have a value of "" (i.e. blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And to see something like this in action you can do the following:
- Go to http://www.bullschmidt.com/login.asp (the ASP Web database demo's login page)
- Click the Continue button
- On the Main Menu click the Invoices Edit button
- On the Invoices Edit Search Dialog's Customer listbox notice that you can choose a rep and/or customer and then click the Edit button to see all the invoices for that rep and/or customer

needhelp26 · 07-08-2004, 01:38 PM

Here is my full coding:

Here is my code:

<%

Dim MyConn, SQL, RS campus, title, A,B

A = "Receptionist"
B = "Data Entry"

campus = request.form("campus")
title = request.form("title")

If title = "A" Then
A = "Receptionist"
Elseif title = "A" Then
A = "Customer service Assitance"
Elseif title = "A" Then
A = "Front Desk Clerk"
End If

If title = "B" Then
B = "Data Entry"
Elseif title = "B" Then
B = "Computer Operator"
End If

SQL = "Select campus, title, hours from Mytable where campus Like '"&%campus%&"' And title Like '"&%title%&"'"

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "MyConnection"

Set RS = Server.Createobject("Adodb.Recordset")

Set RS=MyConn.Execute (sql)

Response.Write "<center><table border=""0"" width='50%'>"

If RS.EOF Then
Response.write "No records were found for : "
Response.write"<font color=""cc3300"">"
Response.write campus & "," & title
Response.write"</font>"
Else
Do While Not RS.EOF
Response.write"<tr bgcolor=silver >"
Response.write "<td >" & RS("campus") & "</td>"
Response.write"<td >" & RS("title") & "</td>"
Response.write"<td >" & RS("hours") & "</td>"
Response.write"</tr>"

RS.Movenext

Loop
RS.Close
Response.write "</table>"
End If
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
The above code is search page code which searches the dept table to find the information. The dept table have all detailed information such as Receptionist, Customer Service Assistant, and Front Desk Assistant. But the search criteria in search page has only Receptionist in the drop down list, which it should be able to pull all records from the dept table related to receptionist. That is why I am doing a Select Case or even if statement to let program know that it has to pull. I am kind of stuck in that part.

In your example, "select * mytable where (1=1)" what does 1=1 do?

Code:

strSQL = "SELECT * MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

By looking at my code, can you please tell how I can improve my coding in order to do the stuff I want to do.

Bullschmidt · 07-08-2004, 11:32 PM

<<
In your example, "select * mytable where (1=1)" what does 1=1 do?
>>

Well actually I just edited my above post to now include the word FROM in the SQL statement. So the code now looks like this:

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

glenngv · 07-09-2004, 07:06 AM

Quote:

Originally Posted by Bullschmidt

And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

You can solve it without 1=1 like this:

Code:

dim whereClause
' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE "
If Rep <> "" Then
  whereClause="(Rep='" & Rep & "')"
End If
If Customer <> "" Then
   if whereClause <> "" then
      whereClause = whereClause & " AND (Customer='" & Customer & "')"
   else
       whereClause = "(Customer='" & Customer & "')"
   end if
End If
strSQL = strSQL & whereClause

needhelp26 · 07-09-2004, 12:34 PM

glenngv,

I have to change only my sql statement, the rest stays the same, right? That is the Select Statement because how will the problem know to pull all records based on 'Receptionist' or sql statement provided by you and Bullschmidt does all that I want to do? Please explain. Thanks.

glenngv · 07-09-2004, 01:01 PM

Yes, the rest stays the same. Just test it, I just simplified Bullschmidt's script.

needhelp26 · 07-10-2004, 04:00 PM

Thankyou. I will test it and see.

needhelp26 · 07-14-2004, 02:59 AM

I tried testing my search page, and it is not doing what I want it to do. That is if I select 'Receptionist' then it should display all the results related to 'Receptionist' - Customer Service Assistant, Front Desk Clerk, etc from the database. What it does right now, if the chosen title is receptionist from the drop down list from the first page, then it shows 'No record found for receptionist' even if customer service assistant, or front desk clerk are there in the database.

glenngv · 07-14-2004, 04:10 AM

Response.write the SQL statement to see if it's correct as you expect it and then try running it in the Query Analyzer to see if there will be records retrieved.

strSQL = strSQL & whereClause
response.write strSQL

That's how you debug database-driven codes.

needhelp26 · 07-14-2004, 04:42 AM

I did response.write strsql and got the response as title='receptionist'. Database used MS Access.

glenngv · 07-14-2004, 09:40 AM

Can you post the whole SQL statement? And did you try executing it in the SQL View of Access to verify that it retrieves records?

needhelp26 · 07-14-2004, 12:19 PM

<%

Dim MyConn, SQL, RS loc, title, A,B, whereclause

A = "Receptionist"
B = "Data Entry"

loc= request.form("loc")
title = request.form("title")

Select Case title
case "A"
A = "Receptionist"
A = "Front Desk Assistant"
A = "Customer Service Assistant"
case "B"
B = "Data Entry"
B = "Computer Operator"
End Select

SQL = "SELECT * FROM MyTable WHERE "
If title <> "" Then
whereClause="(title='" & title & "')"
End If
If loc <> "" Then
if whereClause <> "" then
whereClause = whereClause & " AND (loc='" & loc & "')"
else
whereClause = "(loc='" & loc & "')"
end if
End If
SQL = SQL & whereClause

'database connection
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "MyConnection"

Set RS = Server.Createobject("Adodb.Recordset")

Set RS=MyConn.Execute (sql)

'display of records
Response.Write "<center><table border=""0"" width='50%'>"

If RS.EOF Then
Response.write "No records were found for : "
Response.write"<font color=""cc3300"">"
Response.write loc & "," & title
Response.write"</font>"
Else
Do While Not RS.EOF
Response.write"<tr bgcolor=silver >"
Response.write "<td >" & RS("loc") & "</td>"
Response.write"<td >" & RS("title") & "</td>"
Response.write"</tr>"

RS.Movenext

Loop
RS.Close
Response.write "</table>"
End If
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>

needhelp26 · 07-15-2004, 01:15 AM

Can someone please help? I need desperate help. If someone can come forward and check my coding and tell what I am missing to implement this search? I am trying to bring all the related fields from the database by assigning a value for each title.

needhelp26 · 07-16-2004, 03:27 AM

can someone take a look at my code and let me know what may be wrong? I don't know why it is not displaying as specified? Is there a problem with Select Statement or the SQL Statement? Thanks.