Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use View Or Stored Procedure As Datasource

Status
Not open for further replies.
jtrapat1

jtrapat1

Programmer
Jan 14, 2001
137
US
I'm using CR8.5 and VB6 against an SQL Server 7 database.
Is it possible to use a view or sp from SQL Server as a datasource?
Here's an example I found here which is very close to what I want to do:

Dim adoConn as ADOdb.Connection
Dim adoRS as ADOdb.Recordset
Dim strSQL as String
Dim Report as CrystalReport1

strSQL = "Your SQL statement"

Set adoRS = New ADOdb.Recordset
With adoRS
.ActiveConnection = adoConn
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Open strSQL
End With

Set Report = New CrystalReport1
Report.Database.SetDataSource adoRS
CRViewer1.ReportSource = Report
CRViewer1.ViewReport

In this example, they're using the Crystal Viewer, where I would be using just the report object, Crystal32.ocx.

How would I set the value of strSQL equal to a view or stored procedure which is out on SQL Server?

Thanks
John
 
Sort by date Sort by votes
Your code is for the RDC technology, not the old OCX technology as you want to use.

If you're set on use the OCX:

a. Write and test the store procedure
b. Create the Crystal Report with the Designer and point it at the stored procedure
c. Run the report using the OCX, passing any parameters necessary before doing a .Action = 1
 
Upvote 0 Downvote
I am using Server2000,asp,ms IIS and crystalreports8.5...
When I run CrystalReports from asp my MS access2000 database gets locked.
What should I do to get rid of that lock?
thanks in advance...

here is my code:
<%@ Language=VBScript %>
<%Response.Buffer=true
Response.Expires = 0
'response.expires = 0
'response.expiresabsolute = Now() - 1
'response.addHeader &quot;pragma&quot;,&quot;no-cache&quot;
'response.addHeader &quot;cache-control&quot;,&quot;private&quot;
'response.CacheControl = &quot;no-cache&quot;
%>

<HTML>
<link rel=&quot;stylesheet&quot; type=&quot;text/css&quot; href=&quot;../StyleSheet/GeneralStyleSheet.css&quot;>
<link rel=&quot;stylesheet&quot; type=&quot;text/css&quot; href=&quot;../StyleSheet/GlobalStyleSheet.css&quot;>
<head>
<META NAME=&quot;Date.Modified&quot; Content=&quot;01.05.2001&quot;>
<META NAME=&quot;Designer&quot; Content=&quot;Aka Yazilim Ltd&quot;>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
<title>Spexor Marketing System</title>
<base>
</head>


<%
' CREATE THE APPLICATION OBJECT
If Not IsObject (session(&quot;oApp&quot;)) Then
Set session(&quot;oApp&quot;) = Server.CreateObject(&quot;Crystal.CRPE.Application&quot;)
End If

'OPEN THE REPORT (but destroy any previous one first)
If IsObject(session(&quot;oRpt&quot;)) then
Set session(&quot;oRpt&quot;) = nothing
End if
' full path to report's file
path = &quot;C:\Inetpub\ reportname = &quot;Report8.rpt&quot;

'Open the report...
Set session(&quot;oRpt&quot;) = session(&quot;oApp&quot;).OpenReport(path & reportname, 1)
'session(&quot;oRpt&quot;).MorePrintEngineErrorMessages =False
'session(&quot;oRpt&quot;).EnableParameterPrompting =False
session(&quot;oRpt&quot;).DiscardSavedData
Set crtable = session(&quot;oRpt&quot;).Database.Tables.Item(1)
'crtable.location = &quot;C:\Inetpub\ crtable.SetLogonInfo &quot;Gokhan&quot;, &quot;NewDb1.mdb&quot;, &quot;&quot;, &quot;&quot;

'Set Parameters..
Set session(&quot;ParamCollection&quot;) = session(&quot;oRpt&quot;).ParameterFields
Set oParam = session(&quot;ParamCollection&quot;).Item(1)
FirmCode=&quot;N*&quot;
Call oParam.SetCurrentValue(cstr(FirmCode),12)
FirmName=&quot;N*&quot;
Call oParam.SetCurrentValue(cstr(FirmName),12)


'set session(&quot;oPageEngine&quot;) = nothing
On Error Resume Next
'session(&quot;oRpt&quot;).ReadRecords

If Err.Number <> 0 Then
Response.Write &quot;Error Occurred Reading Records: &quot; & Err.Description
Session.Abandon
Response.End
Else
If IsObject(session(&quot;oPageEngine&quot;)) Then
set session(&quot;oPageEngine&quot;) = nothing
End If
set session(&quot;oPageEngine&quot;) = session(&quot;oRpt&quot;).PageEngine
End If

Set Session(&quot;oRpt&quot;) = nothing
Set Session(&quot;oApp&quot;) = nothing

'Session.Abandon
'Response.End
%>
<!-- #include file=&quot;../../MyScriptLibrary/SmartViewerActiveX.asp&quot; -->
</HTML>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
Outlook as a Datasource?
Replies
0
Views
534
dgillz
dgillz
Ivan Kok
  • Locked
  • Question
Crystal Reports Runtime causing AS/400 DB2 Database ID to be disabled intermittently
Replies
0
Views
553
Ivan Kok
Ivan Kok
BTPJayW
  • Locked
  • Question
Verify Database Not Working Against Stored Procedure
Replies
1
Views
619
hilfy
hilfy
dgillz
  • Locked
  • Question
Cannot connect to any ODBC datasource with MS SQL Server data in crystal
Replies
8
Views
919
LyleU
LyleU
RenaG
  • Locked
  • Question
Max number of tables in the datasource for Crystal Reports
Replies
0
Views
111
RenaG
RenaG

Part and Inventory Search

Sponsor

Back
Top