Help with dynamic server/db change - vb.net cr9/ce9

[ciscowiz]

We are using CR9 and CE9 with the acitvex viewer to call reports through our vb.net web app.

We use a datagrid, populate the grid with the reports based on a property "coid", so whatever the user's "coid" is, they would get the grid populated with the reports in that specific folder. For instance the reports are in reports/"coid". We use SQL Server system DSNs for all of the login info for the reports. Currently, we need the same set of reports for each "coid". Well as we grow and add more "coid"s, managing the reports is becoming a nightmare as each "coid" has about 70 reports.

We need one basic set of reports and to be able to just use the datagrid to populate with that general set of reports. Then when a user clicks on a report listed in the grid, it should automatically change the DSN and update the views/SPs to whatever "coid" the user is. This is what I do not know how to do.

Below is the current code we use to populate the datagrid and open the activex viewer:

Dim dv As New DataView

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Dim di As Directory
            If di.Exists("d:\XXXXXXX\webpub\reports\" & Request.QueryString("coid") & "\") Then
                Dim fi As FileInfo()
                Dim dis As New DirectoryInfo("d:\ctg\webpub\reports\" & Request.QueryString("coid") & "\")
                fi = dis.GetFiles()
                Dim dt As New DataTable("Files")
                dt.Columns.Add(New DataColumn("FileName"))
                dt.Columns.Add(New DataColumn("CreatedDate"))
                dt.Columns.Add(New DataColumn("ModifiedDate"))
                For Each f As FileInfo In fi
                    If LCase(Right(f.Name.ToString, 4)) = ".rpt" Then
                        Dim dr As DataRow = dt.NewRow()
                        dr("FileName") = Left(f.Name.ToString, Len(f.Name.ToString) - 4)
                        dt.Rows.Add(dr)
                    End If
                Next

                dv.Table = dt
                Session("dvdocs") = dv
                DataGrid1.DataSource = dv
                DataGrid1.DataBind()
                addAttrib()
            End If
        End If
    End Sub
Private Sub addAttrib()
        Dim strColor As String = "white"
        Dim UserAgent As String = Request.ServerVariables("HTTP_USER_AGENT")
        For Each i As DataGridItem In DataGrid1.Items
            i.Attributes.Add("onmouseover", "style.backgroundColor='yellow';style.cursor='hand';")
            i.Attributes.Add("onmouseout", "style.backgroundColor='" & strColor & "';style.cursor='default';")
            If InStr(1, UserAgent, "Macintosh") > 0 Or InStr(1, UserAgent, "Mac_PowerPC") > 0 Then
                i.Attributes.Add("onclick", "window.open('/reports/" & Request.QueryString("coid") & "/" & dv.Table.Rows(i.DataSetIndex)("FileName") & ".rpt?init=html_frame&apsuser=administrator&apspassword=&apsauthtype=secenterprise')")
            Else
                i.Attributes.Add("onclick", "style.color='purple';window.open('/reports/" & Request.QueryString("coid") & "/" & dv.Table.Rows(i.DataSetIndex)("FileName") & ".rpt?init=actx&apsuser=administrator&apspassword=&apsauthtype=secenterprise')")
            End If
            If strColor = "white" Then
                strColor = "#cccccc"
            Else
                strColor = "white"
            End If
        Next
    End Sub

We want to keep this same method of calling reports so what would I need to do to be able to change the login info? It would be nice if you could pass info into the query string but I don't think it works like that.

Thanks in advance,
Bryan