3 Replies - 458 Views - Last Post: 01 November 2018 - 05:27 PM Rate Topic: -----

#1 MahdiSec   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-September 18

Generate an RDLC report from stored procedure passing multiples params

Posted 19 October 2018 - 11:14 PM

I have a report that's generated from a stored procedure that passing parameters. I've created a test function that worked just fine for the test scenario. This is a Visual Basic asp.net web form.

As is, the user can enter a job number into a text box and a report is populated and returned, given the job number is valid. My dilemma now is, moving forward...I don't know how to rewrite this function to account for additional criteria. For example, there's 10 text boxes to catch job number, workorder, priority, sequence, etc.

Here's the code that works if a user enters a valid job number into the text box:

Public Class _Default

    Inherits Page

 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

 

        If Not IsPostBack Then

            ReportViewer1.ProcessingMode = ProcessingMode.Local

            ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")

            Dim dsJobs As Jobs = GetData()

            Dim datasource As New ReportDataSource("Jobs", dsJobs.Tables(0))

            ReportViewer1.LocalReport.DataSources.Clear()

            ReportViewer1.LocalReport.DataSources.Add(datasource)

 

        End If

 

    End Sub

 

    Protected Function GetData() As Jobs
  

        Dim conString As String = "Data Source=**************"

        Dim cmd As New SqlCommand("my_stored_procedure")

        Using con As New SqlConnection(conString)

            Using sda As New SqlDataAdapter()

                cmd.Connection = con

                cmd.Parameters.AddWithValue("@job", txtJobs.Text)
               'here would I add the additional parameters for the other txt box controls??
                cmd.CommandType = CommandType.StoredProcedure

                cmd.CommandText = "xl_BarcodeStatus"

                sda.SelectCommand = cmd

                Using dsJobs As New Jobs()

                    sda.Fill(dsJobs, "DataTable1")

                    Return dsJobs

                End Using

            End Using

        End Using

    End Function

 

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

 

        If txtJobs.Text <> "" Then


            ReportViewer1.Visible = True

            ReportViewer1.ProcessingMode = ProcessingMode.Local

            ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")

            Dim dsJobs As Jobs = GetData()

            Dim datasource As New ReportDataSource("Jobs", dsJobs.Tables(0))

            ReportViewer1.LocalReport.DataSources.Clear()

            ReportViewer1.LocalReport.DataSources.Add(datasource)

            txtJobs.Text = ""

        End If

    End Sub

End Class



Would I simply just add the additional parameters like,

cmd.Parameters.AddWithValue("@workorder", txtWorkorders.Text)
cmd.Parameters.AddWithValue("@priorities", txtPriority.Text)


?? Or will I have to write a function for each parameter? If anyone could point me in the right direction I'd be tremendously honored and humbled. Even some links to some tutorials or examples of this being done. Oddly I'm not coming across anything STFW. Thanks in advance....

Is This A Good Question/Topic? 0
  • +

Replies To: Generate an RDLC report from stored procedure passing multiples params

#2 TechnoBear   User is online

  • Lady A
  • member icon

Reputation: 302
  • View blog
  • Posts: 1,148
  • Joined: 02-November 11

Re: Generate an RDLC report from stored procedure passing multiples params

Posted 01 November 2018 - 09:17 AM

DateTime dtStartDate = dateTimePicker1.Value;     
DateTime dtEndDate = dateTimePicker2.Value;     
ReportParameter[] params = new ReportParameter[2]; 
params[0] = new ReportParameter("StartDate", dtStartDate, false); 
params[1] = new ReportParameter("EndDate", dtEndDate, false); 
this.ReportViewer1.ServerReport.SetParameters(params);

This is just to show how Parameters are added, It's written in C# because that is what I am currently working in and couldn't be bothered to open another window. Looks like you are writing in VB, so just convert it to your needs.
Was This Post Helpful? 1
  • +
  • -

#3 MahdiSec   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-September 18

Re: Generate an RDLC report from stored procedure passing multiples params

Posted 01 November 2018 - 04:48 PM

Thank you. I actually gave it in the manner that I'm used to accomplishing such a task. I'm sure we're doing the same thing but going about it in a different way. For instance:
 
Dim strJob As String

Dim strWOID As String

Dim strBarCode As String



and then-
strJob = jobTextBox.Text.ToString

strWOID = workorderTextBox.Text.ToString

strBarCode = barcodeTextBox.Text.ToString


Next,
 
Dim pJob As SqlParameter = New SqlParameter("@job", SqlDbType.NVarChar, 21)

cmd.Parameters.Add(pJob)

Dim pWOID As SqlParameter = New SqlParameter("@workorder", SqlDbType.NVarChar, 1500)

cmd.Parameters.Add(pWOID)

Dim pBarCode As SqlParameter = New SqlParameter("@barcode", SqlDbType.NVarChar, 1500)

cmd.Parameters.Add(pBarCode)


And finally... (and so forth to account for the additional criteria)
 
pJob.Value = strJob

pWOID.Value = strWOID

pBarCode.Value = strBarCode



Now, I have a hunch that's the long way of doing it and there's certainly a more condensed elegant way of getting that accomplished? But hey, it's pulling data from the database and populating records into the report accordingly. However, now I'm faced with a somewhat different problem.

This code here that I have on my button click event-
Protected Sub shwClmnsRprtButton_Click(sender As Object, e As EventArgs) Handles shwClmnsRprtButton.Click

 

        'If jobTextBox.Text & workorderTextBox.Text & barcodeTextBox.Text <> "" Then

 

        If jobTextBox.Text <> "" Then

 

            ReportViewer1.Visible = True

            ReportViewer1.ProcessingMode = ProcessingMode.Local

            ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/BCStatusReport2.rdlc")

            Dim dsJobs As Jobs = GetData()

            Dim datasource As New ReportDataSource("Jobs", dsJobs.Tables(0))

            ReportViewer1.LocalReport.DataSources.Clear()

            ReportViewer1.LocalReport.DataSources.Add(datasource)

            jobTextBox.Text = ""

 

 

 

        End If

 

    End Sub




Right now, everything is working as coded with the Report loading in ReportViewer at the top of the web page. What I'd like to do is when the user clicks the button, is have the report and ReportViewer open in a separate web page. I couldn't figure out how to do that by storing persistent values through session variables. I tried another approach using the AJAX popupextender in a panel but that didn't work because my script manager is on Site.Master (my master page)and when I try compiling it tells me that "there can only be one instance of scriptmanager per page" <-- something to that nature. My other approach is to hide all the other controls on button click except the relevant ones and adding a button to restore visible should the user need to go back. (granted, that's overkill IMHO). I'm open to any other solutions you can suggest while I stay slugging at it.

Thank you for your response.
Was This Post Helpful? 0
  • +
  • -

#4 TechnoBear   User is online

  • Lady A
  • member icon

Reputation: 302
  • View blog
  • Posts: 1,148
  • Joined: 02-November 11

Re: Generate an RDLC report from stored procedure passing multiples params

Posted 01 November 2018 - 05:27 PM

I'm not in front of a computer now until Monday, and I'm not as great at RDLC from memory because I haven't used it in nearly 10 years. However, you can create other pages, direct it to that page and store the variables as session variables or pass them as URL parameters.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1