Link to home
Start Free TrialLog in
Avatar of dwanto
dwanto

asked on

cannot open sql server

hii,

i always see this error "cannot open sql server" when i run this code, but if a verify database on crystal report before i run it,it doesn't error.

why??

this is my code:

crystal.Connect = dsnreport
crystal.DataFiles(0) = "Proc(am_jualkwitansi1)"
crystal.ReportFileName = App.Path & "\report\jualkwitansi.rpt"

    crystal.ParameterFields(0) = "@noinv1;" & txtpo1 & ";true"
    crystal.ParameterFields(1) = "@noinv2;" & txtpo1 & ";true"
    crystal.ParameterFields(2) = "@tanggal1;" & Format(date1, "yyyyMMdd") & ";true"
    crystal.ParameterFields(3) = "@tanggal2;" & Format(date1, "yyyyMMdd") & ";true"
    crystal.ParameterFields(4) = "@pilih;" & "no" & ";true"
    crystal.RetrieveDataFiles
   
    crystal.Action = 1
--------------------------------------
am_jualkwitansi1 is stored procedure with 5 parameters
jualkwitansi.rpt is report with 3 sub report inside it
each subreport has own stored procedure
crystal is crystal repot control
dsnreport = "DSN=" & server & ";UID=" & user & ";PWD=" & password & ";DSQ=" & database
--------------------------------------
please tell me the solution

thanks
Avatar of QJohnson
QJohnson

Try inserting:

 "Driver={SQL Server}"

at the beginning of your DSN string.
Avatar of dwanto

ASKER

sorry it doesn't work
Avatar of dwanto

ASKER

please, some body help me!!!
i need the solution for my problem as soon as posible.
You are clearly doing something very RIGHT in the way you have set up the design environment of crystal.  I haven't used it myself in a couple of years and have never used a version later than 8.0.  So I'm no expert on it.

But if you can figure out what information crystal has been given so that the Verify Database is successful, you will solve your problem by making that same information available in your connection string.

You may be able to get ADO to tell you what is missing.  You can use:

 myConnection.Properties("Prompt") = adPromptCompleteRequired

If that isn't available...

You can actually build a DSN-less connection (one for which you don't have to provide a DSN at all).  

Here's how:

myConnection.ConnectionString = "DRIVER=SQL Server" & _
       ";Server=" & sServerName & _
       ";UID=" & sLoginID & _
       ";PWD=" & sPassword & _
       ";DATABASE=" & sDBname
myConnection.Open
Avatar of dwanto

ASKER

sorry qjohnson , it doesn't work to
but thank you any way
Then I would simply try and build a standard ADO recordset for cyrstal's use and pass it to the routine you use to call your report rather than use their default connection strategy.

I believe the property used to be called
   CrystalReport.DataFiles(0)

After that I would just log a call to Crystal support or try for help at www.crystaldecsions.com

Good luck
Avatar of dwanto

ASKER

thank you
Avatar of dwanto

ASKER

the problem is, if a can supply a logon info to all sub report in my main report maybe, just maybe it works.
but i don't know how to supply a logon info to sub report.

if you guy's know how to do it, please tell me. okey
Can you just set the data source property for each subreport with the same technique you do it for the main form?  Again, perhaps the property is called ReportObject.DataFiles(X), where X is a zero-based integer sequence for the various data sources needed in your report.

If you use the DSN-less syntax, the user will never be prompted for any information and you have the opportunity of providing the UID/PWD you need to use.
Avatar of dwanto

ASKER

i already try that but still error
Checking Data Sources in the developer's help file, I found a link to the documentation section of their knowledgebase and found an article that looks on topic for you:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr_dev_dbissues.pdf.asp

Chapter four of this pdf 'booklet' covers 19 SQL Server connection problems.   I'm pasting here the text from the table of contents, so that you can determine whether or not the link may be useful:

599 or 20599  Cannot Open SQL Server

c2008952 -- Connecting to SQL Server 2000 natively with integrated security
and OCX fails

c2008680 -- Error: '599' when using PELogonSQLServerWithPrivateInfo

c2008291 -- Using a Trusted connection or Integrated Security with the OCX at runtime

c2007612 -- Distributed report using ODBC fails to run on a Windows NT/2000 computer

c2007257 -- "Error 20599  Could not open SQL Server" when using the OCX and Lotus Notes

c2006438 -- Error: Data source name not found and no default driver specified

c2005827 -- "Error 20599" When Using a Trusted Connection

c2005632 -- Limitations of using Crystal Queries and Dictionaries for development purposes

c2005615 -- Error 20599 when changing the Server name and Stored Procedure at runtime.

c2004937 -- Using a Trusted connection or Integrated Security with the OCX at runtime

c2004178 -- "Error 20599 Cannot Open SQL Server" Calling a Report from Active Server Pages

c2003803 -- "CRPE Error 599" & "Cannot Open SQL Server" when connecting to Microsoft Access

c2002982 -- Error 599 or 20599 when running a report using active data

c2002782 -- Active Data Report is Still Looking for ODBC Datasource

c2002650 -- What is the difference between LogOnServer and SetLogOnInfo methods

c2001630 -- Error 536/599 - Incorrect LogOn Parameters

c2001383 -- Visual FoxPro applications and database connectivity

c2001142 -- Changing servers using LogonServer creates error 20599
Avatar of dwanto

ASKER

thank you qjohnson, i will try that
Avatar of dwanto

ASKER

i look and read one by one, but still not found.
i think, if i know how to change logoninfo each sub report maybe the error will gone.

because,
i use that code for all my reports and they just work fine.
even i run it in different server and database.
the different between report thet give me error message and notm, is the report with a sub report on it, the report with out sub report is run find even a run it in different server or database and report with sub report will give me error if a change server or database.

so, qjohnson can you help me ?

regards
dwanto
Here's what I found in the developer help file for Crystal 8.0.  I am rearranging the types of connections they give in the examples so that they are likely in the order in which you will find them useful.  I'm also adding some separator text to make it easier to browse the text.

Setting the data source for a subreport
=====================================================

You change the database location (change the data source) for a subreport in much the same way as you set the database location for the main report. Before you can change the database location for a subreport, you first need to open the subreport. See Referencing objects in a subreport  for information on how to do that.
Once you have opened the subreport you can change the database location. The following examples show you how to do that for a variety of different types of data sources.

ODBC connection to a PC or SQL database
-------------------------------------------------
 If the datasource of the subreport is a PC-type (for example, MS Access) or SQL-type (for example, MS SQL Server), and the report is connecting via ODBC (P2SODBC.DLL), you can use code similar to the following:

1     Get the Tables collection for the subreport.

Set CRXTables = CRXSubreport.Database.Tables

2     Get the first table from the Tables collection.

Set CRXTable = CRXTables.Item(1)

3     Finally, set the location of the.mdb file.

CRXTable.SetLogOnInfo <ODBC_DSN>, <databasename>, <userid>, <password>

Active data connection to a PC or SQL database
------------------------------------------------------
If the datasource of the subreport is an active data source (ex. DAO, RDO, ADO, CDO, etc.), and the report is connecting using the active data driver (P2SMON.DLL), then you can use code similar to the following:

1     Get the Tables collection for the subreport.

Set CRXTables = CRXSubreport.Database.Tables

2     Get the first table from the Tables collection.

Set CRXTable = CRXTables.Item(1)

3     Finally, set the location of the.mdb file.

CRXTable.SetDataSource rs, 3

Native connection to SQL database
------------------------------------------
If the datasource of the subreport is of an SQL-type (for example, MS SQL Server), and the report is connecting natively (P2SSQL.DLL), then you can use code similar to the following:

1     Get the Tables collection for the subreport.

Set CRXTables = CRXSubreport.Database.Tables

2     Get the first table from the Tables collection.

Set CRXTable = CRXTables.Item(1)

3     Finally, set the location of the.mdb file.

CRXTable.SetLogOnInfo <servername>, <databasename>, <userid>, <password>

Native connection to PC database
---------------------------------------
If the datasource of the subreport is a PC-type database (for example, MS Access), and the report is connecting natively (P2BDAO.DLL), then you can use code similar to the following:

1     Get the Tables collection for the subreport.

Set CRXTables = CRXSubreport.Database.Tables

2     Get the first table from the Tables collection.

Set CRXTable = CRXTables.Item(1)

3     Finally, set the location of the.mdb file.

CRXTable.Location = "C:\My_Application\My_DB.mdb"


There are many ways that you can access a subreport, and change the database location for a subreport. The above examples are simplified and generic so that they can be used and altered to accommodate and application with any report.
Avatar of dwanto

ASKER

sorry qjohnson i was a beginer and i would like you to help me what i must do ?
what i must declare first ? and what reference i must add ?

regards
dwanto
Here is some text I found that may be useful

>>> pasted from Cyrstal's Developer Help file >>>A SubreportObject object is, essentially, another report inside the original report. Once you have obtained a SubreportObject, you can work with any aspect of it just as if it were a standard Report object.

NOTE: You cannot print or export a subreport, or preview it in the Report Viewer outside of its
primary report.

You can obtain a SubreportObject through the ReportObjects collection. The following example shows how to iterate through the sections of a report and change the background color of each subreport to magenta.

Dim Report As New CrystalReport1
Dim subReport As SubreportObject

For Each sect In Report.Sections
      For Each rptObject In sect.ReportObjects
            If rptObject.Kind = crSubreportObject Then
                  Set subReport = rptObject
                  subReport.BackColor = RGB(255, 0, 255)
                  Set subReport = Nothing
            End If
      Next
Next

NOTE: Currently, the Seagate Crystal Report Designer Component does not support subreports inside of subreports. The report iterations cannot go more than one subreport deep. However, you can have multiple subreports inside the main report.

<<< end of pasted text  <<<

Since I'm no Crystal Expert, I'm going to make some general suggestions.  These will be more valuable that someone fixing your code if you use them because they will make your more self-sufficient in solving your problems.

Get acquainted with the docs for the development tools you use.  In Crystal version 8.0, the product installed a folder structure that (on my machine at least) includes:
C:\Program Files\Seagate Software\Crystal Reports\Developer Files\Help
The Developer.hlp file there is enough information to learn the product by yourself if you spend enough time with it.

There is also a folder with VB code samples:
C:\Program Files\Seagate Software\Crystal Reports\Samples\Code\Visual Basic

and a folder with sample reports:
C:\Program Files\Seagate Software\Crystal Reports\Samples\Reports
that has two subfolders that display (1) reports designed to model specific features of the product and  (2) sixteen sample business reports.  

You may have exactly the same directory structure in your version of the product.  Or you may not have chosen to install as many of the sample and help files - in which case you can certainly find them on the CD from which you did your installation.

Finally, since you obviously have an internet connection,
 http://www.crystaldecisions.com/products/dev_zone/
should provide you with a very large number of options with which to either improve your understanding of the product or solve specific problems.  In particular, investigate the items in the "Support Center" box at the lower right hand corner of the page.

Using the "technical articles", for example, I found this URL:
http://support.crystaldecisions.com/communityCS/TechnicalPapers/ocx_subreports.pdf.asp    (which is described as: This document gives an overview of how to access or modify a subreport at runtime using the Crystal Reports ActiveX (OCX) control. The ActiveX control includes two methods and one property which can be used to control certain aspects of subreports)
and
http://support.crystaldecisions.com/communityCS/TechnicalPapers/rdc_subreport.pdf.asp        (which is described as: This document describes how to use the Subreport object in the Seagate Crystal Reports Designer Component (RDC). Topics include most common issues with regards to working with subreports and setting the datasource of the Subreport object.)
and
http://support.crystaldecisions.com/communityCS/TechnicalPapers/subapi.pdf.asp
and
http://support.crystaldecisions.com/communityCS/TechnicalPapers/apps_ocx.pdf.asp

Crystal is a complicated product, to be sure.  But if you are willing to become familiar with vendors docs and web site resources, you don't have to rely on the help of other developers very often to get your projects done.  

Good luck.

Avatar of dwanto

ASKER

thank you qjohnson it works.

i look at http://support.crystaldecisions.com/communityCS/TechnicalPapers/ocx_subreports.pdf.asp
and i find the solution
i just add this code :

==================================================
dim x,y as integer

Y = crystal.GetNSubreports - 1
For X = 0 To Y
 crystal.SubreportToChange = crystal.GetNthSubreportName(X)
 crystal.Connect = dsnreport
 If X = 0 Then
    crystal.DataFiles(0) = "Proc(am_jualkwitansi2)"
 End If
 If X = 1 Then
    crystal.DataFiles(0) = "Proc(am_jualkwitansi3)"
 End If
 If X = 2 Then
    crystal.DataFiles(0) = "Proc(am_jualkwitansi4)"
 End If
Next X
======================================================
this code work fine.
thank you again qjohnson

qjohnson if you have an email adress, send me to febdwi@yahoo.com okey.

thank very much

regards
dwanto
Congratulations, dwanto!

ASKER CERTIFIED SOLUTION
Avatar of QJohnson
QJohnson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
a grade of B - you have got to be kidding.  Do you have any idea how much of my time I gave in providing the information in this thread?  

It might be judged foolish on my part to have devoted this much effort to a question work so few points (note that no one else choose to get involved), but I did it because I hoped it would help you "learn to fish" rather than just "feed you for the day."  

Clearly the points were not my motivation here or I would have deserted you after the second or third reply.

But for you to award less than an A for the effort is insulting, rude, and quite ungrateful.  

Again, it wasn't about the points in this case.  But you have to consider what kind of incentive this will give other to invest time in answering your future questions.  Who have you hurt most by your action?
Avatar of dwanto

ASKER

sorry qjohnson

it's my first time here, so don't be angry about that and i'm sorry if i hurt your feeling i will not do that again.

if there is another way to change the grade i will change it for you.

regards
dwiwanto
I don't know if you can change it or not.  (If any one can do it, the folks at "Community Support" are the ones to contact.  But it really isn't worth your time.) Your willingness to do so is quite welcome and I happily accept your apology.  That is worth more than a few extra points and I thank you for responding here.

That willingness should also make clear to anyone who saw these remarks that you "know better" now and the fact that this was your first use of the forum is clearly sufficient justification for the small misunderstanding that took place here.  To say it more simply, I don't think that there will be any damage to YOU.  

And I certainly don't feel any now.

Good luck, my friend.
Avatar of dwanto

ASKER

thank you qjohnson
Grade changed from B to A per questioner's request.

SpideyMod
Community Support Moderator @Experts Exchange