Dataset does not fill

[cglinn]

Something really strange has started occurring with a few of my datasets. I'll try to explain as best I can. I am creating a dataset by running queries against an Oracle database and merging the results into one dataset. The dataset contains two tables. When I run the code now, the first table is empty...however, the second table contains data. If I run the query for the first table using PL/SQL developer, I return close to 1000 records. This was not a problem until recently and nothing has changed with the code. Does anyone have any ideas as to what could be causing this? I don't know how helpful the information is that I supplied, I can offer up more if needed.

Thank you.

Chris

 

[DaZZleD]

basically a code that used to return 1000 rows now returns nothing?

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[checkai]

you may want to post some code as to how you fill the datatables and dataset...

 

[cglinn]

Yes, that is correct. Code that was returning records now returns nothing, however I know the query works. Below is some code to show what I am doing:

strSQL = New StringBuilder("")
strSQL.Append("select distinct libegb ""Current status[W]"", tabdos.coddos || '-' || codpay || '-' || codtyp || tabbre.numero ""File Number"", ")
strSQL.Append("codpay ""Country [C]"", codage ""AGT"", (select distinct to_char(sui.datrea, 'MM/DD/YYYY') ")
strSQL.Append("from tabsui sui, tabeve eve ")
strSQL.Append("where sui.idedos = tabbre.idebre and sui.typdos = 'BRE' and ")
strSQL.Append("eve.ideeve = sui.ideeve and eve.codeve = 'SFA') as sfa_dte, ")
strSQL.Append("(select distinct to_char(sui2.datrea, 'MM/DD/YYYY') ")
strSQL.Append("from tabsui sui2, tabeve eve2 ")
strSQL.Append("where sui2.idedos = tabbre.idebre and sui2.typdos = 'BRE' and ")
strSQL.Append("eve2.ideeve = sui2.ideeve and eve2.codeve = 'FLG' and codpay != 'US') as for_file, ")
strSQL.Append("(select distinct to_char(sui3.datrea, 'MM/DD/YYYY') ")
strSQL.Append("from tabsui sui3, tabeve eve3, tabpay pay3, tabbre bre3 ")
strSQL.Append("where sui3.idedos = bre3.idedos and sui3.typdos = 'BRE' and ")
strSQL.Append("eve3.ideeve = sui3.ideeve and eve3.codeve = 'FLG' and ")
strSQL.Append("bre3.idepay = pay3.idepay and pay3.codpay = 'US' and ")
strSQL.Append("tabdos.idedos = bre3.idedos) as us_file, ")
strSQL.Append("(select distinct to_char(sui4.datrea, 'MM/DD/YYYY') ")
strSQL.Append("from tabsui sui4, tabeve eve4, tabpay pay4, tabbre bre4 ")
strSQL.Append("where sui4.idedos = bre4.idedos and sui4.typdos = 'BRE' and ")
strSQL.Append("eve4.ideeve = sui4.ideeve and eve4.codeve = 'PTO' and ")
strSQL.Append("bre4.idepay = pay4.idepay and pay4.codpay = 'US' and ")
strSQL.Append("tabdos.idedos = bre4.idedos) as mld_dte, ")
strSQL.Append("to_char(brepri.datdep, 'MM/DD/YYYY') as cnv_dte ")
strSQL.Append("from brepri, tabbre, tabdos, tabpay, tabtyp, tabing, tabage, tabeve, tabtec ")
strSQL.Append(" where tabdos.idedos = tabbre.idedos and tabpay.idepay = tabbre.idepay and ")
strSQL.Append("tabtyp.idetyp = tabbre.idetyp and tabbre.ideatt = tabing.ideing and ")
strSQL.Append("tabbre.ageloc = tabage.ideage(+) and ")
strSQL.Append("tabbre.idebre = brepri.idebre(+) and ")
strSQL.Append("tabeve.ideeve = tabbre.ideeve and ")
strSQL.Append("tabbre.idetec = tabtec.idetec and ")
strSQL.Append("(codpay != 'US' and codeve in ('ABD', 'FLD', 'FLG', 'SFA') and codtec != 'TIJ' and ")
strSQL.Append("codtyp not in ('DP', 'RD') ")
strSQL.Append("and brepri.idepri = (select min(idepri) from brepri where brepri.idebre = tabbre.idebre and brepri.datdep = (select min(datdep) from brepri where brepri.idebre = tabbre.idebre)) ") ' and ")

sqlGROUP = New StringBuilder("")
sqlGROUP.Append("ORDER BY codpay, codage, cnv_dte ")
Session("strSQL") = strSQL.ToString
Session("sqlGroup") = sqlGROUP.ToString
critSQL.Append("tabbre.idebre = (select distinct tabbre.idebre ")
critSQL.Append("from tabsui suiUS, tabeve eveUS, tabpay payUS, tabbre breUS ")
critSQL.Append("where suiUS.idedos = breUS.idedos and suiUS.typdos = 'BRE' and ")
critSQL.Append("eveUS.ideeve = suiUS.ideeve and eveUS.codeve = 'FLG' and ")
critSQL.Append("breUS.idepay = payUS.idepay and payUS.codpay = 'US' and ")
critSQL.Append("tabdos.idedos = breUS.idedos and suiUS.datrea between to_date('" & startDate.ToString("yyyyMMdd") & "','YYYYMMDD') and to_date('" & endDate.ToString("yyyyMMdd") & "','YYYYMMDD')) ")

Session("critSQL") = critSQL.ToString
myDataSet = getDataSource.GetDataSet(Session("strSQL") & Session("critSQL") & Session("sqlGROUP"), strConn, "tblFORFIL")
myDataSet2 = getDataSource.GetDataSet(Session("sqlNotCounted") & Session("critSQL") & Session("sqlGROUP"), strConn, "tblFORFIL2")
myDataSet.Merge(myDataSet2)
getDataSource.Disconnect()