VB6 UDL for Crystal Report 8

[[email protected]]

Hi,

If someone could help regarding how to connect programatically Crystal Report 8 in VB6.

.Connect = "Driver={SQL Server}; " & "Server=LTISERVER; " & "Database=DotNetNuke; " & "UID=sa; " & "PWD=sa; "
this statement can connect,

but using this one statement cant :

.Connect = "FILE NAME=" & App.Path & " \datalink\xxx.udl"
it gives me Cant connect SQL server.

If someone could help me it will be much appreciated.

regards,

Clint

[jggtz]

About UDL files :
The .udl file consists of a connection string written in the same format used when opening a database (not Crystal Reports rpt) from programming code. Using this way there are not fixed connection strings inside the project.

About how to connect programatically Crystal Report 8 in VB6 :
Explain how are You calling Crystal Reports from Your app, by RDC or OCX ?

[[email protected]]

Hi,
Thank you for the reply.

When using UDL in ADODC like Adodc1.Connection = "FILE NAME=" & App.Path & " \datalink\xxx.udl" i can connect successfully, im trying to create a connection from crystal report using Crystal1.Connect = "FILE NAME=" & App.Path & " \datalink\xxx.udl" but is it not successful,

Yes i know that the connection string is not fixed and it have an purpose, I want create a conenction that will adjust automatically from any server and by not recompiling it again.

Sorry for the late reply because of our local holidays.

Regards,

Clint

[jggtz]

Then You could store the connection data (Driver name, Server name, Database name, User name, Password) in a table or txt file or ini file & read them at app start, constructs the string and use it to connect using Your method

.Connect = "Driver={SQL Server}; " & "Server=LTISERVER; " & "Database=DotNetNuke; " & "UID=sa; " & "PWD=sa; "
this statement can connect

[[email protected]]

Hi,

Thank you for the reply I thought the problem is SQL server only but when I try MS Access the same problem occurs, how can I establish a

.Connect = "Driver={SQL Server}; " & "Server=LTISERVER; " & "Database=DotNetNuke; " & "UID=sa; " & "PWD=sa; "

if using MS Access?

Thank you for the Tip, Im almost getting it.

Regards,

Clint

[jggtz]

Try with:
“Driver={Microsoft Access Driver (*.mdb)};DBQ=path to mdb file;UID=sa;PWD=;”

[[email protected]]

Hi,

Thank you!

I thought its gonna work but its giving me hard time, message said "Cannot Open SQL server" error no, 20599, in yet im using Access Drivers, what went wrong,

Here is my connection string:

.Connect = "Driver{Microsoft Access Driver (*.mdb)}; " & "DBQ=D:\database; " & "UID=admin; " & "PWD=password; "

if i dont put .SQLquery in the statement it is successful but when I put some .sqlquery Cannot Open SQL server error appears.

regards,

Clint

[jggtz]

Post all the relevant code, please

[[email protected]]

With rep

frmform.MousePointer = vbHourglass
.ReportFileName = "D:\REPORT\xxx.rpt"
.Connect = "Driver{Microsoft Access Driver (*.mdb)}; " & "DBQ=D:\database\Employee Record.MDB; " & "UID=admin; " & "PWD=emprec08; "


.Formulas(0) = "FROM='" & sref1 & "'"
.Formulas(1) = "TO='" & sref2 & "'"
.RetrieveSQLQuery

.SQLQuery = "SELECT tbl_EmpRecord.`EmpNo`, tbl_EmpRecord.`LastName`, tbl_EmpRecord.`FirstName`, tbl_EmpRecord.`MidName`, tbl_EmpRecord.`DATE_REG`, tbl_EmpRecord.`ILCredit`, tbl_attendance1.`AbsDate`, tbl_attendance1.`AbsReason` From tbl_EmpRecord tbl_EmpRecord LEFT OUTER JOIN tbl_attendance1 tbl_attendance1 ON tbl_EmpRecord.`EmpNo` = tbl_attendance1.`EmpNo` Where tbl_attendance1.`Type` = 1 "

.WindowTitle = "IReport"
.PrintFileType = crptCrystal
.WindowState = crptMaximized
.WindowShowRefreshBtn = True
.PrinterSelect
.Action = 1
frmform.MousePointer .MousePointer = vbNormal
End With


thank you in advance,

regards,

Clint

[jggtz]

SQLQuery specifies the SQL query string used by the specified report.
Syntax : [form.]Report.SQLQuery[=SQLQuery$]
For example:
CrystalReport1.SQLQuery = “SELECT authors.au_id, authors.au_lname,authors.au_fname FROM pubs2.dbo.authors authors WHERE authors.au_lname >‘Madison’"

You may only change the WHERE, FROM, and ORDER BY sections of an SQLquery.
Although the property requires that you enter the entire SQL query, the SELECT section must not be different from the original query in the report.

**
To change the ORDER BY clause, you must place a carriage return and
linefeed characters after the WHERE clause and before the ORDER BY clause
in this way: CrystalReport1.SQLQuery=”SELECT...FROM... WHERE...” + CHR$(13) + CHR$(10) + “ORDER BY...”
This property is active only if you are using an SQL or ODBC data source in your report.**


You can take out the next from Your code
PrintFileType specifies the type of print file to use when printing a report to a file.
Syntax : [form.]Report.PrintFileType[= FileType%]
For example, if You use :
CrystalReport1.PrintFileType = crptCrystal
A standard Crystal Reports (RPT) format is used. Most often used for sending the report to another user via e-mail.



PrinterSelect displays the Printer Selection common dialog box which enables the user to specify a different printer.
Syntax : [form.]Report.PrinterSelect
For example:
CrystalReport1.PrinterSelect
«When the user prints, a dialog box appears for selecting the desired printed for printing the report.»
Remarks
This method allows you to pick the printer used for printing the report.
This method is intended primarily for printing reports to a printer. However, changes made to the Print Setup dialog box may also affect how reports appear in preview windows and when exported.



Connect specifies the information required to log on to an SQL server or an ODBC data source.
Syntax : [form.]Report.Connect[= DataSourceName;UserID;Password;DatabaseQualifier$]
For example:
CrystalReport1.Connect = “DSN = Accounting;UID = 734;PWD = bigboard;DSQ= Administration”
«Connects to the “Administration” database on the “Accounting” server using the user ID #734 and the password “bigboard”.»

CrystalReport1.Connect = “dsn=; uid=; pwd=bigboard; dsq=“
«Connects to a password-protected Paradox database. All that is being passed is the password “bigboard”.»
Remarks
Use the Connect property when the report connects to only a single ODBC data source or SQL server, and only a single set of log on information is required. If the report connects to multiple data sources that require different log on information, use “LogOnInfo”.
Enter the parameters necessary to log on to the SQL server that you need to be activated for your report. Parameters should be in the following format:
DSN = name;UID = userID;PWD = password;DSQ = database qualifier

name is the server name or ODBC data source name,
userID is the name you have been assigned for logging on to the SQL server,
password is the password you have been assigned for logging on to the SQL server,
and database qualifier is the database name if your server uses the database concept.

The database qualifier parameter, DSQ, is required only when it is applicable to the ODBC/SQL driver you are using. If your DBMS does not use the database concept, you do not need to specify the DSQ parameter.
Before you can use this property for an ODBC/SQL database, you must install the ODBC/SQL driver for whatever SQL database you are planning to use, and put the Database/BIN location in your path.
If you are connecting to an SQL or other password protected database directly,without going through ODBC, use the name of the SQL server for the DSN parameter.

[[email protected]]

Hi,

Thank you so much for your detailed information, but still Icant connect to the database Access but SQL server i can,

Regards,

Clint

[jggtz]

Did You create a DSN to connect to an Access file?

[[email protected]]

Hi,

Yes, but still cant connect may be i got it wrong, so i decided to use datareport instead which is much harder,



Regards,

CLint

[tifa_party]

what Dns and how can i install that

[vpm_sampath]

   Crp.Reset
    Crp.DataFiles(0) = App.Path + "\" & MyCom.ID & "\Inventory.Mdb"
    Crp.Password = V_CrystalPass
    Crp.SelectionFormula = "{DebitRpt.date} >=#" & (Dtp1.Value) & "# and {DebitRpt.date} <=#" & (Dtp2.Value) & "# " & V_SqlConditions
    Crp.Formulas(0) = "Company = '" & MyCom.Name & "'"
    Crp.Formulas(1) = "Ason= '" & Format(Date, "dd-MM-yyyy") & "'"
    Crp.Formulas(2) = "Title = 'Debit Note Report " & V_Title & "'"
    Crp.Formulas(3) = "fromto = 'From " & Format(Dtp1.Value, "dd-MM-yyyy") & "  To  " & Format(Dtp2.Value, "dd-MM-yyyy") & " '"
    Crp.ReportFileName = App.Path + "\DNote.rpt"
    Crp.WindowState = crptMaximized
    Crp.Action = 1

This how i use with crystal 8.5 try it