Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-04-06
1
Medium Priority
?
1,181 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 300 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

916 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