locked
Passing parameter value to crystal report in Vb.net RRS feed

  • Question

  •  

    I am really frustrated right now. I need to send parameter to crystal report and I am using sql query as a datasource of my report.

    Here is my code:

    Dim rpt As New rptIntervals()

            Dim con As SqlConnection

            Dim sql As String

            Dim da As New SqlDataAdapter

            Dim ds As New DataSet

            Dim dc As CrystalDecisions.Shared.DiscreteOrRangeKind

            con = New SqlConnection(strCon)

            con.Open()

            Sql = " SELECT     WorDescription, isnull(d14,'') as d14 ,"

            Sql &= " isnull(d29,'') as d29, isnull(d44,'') as d44,"

            Sql &= " isnull(d59,'') as d59, isnull(d74,'') as d74,"

            Sql &= " isnull(d89,'') as d89, isnull(d90,'') as d90"

            Sql &= " FROM dbo.vIntervals "

     

            da = New SqlDataAdapter(Sql, con)

            da.Fill(ds)

            rpt.Refresh()

            rpt.SetDataSource(ds.Tables(0))

            rpt.SetParameterValue("@Test", txtStartdate.Text) ***

            rpt.Refresh()

            CrystalReportViewer1.ReportSource = rpt

            rpt.Refresh()

            CrystalReportViewer1.Zoom(75)

            Publicds = ds.Copy()

    Line *** is not working and each time I run the application ,Enter Parameter value window pops up and asks for Discrete value!!!!!!

     

    Whats hould I do Please Please Please Please Help me.

    • Moved by CaddreModerator Saturday, September 6, 2008 1:43 PM wrong forum (Moved from Visual Basic General to Crystal Reports for Visual Studio)
    Friday, September 5, 2008 10:23 PM

Answers

  • I found it:
                    Dim paramFields As New CrystalDecisions.Shared.ParameterFields()  
                    Dim paramField As New CrystalDecisions.Shared.ParameterField()  
                    Dim discreteVal As New CrystalDecisions.Shared.ParameterDiscreteValue()  
                    Dim paramField2 As New CrystalDecisions.Shared.ParameterField()  
                    Dim discreteVal2 As New CrystalDecisions.Shared.ParameterDiscreteValue()  
     
                    paramField.ParameterFieldName = "s" 
                    Dim str As String = txtStartdate.Text.ToString  
                    discreteVal.Value = str 
                    paramField.CurrentValues.Add(discreteVal)  
                    paramFields.Add(paramField)  
     
     
     
                    paramField2.ParameterFieldName = "E" 
                    Dim str2 As String = txtEndDate.Text.ToString  
                    discreteVal2.Value = str2 
                    paramField2.CurrentValues.Add(discreteVal2)  
                    paramFields.Add(paramField2)  
                    CrystalReportViewer1.ParameterFieldInfo = paramFields 
    • Marked as answer by jack 321 Wednesday, September 10, 2008 6:17 AM
    Monday, September 8, 2008 10:46 PM

All replies

  • Make sure you pass all the parameter values that the report needs also just to test hard code some value and see how it behaves, if report pops out then check the value passed and its datatype.
    Saturday, September 6, 2008 11:39 PM
  •  Hi,Thanks for reply.
    I had done all yout tips but I still have the same problem.

    The other problem that I am facing now is my subreport which is another report is not working inside the master report but when I run it alone it is working fine !!!

    Here is the code:
    Private Sub btnNewSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewSearch.Click
    Dim rpt As New rptActiveCase()
    Dim rptSub As New rptActiveCaseIssue()
    Dim subReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
    Dim con As SqlConnection
    Dim sql As String
    Dim da As New SqlDataAdapter
    Dim ds As New DataSet
    Dim sqlSub As String
    Dim daSub As New SqlDataAdapter
    Dim dsSub As New DataSet
    con = New SqlConnection(strCon)
    con.Open()
    sql = " SELECT [Case ID], [Open Date], Name, Worker, Service, [Issue Type], Fee,OD"
    Sql &= " FROM dbo.vOpenCase "
    If txtStartdate.Text <> "" And txtEndDate.Text <> "" Then sql &= " WHERE (OD BETWEEN '" & txtStartdate.Text & "' AND '" & txtEndDate.Text & "')"
    da = New SqlDataAdapter(Sql, con)
    da.Fill(ds)
    rpt.Refresh()
    rpt.SetDataSource(ds.Tables(0))

    sqlSub = "SELECT [Issue Type], COUNT([Issue Type]) AS Iss"
    sqlSub &= " FROM dbo.vOpenCase "
    If txtStartdate.Text <> "" And txtEndDate.Text <> "" Then sqlSub &= " WHERE (OD BETWEEN '" & txtStartdate.Text & "' AND '" & txtEndDate.Text & "') AND ([Issue Type] IS NOT NULL)"
    sqlSub &= " GROUP BY [Issue Type]"
    sqlSub &= " UNION ALL "
    sqlSub &= " SELECT 'ServiceDecsription' AS Expr1, '99999' AS counting"
    sqlSub &= " UNION ALL "
    sqlSub &= " SELECT Service AS Description, COUNT(Service) AS counting"
    sqlSub &= " FROM dbo.vOpenCase AS vOpenCase_1"
    If txtStartdate.Text <> "" And txtEndDate.Text <> "" Then sqlSub &= " WHERE (OD BETWEEN '" & txtStartdate.Text & "' AND '" & txtEndDate.Text & "') AND ([Service] IS NOT NULL)"
    sqlSub &= " GROUP BY Service"
    sqlSub &= " UNION ALL "
    sqlSub &= " SELECT 'Fee' AS Expr1, '99999' AS counting"
    sqlSub &= " UNION ALL "
    sqlSub &= " SELECT Fee AS Description, COUNT(Fee) AS counting"
    sqlSub &= " FROM dbo.vOpenCase AS vOpenCase_1"
    If txtStartdate.Text <> "" And txtEndDate.Text <> "" Then sqlSub &= " WHERE (OD BETWEEN '" & txtStartdate.Text & "' AND '" & txtEndDate.Text & "') AND ([Fee] IS NOT NULL)"
    sqlSub &= " GROUP BY Fee"


    daSub = New SqlDataAdapter(sqlSub, con)
    daSub.Fill(dsSub)
    rptSub.Refresh()
    rptSub.SetDataSource(dsSub.Tables(0))
    rptSub.Refresh()
    subReport = rpt.OpenSubreport("rptActiveCaseIssue")
    subReport.SetDataSource(dsSub)
    rpt.Refresh()
    CrystalReportViewer1.Zoom(75)
    Publicds = ds.Copy()
    CrystalReportViewer1.ReportSource = rpt
    rpt.OpenSubreport("rptActiveCaseIssue")
    CrystalReportViewer1.RefreshReport()
    End Sub



    Please help me now I have 2 problem and I really need help.
    Sunday, September 7, 2008 12:00 AM
  • Hi. Sepidehhhhhh

    Did you try to use

            rpt.SetParameterValue("Test", txtStartdate.Text)
    instead of

            rpt.SetParameterValue("@Test", txtStartdate.Text) ***


    I assume your parameter's name is Test, not @Test.

    Hope that helps.
    • Proposed as answer by Sean Zhu Sunday, September 7, 2008 4:03 AM
    Sunday, September 7, 2008 4:03 AM
  • Hi Sean Zhun,
    Thank you for your reply but 

    Unfortunately the name of the parameter is correct and it has @ in the beginning.Any other suggestion?

    Monday, September 8, 2008 3:47 PM
  • Since you mentioned  sub-report. By any chance, that you forgot to pass a parameter for sub-report?
    Monday, September 8, 2008 8:18 PM
  • No.Subreport does not have any parameter.
    Monday, September 8, 2008 8:47 PM
  • I found it:
                    Dim paramFields As New CrystalDecisions.Shared.ParameterFields()  
                    Dim paramField As New CrystalDecisions.Shared.ParameterField()  
                    Dim discreteVal As New CrystalDecisions.Shared.ParameterDiscreteValue()  
                    Dim paramField2 As New CrystalDecisions.Shared.ParameterField()  
                    Dim discreteVal2 As New CrystalDecisions.Shared.ParameterDiscreteValue()  
     
                    paramField.ParameterFieldName = "s" 
                    Dim str As String = txtStartdate.Text.ToString  
                    discreteVal.Value = str 
                    paramField.CurrentValues.Add(discreteVal)  
                    paramFields.Add(paramField)  
     
     
     
                    paramField2.ParameterFieldName = "E" 
                    Dim str2 As String = txtEndDate.Text.ToString  
                    discreteVal2.Value = str2 
                    paramField2.CurrentValues.Add(discreteVal2)  
                    paramFields.Add(paramField2)  
                    CrystalReportViewer1.ParameterFieldInfo = paramFields 
    • Marked as answer by jack 321 Wednesday, September 10, 2008 6:17 AM
    Monday, September 8, 2008 10:46 PM