CheckBoxList Not Returning Results
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
And the SQL (2008)
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 FunctionAnd 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 ROLLUPASKER
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!
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
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.
Where clause seems fine to me. I suspect the problem is in join. An alternative is to use the CharIndex function.
ASKER
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 ROLLUPALTER 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 ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Finally got it all working! Thanks for getting me corrected and pointed in the right direction!
I suspect this is where the problem is
join dbo.fn_SplitString(@church
on church.StringValue = users.church