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

x
?
Solved

Passing parameters from VB.Net to SQL Query

Posted on 2008-06-23
17
Medium Priority
?
3,857 Views
Last Modified: 2013-11-15
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
Comment
Question by:proc2020
  • 10
  • 7
17 Comments
 

Author Comment

by:proc2020
ID: 21847375
If I sent data via the parameter prompt, the report displays data correctly.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 21847849
What error are you getting?

Does the report have saved data?

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

mlmcc
0
 

Author Comment

by:proc2020
ID: 21848103
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 101

Expert Comment

by:mlmcc
ID: 21848550
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
 

Author Comment

by:proc2020
ID: 21848969
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 21849761
It shouldn't be.

mlmcc
0
 

Author Comment

by:proc2020
ID: 21850053
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 21850262
0
 

Author Comment

by:proc2020
ID: 21850955
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 21851369
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
 

Author Comment

by:proc2020
ID: 21856509
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 21857578
Which field is highlighted?

mlmcc
0
 

Author Comment

by:proc2020
ID: 21857707
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
 

Author Comment

by:proc2020
ID: 21860665
Could you send a code snippet on how to send date parameters to crystal reports xi from visual studio 2005 webform?
0
 

Author Comment

by:proc2020
ID: 21860953
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 21861368
Here is an example for passing a date parameter
http://support.businessobjects.com/library/kbase/articles/c2010247.asp

mlmcc
0
 

Author Closing Comment

by:proc2020
ID: 31469748
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

963 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