VB6 & SetLogOnInfo Method 1

[vza]

I am currently working on a Administrative User application in vb6. I have created a report using Crystal Reports (which came with vb6) to display all usernames and passwords listed within an Oracle table. This Admin User tool will be utilized on numerous diferent databases. The program requires the user to insert a username/password of the database they wish to connect to. When i try to pass these global variables into the SetLogOnInfo method, the report does not print during runtime, giving the error "Server has not yet been opened." Is there a reason that this method does not take in variables, or do the database username, password and datasource HAVE to be hard-coded in order for the SetLogonInfo method to work?? Any help would be greatly appreciated.

Thanks
-vza

 

[FVTrainer]

No, they do not have to be hard-coded. The arguments for SetLogOnInfo vary based on the connectivity being used. Please post your code where you are attempting to use the SetLogonInfo method, and also tell us what connectivity (ODBC or Native Driver) you are using.

 

[vza]

Here is my code:
Using an Oracle Driver
(MSADORA.1)

Private Sub ReportButton_Click()
' Variables
     Dim CRApp As New CRAXDRT.Application
     Dim CRReport As New CRAXDRT.Report
 ' Print User Information Report
     Set CRReport = CRApp.OpenReport(App.Path & "\name.rpt", 1)
     CRReport.RecordSelectionFormula = ""
     On Error GoTo Error:
     CRReport.Database.Tables(1).SetLogOnInfo "", 
      "'"& DataBaseConnect.TextDataSource.Text &"'", 
      "'" & DataBaseConnect.TextUser.Text & "'"
      "'" & DataBaseConnect.TextPassword.Text & "'"
     On Error GoTo Error:          
     CRReport.PrintOut True
End Sub

The debugger would allow the SetLogOnInfo line to be executed.....but the PrintOut line would produce a 'Server not yet opened' error.

Thanks
-vza

 

[FVTrainer]

Well, since I can't see what the actual values are for DatabaseConnect.textDataSource.Text, etc. I'm a little at a disadvantage. But, since you appear to be using a native connection (not p2sodbc.dll), your values in the SetLogOnInfo method should look like:

'For a native connection, SetLogOnInfo arguments are ServerName, DatabaseName, UserName, and UserPassword
CRReport.Database.Tables(1).SetLogOnInfo "servername", "databasename", "Username", "Password"

It looks like you're passing a blank string for the database name, which appears to me to be incorrect. It should be the name of the oracle server.

 

[vza]

My apologies for such a late response....

Those text values are strings which hold the Oracle DB username, password and datasource (DB Login when program starts)

The blank value is for the Servername (I do not know what to place in there...and when I left it blank no error arose)I placed the Oracle database name in the databasename argument

Thanks
-vza

 

[FVTrainer]

Well, leaving the servername blank in the code wouldn't cause an error until you actually tried to call the report, which is what actually happened. The error you got, "Server has not yet been opened", is the error you receive when any of the values of the SetLogOnInfo method are incorrect.

So, did you place the Oracle server name in the servername argument?

 

[vza]

By Oracle servername you mean SID correct?
Isn't that the same as the Datasource name?
I tried to place the SID in the Servername argument but still recieved the same error....Here is the code I have:

Set CRReport = CRApp.OpenReport(App.Path & "\development.rpt", 1)

CRReport.Database.Tables(1).SetLogOnInfo "'" & DataBaseConnect.TextDataSource.Text & "'", "'" & DataBaseConnect.TextDataSource.Text & "'", "'" & DataBaseConnect.TextUser.Text & "'", "'" & DataBaseConnect.TextPassword.Text & "'"
        
CRReport.PrintOut True

As I said before, those text boxes contain the strings of the username, password, and DataSource name of the Oracle DB inserted by the user. (when I debug, the values are correct but the like still produces 'Server not yet opened' error.

I appreciate all your assistance in this matter.

Thanks
-vza

 

[FVTrainer]

Well, I'm not an Oracle user, so I can't directly answer your question, but we ought to be able to figure this out easily enough.

In Crystal Reports, open up your report and select the Database | Set Location menu item. Look at the value stored in the Server Name property listed on this dialog. If your application is running against the same Oracle server as the report was created against, this is the value that should be passed as the servername. If you are connecting to a different Oracle server, you will need to determine the corresponding server name and pass it to the report at runtime.

Another way to figure this out is to run the following line of code:

debug.Print CRReport.Database.Tables(1).LogOnServerName

This will tell you what servername is stored in the report. Interestingly, Crystal stores this info, but still requires you to pass it in at runtime.

So, either pass this same servername (if running against the same Oracle server as the report was designed against) or pass the name of the server you are attempting to run the report against.

 

[vza]

I ran the line of code:

debug.Print CRReport.Database.Tables(1).LogOnServerName

and it returned the Oracle Datasource Name
This is the same value my text box string returns...
If I physically type the DS Name into the SetLogonInfo
(with the ServerName argument blank)
method there is no problem...I do not think this method takes variables.....

Thanks,
-vza

 

[FVTrainer]

Did you run the debug line before or after your SetLogonInfo call? You should run it before.

 

[FVTrainer]

You know, I've read up a little on Oracle and it appears that the database argument is supposed to be a blank string. So, your SetLogonInfo should be:

CRReport.Database.Tables(1).SetLogOnInfo "'"& DataBaseConnect.TextDataSource.Text &"'", "", "'" & DataBaseConnect.TextUser.Text & "'", "'" & DataBaseConnect.TextPassword.Text & "'"

 

[vza]

I ran the debug line before the SetLogOnInfo call, and also tried replacing the DBName argument with a blank string...I am still getting the same error...
I am stumped.....
Sorry for all the trouble...I appreciate the effort.

Thanks
-vza

 

[vza]

Could it have anything to do with my print out line, which comes after the SetLogOn method??

CRReport.PrintOut True

[\code]

-vza

 

[FVTrainer]

In order for the printout method to work, you have to have successfully logged on. I don't think there is anything wrong with the PrintOut method as you are using it.

However, and I should have seen this earlier, the "'" marks you have around the variable names are unnecessary, and probably wrong. In other words,
your string should look like:

CRReport.Database.Tables(1).SetLogOnInfo DataBaseConnect.TextDataSource.Text , "", DataBaseConnect.TextUser.Text, DataBaseConnect.TextPassword.Text

Now, if that doesn't work, compare the value of

CRReport.Database.Tables(1).LogOnServerName

(before calling the SetLogoInfo method) with the value you are hardcoding. I know they appear the same, but in code do something like

debug.print debug.Print CRReport.Database.Tables(1).LogOnServerName = <hardcoded value>

before calling the SetLogonInfo method. If it returns true, then I am really at a loss help.

 

[vza]

FVtrainer,

You were right....the quotes are not necessary!
I think I am to used to using quotes from SQL queries...
I really appreciate all the help...Thanks Lots!

Here is a well deserved star for your efforts!

-vza

 

[vza]

Is there any way to incorporate a query into the report which is used to gather information? I thought there was a method which does this....I am not sure....

Thanks
-vza

 

[FVTrainer]

Since you're working in vb (but with an older version of CR), there are several ways to deal with this. But just as an aside, there is one approach you should NOT do, and that is use a Crystal SQL Designer query as your data source. Nothing good can come from that.

Ok, what are your choices? Primarily:

1) Write a stored procedure or view in oracle and use it as your data source for the report. This is by far and away the most popular approach. Although stored proc's in Oracle are a bit more of a pain than they are in SQL Server (at least in my opinion, but that's probably not a universal opinion).

2) Write the query in vb using ADO (i.e., creata an ADO recordset in VB). Build your report to use an ADO dataset as its report source. At runtime, pass the ADO recordset from your app to the report. This approach has what is to me a serious advantage in that the report requires no connectivity to the database. You handle the connectivity in vb when you make your ado recordset. You pass the recordset to the report. The report is just a recipient of the data, rather than having to connect to the database and run its own query.

There are threads on both topics througout the Crystal forums, primarily in this forum and the Crystal Reports 2 Data forum. If you're interested in either approach, search for threads related to ADO and stored procedures. Also, search the Crystal Decisions kb for &quot;RDC ADO sample&quot;.

 

[vza]

I do not think I have the Active Data Driver (p2smon.dll) on my machine...is there anywhere I can DL it from?? I checked the Crystal website but all DL's are for newer versions (I am using the version which is installed with VB6 - Crystal Reports 4.6.1.0).

Thanks
-vza