Loading crystal report data dynamically with xsd file. - No value given for one or more required parameters

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-08-14
Hi experts,

I am a bit stymied as to why I am getting a "no value given for one or more required parameters" runtime error when
running the code below.  The DataAdapter fill method can take up to 7 different overloads and I am using one of
them that passes in a dataset object and a schema file (xsd) to apply when filling the dataset.  What am I doing
wrong?  I am trying to dynamically populate a crystal report with data based on user selections.

Private Sub LoadRpt(ByVal sRptSQL As String)

        '|  Derive our paths to database, report, and schema files from |
        '|  selections the user has chosen in the two dropdowns on the  |
        '|  main form.                                                  |
        Dim sDBFFilePath As String = ".\App_Data\Archive\" & DirectCast(Me.FindControl("cboArchiveList"), DropDownList).Text & ".mdb"
        Dim sRptName As String = DirectCast(Me.FindControl("cboReportList"), DropDownList).Text
        Dim ioptSpeedUnits As Integer = (DirectCast(((Me.FindControl("rbtnlstSpeedUnits"))), RadioButtonList).SelectedIndex)

        '|  Loads the crystal report with dynamic SQL created by the user   |
        '|  via record selection criteria on the reporting tab.             |

        '** Load XML Schema file
        Dim strConnection As String = ""

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MapPath(sDBFFilePath)

        ' Define Connection
        Dim Connection As New System.Data.OleDb.OleDbConnection(strConnection)
        Dim DA As New System.Data.OleDb.OleDbDataAdapter(sRptSQL, Connection)
        Dim DS As New System.Data.DataSet

        If Connection.State = ConnectionState.Closed Then
        End If

        ' Create a datatable in your dataset. The datatable's name 
        ' must match that in the schema file used by the report.

        Dim sSchemaFile As String = "ds" & sRptName

        'If optDataArchive.Checked Then
        If Session("ReportType") = "DATA" Then
            DA.Fill(DS, sSchemaFile)
            DA.Fill(DS, sSchemaFile)

            Dim n As Integer
            Dim sngSpeedMultiplier As Single

            Select Case ioptSpeedUnits
                Case Is = 0
                    sngSpeedMultiplier = 1      ' Knots
                Case Is = 1
                    sngSpeedMultiplier = 1.852  ' KPH
                Case Is = 2
                    sngSpeedMultiplier = 1.15   ' MPH
            End Select

            For n = 0 To DS.Tables(0).Rows.Count - 1
                DS.Tables(0).Rows(n).Item("Speed") = DS.Tables(0).Rows(n).Item("Speed") * sngSpeedMultiplier

        End If

        'Verify the path to the Crystal Report's .RPT file:
        Dim strReportPath As String = ""

        strReportPath &= ".\Reports\rpt" & Session("RptName") & ".rpt"

        If Not IO.File.Exists(MapPath(strReportPath)) Then
            Throw (New Exception("Unable to locate report file:" & _
              vbCrLf & strReportPath))
        End If

        'Load the Crystal report's .RPT file and pass in the DataTable:

        Dim cr As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

        cr.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape
        cr.SetDataSource(DS.Tables("ds" & sRptName))

        crpv.ReportSource = cr


    End Sub

Open in new window

Question by:sharizod
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Author Comment

ID: 26293478
Forgot to mention, it fails on line 43: Da.Fill(DS, sSchemaFile) with the "no value given for
required parameters" error.
LVL 101

Assisted Solution

mlmcc earned 2000 total points
ID: 26294656
One issue that you haven't got to is this line

        cr.SetDataSource(DS.Tables("ds" & sRptName))

I believe should be


What query are you running?

Since the connection may not be open when you declare the dataset, does it get opened when you do open the connection?


Author Comment

ID: 26295977
Hi mlmcc,

Thanks for your reply!

I can't even get to that line (cr.SetDataSource) since it errs out way before that, so it is not causing my problem.  It is supposed to be
that way because it is using an xsd file in combination with a dataset object and its name is dsrptGPS.  The objects below and their values
when I run through the UI of the web app are as below.  dsrptGPS is a dataset object (XSD file) also in the same
folder as the rptGPS.rpt crystal reports file.  I use the XSD file to bind to the report so that I have fields to
populate on the report at design time.   Then, I can pass in any SQL string at runtime and change what is displayed
based on user selections.

sDBFFilePath = ".\App_Data\Archive\Data.mdb"
sRptName = "rptGPS"
iOptSpeedUnits = 1
Session("ReportType") = "GPS"
sSchemaFile = "dsrptGPS"

sDBFFilePath As String = ".\App_Data\Archive\" & DirectCast(Me.FindControl("cboArchiveList"), DropDownList).Text & ".mdb"
        Dim sRptName As String = DirectCast(Me.FindControl("cboReportList"), DropDownList).Text
        Dim ioptSpeedUnits As In

The query (which is dynamic based on what fields a user selects to see in the report) is similar to the following:

SELECT *, #1/12/2010# AS FromDate, #1/12/2010# AS ToDate FROM Archive WHERE [DateTime] BETWEEN #01/12/2010 12:00:00 AM# AND #01/12/2010 12:00:00 AM# ORDER BY Unit_Name , Unit_Operator , Date_Time

When you say connection, which do you mean?  I queried the status of the Connection object from my code and it returns 1 (Open).
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

LVL 101

Expert Comment

ID: 26298805
If you look at this method, it may help you see where the error is


        ' Define Connection
        Dim Connection As New System.Data.OleDb.OleDbConnection(strConnection)
        Dim DA As New System.Data.OleDb.OleDbDataAdapter(sRptSQL, Connection)
        Dim DS As New System.Data.DataSet
If the declaration of the connection also opens it then that is fine.  when I have done this I have always just declared the connection object then used a statement to open the connection when I needed it.


Accepted Solution

sharizod earned 0 total points
ID: 26300734
Hi again,

I found the problem.  Neither of us saw it at first, but in your first post, you asked about what query I am using.  I posted the
text of the query which was generated by a previous function and passed in as a parameter to the LoadRpt function.  The issue
of no value for parameter is being caused by the [DateTime] in the generated select query.  That is actually a misspelling and
should actually have been Date_Time.  Once I made that change, the report was able to render just fine!  MSAccess
treats anything in [ ] as a parameter, unless, for some strange reason, you have a field named the same as one of the
TSQL keywords such as DateTime (which I did not).

As for the connection, yep, you can do it in one step or two.  That is what is so confusing regarding data connections,
there are dozens of possible ways to accomplish the same thing!

Thanks for the pointers!

Author Comment

ID: 26304503
Just an addendum to the post.  I kept having problems with the report data disappearing with the code from my initial post.
All properties seemed to be set correctly but there was no viewstate for the crystal reports viewer itself (which is fine with me
since there is enough viewstate in the application already).  I found that to prevent it from disappearing, I had to
set the ReportSource property of the reportviewer at design time to the reportsource on the form.  Then I commented
out the lines referring to the cr CrystalReportDocument object and added the following lines at the end of LoadRpt but before
the connection.close statement.

        '** crs = CrystalReportSource object
        crs.ReportDocument.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape
        crs.ReportDocument.SetDataSource(DS.Tables("ds" & sRptName))

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

762 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