为什么记录集返回记录条数为0呢,我用Sql*plus返回2000多条啊! 看的头都晕了!-:)如果sql*plus执行这条sql语句没错的话,可能是别的地方有问题;show your other code; 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 没有其他问题的,我把.net的语句给你看 我把上面的Sql写成了一个view, 名为hcb strSql = "select * from hcb"; cmd.CommandText = strSql; cmd.Connection = conn; da = new OracleDataAdapter(cmd); da.Fill(ds,"Project"); private void Query() { OracleConnection conn=new OracleConnection(connString); OracleCommand cmd=new OracleCommand(); OracleDataAdapter da=new OracleDataAdapter(); string strDepCode =(string) Session["DepCode"]; string strProjectCode = (string)Session["ProjectCode"]; string strSql ; string strTmp; string mainSql; int i; QDate = listYear.SelectedItem.Value.Trim() + "年" + listMonth.SelectedItem.Value.Trim() + "月"; //部门名称 strSql = "select nvl(a.DepDesc,' ') as DepDesc, nvl(b.ProjectDesc,' ') as ProjectDesc, nvl(b.GSZZ,0), nvl(Scale,' ') as Scale " + " from Department a, b_Project b " + " where b.ProjectCode = '" + listProject.SelectedItem.Value.Trim() + "' " + " and a.DepCode = b.DepCode "; cmd.CommandText = strSql; cmd.Connection = conn; da = new OracleDataAdapter(cmd); da.Fill(ds,"DepDesc"); if (ds.Tables["DepDesc"].Rows.Count>0) { QProjectDesc = (string)ds.Tables["DepDesc"].Rows[0].ItemArray[1]; QDepDesc = (string)ds.Tables["DepDesc"].Rows[0].ItemArray[0]; } else { QProjectDesc = " "; QDepDesc = " "; } try { mainSql = "select a.Parentcode, a.Itemcode, a.shownum, a.itemdesc, ' ' as Scale, " + " nvl(a.value_gs,0) + nvl(a.value_jz,0) + nvl(a.value_az,0) + nvl(a.value_sb,0) + nvl(a.value_Other,0) as gcztz, " + " d.FundPreFinish, b.investvalue as InvestYearPlan, c.InvestValue as YearMoney, d.FundYearFinish, " + " d.FundYearFinishJZ, d.FundYearFinishAZ, d.FundYearFinishSB, d.FUndYearFinishQT," + " e.newgdzc, e.newprosum, e.newpronum, e.newprocap, e.TCDate," + " f.yeararea, f.newarea, f.finisharea, f.finishhousearea, a.SortNo " + " from b_gaisuan a," + " (select u.itemcode, u.investvalue" + " from b_Fundyearplangs u " + " where u.projectcode = '" + strProjectCode + "' " + " and u.year = " + listYear.SelectedItem.Value.Trim() + ") b, " + " (select v.ItemCode, sum(nvl(v.Investvalue,0)) as InvestValue " + " from b_fundmonthgs v " + " where v.projectcode = '" + strProjectCode + "' " + " and v.year = " + listYear.SelectedItem.Value.Trim() + " " + " and v.month <= " + listMonth.SelectedItem.Value.Trim() + " group by v.ItemCode) c, " + " (select t.itemcode, sum(Decode(sign(t.Year-to_char(sysdate,'YYYY')),-1, nvl(t.fundfinishjz,0)+nvl(t.fundfinishaz,0)+nvl(t.fundfinishsb,0)+nvl(t.fundfinishqt,0),0)) as FundPreFinish," + " sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishjz,0)+nvl(t.fundfinishaz,0)+nvl(t.fundfinishsb,0)+nvl(t.fundfinishqt,0)),0)) as FundYearFinish," + " sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishjz,0)),0)) as FundYearFinishjz," + " sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishaz,0)),0)) as FundYearFinishaz," + " sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishsb,0)),0)) as FundYearFinishsb," + " sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishqt,0)),0)) as FundYearFinishqt " + " from b_investfinish t " + " where t.projectcode = '" + strProjectCode + "' " + " group by t.ItemCode ) d," + " (select w.itemcode, sum(nvl(w.newgdzc,0)) as newGDZC, sum(nvl(w.newprosum,0)) as newprosum, sum(nvl(w.newpronum,0)) as newpronum, sum(nvl(w.newprocap,0)) as newprocap, max(to_char(w.tcdate, 'YYYY-MM-DD')) as TCDate " + " from b_Newpro w " + " where w.projectcode = '" + strProjectCode + "' " + " and w.year = " + listYear.SelectedItem.Value.Trim() + " and w.Month <= " + listMonth.SelectedItem.Value.Trim() + " group by w.ItemCode) e," + " (select x.itemcode, sum(nvl(x.yeararea,0)) as yeararea, sum(nvl(x.newarea,0)) as newarea, sum(nvl(x.finisharea,0)) as finisharea, " + " sum(nvl(x.finishhousearea,0)) as finishhousearea " + " from b_Projectfinish x " + " where x.projectcode = '" + strProjectCode + "' " + " and x.year =" + listYear.SelectedItem.Value.Trim() + " and x.Month <= " + listMonth.SelectedItem.Value.Trim() + " group by x.ItemCode ) f " + " where a.Projectcode = '" + strProjectCode + "' " + " and a.itemcode = b.ItemCode(+) " + " and a.itemcode = c.ItemCode(+) " + " and a.Itemcode = d.ItemCode(+) " + " and a.itemcode = e.ItemCode(+) " + " and a.Itemcode = f.ItemCode(+) " + " order by a.parentcode, a.sortno "; strSql = "select ' ' as Parentcode, ' ' as Itemcode, ' ' as shownum, '合计' as itemdesc, ' ' as Scale, " + " sum(nvl(gcztz,0)) as gcztz, sum(nvl(FundPreFinish,0)) as FundPreFinish, sum(nvl(InvestYearPlan,0)) as InvestYearPlan, " + " sum(nvl(YearMoney,0)), sum(nvl(FundYearFinish,0)) as FundYearFinish, " + " sum(nvl(FundYearFinishJZ,0)) as FundYearFinishJZ, sum(nvl(FundYearFinishAZ,0)) as FundYearFinishAZ, " + " sum(nvl(FundYearFinishSB,0)) as FundYearFinishSB, sum(nvl(FUndYearFinishQT,0)) as FUndYearFinishQT," + " sum(nvl(newgdzc,0)) as newgdzc, sum(nvl(newprosum,0)) as newprosum, sum(nvl(newpronum,0)) as newpronum, " + " sum(nvl(newprocap,0)) as newprocap, max(TCDate) as TCDate," + " sum(nvl(yeararea,0)) as yeararea, sum(nvl(newarea,0)) as newarea, sum(nvl(finisharea,0)) as finisharea, " + " sum(nvl(finishhousearea,0)) as finishhousearea, ' ' as SortNo " + " from (" + mainSql + ") "; cmd.CommandText = strSql; da = new OracleDataAdapter(cmd); da.Fill(ds,"ProjectHead"); cmd.Dispose(); cmd.CommandText = strSql; cmd.Connection = conn; da.Dispose(); da = new OracleDataAdapter(cmd); da.Fill(ds,"Project"); } catch(Exception ex) { strTmp=ex.Message; strTmp = strTmp.Replace("\n",""); strTmp = strTmp.Replace("\r",""); strTmp = strTmp.Replace("\"", ""); Response.Write(Convert.ToChar(10)); Response.Write("<script language=\"vbscript\">"); Response.Write(Convert.ToChar(10)); Response.Write("window.alert(\"" + strTmp + "\")"); Response.Write(Convert.ToChar(10)); Response.Write("</script>"); Response.Write(Convert.ToChar(10)); return; } } cmd.CommandText = strSql;da = new OracleDataAdapter(cmd);da.Fill(ds,"ProjectHead");cmd.Dispose();cmd.CommandText = strSql;cmd.Connection = conn;da.Dispose();da = new OracleDataAdapter(cmd);da.Fill(ds,"Project");这一段在干吗???既然只有select语句,可以不用command,只用adapter就行了; strSql = "select * from hcb"; cmd.CommandText = strSql; cmd.Connection = conn; da = new OracleDataAdapter(cmd); da.Fill(ds,"Project");若不用OracleDataAdapter,怎么把数据放到DataSet里呢? strSql = "select * from hcb";da = new OracleDataAdapter(strsql,conn);da.Fill(ds,"Project");da.Fill(ds,"ProjectHead");DataGrid1.DataSource=ds.Tables["Project"];DataGrid1.DataBind();DataGrid2.DataSource=ds.Tables["ProjectHead"];DataGrid2.DataBind();这样会没有数据? 还是用程序解决计算问题 不要全用sql写 没有数据,真的,我换一个简单的SQL语句,同样的记录条数就返回结果 有可能发生这种情况,但是我认为可能有几种原因,一是数据库的内存不够用,二是.net的数据接口有问题(这种可能性比较大),你可以先把数据写到临时表里,再从临时表里查询 .net发送邮件问题 怎么实例 上传File Field 呢 高分求助,C#如何执行Java程序并获取返回值?? 求任意数组的交集 C#中,有什么技术可以将多线程应用在界面绘制上? 验证控件,要求textbox里的为数字并在0和2000之间 同时运行两个模块 我这样在dataGrid中嵌套一个日期下拉控件时会提示“未将对象引用设置到对象实例”? C#工厂模式的问题 关于vs中的水晶报表版权的问题 客户端脚本c#与java的比较? 為何出The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION錯?
我把上面的Sql写成了一个view, 名为hcb
strSql = "select * from hcb";
cmd.CommandText = strSql;
cmd.Connection = conn;
da = new OracleDataAdapter(cmd);
da.Fill(ds,"Project");
{
OracleConnection conn=new OracleConnection(connString);
OracleCommand cmd=new OracleCommand();
OracleDataAdapter da=new OracleDataAdapter();
string strDepCode =(string) Session["DepCode"];
string strProjectCode = (string)Session["ProjectCode"];
string strSql ;
string strTmp;
string mainSql;
int i;
QDate = listYear.SelectedItem.Value.Trim() + "年" + listMonth.SelectedItem.Value.Trim() + "月";
//部门名称
strSql = "select nvl(a.DepDesc,' ') as DepDesc, nvl(b.ProjectDesc,' ') as ProjectDesc, nvl(b.GSZZ,0), nvl(Scale,' ') as Scale " +
" from Department a, b_Project b " +
" where b.ProjectCode = '" + listProject.SelectedItem.Value.Trim() + "' " +
" and a.DepCode = b.DepCode ";
cmd.CommandText = strSql;
cmd.Connection = conn;
da = new OracleDataAdapter(cmd);
da.Fill(ds,"DepDesc");
if (ds.Tables["DepDesc"].Rows.Count>0)
{
QProjectDesc = (string)ds.Tables["DepDesc"].Rows[0].ItemArray[1];
QDepDesc = (string)ds.Tables["DepDesc"].Rows[0].ItemArray[0];
}
else
{
QProjectDesc = " ";
QDepDesc = " ";
}
try
{
mainSql = "select a.Parentcode, a.Itemcode, a.shownum, a.itemdesc, ' ' as Scale, " +
" nvl(a.value_gs,0) + nvl(a.value_jz,0) + nvl(a.value_az,0) + nvl(a.value_sb,0) + nvl(a.value_Other,0) as gcztz, " +
" d.FundPreFinish, b.investvalue as InvestYearPlan, c.InvestValue as YearMoney, d.FundYearFinish, " +
" d.FundYearFinishJZ, d.FundYearFinishAZ, d.FundYearFinishSB, d.FUndYearFinishQT," +
" e.newgdzc, e.newprosum, e.newpronum, e.newprocap, e.TCDate," +
" f.yeararea, f.newarea, f.finisharea, f.finishhousearea, a.SortNo " +
" from b_gaisuan a," +
" (select u.itemcode, u.investvalue" +
" from b_Fundyearplangs u " +
" where u.projectcode = '" + strProjectCode + "' " +
" and u.year = " + listYear.SelectedItem.Value.Trim() + ") b, " +
" (select v.ItemCode, sum(nvl(v.Investvalue,0)) as InvestValue " +
" from b_fundmonthgs v " +
" where v.projectcode = '" + strProjectCode + "' " +
" and v.year = " + listYear.SelectedItem.Value.Trim() + " " +
" and v.month <= " + listMonth.SelectedItem.Value.Trim() +
" group by v.ItemCode) c, " +
" (select t.itemcode, sum(Decode(sign(t.Year-to_char(sysdate,'YYYY')),-1, nvl(t.fundfinishjz,0)+nvl(t.fundfinishaz,0)+nvl(t.fundfinishsb,0)+nvl(t.fundfinishqt,0),0)) as FundPreFinish," +
" sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishjz,0)+nvl(t.fundfinishaz,0)+nvl(t.fundfinishsb,0)+nvl(t.fundfinishqt,0)),0)) as FundYearFinish," +
" sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishjz,0)),0)) as FundYearFinishjz," +
" sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishaz,0)),0)) as FundYearFinishaz," +
" sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishsb,0)),0)) as FundYearFinishsb," +
" sum(Decode(t.Year, " + listYear.SelectedItem.Value.Trim() + ", Decode(sign(t.Month-" + listMonth.SelectedItem.Value.Trim() + "), 1, 0, nvl(t.fundfinishqt,0)),0)) as FundYearFinishqt " +
" from b_investfinish t " +
" where t.projectcode = '" + strProjectCode + "' " +
" group by t.ItemCode ) d," +
" (select w.itemcode, sum(nvl(w.newgdzc,0)) as newGDZC, sum(nvl(w.newprosum,0)) as newprosum, sum(nvl(w.newpronum,0)) as newpronum, sum(nvl(w.newprocap,0)) as newprocap, max(to_char(w.tcdate, 'YYYY-MM-DD')) as TCDate " +
" from b_Newpro w " +
" where w.projectcode = '" + strProjectCode + "' " +
" and w.year = " + listYear.SelectedItem.Value.Trim() +
" and w.Month <= " + listMonth.SelectedItem.Value.Trim() +
" group by w.ItemCode) e," +
" (select x.itemcode, sum(nvl(x.yeararea,0)) as yeararea, sum(nvl(x.newarea,0)) as newarea, sum(nvl(x.finisharea,0)) as finisharea, " +
" sum(nvl(x.finishhousearea,0)) as finishhousearea " +
" from b_Projectfinish x " +
" where x.projectcode = '" + strProjectCode + "' " +
" and x.year =" + listYear.SelectedItem.Value.Trim() +
" and x.Month <= " + listMonth.SelectedItem.Value.Trim() +
" group by x.ItemCode ) f " +
" where a.Projectcode = '" + strProjectCode + "' " +
" and a.itemcode = b.ItemCode(+) " +
" and a.itemcode = c.ItemCode(+) " +
" and a.Itemcode = d.ItemCode(+) " +
" and a.itemcode = e.ItemCode(+) " +
" and a.Itemcode = f.ItemCode(+) " +
" order by a.parentcode, a.sortno ";
strSql = "select ' ' as Parentcode, ' ' as Itemcode, ' ' as shownum, '合计' as itemdesc, ' ' as Scale, " +
" sum(nvl(gcztz,0)) as gcztz, sum(nvl(FundPreFinish,0)) as FundPreFinish, sum(nvl(InvestYearPlan,0)) as InvestYearPlan, " +
" sum(nvl(YearMoney,0)), sum(nvl(FundYearFinish,0)) as FundYearFinish, " +
" sum(nvl(FundYearFinishJZ,0)) as FundYearFinishJZ, sum(nvl(FundYearFinishAZ,0)) as FundYearFinishAZ, " +
" sum(nvl(FundYearFinishSB,0)) as FundYearFinishSB, sum(nvl(FUndYearFinishQT,0)) as FUndYearFinishQT," +
" sum(nvl(newgdzc,0)) as newgdzc, sum(nvl(newprosum,0)) as newprosum, sum(nvl(newpronum,0)) as newpronum, " +
" sum(nvl(newprocap,0)) as newprocap, max(TCDate) as TCDate," +
" sum(nvl(yeararea,0)) as yeararea, sum(nvl(newarea,0)) as newarea, sum(nvl(finisharea,0)) as finisharea, " +
" sum(nvl(finishhousearea,0)) as finishhousearea, ' ' as SortNo " +
" from (" + mainSql + ") ";
cmd.CommandText = strSql;
da = new OracleDataAdapter(cmd);
da.Fill(ds,"ProjectHead"); cmd.Dispose();
cmd.CommandText = strSql;
cmd.Connection = conn;
da.Dispose();
da = new OracleDataAdapter(cmd);
da.Fill(ds,"Project");
}
catch(Exception ex)
{
strTmp=ex.Message;
strTmp = strTmp.Replace("\n","");
strTmp = strTmp.Replace("\r","");
strTmp = strTmp.Replace("\"", "");
Response.Write(Convert.ToChar(10));
Response.Write("<script language=\"vbscript\">");
Response.Write(Convert.ToChar(10));
Response.Write("window.alert(\"" + strTmp + "\")");
Response.Write(Convert.ToChar(10));
Response.Write("</script>");
Response.Write(Convert.ToChar(10));
return;
}
}
da = new OracleDataAdapter(cmd);
da.Fill(ds,"ProjectHead");
cmd.Dispose();
cmd.CommandText = strSql;
cmd.Connection = conn;
da.Dispose();
da = new OracleDataAdapter(cmd);
da.Fill(ds,"Project");
这一段在干吗???既然只有select语句,可以不用command,只用adapter就行了;
cmd.CommandText = strSql;
cmd.Connection = conn;
da = new OracleDataAdapter(cmd);
da.Fill(ds,"Project");
若不用OracleDataAdapter,
怎么把数据放到DataSet里呢?
da = new OracleDataAdapter(strsql,conn);
da.Fill(ds,"Project");
da.Fill(ds,"ProjectHead");
DataGrid1.DataSource=ds.Tables["Project"];
DataGrid1.DataBind();
DataGrid2.DataSource=ds.Tables["ProjectHead"];
DataGrid2.DataBind();
这样会没有数据?
我换一个简单的SQL语句,同样的记录条数就返回结果
但是我认为可能有几种原因,一是数据库的内存不够用,
二是.net的数据接口有问题(这种可能性比较大),
你可以先把数据写到临时表里,再从临时表里查询