Crystal Reports, Advanced SQL Commands and Parameters

Crystal Reports Pic 4 - Select Data Tables (Photo credit: wmharshana)

I've not written a Crystal Report that required a more advanced query in quite a while, and had a need to union two selects and pass in a parameter to both queries.

I initially just wrote the Union query and attempted to place my {?CustomerName} in both places using the "=" comparison operator, which worked.

When I wanted to add the ability for the user to only add a portion of the customer name in the query and use the LIKE operator, Crystal had some issues and thought I was trying to pass a column name or some such trash.

Eventually I found an article that suggested the use of a DECLARE and SET to hold the parameter that Crystal passes, then hand it off to the query. This also failed until I wrapped the entire UNION in a SELECT and passed in the parameter once.

Yay!

DECLARE @Cust VarChar(200)
SET @Cust = '{?CustomerName}'

SELECT * FROM (
SELECT Name, Address, City, State, Zip From CustArch
UNION
SELECT Name, Address, City, State, Zip From Customers
) a
WHERE Name LIKE '%' + @Cust + '%'

Find GridView Cell Values on RowCommand with Paging Enabled

This was confounding me this morning until I found this post on the ASP.NET forums.

I was setting up a GridView and needed to check the values of a couple of cells before I let users click a button in an ItemTemplate.

I initially used the Container.DateItemIndex method to determine which row they clicked but quickly found out that this only works without Paging Enabled as noted on the MSDN.

All this does is tell you the index of the data, but since the GridView index needs to be reset on each page (0-your page size) you'll never find your values.

Instead set is thusly:

You'll be able to easily access your cells then:

                    Dim index As Integer = Int32.Parse(e.CommandArgument.ToString())
                    Dim row As GridViewRow = GV_OrderHeaders.Rows(index)
                    Dim ifs As String = DirectCast(row.FindControl("lblIfsId"), Label).Text
                    Dim oid As String = DirectCast(row.FindControl("lblOrderId"), Label).Text
                    Dim qid As String = GV_OrderHeaders.DataKeys(index).Value