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.
Free Windows Admin Tool Kit Click here and download it now
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
Free Windows Admin Tool Kit Click here and download it now
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