Link to home
Start Free TrialLog in
Avatar of JEClark
JEClarkFlag for United States of America

asked on

SSRS Custom Code to read from database

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
Avatar of Auric1983
Auric1983
Flag of Canada image

I think your add parameter line should be before the query
Avatar of JEClark

ASKER

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

ASKER

Thanks, but I'm really wanting the flexibility of writing custom functions that extract infomration for a SQL database, not limited to just count.
ASKER CERTIFIED SOLUTION
Avatar of JEClark
JEClark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial