Crystal V9 Login Failed for User 'sa' 1

[kennedymr2]

I am running windows2000,crystal v9,visual basic v6,sp5,SQL2000

I am atempting to load a crystal report in my VB6 program and it will not log onto the server.
When i run it in crystal reports program, all is ok.

Ado error code
OBBD DRIVER etc... Login Failed for User 'sa'

I notice under crystal8 you can enter a command to negate this.

How do you get a crystal report to logon correctly without the above error

Mt user is sa Password = xyz

Appreciate any help.

 

[FVTrainer]

I'm a little confused by your error message. It's best if you actually show the complete, literal error message.

What kind of a database connection do you actually have? Did you connect via ODBC, OLEDB, or via another method?

Below is sample code for loging on. It logs on, essentially, a table at a time. You can log onto the whole database at once, but if your report contains tables from mutliple databases on the same server (a common occurance with accounting applications), that is not sufficient. The code snippet assumes you've created a report object called crRpt.

This is code for a native sql connection:

Dim crTable as CRAXDRT.Database.Table
Dim crTables as CRAXDRT.Database.Tables

Set crTables = crRpt.Database.Tables

For Each crTable In crTables
'For a native connection, SetLogOnInfo arguments are ServerName, DatabaseName, UserName, and UserPassword
crTable.SetLogOnInfo "dell-laptop", "Northwind", "sa", "xyz"

Next

This is code for an odbc connection:
For Each crTable In crTables
'For an ODBC connection, SetLogOnInfo arguments are ODBC ConnectionName, DatabaseName, UserName, and UserPassword
crTable.SetLogOnInfo "Northwind", "Northwind", "sa", "xyz"

Next


By the way, if there is sample code for version 8, it most likely works in cr 9. There are a handful of depricated methods and properties, but not many.

 

[kennedymr2]

I can now appreciate the problem.
I did try altering the password for the user on the sql2000 server to a blank, and altered the odbc to not include a password. This all worked perfectly ok. But i do not wish to leave it like this. ie i would like a password.

I am using ODBC for testing
but would like to use...

If OpenType = 1 Then
Cnn1.Open "DSN=xxx;UID=sa;PWD=xxx"
End If

If OpenType = 2 Then 'Local/Lan
sqlProvider = "SQLOLEDB.1"
sqlNetLib = ""
sqlNetLibPort = ""
sqlDatSource = "xxx"
End If

If OpenType = 3 Then 'Remote (Internet)
sqlProvider = "SQLOLEDB.1"
sqlNetLib = "DBMSSOCN"
sqlDatSource = RemoteIp
sqlNetLibPort = "," & "1433"
End If


and...
Strcnn = "Provider=" & sqlProvider & _
";Persist Security Info=" & sqlNTSec & _
";User ID=" & sqlUid & _
";Password=" & sqlPws & _
";Initial Catalog=" & sqlDatBase & _
";Data Source=" & sqlDatSource & sqlNetLibPort & _
";Network Library=" & sqlNetLib
---------------------------------------------------


Dim crTable As CRAXDRT.Database.Table

I included this in my code and received an error message
compile error
type mismatch

Any Idea why i am getting this

Appreciate the help being offered, was becoming a little dismayed !!@@!!! if thats the word??

 

[FVTrainer]

Oops. My fingers created a non-existant object in the Crystal data model.

The correct code should be:

Dim crTables As CRAXDRT.DatabaseTables
Dim crTable As CRAXDRT.DatabaseTable

Set crTables = crRpt.DatabaseTables


Now, having corrected myself, I might suggest that you look at a different approach to using Crystal. What we've looked at so far requires Crystal to have connectivity to the database. So you have to logon. But since you know how to use the ADO provider string, why not build your report using the Active Data driver? The difference at design time is not that different, but at runtime we treat database connectivity differently. Essentially what happens is this: with the Active Driver, Crystal does not maintain a database connection. Instead, it expects to be handed a recordset in its entirety. In otherwords, you create an ADO recordset and pass that recordset to the report. The report itself never logs onto the database.

The one aspect to dealing with this scenario that I do differently has to do with designing the report. Most of the documentation seems to assume that you'll use a ttx file when building the report(a data definition file that contains the schema but very little actual data). But nothing stops you from creating a SQL statement that returns data equivalent to what will be returned at runtime and using that as the basis for the report. The key is that the fields returned by your temporary sql statement must be identical, in terms of the field names, data types, and the order in which they appear, to what will be passed to the report at runtime. There are samples you can download from the Crystal KB that illustrate how to pass your ADO recordset to your report.

I find this approach easier than having Crystal connect to the database primarily because it is easier to troubleshoot ADO connectivity problems than Crystal connectivity problems. Also, since your code for building the recordset is in vb (or perhaps call a stored proc in your database), you have extreme flexibility in building every aspect of the sql query. Your database can change, your tables can change, your WHERE clause can change, etc. The only requirement is that the fields passed into the report have to match up identically to the fields that were placed in the report at design time. Something to think about.

 

[kennedymr2]

FVTrainer,
Thank very much, once again, for your assistance.
I am now a lot clearer with the whole crystal report thing.!!
Would be nice if they gave a more practical example on the cd, for vb. Most of the examples just connect to a mdb file.

Will now proceeed with my project with a lot clearer mind!!

Thanks again