Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > A problem with CR connected to SQL Server in ASP

Reply
Thread Tools

A problem with CR connected to SQL Server in ASP

 
 
jane
Guest
Posts: n/a
 
      08-01-2003
I use following code to create a Crystal Report by
connecting to an access db in ASP. It works fine. Now I
switch to SQL Server and it doesn't work. It seems to me
there is nowhere I can add login information (userid and
password). Can someone help me or point me to somewhere I
can find sample code? Thanks in advance.

'-- get report name
reportname = "Report1.rpt"

If Not IsObject (session("oApp"))
Then
Set session("oApp") = Server.CreateObject
("CrystalRuntime.Application")
End
If
Call session("oApp").SetMorePrintEngineErrorMessages(0)
Call session("oApp").SetMatchLogonInfo(1)

Path = Request.ServerVariables
("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <>
0)
iLen = Len(Path) -
1
Path = Left(Path,
iLen)
Wend
'Response.Write (Path) & "<br>"

If IsObject(session("oRpt")) then
Set session("oRpt") = nothing
End if

On error resume next
'-- get report file path
Set session("oRpt") = session("oApp").OpenReport(path &
reportname, 1)

If Err.Number <> 0 Then
Response.Write "Error Occurred creating Report Object: "
& Err.Description
Set Session("oRpt") = nothing
Set Session("oApp") = nothing
Session.Abandon
Response.End
End If

'-- disable the Error reporting
session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False

session("oRpt").DiscardSavedData

NewQueryString="Select * From view1"
'Response.Write (NewQueryString) & "<br>"
session("oRpt").SQLQueryString = CStr(NewQueryString)

On Error Resume Next
session("oRpt").ReadRecords
If Err.Number <> 0
Then
Response.Write "Error Occurred Reading Records: " &
Err.number & ", " & Err.Description
Set Session("oRpt") = nothing
Set Session("oApp") = nothing
Session.Abandon
Response.End
Else
If IsObject(session("oPageEngine"))
Then
set session("oPageEngine") = nothing
End If
set session("oPageEngine") = session("oRpt").PageEngine
End If
%>

<OBJECT ID="CRViewer1"
CLASSID="CLSID:C4847596-972C-11D0-9567-
00A0C9273C2A"
WIDTH=100%
HEIGHT=100%

codebase="/viewer/activeXViewer/activexviewer.cab#Version=8
,5,0,217">
<param name="EnableGroupTree" value=1>
<param name="DisplayGroupTree" value=1>
<param name="EnableRefreshButton" value=1>
<param name="EnablePrintButton" value=1>
<param name="EnableExportButton" value=1>
<param name="EnableSearchControl" value=1>
<param name="EnableDrillDown" value=1>
<param name="EnableAnimationControl" value=1>
<param name="EnableZoomControl" value=1>
</OBJECT>

<SCRIPT LANGUAGE="VBScript">
Call Page_Initialize

Sub Page_Initialize
On Error Resume Next
Dim WebBroker1
Set WebBroker1 = CreateObject
("WebReportBroker.WebReportBroker")
Call GetURL(URL)
if ScriptEngineMajorVersion < 2 then
window.alert "IE 3.02 users on NT4 need
to get the latest version of VBScript or install IE 4.01
SP1. IE 3.02 users on Win95 need DCOM95 and latest version
of VBScript, or install IE 4.01 SP1. These files are
available at Microsoft's web site."
CRViewer1.ReportName = URL
+ "rptserver.asp"
else
Dim WebSource1
Set WebSource1 = CreateObject
("WebReportSource.WebReportSource")
WebSource1.ReportSource = WebBroker1
WebSource1.URL = URL + "rptserver.asp"
WebSource1.PromptOnRefresh = true
CRViewer1.ReportSource = WebSource1
Response.Write (WebSource1) & "b<br>"
end if
CRViewer1.ViewReport
End Sub

Sub GetURL(ByRef URL)
HREF = Location.Href
URL= ""
nPos = InStr(HREF, "/")
While (nPos > 0)
URL = URL & Left( HREF, nPos )
HREF = Mid(HREF, nPos + 1)
nPos = InStr(HREF, "/")
Wend
End Sub
 
Reply With Quote
 
 
 
 
Jane
Guest
Posts: n/a
 
      08-01-2003
Thanks so much.

There was a problem that I used OLE DB rather than ODBC
connection when generating a report in CR.

Now I have another question. I am using a parameterized
stored procedure in my report and use following code in
ASP. But no record has been returned (should return some
records).

Thanks in advance!

-Jane

******************************************
set StoredProcParamCollection = Session
("oRpt").ParameterFields

Set ThisParam = StoredProcParamCollection.item(1)
Set ThisParam2 = StoredProcParamCollection.item(2)

NewParamValue = myvalue1
NewParamValue2 = myvalue2

ThisParam.SetCurrentValue cDate(NewParamValue), ?
ThisParam2.SetCurrentValue cDate(NewParamValue2), ?

//which number I can use to replace ?(above)? I couldn't
find it in sample code offered by Crystal Decisions //
************************************************** *******


>-----Original Message-----
>for iNdx = 1 to session("oRpt").Database.Tables.Count
> session("oRpt").Database.Tables(iNdx).SetLogonInfo
>ServerIP, DatabaseName, UserId, Password
>next
>
>HTH
>John
>
>
>>-----Original Message-----
>>I use following code to create a Crystal Report by
>>connecting to an access db in ASP. It works fine. Now I
>>switch to SQL Server and it doesn't work. It seems to me
>>there is nowhere I can add login information (userid and
>>password). Can someone help me or point me to somewhere

I
>>can find sample code? Thanks in advance.
>>
>>'-- get report name
>>reportname = "Report1.rpt"
>>


>
>>If Not IsObject (session("oApp"))
>>Then
>> Set session("oApp") = Server.CreateObject
>>("CrystalRuntime.Application")
>>End
>>If
>>Call session("oApp").SetMorePrintEngineErrorMessages(0)
>>Call session("oApp").SetMatchLogonInfo(1)
>>
>>Path = Request.ServerVariables
>>("PATH_TRANSLATED")
>>While (Right(Path, 1) <> "\" And Len(Path) <>
>>0)
>> iLen = Len(Path) -
>>1
>> Path = Left(Path,
>>iLen)
>>Wend
>>'Response.Write (Path) & "<br>"
>>
>>If IsObject(session("oRpt")) then
>> Set session("oRpt") = nothing
>>End

if
>>
>>On error resume next
>>'-- get report file path
>>Set session("oRpt") = session("oApp").OpenReport(path &
>>reportname, 1)
>>
>>If Err.Number <> 0 Then
>> Response.Write "Error Occurred creating Report

>Object: "
>>& Err.Description
>> Set Session("oRpt") = nothing
>> Set Session("oApp") = nothing
>> Session.Abandon
>> Response.End
>>End If
>>
>>'-- disable the Error reporting
>>session("oRpt").MorePrintEngineErrorMessages = False
>>session("oRpt").EnableParameterPrompting = False
>>
>>session("oRpt").DiscardSavedData
>>
>>NewQueryString="Select * From view1"
>>'Response.Write (NewQueryString) & "<br>"
>>session("oRpt").SQLQueryString = CStr(NewQueryString)
>>


>
>>On Error Resume Next
>>session("oRpt").ReadRecords
>>If Err.Number <> 0
>>Then
>> Response.Write "Error Occurred Reading Records: " &
>>Err.number & ", " & Err.Description
>> Set Session("oRpt") = nothing
>> Set Session("oApp") = nothing
>> Session.Abandon
>> Response.End
>>Else
>> If IsObject(session("oPageEngine"))
>>Then
>> set session("oPageEngine") = nothing
>> End If
>> set session("oPageEngine") = session("oRpt").PageEngine
>>End If
>>%>
>>
>><OBJECT ID="CRViewer1"
>> CLASSID="CLSID:C4847596-972C-11D0-9567-
>>00A0C9273C2A"
>> WIDTH=100%
>> HEIGHT=100%
>>
>>codebase="/viewer/activeXViewer/activexviewer.cab#Version

=
>8
>>,5,0,217">
>><param name="EnableGroupTree" value=1>
>><param name="DisplayGroupTree" value=1>
>><param name="EnableRefreshButton" value=1>
>><param name="EnablePrintButton" value=1>
>><param name="EnableExportButton" value=1>
>><param name="EnableSearchControl" value=1>
>><param name="EnableDrillDown" value=1>
>><param name="EnableAnimationControl" value=1>
>><param name="EnableZoomControl" value=1>
>></OBJECT>
>>
>><SCRIPT LANGUAGE="VBScript">
>>Call Page_Initialize
>>
>>Sub Page_Initialize
>> On Error Resume Next
>> Dim WebBroker1
>> Set WebBroker1 = CreateObject
>>("WebReportBroker.WebReportBroker")
>> Call GetURL(URL)
>> if ScriptEngineMajorVersion < 2 then
>> window.alert "IE 3.02 users on NT4 need
>>to get the latest version of VBScript or install IE 4.01
>>SP1. IE 3.02 users on Win95 need DCOM95 and latest

>version
>>of VBScript, or install IE 4.01 SP1. These files are
>>available at Microsoft's web site."
>> CRViewer1.ReportName = URL
>>+ "rptserver.asp"
>> else
>> Dim WebSource1
>> Set WebSource1 = CreateObject
>>("WebReportSource.WebReportSource")
>> WebSource1.ReportSource = WebBroker1
>> WebSource1.URL = URL + "rptserver.asp"
>> WebSource1.PromptOnRefresh = true
>> CRViewer1.ReportSource = WebSource1
>> Response.Write (WebSource1) & "b<br>"
>> end if
>> CRViewer1.ViewReport
>>End Sub
>>
>>Sub GetURL(ByRef URL)
>> HREF = Location.Href
>> URL= ""
>> nPos = InStr(HREF, "/")
>> While (nPos > 0)
>> URL = URL & Left( HREF, nPos )
>> HREF = Mid(HREF, nPos + 1)
>> nPos = InStr(HREF, "/")
>> Wend
>>End Sub
>>.
>>

>.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
Asp script connected to sql server gives error Jack ASP General 6 12-20-2005 11:07 PM
connected but not connected =?Utf-8?B?R2xlbm4=?= Wireless Networking 1 12-04-2005 12:35 AM
problem: connected, but not connected to a wireless network Andrew Smith Wireless Networking 0 08-20-2004 07:51 PM
Connected Icon Showing, But In Reality Not Connected. Thoughts On ? Robert11 Computer Support 2 03-02-2004 04:02 PM



Advertisments