需要帮助...复选框列表 - 将所选值传递给sql逗号分隔函数 [英] need help...checkbox list - passing selected values to sql comma separated function

问题描述

我有一个复选框列表和一对多表。我试图使用复选框列表来过滤SQL查询，该功能正常工作。查询工作，但当我尝试将html复选框列表连接到查询..它崩溃。



我一直在尝试使用sqldatasource向导把所有东西都塞进去。我将复选框列表设置为控制参数，但由于某种原因，当我尝试调试页面时查询无法正常工作。



我该怎么做将复选框选中的值发送到拆分功能？是否可以使用asp sqldatasource向导执行此操作？如果不是......我怎么做到这一点？



这里是选择参数：

 <   asp：ControlParameter     ControlID   =  DropDownCheckBoxes1  
   
                                                   ConvertEmptyStringToNull   =  False    DefaultValue   =  0   名称  =  Ids  
   
              < span class =code-attribute>                                     PropertyName   =  SelectedValue   大小 < span class =code-keyword> =  8000   类型  = 字符串    /  >  

这里是sql函数：

  ALTER  < span class =code-keyword> FUNCTION  SplitDelimiterString1（ @ StringWithDelimiter   VARCHAR （ 8000 ）， @ Delimiter   VARCHAR （ 8 ））
 
  RETURNS   @ ItemTable   TABLE （Item  VARCHAR （ 8000 ））
 
  AS  
  BEGIN  
  DECLARE   @ StartingPosition   INT ; 
  DECLARE   @ ItemInString   VARCHAR  （ 8000 ）; 
 
  SELECT   @ StartingPosition  =  1 ; 
   -   如果字符串为空或空，则返回 
  IF  LEN（ @ StringWithDelimiter ）=  0   OR   @ StringWithDelimiter   IS   NULL   RETURN ; 
 
  WHILE   @ StartingPosition >  0  
  BEGIN  
   -  获取分隔符的起始索引..如果字符串 
   -   不包含任何分隔符，而不包含任何分隔符 
  SET   @StartingPosition  = CHARINDEX（ @ Delimiter ， @ StringWithDelimiter ）; 
 
   -   从字符串中获取项目 
  IF   @ StartingPosition >  0  
  SET   @ ItemInString  = SUBSTRING（ @ StringWithDelimiter ， 0 ， @ StartingPosition ）
  ELSE  
  SET   @ItemInString  =  @ StringWithDelimiter ; 
   -   如果item不为空而不是添加到返回表 
 < span class =code-keyword> IF （LEN（ @ ItemInString ）>  0 ）
  INSERT   INTO   @ ItemTable （Item） VALUES （ @ ItemInString ）; 
 
   -   从字符串中删除插入的项目 
  SET   @ StringWithDelimiter  = SUBSTRING（ @ StringWithDelimiter ， @ StartingPosition  + 
 LEN（ @Delimiter ），LEN（ @StringWithDelimiter ） -   @ StartingPosition ）
 
   -   如果字符串为空则中断循环 
  IF  LEN（ @StringWithDelimiter ）=  0   BREAK ; 
  END  
 
 返回 
  END  

这里是sql查询

< pre lang =SQL> SELECT DISTINCT
person.personID，sports.sportsID，person .personName，sports.sport，

FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID

WHERE （person.personName LIKE ' ％' + @ personName + ' ％'）
AND
（sports.sportID IN
（ SELECT ID FROM dbo.fnSplitter （ @ IDs ） AS fnSplitter_1）或 @ IDs = 0 ）

解决方案

你不能直接将逗号分隔的值传递给T-SQL函数！您需要使用存储过程 [ ^ ]你在其中'将调用 SplitDelimiterString1 函数。



如何调用存储过程？请参阅：如何：使用Visual C＃.NET在ASP.NET中调用SQL Server存储过程 [ ^ ]



如何选中复选框到字符串变量？ http://stackoverflow.com/questions/ 3655068 /如何获取最新选择的价值来自复选框列表 [ ^ ]



备用拆分功能：在SQL IN中使用逗号分隔值参数字符串条款 [ ^ ]

I have a checkbox list and a many to many table. i am trying to use the checkbox list to filter a sql query and the function works. the query works, but when i try to hook up the html checkbox list to the query..it falls apart.

i''ve been trying to use the sqldatasource wizard to plug everything together. i set the checkbox list as the control parameter, but for some reason, the query doesn''t work properly when i try to debug the page.

how do i send the checkbox selected values to the split function? is it possible to do this with the asp sqldatasource wizard? if not...how do i accomplish this?

here''s the select parameter:

<asp:ControlParameter ControlID="DropDownCheckBoxes1"

                        ConvertEmptyStringToNull="False" DefaultValue="0" Name="Ids"

                        PropertyName="SelectedValue" Size="8000" Type="String" />

here''s the sql function:

ALTER FUNCTION SplitDelimiterString1 (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

RETURNS @ItemTable TABLE (Item VARCHAR(8000))

AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);

    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 
    
    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 
        
        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            
        
        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)
        
        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END
     
    RETURN
END

here''s the sql query

SELECT DISTINCT 
                         person.personID, sports.sportsID, person.personName, sports.sport,
 
FROM            sports INNER JOIN
                person INNER JOIN
                personSport ON personSport.personID = person.personID 
                       INNER JOIN
                personSport ON personSport.sportID = sports.sportsID
 
WHERE        (person.personName LIKE '%' + @personName + '%') 
              AND 
             (sports.sportID IN
                    (SELECT   ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)

解决方案

You can''t pass comma separated values into T-SQL function directly! You need to use stored procedure[^] in which you''ll call SplitDelimiterString1 function.

How to call stored procedure? See this: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET[^]

How to get selected checkboxes into string variable? http://stackoverflow.com/questions/3655068/how-to-get-the-latest-selected-value-from-a-checkbox-list[^]

Alternative split function: Using comma separated value parameter strings in SQL IN clauses[^]

这篇关于需要帮助...复选框列表 - 将所选值传递给sql逗号分隔函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！