A problem with CR connected to SQL Server in ASP

[jane]

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

 

[Jane]

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 //
*********************************************************