ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

Searching a database

Discuss Searching a database in the ASP Programming forum on Dev Shed.
Searching a database ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 7th, 2003, 09:58 PM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
Searching a database
I want to search my database using a person's name. My names are separated into 'First' and 'Last' name fields.

The SQL I'm using so far works if you search only by the first name, or only by the surname, but if you enter both names, it comes back with no results.

How do you do it so that it will work in all cases?

Here's my code:

Sql = "SELECT * FROM kbs, authors WHERE authors.authorID = kbs.author"

If Request.Form("TypeSearch") = "author" Then
Sql = Sql & " AND (authors.authorLastName LIKE '%" & Request.Form("kbsSearch") & "%' OR authors.authorFirstName LIKE '%" & Request.Form("kbsSearch") & "%')"
End If

set rs = Server.CreateObject("ADODB.Recordset")
set rs=conn.execute(Sql)
Reply With Quote
  #2  
Old October 8th, 2003, 12:50 AM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
You need to split the kbsSearch into a first and lastname variable.
Then use these variables in your sql.

The next question, what if only one name is entered?
When use the vb split command, it creates an array of the splitted "words",
if the number of elements in this array is 1, then only one word was entered, thus set both the firstname and lastname variables to this word...

Should work in theory
__________________
How can I soar like an eagle when
I'm flying with turkey's?
Reply With Quote
  #3  
Old October 8th, 2003, 01:34 AM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
I tried this:

If Request.Form("TypeSearch") = "author" Then
nameArray = split(TypeSearch, " ")
firstName = nameArray(0)
lastName = nameArray(1)
if count(nameArray) = 1 then
Sql = Sql & " AND (authors.authorLastName LIKE '%" & Request.Form("kbsSearch") & "%' OR authors.authorFirstName LIKE '%" & Request.Form("kbsSearch") & "%')"
else
Sql = Sql & " AND (authors.authorLastName LIKE '%" & lastName & "%' OR authors.authorFirstName LIKE '%" & firstName & "%')"
End If
End If


but I got an Subscript out of range: '[number: 0]' error message.
I'm guessing that my 'nameArray =' line isn't right. Should it be set to split 'TypeSearch' or 'author' and am I right to be splitting on "(space)" or do I have to enter an integer that represents that character?
Reply With Quote
  #4  
Old October 8th, 2003, 01:42 AM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
try:
nameArray = split(Request.Form("kbsSearch")," ")
Reply With Quote
  #5  
Old October 8th, 2003, 01:51 AM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
Now my error message has changed from [number: 0] to [number: 1].

Also, I wasn't sure about this line:

if count(nameArray) = 1 then

being the right way to tell if my array has only one value or not and when I try a two word search now I get Type mismatch: 'count' .
Reply With Quote
  #6  
Old October 8th, 2003, 01:57 AM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Better yet....

Code:
If Request.Form("TypeSearch") = "author" Then 
    nameArray = split(Request.Form("kbsSearch"), " ") 
    if IsArray(nameArray) and UBound(nameArray) > 1 Then
        firstName = nameArray(0) 
        lastName = nameArray(1) 
        Sql = Sql & " AND (authors.authorLastName LIKE '%" & lastName & "%' OR authors.authorFirstName LIKE '%" & firstName & "%')" 
    else
        Sql = Sql & " AND (authors.authorLastName LIKE '%" & Request.Form("kbsSearch") & "%' OR authors.authorFirstName LIKE '%" & Request.Form("kbsSearch") & "%')" 
    End If 
End If


I forgot about asp built-in type checking.

See how you go.
Reply With Quote
  #7  
Old October 8th, 2003, 02:03 AM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
That returned a list of everything in the database (good thing it's not too big!)
Reply With Quote
  #8  
Old October 8th, 2003, 02:06 AM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Ok, so the first bit works the
Next, I would start some debugging, such as what values are being stored in first and lastname

i.e. Response.Write lastName & "-" & firstName

If either of these come up blank, then you'll get all records back.
Also it might be worth changing the sql to look for both first and last as follows
Code:
Sql = Sql & " AND (authors.authorLastName LIKE '%" & lastName & "%' AND authors.authorFirstName LIKE '%" & firstName & "%')"


We'll get there
Reply With Quote
  #9  
Old October 8th, 2003, 05:18 PM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
Quote:
Originally posted by mohecan
i.e. Response.Write lastName & "-" & firstName

If either of these come up blank, then you'll get all records back.


Yeah, it looks like my fields are coming up blank.
Reply With Quote
  #10  
Old October 8th, 2003, 06:20 PM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
What is the value of "kbsSearch" that is coming through?

Response.Write Request.Form("kbsSearch")
Reply With Quote
  #11  
Old October 8th, 2003, 07:39 PM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
I've been fiddling around with it for a while, but I'm not getting anything through when I <% Response.Write Request.Form("kbsSearch") %>.

But how can that be - I know it's reading values from the form when I do a search on title or even on author when it's only looking for one word?

I also did a quick test on whether the set up of my array was working properly:

Code:
<%
	dim terms
	terms = ("My Name")
	dim nameArray
	nameArray = split(terms, " ") 
    'if IsArray(nameArray) and UBound(nameArray) > 1 Then
        firstName = nameArray(0) 
        lastName = nameArray(1)
	'End If
	response.write(firstName) & "<br>"
	response.write(LastName)
%>


and I got results back when I commented out the if IsArray line, but I guess my problem's before it even gets that far, because it's not reading anything into the array?
Reply With Quote
  #12  
Old October 8th, 2003, 07:42 PM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Try using the split command without the delimiter

i.e. split(terms)

I've just had a look at DevGuru - Split and found that split, by default, works on spaces, unless directed otherwise.

See how you go...
Reply With Quote
  #13  
Old October 8th, 2003, 10:03 PM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
I'm a bit closer, but still not quite there.

Code:
Dim searchTerm
searchTerm = Request.Form("kbsSearch")
Dim nameArray
nameArray = split(searchTerm) 

If Request.Form("TypeSearch") = "author" Then
	if IsArray(nameArray) and UBound(nameArray) > 1 Then
	'response.write(nameArray(0))
	Sql = Sql & " AND (authors.authorLastName LIKE '%" & nameArray(1) & "%' OR authors.authorFirstName LIKE '%" & nameArray(0) & "%')"

else
	Sql = Sql & " AND (authors.authorLastName LIKE '%" & Request.Form("kbsSearch") & "%' OR authors.authorFirstName LIKE '%" & Request.Form("kbsSearch") & "%')"
End If
End If


Now, this works if I enter just one search term (either first or last name), but brings up the "we did not find any records" message if I enter a two word search.
I tried commenting out the query and inserting the response.write(nameArray(0)) and got the same thing. But if I put the response.write ahead of the If Request.Form("TypeSearch") it prints out what was entered.
If I comment out the if IsArray(nameArray) and UBound(nameArray) > 1 Then line the search works for two words but brings up an error message like I originally had (see first post) for just a one word search.
Reply With Quote
  #14  
Old October 8th, 2003, 10:23 PM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Try this:
Code:
Dim searchTerm
searchTerm = Request.Form("kbsSearch")
Dim nameArray
nameArray = split(searchTerm) 

If Request.Form("TypeSearch") = "author" Then
    'if nameArray has two elements (upper bound =1) then we have
    'a first and last name, if we don't have two elements, something was wrong
    'in the split (ie we got more names)
    if IsArray(nameArray) and UBound(nameArray) = 1 Then
        'response.write(nameArray(0))
        Sql = Sql & " AND (authors.authorLastName LIKE '%" & nameArray(1) & "%' OR authors.authorFirstName LIKE '%" & nameArray(0) & "%')"
    else
        Sql = Sql & " AND (authors.authorLastName LIKE '%" & Request.Form("kbsSearch") & "%' OR authors.authorFirstName LIKE '%" & Request.Form("kbsSearch") & "%')"
    End If
End If
Reply With Quote
  #15  
Old October 8th, 2003, 10:39 PM
Tyssen Tyssen is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2003
Location: Brisbane
Posts: 129 Tyssen User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 31 m 55 sec
Reputation Power: 5
Thumbs up
Yep, changing the 0 to 1 did it!!!

Thanks for all your help mate!
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Searching a database

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump