locked
How can i programmatically change the datasource location? RRS feed

  • Question

  • Hi, I'm using VS 2005 and vb.net, at the moment i have a scenario where i have a report .rdlc which is an envelope format and i have some code when the program loads to select which range of clients i want to print envelopes for and it fills the datasource ("MainDataSource" from the built in datasource functionality) using an sql query .selectcommand change which works beautifully... however if i load the program onto another machine it looks for the location of "MainDataSource" in the local folder on my machine and so it fails to load because the same foler isnt there on that machine. Is there any way i can change the MainDataSource file location programmatically so it'll work on another machine or specify the datasource for the envelope reports that is not a built in datasource so i can use OLEDB or ADODB and a connection string to link it to? Thanks

    Michael
    Tuesday, June 9, 2009 8:09 AM

Answers

  • I've solved the problem in case anyone is reading. It was just a simple line of code that needed to be added in the reporting forms' vb code. Mine is called frmReports.vb. You put this line in under form load replacing tblClientTableAdapter with your table adapter name:

    Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath


    If you have other code under load that fills the table just put it up the top so it'd look like this:

    Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath
        Me.tblClientTableAdapter.Fill(Me.MainDataSource.tblClient)
        Me.ReportViewer1.RefreshReport()
    End Sub


    I have mine in another class called LoadSQL because i need to change the sql query at run time. If anyone is wondering how to do this my entire frmreports.vb file is like this:

    Public Class frmReports
        Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Application.DoEvents()
            Me.ReportViewer1.RefreshReport()
        End Sub
    
        Public Sub LoadSQL(ByVal Query As String)
            Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath
            Me.tblClientTableAdapter.SelectCommand(0).CommandText = Query
            Me.tblClientTableAdapter.Fill(Me.MainDataSource.tblClient)
        End Sub
    End Class
    ...and the file which supports the SelectCommand() statement looks like this... (below) so you need to create a new .vb file, i called this one ReportsClass.vb:

    Option Strict Off
    Option Explicit On
    
    Imports System
    
    Namespace MainDataSourceTableAdapters
        Partial Public Class tblClientTableAdapter
            Inherits System.ComponentModel.Component
    
            Public Property SelectCommand() As OleDb.OleDbCommand()
                Get
                    If (Me._commandCollection Is Nothing) Then
                        Me.InitCommandCollection()
                    End If
                    Return Me._commandCollection
                End Get
    
                Set(ByVal value As OleDb.OleDbCommand())
                    Me._commandCollection = value
                End Set
            End Property
        End Class
    End Namespace
    ...then i simply load and show the report by these lines:

    frmReports.LoadSQL("SELECT * FROM tblClient WHERE ClientIndexNumber=" & IndexNumber)
    frmReports.Show(Me)

    Michael
    • Marked as answer by Michael.SG Tuesday, July 7, 2009 5:15 AM
    • Edited by Michael.SG Tuesday, July 7, 2009 5:24 AM
    Tuesday, July 7, 2009 5:15 AM

All replies

  • Hi Michael,

    I'm not sure what you mean by built in datasource functionality. Is MainDataSource a file or an object?

    Can you post some code that shows how MainDataSource is loaded and used? It may be just a case of needing to use relative paths.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 10, 2009 5:16 PM
  • Built in data source functionality... in the solution explorer theres a tab down the bottom of that that says Solution Explorer / Data Sources.. click data sources and MainDataSource is in there.

    I've already changed the code to allow the sql query to be changed and applied at runtime for this datasource (this had to be put in by creating aother class editing the namespace and putting Public Property SelectCommand() in.. otherwise it doesnt let you do it), i just need a way to change the file location of the data source now, if thats possible. Or if theres any other way to have a data object using a .mdb file as the source and the microsoft inbuilt reporting functionality as the output, being able to change the file location and the sql query at runtime.
    Thursday, June 11, 2009 9:20 AM
  • Does anyone know?
    Wednesday, June 17, 2009 7:35 AM
  • I've solved the problem in case anyone is reading. It was just a simple line of code that needed to be added in the reporting forms' vb code. Mine is called frmReports.vb. You put this line in under form load replacing tblClientTableAdapter with your table adapter name:

    Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath


    If you have other code under load that fills the table just put it up the top so it'd look like this:

    Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath
        Me.tblClientTableAdapter.Fill(Me.MainDataSource.tblClient)
        Me.ReportViewer1.RefreshReport()
    End Sub


    I have mine in another class called LoadSQL because i need to change the sql query at run time. If anyone is wondering how to do this my entire frmreports.vb file is like this:

    Public Class frmReports
        Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Application.DoEvents()
            Me.ReportViewer1.RefreshReport()
        End Sub
    
        Public Sub LoadSQL(ByVal Query As String)
            Me.tblClientTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFilePath
            Me.tblClientTableAdapter.SelectCommand(0).CommandText = Query
            Me.tblClientTableAdapter.Fill(Me.MainDataSource.tblClient)
        End Sub
    End Class
    ...and the file which supports the SelectCommand() statement looks like this... (below) so you need to create a new .vb file, i called this one ReportsClass.vb:

    Option Strict Off
    Option Explicit On
    
    Imports System
    
    Namespace MainDataSourceTableAdapters
        Partial Public Class tblClientTableAdapter
            Inherits System.ComponentModel.Component
    
            Public Property SelectCommand() As OleDb.OleDbCommand()
                Get
                    If (Me._commandCollection Is Nothing) Then
                        Me.InitCommandCollection()
                    End If
                    Return Me._commandCollection
                End Get
    
                Set(ByVal value As OleDb.OleDbCommand())
                    Me._commandCollection = value
                End Set
            End Property
        End Class
    End Namespace
    ...then i simply load and show the report by these lines:

    frmReports.LoadSQL("SELECT * FROM tblClient WHERE ClientIndexNumber=" & IndexNumber)
    frmReports.Show(Me)

    Michael
    • Marked as answer by Michael.SG Tuesday, July 7, 2009 5:15 AM
    • Edited by Michael.SG Tuesday, July 7, 2009 5:24 AM
    Tuesday, July 7, 2009 5:15 AM