Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Passing parameters from VB.Net to SQL Query

Posted on 2008-06-23
17
Medium Priority
?
3,853 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
[X]
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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

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!

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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