SQL Database recordsets?

Question

I've created 3 recordsets where the user can search for three different columns separately from the table. How do I make it so the user can type three attributes into the box which then combines to search down the column to narrow down the results. They are instructed on what keywords to search to no variables needed as they know what data is in the table.

For example

Box one Name : Wayne

When submitted finds all the names containing Wayne

Box 2 Last Name Rooney

When submitted finds all the names containing Rooney

Box 3 Age 26

When submitted finds all the ages containing 26

How do I combine the submit button so it searches for all three to narrow the search

Answer 1

That depends on whether the user is required to supply criteria for all 3 or not. If not, you should have logic to execute one of the 7 possible query combinations depending on :



1. Only Box 1

SELECT FROM someTable

WHERE firstName LIKE "%%"



2. Only Box 2

SELECT FROM someTable

WHERE lastName LIKE "%%"



...



6. All 3

SELECT FROM someTable

WHERE firstName LIKE "%%" AND

lastName LIKE "%%"

AND age = CAST(, int)



7. None (get everything)

SELECT FROM someTable



Just remember that you should inspect/massage the text entry fields to prevent SQL injection attacks or use parameterized queries

Answer 2

Hello,

Please try the following solution which first requires the creation of an SQL stored procedure



create procedure dbsp_UserSearch (

@firstname varchar(10),

@lastname varchar(10),

@age smallint

)

as

select *

from UserSearch

where

( firstname like '%' + @firstname + '%' OR @firstname is null )

AND

( lastname like '%' + @lastname + '%' OR @lastname is null )

AND

( age = @age OR @age is null )



Then you can call this SP using below SP calls :

exec dbsp_UserSearch 'wayn', null, null

exec dbsp_UserSearch null, 'roon', null

exec dbsp_UserSearch null, null, 36

exec dbsp_UserSearch 'wayn', null, 36



I hope that helps

Answer 3

Pseudo code:



Create initial SQL select = "SELECT * FROM myTable"

If there's an entry in any of the 3 search boxes add " WHERE " to the SQL string

If there's an entry in box 1 add "MyTable.Name = " plus the value from box 1 plus "AND"

If there's an entry in box 2 add "MyTable.LastName =" plus the value from box 2 plus "AND"

If there's an entry in box 3 add "MyTable.Age= " plus the value from box 3 plus "AND"

If the SQL string ends in "AND" remove it from the string.

Run SQL statement



You can replace = with LIKE, or you can get fancy and use the same kind of logic to let users select the type of comparison they want to use.