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

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

Who is Participating?
sharizodConnect With a Mentor Author Commented:
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!
sharizodAuthor Commented:
Forgot to mention, it fails on line 43: Da.Fill(DS, sSchemaFile) with the "no value given for
required parameters" error.
mlmccConnect With a Mentor Commented:
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?

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

sharizodAuthor Commented:
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).
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.

sharizodAuthor Commented:
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))
All Courses

From novice to tech pro — start learning today.