JEClark
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.GetCusto merOrderCo unt(Fields !CustomerI D.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(ByVa l CustomerID As String) As Integer
Dim oConn As New System.Data.SqlClient.SqlC onnection
oConn.ConnectionString = "Data Source=MyServer;Initial Catalog=Northwind;Integrat ed Security=SSPI;"
oConn.Open()
Dim oCmd As New System.Data.SqlClient.SqlC ommand
oCmd.Connection = oConn
oCmd.CommandText = "Select count(*) From Orders Where CustomerID = @CustomerID"
oCmd.Parameters.AddWithVal ue("@Custo merID", CustomerID)
Dim nRetVal As Integer = oCmd.ExecuteScalar()
oConn.Close()
Return nRetVal
End Function
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.GetCusto
Function GetCustomerOrderCount(ByVa
Dim oConn As New System.Data.SqlClient.SqlC
oConn.ConnectionString = "Data Source=MyServer;Initial Catalog=Northwind;Integrat
oConn.Open()
Dim oCmd As New System.Data.SqlClient.SqlC
oCmd.Connection = oConn
oCmd.CommandText = "Select count(*) From Orders Where CustomerID = @CustomerID"
oCmd.Parameters.AddWithVal
Dim nRetVal As Integer = oCmd.ExecuteScalar()
oConn.Close()
Return nRetVal
End Function
I think your add parameter line should be before the query
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.