Vendor code 12154 and 1005 error

[Former Member]

Hi,

I have developed an application which users the RDC API set (CR XI R2 SP4.5 version) to preview crystal reports. All types of reports works fine but except one. This report is a blank report with one sub-report. I have used native oracle connection to connect to the Oracle database when creating this layout. The main report is blank but it has a sub report with one view. When I run the report from Crystal Report Designer it works fine, but when I run it through my application it give the following error. Other report with sub-reports where the main report also has views works fine. Itu2019s just that reports with sub-reports where there are no views in the main report gives this error. I have debugged the code and found that it gives this error when Iu2019m setting the table location of the view in the sub-report. Hope you can help me to solve this issue.

I get two errors and they are,

Fail to open the connection. Database Vendor Code 12154

Fail to open the connection. Database Vendor Code 1005

Thanks in advance,

Chanaka

[former_member183750 · Accepted solution]

Please provide the code you are using to log on to the database. Also, open the report in the CR designer, got to the subreport. Then to the Database menu and expand the properties icon. What does it say at Database type?

Also, please confirm that this is happening on the same computer where you designed the report?

And, if you take out the subreport and run it on it's own as a main report, can you log on to it in code?

Ludek

Edited by: Ludek Uher on Mar 19, 2009 6:26 AM

[Former Member · Accepted solution]

Hi Ludek,

Thank you for replying to my question. Here are the information you requested for.

Q: Also, please confirm that this is happening on the same computer where you designed the report?

A: I get the same errors when I run my application and try to view the reports from the same machine where I have installed Crystal Report Designer and created the reports from.

Q: And, if you take out the subreport and run it on it's own as a main report, can you log on to it in code?

A: If I take the sub-report and run it on itu2019s own it works fine from my code

Normally when I create reports with sub-reports I donu2019t browse and import and existing sub-report but I create a sub-report with the Report Wizard. All sub-reports are not on demand sub-reports as well.

Also, as I said before, other reports work fine. That is, reports without sub-reports which has views in the main report, reports without sub-reports where there are no views in the main report as well (blank report), reports with sub-reports where there are views in the main and sub-reports both. Also, for all these combinations I have checked with and without parameters in the main and sub-reports. The errors are only shown when the main report is blank (no views added) and has a sub-reports with views.

Q: Also, open the report in the CR designer, got to the subreport. Then to the Database menu and expand the properties icon. What does it say at Database type?

A: Selecting the sub-report and going the Database\Set Database Location and by clicking on the Properties dropdown (the + sign) I can see that the Database Type is Oracle Server which is correct as we always use the Native Oracle connection to connect to the database at design-time and at runtime when previewing the report from the application.

Q: Please provide the code you are using to log on to the database

A: Code is given below.

Further investigating in to the issue I saw that if the sub-report has parameters I get the error u201CFail to open the connection. Details: [Database Vendor Code: 12154]u201D. If the sub-reports doesnu2019t have parameters then I get the error u201CLogon failed. Details: [Database Vendor Code: 1005]u201D. Debugging in to the code from VB I see that itu2019s throwing the error when I try to set the table location for a view in the sub-report. Here the error number is -2147189176 and the error description is Logon failed. My guess is that both the errors are linked to this location setting and the main report not having views or database connection.

Regards,

Chanaka

In Main section

For tableCountInMainReport= 1 To crxReport.database.Tables.count
  RDCSetNthTableLogOnInfo(u2026u2026u2026.)
  RDCSetNthTableLocation(u2026u2026u2026u2026u2026..)
Next tableCountInMainReport

For each section in report 
   If sub-report exist
       RDCSetNTableLogonAndLocSubReport(u2026u2026u2026u2026u2026)
   End if
Next section

Methods called from the main method

Public Function RDCSetNthTableLogOnInfo(ByVal tableIndex As Integer, ByVal server As String, ByVal database As String, ByVal userName As String, ByVal password As String) As String

   On Error GoTo ErrorHandler
   crxReport.database.Tables(tableIndex).ConnectionProperties.Item("Server") = server
   crxReport.database.Tables(tableIndex).ConnectionProperties.Item("User ID") = userName
   crxReport.database.Tables(tableIndex).ConnectionProperties.Item("Password") = password

   If (database <> Null) Or (database <> "") Then
      crxReport.database.Tables(tableIndex).ConnectionProperties.Add "Database", database
      RDCSetNthTableLogOnInfo = "True"
   End If
   
   RDCSetNthTableLogOnInfo = "True"

ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Error in setting table logon information for " & crxReport.database.Tables(tableIndex).Name & "." & "Error number : " & Err.Number & " Error description : " & Err.Description, vbOKOnly, "Error"
      RDCSetNthTableLogOnInfo = "CR_ERROR"
   End If

End Function

Public Function RDCSetNthTableLocation(ByVal tableIndex As Integer, ByVal location As String) As String

   On Error GoTo ErrorHandler
   crxReport.database.Tables(tableIndex).location = location + "." + crxReport.database.Tables(tableIndex).location
   RDCSetNthTableLocation = "True"

ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Error in setting the table location. Error number : " & Err.Number & " Error description : " & Err.Description, vbOKOnly, "Error"
      RDCSetNthTableLocation = "CR_ERROR"
   End If
  
End Function

Public Function RDCSetNTableLogonAndLocSubReport(ByVal setionCode As String, ByVal subreport As Integer, ByVal servers As String, ByVal databases As String, ByVal userNames As String, ByVal passwords As String, ByVal locations As String) As String

   Dim oSubreportObject As CRAXDRT.SubreportObject
   Dim locRet As String
   Dim sServers() As String
   Dim sDatabases() As String
   Dim sUsers() As String
   Dim sPasswords() As String
   Dim sLocations() As String
   Dim i As Integer
   
   On Error GoTo ErrorHandler
   If crxReport.Sections(setionCode).ReportObjects(subreport).Kind = crSubreportObject Then
   
      Set oSubreportObject = crxReport.Sections(setionCode).ReportObjects(subreport)
      Set oSubreport = oSubreportObject.OpenSubreport
      
      sServers = Split(servers, Chr$(31))
      sDatabases = Split(databases, Chr$(31))
      sUsers = Split(userNames, Chr$(31))
      sPasswords = Split(passwords, Chr$(31))
      sLocations = Split(locations, Chr$(31))

      If (oSubreport.database.Tables.count = UBound(sServers)) And _
         (oSubreport.database.Tables.count = UBound(sDatabases)) And _
         (oSubreport.database.Tables.count = UBound(sUsers)) And _
         (oSubreport.database.Tables.count = UBound(sPasswords)) And _
         (oSubreport.database.Tables.count = UBound(sLocations)) Then

         For i = 1 To oSubreport.database.Tables.count
            oSubreport.database.Tables(i).ConnectionProperties.Item("Server") = sServers(i)
            oSubreport.database.Tables(i).ConnectionProperties.Item("User ID") = sUsers(i)
            oSubreport.database.Tables(i).ConnectionProperties.Item("Password") = sPasswords(i)
    
            If (sDatabases(i) <> Null) Or (sDatabases(i) <> "") Then
               oSubreport.database.Tables(i).ConnectionProperties.Add "Database", sDatabases(i)
            End If
            
            locRet = ""
            locRet = RDCSetNthTableLocationSubReport(setionCode, subreport, i, sLocations(i))
            
            If (locRet <> "True") Then
               RDCSetNTableLogonAndLocSubReport = locRet
            End If
         Next i
         RDCSetNTableLogonAndLocSubReport = "True"
      Else
         RDCSetNTableLogonAndLocSubReport = "CR_ERROR"
      End If
   End If

ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Error in setting logon information for " & oSubreport.database.Tables(i).Name & "in subreport." & "Error number : " & Err.Number & " Error description : " & Err.Description, vbOKOnly, "Error"
      RDCSetNTableLogonAndLocSubReport = "CR_ERROR"
   End If

End Function

Public Function RDCSetNthTableLocationSubReport(ByVal setionCode As String, ByVal subreport As Integer, ByVal tableIndex, ByVal location) As String

   Dim oSubreportObject As CRAXDRT.SubreportObject

   On Error GoTo ErrorHandler
   If crxReport.Sections(setionCode).ReportObjects(subreport).Kind = crSubreportObject Then
      Set oSubreportObject = crxReport.Sections(setionCode).ReportObjects(subreport)
      oSubreportObject.OpenSubreport.database.Tables(tableIndex).SetTableLocation location + "." + oSubreportObject.OpenSubreport.database.Tables(tableIndex).location, oSubreportObject.OpenSubreport.database.Tables(tableIndex).Name, ""
      RDCSetNthTableLocationSubReport = "True"
   Else
      RDCSetNthTableLocationSubReport = "CR_ERROR"
   End If
     
ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Error in setting the table location in subreport. Error number : " & Err.Number & " Error description : " & Err.Description, vbOKOnly, "Error"
      RDCSetNthTableLocationSubReport = "CR_ERROR"
   End If

End Function

[former_member183750 · Accepted solution]

There are a few inconsistencies in your code. Essentially you appear to be mixing deprecated properties, new properties and you are using the new properties that don't really make sense:

1) SetNthTableLogOnInfo and SetNthTableLocation are deprecated methods

2) Then you are mixing in the connection properties:

crxReport.database.Tables(tableIndex).ConnectionProperties.Add "Database", database

3) You are using the .Add method which you would normally use only after using .DeleteAll.

The code should be as simple as:

crxReport.Database.Tables(1).Connectionproperties("Server") = "the server name"

crxReport.Database.Tables(1).Connectionproperties("user id") = "the user id"

crxReport.Database.Tables(1).Connectionproperties("password" = "the password"

Now, for starters, I'd like you to just use the password as the report will remember the server name and user id.

I do not know if in this particular report you actually have a db connection in the main report. If you do, use the above code for the main report. Then use that code for the subreport as:

oSubreport.Database.Tables(1).Connectionproperties("Server") = "the server name"

oSubreport.Database.Tables(1).Connectionproperties("user id") = "the user id"

oSubreport.Database.Tables(1).Connectionproperties("password" = "the password"

Do not change the server names or anything for now and let me know if the code works for you if you only use

crxReport.Database.Tables(1).Connectionproperties("password" = "the password"

and

oSubreport.Database.Tables(1).Connectionproperties("password" = "the password"

For mre info see [this|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4ef81] article.

Ludek

Edited by: Ludek Uher on Mar 20, 2009 6:36 AM

[Former Member · Accepted solution]

Hi Ludek,

Here are the answers to you questions.

1) SetNthTableLogOnInfo and SetNthTableLocation are deprecated methods

I can see your confusion. These are just method names. Earlier we had the application developed with the Print Engine API but since these were deprecated we moved to RDC API (CRXI R2 SP4 version). Yet we just had part of the method names for making our reference easier. So methods we have is RDCSetNthTableLogOnInfo and RDCSetNthTableLocation and so on. Yet these are just method names and nothing else.

2) Then you are mixing in the connection properties

crxReport.database.Tables(tableIndex).ConnectionProperties.Add "Database", database

What we really want to do is to add the new connection property called Database and add the value. This line of code was added by looking at some examples in the forum.

3) You are using the .Add method which yiou would normally use only after using .DeleteAll.

Not sure what you mean. I guess you are referring to the above point.

Q: Now, for starters, I'd like you to just use the password as the report will remember the server name and user id.

A: I tried this and it doesnu2019t have any affect. Still I get the error. I can send you my sample application and the rpt file for your reference .

Regards,

Chanaka

[former_member11696 · Accepted solution]

Hi Chanaka,

With Oracle there is no Database, set this field as an empty string. Other DB's use it but Oracle doesn't.

Try hard coding the values for each connection property and see if that works.

You cannot attach anythign to these postings. If you need to work directly and apss samples back and for you'll have to purchase a case on line.

Thank you

Don

[former_member183750 · Accepted solution]

Ok. I want to start with a brand new application. Here is the code we'll use for starters:

report.database.tables(1).connectionProperties("password") = "the password"

'''''Obtain a SubreportObject through the ReportObjects collection.

Dim subReport As SubreportObject

Dim sect As Section

Dim rptObject As Object

For Each sect In Report.Sections

For Each rptObject In sect.ReportObjects

If rptObject.Kind = crSubreportObject Then

Set subReport = rptObject

subReport.database.tables(1).connectionProperties("password") = "the password"

Set subReport = Nothing

End If

Next

Next

In the above code, all I want to do is set the password to the report and subreport. No changes to the logon (e.g., no changes to server, ODBC, etc. - the report "remembers" all that info, with the exception of the password) Does that work?

If it does, do the following to change and add what ever property you want:

Dim Report As New CrystalReport1

Dim subReport As craxdr.report

Dim subReportObj As SubreportObject

Dim sect As Section

Dim rptObject As Object

''''Logon the main report

Report.database.tables(1).connectionproperties.deleteAll

Report.database.tables(1).connectionproperties.add "DSN", "dsn name"

Report.database.tables(1).connectionproperties.add "database", "db name"

Report.database.tables(1).connectionproperties.add "User ID", "user's id"

Report.database.tables(1).connectionproperties.add "PASSWORD", "whatever"

''''Now logon the subreport

For Each sect In Report.Sections

For Each rptObject In sect.ReportObjects

If rptObject.Kind = crSubreportObject Then

subReportObj = rptObject

set subReport = report.openSubreport(subReportObj.subreportname)

subReport.database.tables(1).connectionproperties.deleteAll

subReport.database.tables(1).connectionproperties.add "DSN", "dsn name"

subReport.database.tables(1).connectionproperties.add "database", "db name"

subReport.database.tables(1).connectionproperties.add "User ID", "user's id"

subReport.database.tables(1).connectionproperties.add "PASSWORD", "whatever"

End If

Next

Next

Ludek

[former_member11696 · Accepted solution]

Once again, Oracle doesn't use a Database name to connect and he's using our native Oracle driver:

Report.database.tables(1).connectionproperties.add "DSN", "dsn name"

Report.database.tables(1).connectionproperties.add "database", "db name"

And in this case he's using the native Oracle driver so the DSN name should be "Server", "your Oracle server name or Instance" and "database", ""

Don

[Former Member · Accepted solution]

Hi Ludek,

I solved the issue. When testing your suggestion I saw that the password was set to null even though I send it from my method. Then I found out that it was actually an issue in my code where I reference a wrong array index when setting the value to the password field of the table connection property. The index should start from 1 where as I have given 0.

I'm guessing why it worked when having views in the main report is that the sub-report uses that login info. When there are no views in the main report the login info set in the sub-report is wrong and I get the error message,

Thank you all for you help.

Chanaka

[former_member183750 · Accepted solution]

Thank you for letting us know it is resolved and how it was resolved. It's post like this that become so valuable for future customers doing searches on these forums.

Have a great weekend,

Ludek