Crystal Report/SQL Server

[Shiv Kumar G.M.]

Dear Sir,

I am having SQL Database. I am connecting thru VB with Crystal. I am connecting through ADO.(ODBC). There it asks for username,password. The report gets created (design time). But when I open it, it gives a error saying "database not yet opened", "unable to login".

Can u give the way, by which I can connect the report of crystal to SQL. I mean how to pass username, password etc.

Sample code.

This is very urgent.

Thank You.

Shivakumar G.M.

[Shiv Kumar G.M.]

Anyone who knows crystal with SQL Server. Pls help me.

Thanks

[AnnMarie]

Do you mean something like this??


private Sub cmdClassSummary_Click()
Dim strMessage as string

With CrystalReport6
.ReportFileName = "C:\windows\desktop\IndexClassSummary.rpt"
.Connect = "DSN=CSC_Reports_TEST; DATABASE=CSC_Reports; UID=sa; PWD=;"
.WindowState = crptMaximized
.Action = 1

'CREATE CORRESPONDING EXCEL FILE?
strMessage = MsgBox("Export to an Excel file?", vbYesNo, "Export to Excel")
If strMessage = vbYes then
frmCSCReports.Hide
.PrintFileType = crptExcel50
.PrintFileName = "C:\windows\desktop\IndexClassSummary.xls"
.Destination = crptToFile
.Action = 2
frmCSCReports.Show
else
MsgBox "Excel file not created"
frmCSCReports.Show
Exit Sub
End
End If

.Reset
End With

[FrankB]

I'm no SQL Server expert and this is just a guess.
When I look at my available ODBC DSNs under Database/Show SQL Query in Crystal Reports design,
I notice that all my DSNs are Machine DSNs and none of them are shareable, including the DSN which I used when designing the report (which can be seen under Database/Set Location). But you seem to indicate that you are already using your DSN to connect to your DB via VB ADO. Is it possible that this conflicts with Crystal's No-sharing demand, in which case all you might need is a to set up (under Start/Settings/Control Panel/ODBC Data Sources(32-Bit)) a second ODBC DSN pointing to your DB, and specify that DSN in the "DSN=" part of your CrystalReport Control's Connect Property, as in the sample code shown by Anne-Marie?

[Shiv Kumar G.M.]

I have already created a crystal report.(CrystalReport.Dsr) which is created within VB using Crystal Report wizard. I just need to open that. But that says error saying "server not yet opened".

Thanks

[Shiv Kumar G.M.]

Pls help me on this

[Shiv Kumar G.M.]

Help me on this. It is very urgent plz.

[manK]

'You did not specify the version of crystal report you are using
'but assuming it to be version 7 which is what i have, then using the following procedure
' to create a report from SQL server
'1. Create VB project
'2. Place a crystal report viewer control on your form
'3. Create your report basing the report on a data definition file (.TTX)
' (See the sample i used below)
'4. Place a command button on you form.I named mine cmdShowReport
'5. Initialize strConnect variable to point to your SQL server
'Clicking the button should display your report

'===SAMPLE DATA DEFINITION FILE
'(Shows fieldnames, Field data type,optional field length and sample data
' this information is separated by tabs)

'============================
'au_lname String 20 Sample
'au_Lname String 20 Sample
'Phone String 20 Sample
'City String 30 Sample
'State String 3 SAS
'===========================



'===FORM DECLARATION SECTION
option Explicit

private m_rpt as CrystalReport1
private m_Rs as ADODB.Recordset

private Sub cmdShowReport_Click()
Dim strConnect as string
set m_Rs = new ADODB.Recordset
With m_Rs
strConnect = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=false;" & _
"User ID=sa;" & _
"Initial Catalog=pubs;" & _
"Data Source=manK" '*** Replace manK with the name of your SQL server
.Source = "Select * from Authors where state ='CA'"
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.ActiveConnection = strConnect
.Open
End With

set m_rpt = new CrystalReport1
m_rpt.Database.SetDataSource m_Rs, 3, 1
CRViewer1.ReportSource = m_rpt
CRViewer1.ViewReport
End Sub


private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight - 1000 ' to leave space to display comand buttons
CRViewer1.Width = ScaleWidth
End Sub

[Cyrus]

following ur example, what is the steps to select the field that will be printed with crystal report. I'am using crytal report XI

thank

cyrus