Solved

Passing parameters from VB.Net to SQL Query

Posted on 2008-06-23
17
3,836 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 100

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
 
LVL 100

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 100

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 100

Expert Comment

by:mlmcc
ID: 21850262
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 100

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 100

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 100

Accepted Solution

by:
mlmcc earned 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now