SSRS Custom Code to read from database

JEClark
JEClark used Ask the Experts™
on
Is there anything obviously incorrect in what I am doing, below?  Any help would be most appreciated.

I am using Northwind database and working on a proof-of-concept.  I have added references to the System.Data and System.XML assemblies using Report-Report Properties-References and have a report with three columnq - CompanyName, ContactName and CustomerOrderCount.  

I am atempting to use custom code in SSRS 2008 to extract CustomerOrderCount (Expression"=Code.GetCustomerOrderCount(Fields!CustomerID.Value") by calling the following code/function which I copied from http://www.devx.com/codemag/Article/33656/1954 in the Report-Report Properties-Code box, but I receive only "#Error" in the CustomerOrderCount:

 Function GetCustomerOrderCount(ByVal CustomerID As String) As Integer
        Dim oConn As New System.Data.SqlClient.SqlConnection

        oConn.ConnectionString = "Data Source=MyServer;Initial Catalog=Northwind;Integrated Security=SSPI;"

        oConn.Open()
        Dim oCmd As New System.Data.SqlClient.SqlCommand
        oCmd.Connection = oConn
        oCmd.CommandText = "Select count(*) From Orders Where CustomerID = @CustomerID"
        oCmd.Parameters.AddWithValue("@CustomerID", CustomerID)
        Dim nRetVal As Integer = oCmd.ExecuteScalar()
        oConn.Close()
        Return nRetVal
        End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think your add parameter line should be before the query

Author

Commented:
Thanks for the suggestion.  I tried moving the parameter line with no luck.
SSRS reports can handle a count value in the report if that is what you are wanting.

Author

Commented:
Thanks, but I'm really wanting the flexibility of writing custom functions that extract infomration for a SQL database, not limited to just count.
Commented:
This was a CAS problem.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial