Simple Search Query

[dwarvenmilitia]

Hi...

I have a small problem and I know it will be simple as anything. I have a text box and if something is entered it ammends the SQL query with the code below. The thing is I want it to search dancers.Name as well as dancers.Surname with the same string of text from the text box. (This is ASP and Access DB I'm working with).

I copied the SQL and entered it under the first but changed it to dancers.Name but it stuffs up any search by returning no values.

This is the code which works fine for a surname but a name field has to be searched eith the same 'varText' string.

If (Request.Form("Text") <> "") Then 
strSQL = strSQL & " AND dancers.Surname LIKE '%" & varText & "%' "
End If

Any help would be greaetly appreciated.

Life is like a box of chocolates. When things get hot it all turns to mush.

 

[jbenson001]

Can you post all of your code that creates the sql string?

Jim

 

[dwarvenmilitia]

Hey Jim.

Cause I have heaps of if's... cause it is about a 20 variable search.

The start of the SQL code goes like this.

strSQL = "SELECT Surname, Name, Image_Thumb, ID, Online FROM dancers WHERE dancers.Online=Yes "

So hence if text is entered into text box from a form named "Text" (This is in another page with POST action to this one), such as below...

If (Request.Form("Text") <> "") Then
strSQL = strSQL & " AND dancers.Surname LIKE '%" & varText & "%' "
End If

I was thinking of something along the lines of this.

If (Request.Form("Text") <> "") Then
strSQL = strSQL & " AND dancers.Surname LIKE '%" & varText & "%' "
strSQL = strSQL & " AND dancers.Name LIKE '%" & varText & "%' "
End If

It doesn't come up with an error but it stops returning any strings entered into the box.

You are unique... Just like everyone else.

 

[jbenson001]

It shouldn't be a problem from what I see. What I would do is to print out strSQL, and don't execute it. Then run it in Access to see if the syntax is OK.

Jim

 

[dwarvenmilitia]

I can't write it in access as I have to enter names cos varText are from Request.Form text inputs.

If I search with only one name and like function it works fine... but I need it to check both not just one.

 

[jbenson001]

What I am saying is to trace through the page. Put a stop on the line just after strSQL is completly populated. Copy the value in strSQL and paste it into Access and run it.

 

[dwarvenmilitia]

How can I go about that? I haven't used SQL much before and I'm new to ASP.

Cheers

 

[jbenson001]

Get the value of strSQL before you execute it, cut and past it into an access query and run it.