• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3859
  • Last Modified:

Passing parameters from VB.Net to SQL Query

Hello,

In my vb.net application, I am sending parameters to the SQL Query in a crystal report  . It prints the parameters in the report but does not refresh the SQL Query, which should display table data.  I have been trying to change the name of the database location since we need to move the report from development to production. The problem has been occurring since then.

My code is:
Private rptCount As New ReportDocument
Dim reportPath As String = Server.MapPath("Rpt1.rpt")
rptCount.SetParameterValue("param1", CDate(Me.DropDownList1.SelectedValue))
rptCount.SetParameterValue("MonD", CDate(Me.DropDownList1.SelectedValue))
CrystalReportViewer1.ReportSource = rptCount
rptCount.Load(reportPath)

Appreciate any help.
Thanks.
0
proc2020
Asked:
proc2020
  • 10
  • 7
1 Solution
 
proc2020Author Commented:
If I sent data via the parameter prompt, the report displays data correctly.
0
 
mlmccCommented:
What error are you getting?

Does the report have saved data?

Are you using the parameter in the filter or Select Expert?

mlmcc
0
 
proc2020Author Commented:
mlmcc, thanks for your reply.
1. There is no error displayed. The report does not get refreshed with the data.
2. I am using static parameter field {param1}  in the database query in the report itself and sending data from the webform to the parameter.
3. The query looks like this:
SELECT
   tmptim_tbltime.`UserID`, tmptim_tbltime.`workdate`, ...
FROM
    tmptim_tbltime...
WHERE    
   and workdate >= {?param1}
   and workdate <= dateAdd("d",6,{?param1})
Yes, the report does have saved data.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mlmccCommented:
Change the setting to not save data.  With saved data the first time you view you get the saved data.

Open the report
Click FILE
Ensure SAVE DATA WITH REPORT is not checked.

mlmcc
0
 
proc2020Author Commented:
I changed the settings. Now, I get an error saying "Invalid report file path."
Is this error linked to the "SAVE DATA WITH REPORT" setting?
0
 
mlmccCommented:
It shouldn't be.

mlmcc
0
 
proc2020Author Commented:
Is there a solution to the error "Invalid report file path." while running a vb.net application from vs 2005 for crystal reports xi(developer edition)? unless i can solve this, i am not sure if i can refresh the query data.
Appreciate your help on this.
0
 
mlmccCommented:
0
 
proc2020Author Commented:
Thanks for the link.
No, I have not installed Release 2 since the report was working fine with saved data and parameters being passed from the webform.
I have downloaded the installation files .Is there a way around without installing Release 2?
0
 
mlmccCommented:
I don't know.  Crystal claims you need Release 2 to work with VS2005.  The reports shouldn't require any changes.  The changes were to the support dlls that are used by VS2005 to call the reports.

mlmcc
0
 
proc2020Author Commented:
The "invalid report file path" error is resolved for now.
When I send paramters values through the parameter prompt, the report works fine. But when I use a dropdown list to send parameters, the report does not refresh itself with data.
Error in formula . 'if date({Command.workdate}) = date({?MonD}) then ' A date-time is required here. Details: errorKind

Is it a mismatch in date format ? I am using access database.
The query is
SELECT
   tmptim_tbltime.`UserID`, tmptim_tbltime.`workdate`, ...
FROM
    tmptim_tbltime...
WHERE    
   and workdate >= {?param1}
   and workdate <= dateAdd("d",6,{?param1})
and I am sending "dd/mm/yyyy" format date value to both parameters param1 and MonD.
0
 
mlmccCommented:
Which field is highlighted?

mlmcc
0
 
proc2020Author Commented:
I have attached a screenshot of the error.
This is the vb.net code :
Dim reportPath As String = Server.MapPath("TestTimeSht.rpt")
        If Not IO.File.Exists(reportPath) Then
            Throw (New Exception("Unable to locate report : " & vbCrLf & reportPath))
        End If

        rptCount.Load(reportPath)

       
       
        Dim str1 As Date
        str1 = CType(Me.DropDownList1.SelectedValue, Date).ToShortDateString

        rptCount.SetParameterValue("param1", str1)
        rptCount.SetParameterValue("MonD", str1)

        '' Customize the Crystal Reports toolbar.
        CrystalReportViewer1.HasCrystalLogo = False
        CrystalReportViewer1.EnableParameterPrompt = False
        CrystalReportViewer1.HasToggleGroupTreeButton = False
        CrystalReportViewer1.HasDrillUpButton = False
        'CrystalReportViewer1.RefreshReport()

        CrystalReportViewer1.ReportSource = rptCount
crysterr.bmp
0
 
proc2020Author Commented:
Could you send a code snippet on how to send date parameters to crystal reports xi from visual studio 2005 webform?
0
 
proc2020Author Commented:
The only solution I found is to click on the refresh button on the report itself which will refresh the display with current values.
0
 
mlmccCommented:
Here is an example for passing a date parameter
http://support.businessobjects.com/library/kbase/articles/c2010247.asp

mlmcc
0
 
proc2020Author Commented:
Thanks for providing me the links. I had to finally resort to the refresh button on the report to solve the problem.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now