Hi,

I get a 'Runtime Error 20599. Cannot open SQL Server' pointing to:

CR1.Action = 1

Database is: MRR_REP01.mdb
Crystal Reports 8.5
Report Filename is: MRR_REP01.rpt

Pls see part of my Code below :


VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{0C8DE9F2-EAFC-44DF-A13F-B5A9B36ED780}#2.0#0"; "lvButton.ocx"
Object = "{00025600-0000-0000-C000-000000000046}#5.2#0"; "Crystl32.OCX"
Begin VB.Form frmMatReceiving
BorderStyle = 3 'Fixed Dialog
ClientHeight = 9300
ClientLeft = 45
ClientTop = 45
ClientWidth = 14865
ControlBox = 0 'False.
.
.
.
.
Begin Crystal.CrystalReport CR1
Left = 8880
Top = 7860
_ExtentX = 741
_ExtentY = 741
_Version = 348160
PrintFileLinesPerPage= 60
End
.
.
.

Private Sub lvPrint_Invoice_Click()
'**********************************
Set rsMRR_REP01 = New ADODB.Recordset
rsMRR_REP01.Open "Select * from MRR_REP01", myCn, 1, 2

If rsMRR_REP01.RecordCount = 0 Then
Exit Sub
End If


CR1.Reset
CR1.Destination = 0
CR1.ReportFileName = "c:\ProjCost\rpt\MRR_REP01.rpt"
CR1.DiscardSavedData = True
CR1.WindowState = crptMaximized
CR1.Action = 1


End Sub


Does anyone knows a solution to this? Thanks in advance.
.

Osgen

may be your login credentials to the data source is not correct. how are you connecting to the sql server from your application?

for better performance use a System-DSN connection and apply this on connecting reports also. to resolve your reporting bugs you can
Visit This Link

tell me what have you got finally.

regards
Shouvik

Hello Shouvik,

First of all, thank u for your advice. Actually I' m new to VB and CR. The link you directed me is still very fuzzy to me. But I think everything is worth a try. I would like to start with the DSN connection you told me. But I have no idea on how to figure this out. If it's not too much for u, can u give me a detailed (step-by-step) instructions or a rood site that could be of help to newbies? Thank you.

Osgen

Hi,

Thanks to Mr Shouvik's advice. I have figured out the way to configure a DSN connection thru Control Panel/Administrative Tools/Data Sources (ODBC). The Runtime Error 20599 has been fixed. The program ran smoothly at first. but the next day I ran it, it began to slow down! It took about 4 to 5 seconds to process and display a 5 item report. I haven't made a major change in my codes and CR layouts. What could have caused the problem? Do I need to re-install my Crystal Report or remake my Report layout (MRR_REP01.rpt) ?

The Code below was added to my Modules:

Global conn As New ADODB.Connection

Public Sub activateconn()

On Error Resume Next
Set conn = New ADODB.Connection
Do While True
conn.Open "DSN=ProjCost"
If Err.Number = 0 Then
Exit Do
End If
Loop

conn.Open
End Sub


The Code below remain unchanged.

Private Sub lvPrint_Invoice_Click()
'**********************************
Set rsMRR_REP01 = New ADODB.Recordset
rsMRR_REP01.Open "Select * from MRR_REP01", myCn, 1, 2

If rsMRR_REP01.RecordCount = 0 Then
Exit Sub
End If

CR1.Reset
CR1.Destination = 0
CR1.ReportFileName = "c:\ProjCost\rpt\MRR_REP01.rpt"
CR1.DiscardSavedData = True
CR1.WindowState = crptMaximized
CR1.Action = 1

End Sub



Osgen

ok....to create a DSN connection follow these steps :-

1. start->settings->control panel->administrative tools->data sources(odbc)
2. select the system dsn tab
3. click add and then select your provider. if you are using sql server database then you should select Sql Server in Create New Data Source list.
4. click finish.
5. now provide a data source name, description (optional). mention server name --> now the server name depends on the location. if your sql server is installed in the same machine from where you are firing your reports you must type "(local)" without quotes. otherwise if your sql server is located in some remote node mention its name.
for example, suppose there are two nodes conencted into the network and they are -> srvmain01 and srvclient01 . now the sql server is located in srvmain01 and you are firing reports from srvclient01. in this case you have to type "srvmain01" in the server name dropdown list.
when you finish click next.
6. now select the authentication mode. if your server is in a remote machine select sql server authentication and provide username & password for the database you wish to connect to. otherwise use windows nt authentication for local server connection.
click next.
7. check on the checkbox "change the default database to" and select your database. click next.
8. select your sql server message language.
9. click finish.
now to test this DSN click on Test DataSource.
if all goes well you will see a msg "TEST COMPLETED SUCCESSFULLY!"

now click ok->ok->ok
now you can see your appeared in the system dsn tab.

now time to connecting the dsn with reports :-
1. open crystal report software.
2. select blank report and from the Data Explorer dialog box do the following :-
a. expand the ODBC node.
b. expand your dsn node.
c. now select the table or view from where you wish to display data on your report.

hope this will help you...
if you got more questions just post it right here..

i'll try my best to give you proper directions.
ok...

regards
Shouvik