Solved

Passing Database Logon information from FileDSN at runtime in Crystal Report using VB.NET

Posted on 2004-04-06
1
1,101 Views
Last Modified: 2007-12-19
I am using stored procedures in SQL SERVER 2000 which are getting data from joins of more than one table. I want to bind the result in crystal report (using vb.net) . The problem is that I am not sure of the Database name and Server name of SQL SERVER 2000 as the cleint can change it also. Can anyone provide with an example how to create crystal report with stored procedure using more then one table which binds server details at runtime or can be changed easily .
I am using this in my app.config
<add key="ConnectionString" value="Server=Aron1;Database=pubs;Trusted_Connection=True;" ></add>
Alternately I can use FILEDSN (with trusted connection) for crystal report.
0
Comment
Question by:ai4u
1 Comment
 
LVL 1

Accepted Solution

by:
zozig earned 100 total points
ID: 10775152
The best thing to do would be to bind your report to a dataset and fill that with your result set,  create a data access object that will execute your query against a dsn that can be assigned at runtime from a modification within the app.config.  Your data access object will have a method called fill dataset that would look something like the following code below, the adminVo object will contain the following properties needed by the fillDataSet
1. Sql to execute against the report
2. DataSource table name, this is the table name that is defined in the dataset that is bound to the report
3. DataSource Name, this is the actual name of the dataSet that is bound to the Report

You'll need to ensure that the sql that is being executed maps EXACTLY to the dataset that is bound, meaning the table defined in the dataset must match the result returned from your query, most likely you will aliases in your query to do the mapping to the dataset.

If you follow this approach you will have a truly object oriented approach to report templating, you can pass in any parameters you might need that would be dynamic for your query such as a date selection etc... If you continue to build out the AdminVO object you can also have the report template be generated at runtime so that multiple reports files could generate multiple reports and you could reuse datasets that you create as well.

Here is the fillDataSet method:

Hope this helps..

Public Function fillDataSet(adminVo As AdminVO, sqlParams() As SqlParameter) As DataSet
   ' Instantiating the Dataset
   Dim ds As New DataSet()
   ' Our SqlCommand object
   Dim cmd As SqlCommand
   ' ArrayList for holding our Column information
   Dim columnArray As New ArrayList()
   
   ' Attempting to Fill the Dataset
   Try
      ' Open our database connection
      ' The database connection info is contained as a standard DataAccessObject
      dbConnection.Open()
     
      ' Instantiate a new sql Command object with our Report Query SQL
      cmd = New SqlCommand(adminVo.getReportQuery(), dbConnection)
     
      ' Iterate through the SqlParamters[] that is passed in
      Dim j As Integer
      For j = 0 To sqlParams.Length - 1
         ' Add each SqlParameter to the SqlCommand object
         cmd.Parameters.Add(sqlParams(j))
      Next j
     
      ' Set the command type to text for evaluating the sql query
      cmd.CommandType = CommandType.Text
     
      ' Setup our dataReader to evaluate the keyinfo which gives us the columns
      ' which are present within the runtime SQL statement.
      Dim dtrCat As SqlDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
     
     
      ' Get the schema information from the SqlReader
      Dim schemaTable As DataTable = dtrCat.GetSchemaTable()
     
      'For each field in the table defined in the runtime SQL
      Dim myField As DataRow
      For Each myField In  schemaTable.Rows
         ' Add each column name to the array
         columnArray.Add(myField("ColumnName").ToString())
      Next myField
     
      ' Close the sql reader so we can do more work on SqlCommand object...
      dtrCat.Close()
     
      ' Instatiate our sql Adapter
      Dim da As New SqlDataAdapter()
      da.SelectCommand = cmd
     
      ' Map the columns that we have from our sql statement
      Dim columnMapArray(columnArray.Count) As DataColumnMapping
     
      ' Iterate through the columns
      Dim i As Integer
      For i = 0 To columnMapArray.Length - 1
         ' Add the mapping for the current column
         columnMapArray(i) = New DataColumnMapping(columnArray(i).ToString(), columnArray(i).ToString())
      Next i
     
      ' Create the entire table mapping, the dataSourceTable is the table as defined in the dataset that is bound to the report
      da.TableMappings.AddRange(New DataTableMapping() {New DataTableMapping("Table", adminVo.getDataSourceTable(), columnMapArray)})
     
      ' Pass in our dataSet name                                          
      ds.DataSetName = adminVo.getDataSourceName()
     
      '
      ds.Locale = New System.Globalization.CultureInfo("en-US")
     
     ' Execute the command again with the appropriate table mappings
      da.SelectCommand = cmd

      ' Fill the Dataset
      da.Fill(ds)
     
      Return ds
   
   Catch e As InvalidOperationException
      Utility.writeToEventLog(("ReportingDAO.fillDataSet() exception caught is:" + e.Message))
      Return Nothing
   Catch e As SqlException
      Utility.writeToEventLog(("ReportingDAO.fillDataSet() exception caught is:" + e.Message))
      Return Nothing
   Catch e As InvalidCastException
      Utility.writeToEventLog(("ReportingDAO.fillDataSet() exception caught is: " + e.Message))
      Return Nothing
   Finally
      dbConnection.Close()
   End Try
End Function 'fillDataSet
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now