Report data from dataset generated at runtime

[ghck17]

I have a vb.net web app with an stiwebreport object that I am trying pull data from a dataset filled by a stored procedure. To design the report, I created tables in the sql database and added a connection to the report to pull data from these tables. It works in preview mode and also at runtime, but I would now like to change the datasource to a strongly typed dataset within my application. However, when using the code below, I am getting data from the original tables and not from the dataset.

First, I would like to know, what is this the best way to design the report when the intention is to use data from a dataset. I was unable to figure out how to make the report designer see the tables within the dataset, or the dataset itself for that matter.

Secondly, what is the preferred method for retrieving data from sql server? dataset with sqldataadapter, sqldatasource object, etc..

Current code does not display the new data from the dataset. It is pulling data from the tables I created for designing the report. Any guidance is greatly appreciated.

Dim con As SqlConnection = New SqlConnection
Dim daHeader As SqlDataAdapter = New SqlDataAdapter
Dim daDetail As SqlDataAdapter = New SqlDataAdapter

Dim cmdHeader As SqlCommand = New SqlCommand
Dim cmdDetail As SqlCommand = New SqlCommand

Dim ds As dsInvoice = New dsInvoice

con.ConnectionString = constr
cmdHeader.Connection = con
cmdDetail.Connection = con
con.Open()

cmdHeader.CommandType = CommandType.StoredProcedure
cmdDetail.CommandText = CommandType.StoredProcedure

cmdHeader.CommandText = "InvoiceRange_Header"
cmdDetail.CommandText = "InvoiceRange_Detail"

daHeader.SelectCommand = cmdHeader
daDetail.SelectCommand = cmdDetail

daHeader.Fill(ds.InvoiceHeader)
daDetail.Fill(ds.InvoiceDetail)

Dim report As Stimulsoft.Report.StiReport = New Stimulsoft.Report.StiReport

report.Load("invoice1.mrt")
report.DataStore.ClearReportDatabase()
report.RegData(ds)
report.Dictionary.Synchronize()

report.Render(False)

StiWebViewer1.Report = report

[Edward]

Hi

First, I would like to know, what is this the best way to design the report when the intention is to use data from a dataset. I was unable to figure out how to make the report designer see the tables within the dataset, or the dataset itself for that matter.

after registering an xsd schema In the Dictionary in the Designer you can press 'Synchronize' command in the Actions menu.

Secondly, what is the preferred method for retrieving data from sql server? dataset with sqldataadapter, sqldatasource object, etc..

It is better to register just a Connection in the report which contains all required DataSources with Sql. Report can be loaded from mrt file or assembly.

Code: Select all

Report.Load(Server.MapPath("myReport.mrt"))
Report.Compile()
Dim ConnectionString As String = "..."
Dim Connection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConnectionString)
Report.RegData("ConnectionName", Connection)

Please try the following in your code:

Code: Select all

report.Dictionary.Databases.Clear()
report.Dictionary.DataSources.Clear()
report.RegData(ds)
report.Dictionary.Synchronize()

Thank you.