...

View Full Version : need help with this search page



needhelp26
07-08-2004, 01: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:


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, 05: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, 02: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?


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-09-2004, 12:32 AM
<<
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, 08:06 AM
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:


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, 01: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, 02:01 PM
Yes, the rest stays the same. Just test it, I just simplified Bullschmidt's script.

needhelp26
07-10-2004, 05:00 PM
Thankyou. I will test it and see.

needhelp26
07-14-2004, 03: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, 05: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, 05:42 AM
I did response.write strsql and got the response as title='receptionist'. Database used MS Access.

glenngv
07-14-2004, 10: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, 01: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, 02: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, 04: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.

Roy Sinclair
07-16-2004, 06:35 PM
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
response.write "<hr>" & SQL & "<hr>"


As requested above, display the SQL statement that your code is generating. If the problem isn't obvious when you see the SQL then cut and paste that SQL into the tools in Access and try running it to see what Access doesn't like about it.

These are basic debugging techniques you need to learn, they'll make you more effective.

needhelp26
07-20-2004, 02:31 PM
I have another question pertaining to the same search page. In the result page, is it possible to add a checkbox for each result, and when check marked on that particular result, should go and fill a form application. This search page is linked to the form application, so when the users select, should fill the form. I am not sure whether this kind of function is possible, and if it is possible, can someone please let me know how may I implement? Thanks.

Bullschmidt
07-20-2004, 02:35 PM
Perhaps this may hopefully give you some ideas:

Classic ASP Design Tips - QueryString
http://www.bullschmidt.com/devtip-querystring.asp

Example URL with a querystring:
http://www.mysite.com/mydir/mypg.asp?myvar1=hey&myvar2=ho

Because a variable might contain some odd characters like spaces, it's usually a good idea to use Server.URLEncode when creating a querystring from a variable:
<a href="http://www.mysite.com/mypage?id=<%= Server.URLEncode(objRS("MyIDFldFromDB")) %>">objRS("MyIDFldFromDB")</a>

And in the page that is opened you can use Request.QueryString("id") to get the value of id.

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 click the Edit button
- On the Invoices Many page notice that each invoice number link includes a querystring such as ?InvID=1064
- Click on any invoice number
- On the Invoices Input page notice that the Web address includes the same querystring

needhelp26
07-20-2004, 03:42 PM
Thankyou but I have done with part of programming, from here whatever I want to select, should go and fill the form. Scenerio:

Form Application - users are filling the application.
Form field - loc and title, lets say jobsearch
Search Page - a link from the Form Application.
Search Result page - for example, they select loc and title as a particular result from the query, that record should be filled in the Form application, that is when they checkmark that record, that data should be filled in the jobsearch field.

needhelp26
07-24-2004, 02:07 AM
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
response.write "<hr>" & SQL & "<hr>"


As requested above, display the SQL statement that your code is generating. If the problem isn't obvious when you see the SQL then cut and paste that SQL into the tools in Access and try running it to see what Access doesn't like about it.

These are basic debugging techniques you need to learn, they'll make you more effective.

I tried writing Responsee.Write to debug, but it is showing as loc="NC" and title="Receptionist", it is not working as I wanted it to be. I also tried to run the query in Access database, but that too didn't work.

My logic is that it should be able to read the variables for title. Right now, it is not reading that part. i.e.

A = Receptionist.

Select case title
case "A"
A = "Receptionist"
A = "Front Desk Operator"
A = "Customer Service Assistant"

End Select

SQL = Sql statement.

How to make variable work for title? Going crazy with this little project, please someone help. Thanks.

glenngv
07-26-2004, 03:24 AM
Try building the SQL statement first in the SQL View of Access so you can test the query at once if it's correct or not. Then once you're sure that it's returning the desired records, you can incorporate it in your ASP code.

needhelp26
07-26-2004, 05:58 PM
I tried testing in SQL View in Access but it is not returning any record if the sql statement as "where title = "receptionist" because there is no record as receptionist in title column and there won't be any since receptionist is a variable assigned word. There can be Customer Service Assistant, Front Desk clerk in the title column but not receptionist. That is what I am trying to do, pulled the records based on the variable assigned work such as receptionist. Am I confusing too much? Sorry I don't know how well to explain.

startbar
07-26-2004, 11:38 PM
needhelp.. you seem to being having lots of problems with this..

do you want some new code for a search page?

this one will work. just email me at halo6ix@hotmail.com if you do.

thanks

needhelp26
07-27-2004, 02:43 AM
Actually, I am not looking into having new code at this time because I have already set up the search very well with the present code and am very comfortable with it, but my only issue is getting the variable to work for the search which I am slogging for a long time. If someone, can come forward to give a helping hand in this one, it will be very greatful.

Startbar, thanks for your help. When I am totally fed up with this, I will email you.

glenngv
07-27-2004, 06:43 AM
Try this:


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

A = Array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = Array("Data Entry Clerk","Computer Operator","Data Entry")

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

select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Clerk"
titleSet = join(B,"','")
end select

if titleSet<>"" then
SQL = "SELECT campus, title, hours FROM Mytable WHERE campus LIKE '%" & campus & "%' AND title IN ('" & titleSet & "')"
else
SQL = "SELECT campus, title, hours FROM Mytable WHERE campus LIKE '%" & campus & "%'"
end if

'debug
response.write "SQL: " & SQL & "<br />"

Set MyConn=Server.CreateObject("ADODB.Connection")
'other codes...

Here's more information on SQL IN (http://www.techonthenet.com/sql/in.htm) function.

needhelp26
07-27-2004, 01:13 PM
Thanks. i will try this but one question. I have some alphabets without an array such C = "Computer Science", C will not have any subset, will that be okay.

glenngv
07-28-2004, 07:14 AM
That's ok. But still you need to define the set as an array so that no code modification is needed.

C = Array("Computer Science")

needhelp26
07-31-2004, 04:40 PM
Thank you so much for taking me to this level. It works but I still have to modify my sql statement. I am not getting a filtered set of records. For example, if I choose "NC" for loc and "Receptionist" for title, I should get only records which are in NC location and all Receptionist related titles. Right now, I am getting only NC but for titles Receptionist I am also getting even Office Assistant, etc which is not in my Array definition for Receptionist. I did response.write SQL, it passing the first if statement and going straight to Else statement which is Sql = "select * from mytable where loc like '%" & loc & "'".

I feel my sql statement should be modified. I tried various changes to the statement but no luck.

glenngv
08-03-2004, 02:54 AM
Are you really sure that the title variable has the value of Receptionist?

title = request.form("title")
Response.write "title:" & title 'debug

Since the SQL statement goes to the "else" part, it means that the title variable doesn't contain Receptionist or Data Entry Clerk which your Select Case statement has.

needhelp26
08-04-2004, 12:48 AM
Thankyou so much for helping me thru this. I am sorry I am not conveying my message properly. title is not a variable, title is a field name which holds all the title values such as Front Desk Clerk, Customer Service Assistant, etc. A, B is a variable assigning to title. So,
If A = Receptionist Then assign A to title and pull all records related to A.

Table spec:

ID
title
loc
etc.,

title values are Front desk Clerk, Customer Service Assistant, Computer Operator, Data Entry Clerk

If Front Desk Clerk can work as Customer Service Assistant, I want to pull both the records when i chose Receptionist. So, I am having this pre-defined titles Receptionist, data Entry Clerk, etc. So Receptionist will hold Customer Service assistant, Front desk Clerk. I am assigning an alphabet for each pre-defined titles:
A - Receptionist
B - Data Entry Operator

This A and B, I want to assign to titles so when I use the word Receptionist, it will pull all the related records which is customer service assistant, front desk clerk,etc.And I have the pre-defined titles listed in my drop-down in the previous page from which is shows result in the second page.

firstpage.asp
<form action="first_page.asp" method="post">
Title:<select name=title >
<option>Receptionist</option>
<option>Data Entry Clerk</option>
</select>
</form>

firstpage.asp is the result page coding which I had posted earlier. Hope this is better explaination of my program.

glenngv
08-04-2004, 09:15 AM
title is a variable. It contains what was selected in the dropdown combobox for title.

title = request.form("title")

The above code gets the selected title and stores it in the title variable.

Try these codes again and post here what output is displayed.


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

A = Array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = Array("Data Entry Clerk","Computer Operator","Data Entry")

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

'debug
response.write "campus: " & campus & "<br />"
response.write "title: " & title & "<br />"

select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Clerk"
titleSet = join(B,"','")
end select

'debug
response.write "titleSet: " & titleSet & "<br />"

if titleSet<>"" then
SQL = "SELECT campus, title, hours FROM Mytable WHERE campus LIKE '%" & campus & "%' AND title IN ('" & titleSet & "')"
else
SQL = "SELECT campus, title, hours FROM Mytable WHERE campus LIKE '%" & campus & "%'"
end if

'debug
response.write "SQL: " & SQL & "<br />"
response.end

needhelp26
08-06-2004, 03:06 PM
I tried response.write for the following:

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

response.write "loc: " & loc & "<br />"
response.write "title: " & title & "<br />"
response.end

Result:

loc: NC
title: Receptionist

Next, I checked:

select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Clerk"
titleSet = join(B,"','")
end select

response.write "titleset: " & titleset & "<br />"
response.end

Result:

titleset:

No values were passed.

I stopped there since the values were not passing, there is not point going to the next step to check.

Why the values are not passing in titleset?

glenngv
08-09-2004, 06:20 AM
Check if the value of the title variable contains spaces. If the value of title is really Receptionist, it should go to case "Receptionist" in the select case statement since they are the same (spelling and case). There may be spaces at the end that we're not seeing. Try doing this:

response.write "length of title: " & len(title) & "<br />"
response.write "title:'" & title & "'<br />"

needhelp26
08-13-2004, 04:56 PM
I tried response.write.

12
title:'Receptionist'

There seems to be no space. what should I do to make this work? I am really frustrated with this one. It has taken a long time to figure out the problem.

glenngv
08-16-2004, 05:00 AM
Can you post the whole code again. There might be other codes affecting the title variable.

needhelp26
08-16-2004, 06:42 PM
Here is my code again.

firstpage.asp




<form action="secondpage.asp" method="post">
<table>
<tr>
<td>Title: <select name=title>
<option>Select One</option>
<option>Receptionist</option>
<option>Data Entry Clerk</option>
</select>
</td>
<td>Location:<select name=loc>
<option>Select One</option>
<option>NC</option>
<option>DC</option>
</select>
</td>
</tr>
</table>
<br />
<input type=submit name=Submit>
</form>


secondpage.asp



<%

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


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

A = array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = array("Data Entry","Computer Operator")

Select case "title"
case "Receptionist"
titlejoin(A,"','")
case "Data Entry Operator"
titlejoin(B,"','")
End Select

If titlejoin <> "" Then
SQL = "Select loc, title from Mytable where loc like '%"&loc&"%' And title IN '"&title&"'"
Else
SQL = "Select loc, title from Mytable where loc Like '%"& loc &"%'"
End If

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 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
%>


I feel that there is some problem with titlejoin because if I comment the if else statement, I am getting an error such as :

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

Command text was not set for the command object
Example:


If titlejoin <> "" Then
SQL = "Select loc, title from Mytable where loc Like '%"&loc&"%' And title IN '"&title&"'"
'Else
'SQL = "Select loc, title from Mytable where loc Like '%"& loc &"%'"
End If

needhelp26
08-18-2004, 03:38 AM
Can anyone see what may be the problem? I need some help. thanks.

glenngv
08-19-2004, 04:24 AM
<%

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

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

A = array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = array("Data Entry","Computer Operator")

Select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Operator"
titleSet = join(B,"','")
End Select

If titleSet <> "" Then
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & titleSet & "')"
Else
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
End If

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

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

Set RS=MyConn.Execute (SQL)

...

%>

needhelp26
08-22-2004, 03:18 PM
I tried the changes in red but no use. do you have a test environment, if you have, can you please load these codes in it, and see whether it is working for you. Thanks.

glenngv
08-23-2004, 03:10 PM
Sorry I don't have a test environment. The only help I could provide is to spot errors in your posted code and it will help you and me if you post again your latest codes and provide details of the error. It will also help a lot if you could provide a link to your page (if possible) or attach your pages here.

needhelp26
08-23-2004, 05:38 PM
Sorry I can't provide a link and there is no point attaching the sample files because what you see here is the same. I haven't made any changes to my code, it is the same as earlier. I know you tried your best to work around with my code, but I really don't know what may be the reason. If someone out there could help, please come forward to sort this problem. thanks.

glenngv
08-24-2004, 05:14 AM
Don't you want to attach the whole files needed in order for me to test them in my side?

needhelp26
08-25-2004, 01:24 PM
First, you said that you don't have a test environment. Secondly, if you want to test, it is easy, you just have to copy and paste in notepad or any editor, and test, why should I attach the files here? Don't you think, it is waste to do so. I just have two files, firstpage and secondpage. If you want, I will post it here, you can copy and paste:

firstpage.asp


<html>
<head>
<title>Testing Search</title>
</head>
<body>
<form action="secondpage.asp" method="post">
<table>
<tr>
<td>Title: <select name=title>
<option>Select One</option>
<option>Receptionist</option>
<option>Data Entry Clerk</option>
</select>
</td>
<td>Location:<select name=loc>
<option>Select One</option>
<option>NC</option>
<option>DC</option>
</select>
</td>
</tr>
</table>
<br />
<input type=submit name=Submit>
</form>
</body>
</html>


secondpage.asp


<%@Language=vbscript%>
<html>
<head>
<title>Result page</title>
</head>
<body>

<%

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

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

A = array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = array("Data Entry","Computer Operator")

Select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Operator"
titleSet = join(B,"','")
End Select

If titleSet <> "" Then
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & titleSet & "')"
Else
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
End If

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 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
%>
</body>
</html>

glenngv
08-26-2004, 06:53 AM
First, you said that you don't have a test environment. Secondly, if you want to test, it is easy, you just have to copy and paste in notepad or any editor, and test, why should I attach the files here? Don't you think, it is waste to do so. I just have two files, firstpage and secondpage. If you want, I will post it here, you can copy and paste:

Can't you say it nicely? :rolleyes:
Don't forget you're the one asking for help here.

needhelp26
08-26-2004, 01:15 PM
I am sorry if I haven't said it in a nice way. But I didn't mean it. I am really frustrated. We all come here to discuss the coding problems and if possible give a solution(and this is a discussion forum), and you have been a great help to me so far, I really appreciate your help.

glenngv
08-26-2004, 01:40 PM
I've tried your code and inserted response.write statements in appropriate locations to keep track the values of variables and it's displaying the expected output. Since I don't have the setup for db, I only tested until the SQL statement.

These are the results when I selected Receptionist as title and NC as location:

title:Receptionist
titleSet:Receptionist','Customer Service Assistant','Front Desk Clerk
SQL:Select loc, title from Mytable where loc like '%NC%' And title IN ('Receptionist','Customer Service Assistant','Front Desk Clerk')

I didn't experience the initial problem you encountered where the titleSet variable is empty.

If you tell me the exact error you're getting, maybe we can finally nail this problem down. Probably the error is in the database part?

PS. I noticed that you have a "Select One" option for Title and Location comboboxes, which means that the user can leave it as that and click the submit button. So your code must be able to check for this and adjust the WHERE clause in the SQL statement accordingly. Something like this:


If titleSet <> "" and loc<>"" Then 'title and location are specified
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & titleSet & "')"
ElseIf titleSet<>"" then 'only title is specified
SQL = "Select loc, title from Mytable where title IN ('" & titleSet & "')"
ElseIf loc<>"" then 'only location is specified
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
Else 'both title and location is not specified, select all records
SQL = "Select loc, title from Mytable"
End If

HTML:


Title: <select name="title">
<option value="">Select One</option>
<option value="Receptionist">Receptionist</option>
<option value="Data Entry Clerk">Data Entry Clerk</option>
</select>
</td>
<td>Location:<select name="loc">
<option value="">Select One</option>
<option value="NC">NC</option>
<option value="DC">DC</option>
</select>

needhelp26
08-27-2004, 03:53 AM
Thankyou.Thankyou. :) I didn't try the sql statement as you have shown now, maybe, if I do as shown here, it will work. Let me try and see. Thankyou again for taking the trouble to test try the code.

needhelp26
09-01-2004, 12:29 AM
how did you get the titleset variable? for me, it didn't work. doing response.write shows emtpy string. that is where the problem for me. And SQL response.write shows the else statement. I don't think it is the database. what could be the possible database issues?

glenngv
09-01-2004, 02:55 AM
I don't know why it didn't work for you. I just copied your codes and inserted some response.writes and executed the page. I selected Receptionist as the title and NC as the location and hit submit and that's it.

needhelp26
09-09-2004, 03:48 AM
Finally, I got it working. But I have another problem. It is not retreiving the data from the database. I tried using the SQL Select statement in Access Sql View and the query worked there but it is not working in the asp page.



title = titleset ' i have included this line of code
If titleSet <> "" and loc<>"" Then 'title and location are specified
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & title & "')"
ElseIf titleSet<>"" then 'only title is specified
SQL = "Select loc, title from Mytable where title IN ('" & title & "')"
ElseIf loc<>"" then 'only location is specified
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
Else 'both title and location is not specified, select all records
SQL = "Select loc, title from Mytable"
End If

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

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

Set RS=MyConn.Execute (SQL)

Response.write (SQL) ' when I response.write, it shows the titleset values
Response.end

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
%>

glenngv
09-09-2004, 04:26 AM
Response.write (SQL) ' when I response.write, it shows the titleset values
Response.end

Did you remove the response.end when you executed it?

Try this code:


<%
...
'title = titleset 'not needed
If titleSet <> "" and loc<>"" Then 'title and location are specified
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & titleSet & "')"
ElseIf titleSet<>"" then 'only title is specified
SQL = "Select loc, title from Mytable where title IN ('" & titleSet & "')"
ElseIf loc<>"" then 'only location is specified
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
Else 'both title and location is not specified, select all records
SQL = "Select loc, title from Mytable"
End If

Response.write (SQL) 'debug

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

Set RS = Server.Createobject("Adodb.Recordset")
RS.open SQL, MyConn, 1, 1

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

If RS.BOF and 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
End If
Response.write "</table>"
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>

I removed the "title = titleset" line that you inserted. That is not needed, it will just add extra work to the server.

You may want to use DSN-less connection (http://www.4guysfromrolla.com/webtech/070399-1.shtml) instead of System DSN. Here's a good resource (http://www.able-consulting.com/ADO_Conn.htm) on connection strings.

needhelp26
09-09-2004, 11:41 PM
I tried removing title = titleset, it was not working, I will try again.Thanks.

glenngv
09-10-2004, 04:21 AM
Just copy and paste the code I posted overwriting the appropriate part of your code then tell me how it goes.

needhelp26
09-11-2004, 11:27 PM
Set RS = Server.Createobject("Adodb.Recordset")
RS.open SQL, MyConn, 1, 1


Can you please tell what the 1, 1 stands for?

BuddhaMan
09-13-2004, 03:55 AM
http://www.w3schools.com/ado/met_rs_open.asp

needhelp26
09-13-2004, 11:26 PM
Buddhaman, thankyou. but the code isn't working. I am not able to figure it out. As glenngv specified, if I remove the title=titleset, it is not displaying the array. Moreover, how will the program know that title is titleset?
Say for example"


A = array("Store Assistant")

Select case title
case "Store Assistant"
titleset = join(A,",")
End Select

title = titleset



If I have just one item as above example, then it works, only it doesn't work when it has multiple values.

Response.write results:


title = titleset
Response.write(title)= ('Receptionist,Customer Service Assistant, Front Desk Clerk')
Response.write(sql) = "select loc, title from mytable where loc Like '%" & loc & "%' and title IN('Receptionist,Customer Service Assistant, Front Desk Clerk')"


Could there be any database issues? I tried copy and paste the SQL from result page to SQL View in Access, first it didn't work. I modified the query such as took the %%sign from the loc and added beginning and ending single quotes to each value such as ('Receptionist','Customer Service Assistant','Front Desk Clerk'), it worked.

glenngv
09-14-2004, 04:12 AM
Let's fix this once and for all.
Just copy and paste this and then tell me how it goes.

firstpage.asp:


<html>
<head>
<title>Testing Search</title>
</head>
<body>
<form action="secondpage.asp" method="post">
<table>
<tr>
<td>Title: <select name="title">
<option value="">Select One</option>
<option value="Receptionist">Receptionist</option>
<option value="Data Entry Clerk">Data Entry Clerk</option>
</select>
</td>
<td>Location:<select name="loc">
<option value="">Select One</option>
<option value="NC">NC</option>
<option value="DC">DC</option>
</select>
</td>
</tr>
</table>
<br />
<input type=submit name=Submit>
</form>
</body>
</html>

secondpage.asp:


<%@Language=vbscript%>
<html>
<head>
<title>Result page</title>
</head>
<body>

<%

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

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

A = array("Receptionist","Customer Service Assistant","Front Desk Clerk")
B = array("Data Entry","Computer Operator")

Select case title
case "Receptionist"
titleSet = join(A,"','")
case "Data Entry Operator"
titleSet = join(B,"','")
End Select

If titleSet <> "" and loc<>"" Then 'title and location are specified
SQL = "Select loc, title from Mytable where loc like '%" & loc & "%' And title IN ('" & titleSet & "')"
ElseIf titleSet<>"" then 'only title is specified
SQL = "Select loc, title from Mytable where title IN ('" & titleSet & "')"
ElseIf loc<>"" then 'only location is specified
SQL = "Select loc, title from Mytable where loc Like '%" & loc & "%'"
Else 'both title and location is not specified, select all records
SQL = "Select loc, title from Mytable"
End If

Response.write "SQL: " & SQL & "<br />" 'debug

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

Set RS = Server.Createobject("ADODB.Recordset")
RS.open SQL, MyConn, 1, 1

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

If RS.BOF and 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
End If
Response.write "</table>"
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
</body>
</html>

needhelp26
09-15-2004, 04:06 AM
Let's fix this once and for all.

At last I got it. Thank you so much for your help all the way along.Thankyou again.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum