A problem with CR connected to SQL Server in ASP

Discussion in 'ASP General' started by jane, Aug 1, 2003.

  1. jane

    jane Guest

    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, Aug 1, 2003
    #1
    1. Advertising

  2. jane

    Jane Guest

    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
    >>.
    >>

    >.
    >
     
    Jane, Aug 1, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Illusionistx
    Replies:
    6
    Views:
    940
    Keith Wansbrough
    Nov 12, 2004
  2. Dundonald
    Replies:
    2
    Views:
    407
    Steve Horsley
    Mar 15, 2005
  3. Keithb
    Replies:
    4
    Views:
    462
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tp?=
    Oct 10, 2006
  4. Bart
    Replies:
    4
    Views:
    354
  5. Jack
    Replies:
    6
    Views:
    195
Loading...

Share This Page