Using Custom Code Modules?
I have a couple of questions: 1) I am returning a dataset from a custom code module on a blank report (Sql Server 2005). Is it possible to use this dataset within the report? 2) What I am really trying to accomplish is to read in values from a text file which I have achieved. If the above is not possible is there a better way to access the return values of the file I/O? Is is possible to access the function from within the sql? Here is my code: Function GetInput() As String Dim oRead As System.IO.StreamReader Dim EntireFile As String oRead = IO.File.OpenText( "C:\input.txt") EntireFile = oRead.ReadToEnd() Return EntireFile End Function Function GetItemInfo() As DataSet Dim returnVal = GetInput() Dim oConn As New System.Data.SqlClient.SqlConnection oConn.ConnectionString = "Data Source=VER-SERVER-NAV;Initial Catalog=Certco;Integrated Security=SSPI " Dim oCmd As New System.Data.SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandText = "SELECT I.Pack, I.Size, I.Description, I.No_ FROM dbo.Certco$Item I WHERE I.No_ IN (" + returnVal + ")" 'sql adapter Dim adapter As New System.Data.SqlClient.SqlDataAdapter() 'data table to hold the results Dim dt As New DataTable() 'int to hold the # of rows affected Dim row As Integer = 0 Try 'open the command objects connection oConn.Open() 'set the adapters command property adapter.SelectCommand = oCmd 'now fill the table adapter.Fill(dt) 'return the results Dim ds As New DataSet() ds = New DataSet() 'creating a dataset ds.Tables.Add(dt) Return ds Catch ex As System.Data.SqlClient.SqlException Throw ex Catch ex As Exception Throw New Exception(ex.Message) Finally oCmd.Connection.Close() adapter.Dispose() End Try End Function
October 12th, 2010 4:16pm
It is not possible to read the text file from the customcode as Dataset. Alternatively , i think you can establish a connection to the text file by selecting ODBC Data source while creating the Data source. Just an idea . Try that and post us the result.
October 12th, 2010 4:34pm
I was able to read the text file into the dataset. That works fine. I just wasn't able to connect the dataset into the report.
October 12th, 2010 8:33pm
Hi JazzyIce, Based on your description and the custom code you posted, I found you still try to access SQL Server database. However, you also need to retrieve the filter string in a text file and combined it into the query, correct? If I have misunderstood your requirement, please point out. To build a query based on a filter string which is in a text file, I would suggest you keep the GetInput() function in the custom code, and then use expression in dataset to combine the filter string which returned by the custom function. Please refer to the steps below: 1. Open report in Design mode. Click the Data tab. 2. In the report Data view, click the Dataset dropdown list and select <New Dataset…> 3. If no data source has been specified before, the Data Source window will be displayed and we need to configure one. Please input the connection string in the Data Source window. Then click OK. 4. Click the Ellipsis button after the Dataset dropdown list. 5. In the Dataset window, click the fx button beside the Query string area. 6. Input the query string as expression. For example, we can combine the expression like ="SELECT I.Pack,I.Size,I.Description,I.No_ FROM dbo.Certco$Item WHERE I.No_ IN (" & Code.getMembers() &")" This expression will return the query string and include the filter by calling custom function. Please correct fields name in it based on your dataset. After that, we can use the dataset filtered by a text file in the report. In addition, we can also refer the dataset created in custom code in the report. Please refer to this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/440ff5de-1c21-47de-95d8-e4212d6cef6d If anything unclear, please feel free to ask. Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help
October 18th, 2010 11:15am
This is exactly what I was looking for. Thank you. After searching fruitlessly for many days, I almost gave up. I thought, "Why have code modules, if all you can do is return very basic scalars???" This helps greatly. Is there any push to have the code modules in C#?
October 18th, 2010 2:20pm