Dataset not taking more than 39 rows!

It has nothing to do with UTF16, or substr(), the sql was returning NULL in some columns and when that was dealt with using NVL() the problem was reloved. Anyhow, if you face such dilemma, use the hot fix from microsoft or .net 1.1 to be able to see the actual warnings from Oracle. " http://support.microsoft.com/?id=331499 This article was previously published under Q331499 SYMPTOMS When you execute a query using Microsoft .NET Framework Managed Provider for Oracle, and if the Oracle database server returns a warning message, you will always receive an empty DataSet, even when the query returns a DataSet that is not empty. RESOLUTION A supported fix is now available from Microsoft, but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next version of .NET Framework that contains this fix. To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site: .. "

Yes, it could. UTF16 is unicode, and uses multiple bytes per character. This forces it to use twice as much buffer space. This might only affect it if the client is using ASCII (which is one byte per character). If it is using UTF-8 or other type of unicode, it probably won't make a difference.

the database server is Oracle9i however the client is still oracle8i

might the following be the reason? from Microsoft .NET Framework Data Provider for Oracle Reference Documentation "System Requirements for the .NET Framework Data Provider for Oracle .... Oracle client software prior to version Oracle 9i cannot access UTF16 databases because UTF16 is a new feature in Oracle 9i. To use this feature, you must upgrade your client software to Oracle 9i or later. "

Yes the SQL procedure works fine manually. When called from the app with the followig: _claimSubmissionReportDataSet = (ClaimSubmissionReportDataSet)OracleHelper.ExecuteMappedDataset(PICommon.DBConnectionString, CommandType.StoredProcedure, schemaName + "." + packageName + "." + procedureName, _claimSubmissionReportDataSet, this.GetMapping(MapType.Query, "ReportDetail"), parms); at first it returned only 39 rows, then when I added the substr to the SQL as below, it loaded the data set with 43 rows but if total is over 100 zero rows are loaded to the dataset. adding the substr sure made a difference. select distinct cf.cf_claim_form_id, pr_provider_id providerId, cf.payee_type_cd payeeTypeCd, pr_trading_name providerName, pr_account_no providerNo, substr(cf.cf_part_last_name,0,30) patientSurName, substr(cf.cf_part_first_name,0,30) patientFirstName, decode(cf.cf_participant_id, NULL,TO_CHAR(cf.su_subs_id), cf.cf_participant_id) subscriberId, cf.pa_dep_no depNo, cd.cd_serv_dt serviceDate, cd.cd_proc_din_no procCd, substr(bc.bc_short_desc,0,30) bcEnglishDesc, substr(bc.bc_french_desc,0,30) bcFrenchDesc, cd.cd_rend_cdn_amt rendAmt, sum(cob.cobc_carrier_paid_amt) paidByOther, cd.cd_paid_amt paidAmt from prod.claim_form cf, prod.clm_detail cd, prod.benefit_category bc, prod.clm_detl_cob_carrier cob, prod.provider pr_prov where cf.cf_subm_provider_id = i_provider_id --106365 and cf.delete_dt is null -- and cf.row_exp_dt is null and cf.cf_lupd_oper_cd = i_user_id and cf.claim_type_cd = 'CL' -- and cf.status_type_cd = 'PC' and cf.cf_claim_form_id = cd.cf_claim_form_id and cf.revision_no = cd.claim_form_revision_no and cf.cf_entered_dt = trunc(i_submission_dt) --and cd.status_type_cd in ('PD', 'AW') and ( cd.cf_claim_form_id = cob.cf_claim_form_id(+) and cd.claim_form_revision_no =cob.claim_form_revision_no(+) and cd.cl_claim_id =cob.cl_claim_id(+) and cd.claim_revision_no =cob.claim_revision_no(+) and cd.cd_detail_id = cob.cd_detail_id (+) ) and bc.bc_bnft_catg_type_cd = cd.bc_bnft_catg_type_cd and bc.bc_bnft_catg_id = cd.bc_bnft_catg_id and ( bc.delete_dt is null and trunc(sysdate)>trunc(bc.row_eff_dt) and (bc.row_exp_dt is null or bc.row_exp_dt>=trunc(sysdate)) ) and pr_prov.pr_provider_id = cf.cf_subm_provider_id and pr_prov.PROVIDER_STATUS_CD = 'E' AND pr_prov.PARTIC_STATUS_CD IN ('NP', 'PA') and pr_prov.delete_dt is null and pr_prov.row_eff_dt <= sysdate and ( pr_prov.row_exp_dt is null or trunc(sysdate) <= pr_prov.row_exp_dt ) group by cf.cf_claim_form_id, pr_provider_id, pr_trading_name, pr_account_no, cf.payee_type_cd, cf.cf_part_first_name, cf.cf_part_last_name, cf.su_subs_id, cf.cf_participant_id, cf.pa_dep_no, cd.cd_serv_dt, cd.cd_proc_din_no, bc.bc_short_desc, bc.bc_french_desc, cd.cd_rend_cdn_amt, cd.cd_paid_amt order by pr_provider_id, cf.payee_type_cd, patientSurName, patientFirstName, subscriberId, cf.pa_dep_no, cd.cd_serv_dt;

Try posting some code. If there is an oversight in your code, we can't see it unless you show it to us. Is this a stored procedure, or a query, etc? What happens when you execute this query manually? Do you get all the records you want? Have you tried another provider, such as an OLEDB.NET provider?

narrowing down the investigation to the SQL, I found that having multiple strings retrieved is causing the dataset not to load. I used substr(the selected string) that seemed to allow about 100 rows to display but not more. Still the problem is not resolved, and What if I don't want to substring the string? what's limiting the mapping into the dataset? any help is appreciated, thanks