I'm building a VB.NET application for employees to enter their time worked in a week. Today, some of the users asked if I could add a report to the app that will allow supervisors to print off the timesheet they're viewing.
I've used Crystal reports extensively in ASP.NET, so I thought this would be a piece of cake. But I've gotten stuck on something.
What I'd like to do is populate the report using a dataset built dynamically in my code.
So I wrote the code below:
===========================
Dim Conn As New SqlConnection()
Dim Cmd As New SqlCommand()
Dim DA As New SqlDataAdapter()
Dim DS As New DataSet()
OpenConnection(Conn)
Cmd.Connection = Conn
Cmd.CommandText = "SELECT ... " 'removed for this post
Cmd.Parameters.Add("@TSID", SqlDbType.Int)
Cmd.Parameters("@TSID").Value = TSID 'TSID is a variable
DA.SelectCommand = Cmd
DA.Fill(DS, "Timesheet")
Dim oRpt As rptTimesheet
oRpt = New rptTimesheet()
oRpt.SetDataSource(DS)
CrystalReportViewer.ReportSource = oRpt
===========================
Now, here's where I'm getting stuck.
When I'm designing the report in VS.NET, if I do not associate the report with a database (via ODBC or whatever), I don't see any database fields. Therefore I can't place the fields I want on the report.
If, however, I link the report to a database during the design process, whenever I run the application, it keeps trying to use that connection. It's not disregarding the design connection for my dynamic code (as ASP.NET has always done). So suddenly users are being prompted for SQL Server login information.
Feeling like I was in a catch 22, I tried a third method. I figured I could create a dataset as an actual XSD design object (rather than doing it by code), and then link that to my report. Then I could just use a bunch of formula fields and such to correct for what the dynamic SQL would have done. Seemed really clunky, but it was worth a shot. Strangely, when I went to link the ADO.NET object to my report, the report designer kept insisting there were no fields in my dataset (even though I was looking right at them in the designer)!
So how can I build the report, populate it via a code-create dataset, and run it without users being prompted for login information?
I'd like to do this without storing any views, etc, on the SQL Server. I'd also like to create the dataset via code (rather than the designer) if at all possible.
Thanks for any help!
I've used Crystal reports extensively in ASP.NET, so I thought this would be a piece of cake. But I've gotten stuck on something.
What I'd like to do is populate the report using a dataset built dynamically in my code.
So I wrote the code below:
===========================
Dim Conn As New SqlConnection()
Dim Cmd As New SqlCommand()
Dim DA As New SqlDataAdapter()
Dim DS As New DataSet()
OpenConnection(Conn)
Cmd.Connection = Conn
Cmd.CommandText = "SELECT ... " 'removed for this post
Cmd.Parameters.Add("@TSID", SqlDbType.Int)
Cmd.Parameters("@TSID").Value = TSID 'TSID is a variable
DA.SelectCommand = Cmd
DA.Fill(DS, "Timesheet")
Dim oRpt As rptTimesheet
oRpt = New rptTimesheet()
oRpt.SetDataSource(DS)
CrystalReportViewer.ReportSource = oRpt
===========================
Now, here's where I'm getting stuck.
When I'm designing the report in VS.NET, if I do not associate the report with a database (via ODBC or whatever), I don't see any database fields. Therefore I can't place the fields I want on the report.
If, however, I link the report to a database during the design process, whenever I run the application, it keeps trying to use that connection. It's not disregarding the design connection for my dynamic code (as ASP.NET has always done). So suddenly users are being prompted for SQL Server login information.
Feeling like I was in a catch 22, I tried a third method. I figured I could create a dataset as an actual XSD design object (rather than doing it by code), and then link that to my report. Then I could just use a bunch of formula fields and such to correct for what the dynamic SQL would have done. Seemed really clunky, but it was worth a shot. Strangely, when I went to link the ADO.NET object to my report, the report designer kept insisting there were no fields in my dataset (even though I was looking right at them in the designer)!
So how can I build the report, populate it via a code-create dataset, and run it without users being prompted for login information?
I'd like to do this without storing any views, etc, on the SQL Server. I'd also like to create the dataset via code (rather than the designer) if at all possible.
Thanks for any help!