Click here to Skip to main content
15,314,282 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear sir
i want i have 10 dropdownlist boxs on a web page. i want to search data if any one select at least on or more than one of given dropdownlist Boxes,,


i want to generate dynamic Sql on base of selected value of Dropdownlist box and find record using such genrated sql..
Please Help to make advance search..


thankyou advance;
Posted
Updated 9-Feb-15 0:23am
v3
Comments
OriginalGriff 9-Feb-15 5:45am
   
And?
What have you tried?
Where are you stuck?
osmo33 9-Feb-15 6:01am
   
i want to make a advance search option where User can select one or more than Dropdownlist and after that click on search button and find record on bases of selected value of droupdownlist boxes from database table..
OriginalGriff 9-Feb-15 6:05am
   
Yes, I gathered that from the original post.

So what have you done so far to achieve this?
What is stopping you going further?
What help do you need?
osmo33 9-Feb-15 6:10am
   
what query should we run to get record for one selection of droupdownlist box or
for selection of more than one...
osmo33 9-Feb-15 6:19am
   
I want to generate dynamic sql query on selection on dropdownlist box.
and search the record on the base of such SQL QUERY.

Please help to do.
..........

"what query should we run to get record for one selection of dropdownlist box or
for selection of more than one..."


It would vary depending on your DB design. If the fields are all in the same table, it's relatively simple:
SQL
SELECT * FROM MyTable WHERE <condition1> OR <Condition2>
Or
SQL
SELECT * FROM MyTable WHERE <condition1> AND <Condition2>

But if they are in different tables, then it gets more complex: potentially a lot more complex.

We can't tell - we don't know what your columns are, your drop downs contain, anything realy. Remember that we can't see your screen, access your HDD, or read your mind! :laugh:

So start by looking at what info you are trying to fetch, and run up queries for various collections of them. It should become fairly obvious with a bit of thinking what you need to do.
   
SQL
--Try this stored proc, pass your required search values form front end by mapping  valid SQL table info.   
 
CREATE PROCEDURE [dbo].[Sp_Studentsearch]  	
	--input paramaters
	@FirstName nvarchar(50) = null,	 
	@Gender nvarchar(50) = null
AS
BEGIN
	--declaring variables
	DECLARE @Query VARCHAR(MAX),
            @WhereQuery VARCHAR(MAX)
      --set query    
      SET @Query = 'SELECT [ID],[FirstName],[LastName],[Gender] FROM [dbo].[Students]'				 
	SET @WhereQuery = 'WHERE 1=1'
	 --search functionality
	 IF ISNULL(@FirstName, '') <> ''
		SET @WhereQuery = @WhereQuery +' AND [FirstName] LIKE' + ''''+  @FirstName + '%' + ''''
	IF ISNULL(@Gender, '') <> ''
		SET @WhereQuery = @WhereQuery +' AND [Gender] LIKE' + ''''+ @Gender + '%' + ''''
	--set
	SET @Query = @Query + @WhereQuery
	--execute query
	EXEC (@Query)
END
GO
   
v2
Comments
Richard Deeming 18-Feb-15 12:53pm
   
Your code is vulnerable to SQL Injection[^].

Use the sp_executesql command[^] to execute the dynamic query, and pass the parameters as parameters.

IF ISNULL(@FirstName, '') != ''
SET @WhereQuery = @WhereQuery + ' AND [FirstName] LIKE @FirstName';

IF ISNULL(@Gender, '') != ''
SET @WhereQuery = @WhereQuery + ' AND [Gender] LIKE @Gender';

SET @Query = @Query + @WhereQuery;

EXEC sp_executesql @Query,
N'@FirstName nvarchar(50), @Gender nvarchar(50)',
@FirstName = @FirstName, @Gender = @Gender;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900