?
Solved

cannot open sql server

Posted on 2003-03-04
24
Medium Priority
?
1,108 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:dwanto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
24 Comments
 
LVL 3

Expert Comment

by:QJohnson
ID: 8071584
Try inserting:

 "Driver={SQL Server}"

at the beginning of your DSN string.
0
 

Author Comment

by:dwanto
ID: 8100844
sorry it doesn't work
0
 

Author Comment

by:dwanto
ID: 8100854
please, some body help me!!!
i need the solution for my problem as soon as posible.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:QJohnson
ID: 8103458
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
0
 

Author Comment

by:dwanto
ID: 8111076
sorry qjohnson , it doesn't work to
but thank you any way
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8111210
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
0
 

Author Comment

by:dwanto
ID: 8117779
thank you
0
 

Author Comment

by:dwanto
ID: 8117797
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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8119634
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.
0
 

Author Comment

by:dwanto
ID: 8127401
i already try that but still error
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8128394
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
0
 

Author Comment

by:dwanto
ID: 8134852
thank you qjohnson, i will try that
0
 

Author Comment

by:dwanto
ID: 8135422
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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8136057
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.
0
 

Author Comment

by:dwanto
ID: 8142205
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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8142882
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.

0
 

Author Comment

by:dwanto
ID: 8149516
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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8151491
Congratulations, dwanto!

0
 
LVL 3

Accepted Solution

by:
QJohnson earned 100 total points
ID: 8158799
may as well indicate the chosen answer and award points, now, right?
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8174413
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?
0
 

Author Comment

by:dwanto
ID: 8179068
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
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8181692
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.
0
 

Author Comment

by:dwanto
ID: 8185223
thank you qjohnson
0
 

Expert Comment

by:SpideyMod
ID: 8188806
Grade changed from B to A per questioner's request.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question