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

How connect to database without DSN?

I have written many applications in vb 6 that produce reports in cr 9.  Each time I use a DSN to connect to the MS SQL Server database both in my vb 6 applications and in the cr 9 reports.  All the cr reports are imported into vb 6.  That always works just fine.

Now, however, my company is asking me to not use DSNs any more and to, instead, use connection strings.  So in my vb 6 code I now use:  

cnOCCS_Job_Request.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OCCS_Job_Request;Data Source=LVXS7601"

instead of:

cnOCCS_Job_Request.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DSN-OCCS_Job_Request"

That method works fine in vb 6.  It also works fine in cr 9 when I create and run the report from within the cr developement environment.  But when I import the cr report into vb 6 and run it, I get the following error message:  "Crystal Reports Viewer Logon failed.  Details:  ADO Error Code: xxxx  Source: Microsoft OLE DB Provider for SQL Server.  Description:  Login failed for user 'sa'...."

I have tried in cr 9 using both the ODBC (RDO) and the OLE DB (ADO) connection methods, but I can't figure out how to get them to work, i.e. to successfully run in my vb 6 application.

Thank you for the help.
0
EYoung
Asked:
EYoung
  • 3
  • 2
1 Solution
 
janmariniCommented:


I'm thinking that you probably have to log in  to each of the tables in your report (and any subreports).  I have code to do this for VB.Net - but it's a little different for VB6 which I haven't used in years, but the concept's pretty much the same - just different syntax.

Here's a link to a similar question that might help you:  

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21587941.html?query=vb6+login+table&topics=40


Here's a code snippet for VB.Net
        'Logs into the tables in the report
        crReportDocument = rptReport
        crDataBase = crReportDocument.Database
        crTables = crDataBase.Tables
        For Each crTable In crTables
            With crConnInfo
                .ServerName = strServer
                .DatabaseName = strDBase
                .UserID = ConfigurationSettings.AppSettings("strUID")
                .Password = ConfigurationSettings.AppSettings("strPWD")
            End With

            crTableLogonInfo = crTable.LogOnInfo
            crTableLogonInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crTableLogonInfo)
            crTable.Location = strDBase & ".dbo." & crTable.Name
            crTable.ApplyLogOnInfo(crTableLogonInfo)
        Next

        'Logs into the tables in the Sub-reports
        crSections = crReportDocument.ReportDefinition.Sections
        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then
                    crSubreportObject = CType(crReportObject, SubreportObject)
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    crDataBase = subRepDoc.Database
                    crTables = crDataBase.Tables
                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = strServer
                            .DatabaseName = strDBase
                            .UserID = ConfigurationSettings.AppSettings("strUID")
                            .Password = ConfigurationSettings.AppSettings("strPWD")
                        End With
                        crTableLogonInfo = crTable.LogOnInfo
                        crTableLogonInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crTableLogonInfo)
                        crTable.Location = strDBase & ".dbo." & crTable.Name
                    Next
                End If
            Next
        Next

0
 
mlmccCommented:
Are you building your reports against the prod database or a development one?

Jan's code is pretty good.

mlmcc
0
 
EYoungAuthor Commented:
I am building it against a production database.

Why do I have to log into each table in vb6?

Thanks for the help.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mlmccCommented:
If you use OLE connection and Windows Authentication for the database you won't have to log in.  You will have to put all the Windows logins into a databse group and give permissions to the group.

mlmcc
0
 
EYoungAuthor Commented:
Thanks everyone for the help.

Can you also look at this question:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21797586.html

Thanks
0
 
mlmccCommented:
Gald i could help

mlmcc
0

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.

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