• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1168
  • Last Modified:

Problems with changing the connection info of a crystal report in VB.Net using SqlExpress

Briefly this is the problem..

I have a module in A VB.Net called Globals in there I have 2 connection strings

Public dsn1 As String = "Data Source=192.168.999.99;Initial Catalog=recycle;Persist Security Info=True;User ID=sa;Password=sa;"
    'Public dsn2 As String = "Data Source=.\SQLExpress;Database=recycle;Uid=11;Pwd=11;" 'Single System

I first connection string  is working fine and with the click of the buttons on my forms my codes runs fine and it reads the database fine..

The problem is I had to create a crystal report...so I used the report wizard.In the database expert I picked the name of the database from the list of connections in the database expert, I chose the database which was represented in my globals as dsn1.

This works fine....

The problem is if I wanted the same report  to read the data that was represented by the database for dsn2... How would I do this the 2 databases are identical...except they are in 2 separates places..

So I followed what was at..

This however stops at the following line when I run it ...

 crTable.Location = "recycle" & "." & "BLUE\marc" & "." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)


SOURCE "rptcontrollers.dll"

This is driving me around the bend...

Any help or insight before the weekend begins would be much appreciated
  • 4
  • 3
1 Solution
Are they the same database type?

nomar2Author Commented:
I have a " recycle" database sitting on the sql server...maybe I set up the "recycle" database up wong on the SQL EXPRESS.

When you say type ...what exactly do you mean??
nomar2Author Commented:
Okay I am looking at it this way ...this is the connection string I am using for the connection into the database and it is working on my form in other words with the click of the button data is being pulled from the database and shown on the screen

Public dsn As String = "Data Source=.\SQLExpress;Database=recycle;trusted_Connection=yes;"

With this string in mind...how would I apply it to the following lines of code...with the exact syntax because it keeps saying..  FAILED TO LOAD DATABASE INFORMATION
                                          NO DATABASE dll has been specified

..so I must be missing something

        For Each crTable In crTables
            With crConnInfo
                .ServerName = servername
                 .DatabaseName = databasename
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo

        crTable.Location = databasename & "." & myowner & "." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
        crSections = cr2.ReportDefinition.Sections

How would you find out the value for "myowner" in the above line of code??

I will increase the points if I can get this working
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Type - Are they both SQL Server 7 or is one SQL Server 7 and the other Access.  There may even be a problem with say SQL SERVER xx and SQL Server Express since they may use differnet database drivers or connection methods.

For myowner have you tried dbowner?

nomar2Author Commented:
After much toil...I think I came up with the solution..it seems to be working for me ( fingers crossed)
Public dsn As String = "Data Source=.\SQLExpress;Database=recycle;trusted_Connection=yes;"
        Dim cr As New rpt_SortReceipt
        Dim D As Integer
        Dim lcServerName As String = Nothing
        Dim lcDatabase As String = Nothing
        Dim lcTrusted As String
        Dim lbTrusted As Boolean

        If lnMode = 1 Then
            Dim stringItems() As String = dsn.Split(";")
            If stringItems(0) <> "" Then
                If InStr(stringItems(0), "Data Source=", CompareMethod.Text) > 0 Then
                    lcServerName = Mid(stringItems(0), 13)
                End If
            End If
            If stringItems(1) <> "" Then
                If InStr(stringItems(1), "Database=", CompareMethod.Text) > 0 Then
                    lcDatabase = Mid(stringItems(1), 10)
                ElseIf InStr(stringItems(1), "Initial Catalog=", CompareMethod.Text) > 0 Then
                    lcDatabase = Mid(stringItems(1), 17)
                End If
            End If
            If stringItems(2) <> "" Then
                If InStr(stringItems(2), "trusted_Connection=", CompareMethod.Text) > 0 Then
                    lcTrusted = Mid(stringItems(2), 20)
                    If UCase(lcTrusted) = "YES" Then
                        lbTrusted = True
                    ElseIf UCase(lcTrusted) = "NO" Then
                        lbTrusted = False
                    End If
                End If
            End If

            ' MsgBox(lcServerName)
            ' MsgBox(lcDatabase)
            ' MsgBox(lbTrusted)

            cr.DataSourceConnections.Item(0).SetConnection(lcServerName, lcDatabase, lbTrusted)
            ' cr.SetDatabaseLogon("sa", "sa") ' commented out for my case
            cr.RecordSelectionFormula = "{tInv.IID}= " & lnBeginID & ""
            For D = 1 To 1
                cr.SetParameterValue("prm_InvoiceID", D)
                cr.PrintToPrinter(1, False, 0, 0)

End If
Since the answer is provided, how about PAQ/Refund

nomar2Author Commented:
I have been using the above solution and it is working perfectly...
PAQed with points refunded (50)

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now