[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CheckBoxList Not Returning Results

Posted on 2012-08-14
6
Medium Priority
?
672 Views
Last Modified: 2012-08-18
I've struggled with this one for a few  days now.  This is the first time I've used the CheckBoxList control and I am trying to simply return some data from the database which is filtered by different organizations.  I've verified that the checkbox list is populating the filtered string to the database correctly by putting a temporary label on the page to post those filters.  It seems that either that data is not returning correctly or that it's not populating the dataset to the gridview correctly.  I've posted my code and the sql query below.  I would appreciate any help.

Thanks

    Protected Sub Button4_Click(sender As Object, e As System.EventArgs) Handles Button4.Click

        'clear the current list
        ltChurchList.Text = String.Empty

        For Each item In grpMembers.Items
            If item.selected Then
                ltChurchList.Text = ltChurchList.Text & item.value & ","
            End If
        Next

        'Trim off the trailing ","
        If Len(ltChurchList.Text) > 0 Then
            ltChurchList.Text = Left(ltChurchList.Text, Len(ltChurchList.Text) - 1)
        End If

        GridView1.DataSource = getchurchData()
        GridView1.DataBind()

    End Sub


    Protected Function getchurchData() As DataSet


        Dim con As New SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.CommandTimeout = 10000

        cmd.Parameters.AddWithValue("@dStart", dStart.Text)
        cmd.Parameters.AddWithValue("@dEnd", dEnd.Text)
        cmd.Parameters.AddWithValue("@churchList", Me.ltChurchList.Text)

        cmd.CommandText = "assCatRptGrp"

        Dim a As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ds As DataSet = New DataSet("churchlist")
        con.Open()


        Try
            a.Fill(ds)

        Catch ex As Exception
            If (Not con Is Nothing) Then
                con.Close()
            End If
        End Try

        Return ds



    End Function

Open in new window


And the SQL (2008)

ALTER PROC [dbo].[assCatRptGRP]

@CoC varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@ChurchList varchar (1000)


AS

SELECT CASE GROUPING(GiftCategory)
 WHEN 1 THEN 'TOTAL'
 ELSE [GiftCategory] END AS 'Gift Category',

sum(cast(
CASE WHEN (
value) NOT LIKE '%[^0-9.]%' THEN (value) END as NUMERIC)) as "Amount",count(giftCategory)AS "Count"


FROM activity join users on activity.username=users.username  JOIN UserCOC on UserId=users.id
join dbo.fn_SplitString(@churchList, ',') church
				on church.StringValue = users.church 


WHERE UserCoC.COC = @CoC AND date >= @dStart AND date <= @dEnd


GROUP BY GiftCategory WITH ROLLUP

Open in new window

0
Comment
Question by:pposton
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38293574
Did you run your query directly in management studio by passing it couple of church values?

I suspect this is where the problem is

join dbo.fn_SplitString(@churchList, ',') church
                        on church.StringValue = users.church
0
 

Author Comment

by:pposton
ID: 38294460
I'm not really sure how to do that.  I mean I can't without just removing it and putting a few values in the WHERE clause and that would probably not be an accurate check.  (anyway I've done that and the rest of the code runs well)  I've also tried using that with WHERE In but no luck with that as well.

I actually grabbed this code from some I had a developer do a couple of years ago on another more in depth site that I was runnning at the time.  I assume that the splitstring function is generic when it comes to spliting out the string.  I'll pass along that function in case that might help.

Thanks for taking a look!

ALTER Function [dbo].[fn_SplitString] ( 
                @csv varchar(8000), 
                @sep char(1) 
) 
returns @StringTable table (
                StringValue varchar(100),
                Position int identity
)
AS
begin

                declare @stringLength int
                select @stringLength = Len(@sep + @csv + @sep)

                insert @StringTable(
                                StringValue
                )
                SELECT 
                                SubString(
                                                @sep + @csv + @sep 
                                                ,number  
                                                ,CharIndex(@sep , @sep + @csv + @sep , number) - number
                                ) AS StringValue
                FROM 
                                fn_numbers(1, @stringLength)
                WHERE 
                                '[' +SubString(@sep + @csv + @sep , number - 1, 1)+']' = '[' + @sep + ']'
                order by 
                                number

                return
end

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38295276
I meant executing the SP assCatRptGRP in management studio by passing parameter values.

Where clause seems fine to me. I suspect the problem is in join. An alternative is to use the CharIndex function.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:pposton
ID: 38303146
Well after some trial and error I have gotten closer  I have the procedure running when I run the function against real values as opposed to the parameter @churchList. However when I use the parameter the grid doesn't poplulate.  I noticed when I ran the procedure with the values entered I had to have the string surrounded by ' '.  I think the problem may be that the list coming in from the checkboxlist is not surrounded by ' '.  I'm not sure how ensure that the list when finally presented in the function is encapsulated with the ' '.  Any direction would be appreciated.  (I am providing the current procedure and the new split string function below)

SELECT CASE GROUPING(GiftCategory)
 WHEN 1 THEN 'TOTAL'
 ELSE [GiftCategory] END AS 'Gift Category',

sum(cast(
CASE WHEN (
activity.value) NOT LIKE '%[^0-9.]%' THEN (activity..value) END as NUMERIC)) as "Amount",count(giftCategory)AS "Count"

FROM activity join users on activity.username=users.username  JOIN UserCOC on UserId=users.id join dbo.ParmsToList('bc,cc,cog,dbc', ',') list ON list.Value = church
WHERE COC='aaa' AND date >= '01/01/2009' AND date <= '01/01/2010'

GROUP BY GiftCategory WITH ROLLUP

Open in new window


ALTER FUNCTION [dbo].[ParmsToList] 
(@Parameters varchar(MAX), @delimiter varchar(10) )
-- SQL 2005+ version would change the varchar(8000) into varchar(max)
returns @result TABLE (Value varchar(MAX), row_num int identity )
AS  
begin
  declare @dx varchar(9)
 
  if @delimiter is null  set @delimiter = ' '
  if datalength(@delimiter) < 1 set @delimiter = ' '
  set @dx = left(@delimiter, datalength(@delimiter)-1)
 
  declare @Value varchar(MAX), @Pos int
 
  set @Parameters = @Parameters + @delimiter
  set @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
  IF REPLACE(@Parameters, @delimiter, @dx) <> ''
  BEGIN
    WHILE @Pos > 0 
    BEGIN
      SET @Value = LEFT(@Parameters, @Pos - 1)
      IF @Value <> ''
      BEGIN
        INSERT INTO @result (Value) 
        VALUES (@Value) 
      END
      SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),8000)
      SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
    END --WHILE @Pos > 0 
  END -- IF REPLACE(@Parameters, @delimiter, @dx) <> ''
   
  RETURN
END 

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38304326
>I think the problem may be that the list coming in from the checkboxlist is not surrounded by ' '

You can append that to the list before sending to server.
0
 

Author Comment

by:pposton
ID: 38308721
Finally got it all working!  Thanks for getting me corrected and pointed in the right direction!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question