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
