如何创建高级搜索.. [英] How to create Advance Search..

问题描述

亲爱的先生

我希望我在网页上有10个下拉列表框。我想搜索数据，如果任何人选择至少一个或多个给定的下拉列表框，





i想生成动态基于Dropdownlist框的选定值的Sql并使用这样的生成sql查找记录..

请帮助进行预先搜索..





thankyou advance;

解决方案

我们应该运行什么查询来获取一个选择的下拉列表框或

选择多个......



这取决于您的数据库设计。如果这些字段都在同一个表中，则相对简单：

  SELECT  *  FROM  MyTable  WHERE < condition1>  OR < Condition2> 

或

  SELECT  *  FROM  MyTable  WHERE < condition1>  AND < Condition2> 

但是如果它们位于不同的表中，那么它会变得更复杂：可能是更复杂。



我们无法分辨 - 我们不知道您的列是什么，您的下拉包含，任何真实的。请记住，我们无法看到您的屏幕，访问您的硬盘或阅读您的想法！ ：笑：



首先看看你想要获取的信息，然后查询各种收集信息。通过一些思考你需要做什么，它应该变得相当明显。

   -   尝试此存储过程，通过映射有效的SQL表信息从前端传递所需的搜索值。  
 
  CREATE   PROCEDURE  [dbo]。[Sp_Studentsearch] 
   -   输入参数 
  @ FirstName   nvarchar （ 50 ）=  null ，
  @ Gender   nvarchar （ 50 ）=  null  
  AS  
  BEGIN  
   -   声明变量 
  DECLARE   @ Query   VARCHAR （MAX）， 
  @ WhereQuery   VARCHAR （MAX）
   -    set query  
  SET   @ Query  = '  SELECT [ID]，[FirstName]，[LastName]，[Gender] FROM [dbo]。[Students]' 
  SET   @ WhereQuery  = '  WHERE 1 = 1' 
   -   搜索功能 
  IF  ISNULL（ @ FirstName ，'  '）<> ' ' 
  SET  < span class =code-sdkkeyword> @ WhereQuery  =  @ WhereQuery  + '  AND [FirstName] LIKE' + ' ''' +  @ FirstName  + ' ％' + ' ''' 
  IF  ISNULL（ @ Gender ，' '）<> ' ' 
  SET  < span class =code-sdkkeyword> @ WhereQuery  =  @ WhereQuery  + '  AND [Gender] LIKE' + ' ''' +  @ Gender  + ' ％' + ' ''' 
   -    set  
  SET   @ Query  =  @ Query  +  @ WhereQuery  
   -   执行查询 
  EXEC （ @曲ery ）
  END  
  GO  

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;

解决方案

"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:

SELECT * FROM MyTable WHERE <condition1> OR <Condition2>

Or

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.

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

这篇关于如何创建高级搜索..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！