Solved

Attach a dataset to a reportview in VB.NET

Posted on 2010-11-29
5
983 Views
Last Modified: 2012-05-10
I am new to reportviewer in visual studio 2008.

I want to create a dataset using code and then display that in a report.

Basically I need to pass a variable to the reportviewer sql statement so it only display's data based on that variable.

Something like "select WO_TYPE, WO_TYPE_ID from WO_TABLE where WO_TYPE = 'variable.value' and then the report will only display the report where that value = the variable.

I have tried to create a report and bind it to the dataset using the qui then modify the binding to use my code generated dataset. But I haven't been successful.  

If any body can tell me how to create a report based on a variable it would be great.

Thanks
Fixitben
0
Comment
Question by:fixitben
5 Comments
 
LVL 2

Assisted Solution

by:yatin_81
yatin_81 earned 166 total points
ID: 34232783
See the code ::
ReportViewer1.ServerReport.ReportServerCredentials.NetworkCredentials = New System.Net.NetworkCredential("report", "report")

      ReportViewer1.Height = Me.Height - 50

      ReportViewer1.Width = Me.Width - 20

      Dim strEntityAdd, strEntityAdd2, strEntityTel, strEntityFax, strEntityWeb As String

      Dim cmd As New SqlCommand("select entityadd,entityadd2,entitytel,entityfax,entityweb from entitymaster where entitycode='" & gbl_entitycode & "'", _Globalobj.globalconn)

      'cmd.CommandType = CommandType.StoredProcedure

      Dim ds As New DataSet

      Dim da As New SqlDataAdapter

      da.SelectCommand = cmd

      da.Fill(ds)



      Me.ReportViewer1.RefreshReport()

      Me.ReportViewer1.ShowExportButton = False

      Me.ReportViewer1.ShowPrintButton = False



      ReportViewer1.ServerReport.ReportServerUrl = New Uri("http://" & rsServerName & ":" & rsPort & "/" & rsSite)

      If strBuySell = "S" Then

        Me.ReportViewer1.ServerReport.ReportPath = "/" & curdb & "/RDLName"

      Else

        Me.ReportViewer1.ServerReport.ReportPath = "/" & curdb & "/RDLName"

      End If

      'ReportViewer1.ServerReport.ReportServerCredentials.ImpersonationUser

      Dim p(3) As Microsoft.Reporting.WinForms.ReportParameter

      p(0) = New Microsoft.Reporting.WinForms.ReportParameter("FromDate", strFromDate)

      p(1) = New Microsoft.Reporting.WinForms.ReportParameter("ToDate", strToDate)

      p(2) = New Microsoft.Reporting.WinForms.ReportParameter("FromCode", strFromCode)

      p(3) = New Microsoft.Reporting.WinForms.ReportParameter("ToCode", strToCode)



      ReportViewer1.ServerReport.SetParameters(CType(p, Global.System.Collections.Generic.IEnumerable(Of Global.Microsoft.Reporting.WinForms.ReportParameter)))

      ReportViewer1.ShowParameterPrompts = False

      ReportViewer1.ProcessingMode = ProcessingMode.Remote

      ReportViewer1.RefreshReport()

Open in new window

0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 167 total points
ID: 34233056
Here is complete tutorial about parameters in reporting services

http://msdn.microsoft.com/en-us/library/aa337432%28v=SQL.90%29.aspx
0
 
LVL 1

Accepted Solution

by:
RADONE242 earned 167 total points
ID: 34233274
Could you put the sql results into a dataset and add the dataset to the reports datasources?

        Me.ctlReportViewer.LocalReport.DataSources.Clear()
       Me.ctlReportViewer.LocalReport.DataSources.Add(New ReportDataSource("DataSourceName", Dataset))
       
        'Parameters
        Me.ctlReportViewer.LocalReport.SetParameters(parItems)
        'Refresh report
        Me.ctlReportViewer.RefreshReport()

Once the datasource has been established you can sort and filter in any table that has the datasource selected.
0
 
LVL 7

Assisted Solution

by:fixitben
fixitben earned 0 total points
ID: 34234153
I got it working using a generated report using the wizard based on a dataset.

Then using this code I can create the same dataset with the changes to the where statement based on a variable.

Now I can control the report and have VS create the report for me.

Thanks For your help.
Fixitben


Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports Microsoft.Reporting.WinForms

Public Class Form2
    Private strConnectionString As String = "Provider=SQLOLEDB;Data Source=xxxxxx;Initial Catalog=xxxxxx;Integrated Security=SSPI;"
    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataTable As DataTable
    Public connectionstring As String
    Private Value As Integer = 1
   

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet2.DataTable1' table. You can move, or remove it, as needed.




        ReportLoad()
        Me.ReportViewer1.RefreshReport()





        'TODO: This line of code loads data into the 'DataSet2.DataTable1' table. You can move, or remove it, as needed.
       
    End Sub



    Private Sub ReportLoad()
        objConnection = New OleDbConnection(strConnectionString)
        objCommand = New OleDbCommand("SELECT	   STAFF_FNAME, STAFF_LNAME, SKILL_NAME, SKILL_DESC, SKILL_LEVEL, ACTIVE FROM STAFF, SKILL_LEVEL, SKILL, SKILL_ACQ WHERE STAFF.STAFF_ID = SKILL_ACQ.STAFF_ID AND SKILL_LEVEL.SKILL_LEVEL_ID = SKILL_ACQ.SKILL_LEVEL_ID AND	   SKILL.SKILL_ID = SKILL_ACQ.SKILL_ID AND STAFF.STAFF_FNAME = '"+texbox.text.tostring+"' ORDER BY STAFF_FNAME", objConnection)

        'Initialize the DataAdapter object and set the SelectCommand property
        objDataAdapter = New OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        'Initialize the DataTable object
        objDataTable = New DataTable
        Dim DataSet2 As DataSet2.DataTable1DataTable = New DataSet2.DataTable1DataTable
        'Populate the DataTable
        objDataAdapter.Fill(DataSet2)


        Dim ReportDataSource As New ReportDataSource("DataSet2_DataTable1", DataSet2)
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(ReportDataSource)
        Me.ReportViewer1.RefreshReport()



        ' Me.DataTable1TableAdapter.Fill(Me.DataSet2.DataTable1)

        '  Me.ReportViewer1.RefreshReport()



    End Sub
End Class

Open in new window

0
 
LVL 7

Author Closing Comment

by:fixitben
ID: 34272815
Thanks guys. You got me pointed in the right direction.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

12 Experts available now in Live!

Get 1:1 Help Now