Skip to Content

am facing an major technical difficulty migrate crystal report. We have an crystal report 8.5 and in that we are passing the sql query from vb.net Currently. We are migrating crystal report 8.5 to 2008 and same query is not working. 

For your information we are using here Crystal Report Viewer instead of Crystal Report ActiveX Control. Please check the below code and confirm back ASAP.

———————————————————————–

Dim CaRpt As New A_CLAIMS_IL
        Dim s_SqlStr As String
        Dim conninfo As New ConnectionInfo
        Dim tablelogoninfos As New TableLogOnInfos()
        Dim tablelogoninfo As New TableLogOnInfo()
        Dim tables As Tables
        Dim table As Table
        Dim crTables As Tables

        Dim l_arr_count As Short

        CaRpt.Refresh()

        Dim l_da_status As New OracleDataAdapter(f_str_where, g_rdoConn)
        Dim l_ds_status As New DataSet

        With conninfo
            .ServerName = g_str_DSN
            .UserID = g_str_ORA_ID
            .Password = g_str_ORA_PWD
        End With
        tables = CaRpt.Database.Tables
        For Each table In tables
            tablelogoninfo = table.LogOnInfo
            tablelogoninfo.ConnectionInfo = conninfo
            table.ApplyLogOnInfo(tablelogoninfo)
        Next

        CaRpt.SetDatabaseLogon(g_str_ORA_ID, g_str_ORA_PWD)

        l_da_status.Fill(l_ds_status, f_str_where)
        crTables = CaRpt.Database.Tables
        CaRpt.SetDataSource(l_ds_status.Tables(0))

        ‘—————————————————–
        If f_int_arrayelements <> 0 Then
            For l_arr_count = 0 To f_int_arrayelements – 1
                CaRpt.DataDefinition.FormulaFields(l_arr_count).Text = f_arr_reportvar(l_arr_count, 1)
            Next l_arr_count
        End If
        ‘—————————————————–
        ‘Neeraj Added this Code for Filter the data in the report End
        CRVCoreRpt.ReportSource = CaRpt
        CRVCoreRpt.Refresh()
        Me.Show()
———————————————————————–

From below code we are passing a query to above code

l_str_SQL = “SELECT Distinct CCURRENCY.DECIMALLEN,V_CLAIMREP.CA_ID, V_CLAIMREP.SECODE, V_CLAIMREP.CE_TYP, V_CLAIMREP.DECL_DT,V_CLAIMREP.EVNT_NBR, V_CLAIMREP.EX_DT, V_CLAIMREP.REC_DT, V_CLAIMREP.PAY_DT,  “
            l_str_SQL = l_str_SQL & “V_CLAIMREP.INCOME, V_CLAIMREP.INCOME_RT,V_CLAIMREP.RES_SECODE, V_CLAIMREP.NEW_STOCK_QTY, V_CLAIMREP.OLD_STOCK_QTY, V_CLAIMREP.FRACT_DISP, V_CLAIMREP.F_PRICE, V_CLAIMREP.NEW_STOCK_QTY_RATE, V_CLAIMREP.CA_CCY,V_CLAIMREP.OTHPROC,V_CLAIMREP.SECDESC,V_CLAIMREP.RES_SECDESC,V_CLAIMREP.PAYOUT_IND,V_CLAIMREP.RATIO_PCT_IND, V_CLAIMREP.AMT_RATE_IND, “
            l_str_SQL = l_str_SQL & “V_CLAIMREP.ACCOUNT, V_CLAIMREP.CNAME, V_CLAIMREP.ADDRESS, V_CLAIMREP.TIPOSEC,V_CLAIMREP.NOMVAL, V_CLAIMREP.REFNO,V_CLAIMREP.TRDT,V_CLAIMREP.SETTLEDT,V_CLAIMREP.SECDT,V_CLAIMREP.QTY,V_CLAIMREP.TAX_RT,V_CLAIMREP.MSTACC, “
            l_str_SQL = l_str_SQL & ” SYS_DATE.SYSTEM_DATE, COMMON_SYS_PARAMS.CORPORATE_NAME “
            l_str_SQL = l_str_SQL & “FROM CCURRENCY CCURRENCY, V_CLAIMREP_IL V_CLAIMREP, COMMON_SYS_PARAMS COMMON_SYS_PARAMS,SYS_DATE SYS_DATE WHERE CCURRENCY.CCYCODE = V_CLAIMREP.CA_CCY  “
            l_str_repoutputfile = getfilename()

            l_str_where = “AND V_CLAIMREP.CA_ID = ‘” & p_str_CaID & “‘”
            l_str_ParamString = p_str_CaID & “|”
            If Len(Trim(uclAccount.TextCbo)) <> 0 Then
                l_rq_MastAccCheck = “SELECT MSTACC FROM CACCOUNT WHERE MSTACC=ACCOUNT AND ACC_STATUS = ‘” & AUTHORISED & “‘ AND ACCOUNT='” & Trim(uclAccount.TextCbo) & “‘”
                l_rs_MastAccCheck.Open(l_rq_MastAccCheck, g_rdoConn)

                If l_rs_MastAccCheck.Count >= 1 Then
                    l_Account = l_rs_MastAccCheck.item(“MSTACC”).ToString()
                    l_str_SQL = l_str_SQL & “AND ACC_STATUS = ‘” & AUTHORISED & “‘ AND MSTACC='” & Trim(uclAccount.TextCbo) & “‘”
                Else
                    l_str_where = l_str_where & ” AND V_CLAIMREP.ACCOUNT ='” & Trim(uclAccount.TextCbo) & “‘”
                    l_str_ParamString = l_str_ParamString & uclAccount.TextCbo & “|”
                End If

            End If

            If Len(Trim(uclDomCP.TextCbo)) <> 0 Then
                l_str_where = l_str_where & ” AND V_CLAIMREP.DOMCPAC ='” & Trim(uclDomCP.TextCbo) & “‘”
                l_str_ParamString = l_str_ParamString & uclDomCP.TextCbo & “|”
            End If
            l_str_SQL = l_str_SQL & l_str_where
            Call frmReport.dispClaimRptDetails(l_coll_Claimrpt, l_str_ParamString, l_str_SQL, l_arr_reportvar, CShort(“4”))

I try to post this in SAP crystal Report site. But not able to find the link in which i will post my query.

Please check and confirm.

Thanks,

Neeraj Rastogi

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply