2个关联表
商品清单(MerchBill)
merchID
profferID
fullName
供货商(ProfferBill)
profferID
address
联合查询的语句是string sql="select * from MerchBill INNER JOIN ProfferBill where(MerchID.profferID=ProfferBill.profferID)"
我的
CrystalReport1很简单 就是 2个表 然后 把merchID
profferID
fullName
merchID
profferID
fullName
2个表联合查询的字段全都放上去了
但是我查询不出结果,希望高手指点。窗体的LOAD 事件 是
string sql="select * from MerchBill INNER JOIN ProfferBill where(MerchID.profferID=ProfferBill.profferID)";
CrystalReport1 crp = new CrystalReport1();
DataSet ds = new DataSet();
string Conn = @"Data Source=ZLY;Initial Catalog=衡水市百货大楼管理系统;Integrated Security=True";
SqlConnection MyConn = new SqlConnection(Conn);
SqlCommand sqlCMD = new SqlCommand(sql, MyConn);
MyConn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCMD;
da.Fill(ds, "sql");
crp.Load(Application.StartupPath, "CrystalReport1.rpt");
crp.SetDataSource(ds.Tables["sql"]);
crystalReportViewer1.ReportSource = crp;
商品清单(MerchBill)
merchID
profferID
fullName
供货商(ProfferBill)
profferID
address
联合查询的语句是string sql="select * from MerchBill INNER JOIN ProfferBill where(MerchID.profferID=ProfferBill.profferID)"
我的
CrystalReport1很简单 就是 2个表 然后 把merchID
profferID
fullName
merchID
profferID
fullName
2个表联合查询的字段全都放上去了
但是我查询不出结果,希望高手指点。窗体的LOAD 事件 是
string sql="select * from MerchBill INNER JOIN ProfferBill where(MerchID.profferID=ProfferBill.profferID)";
CrystalReport1 crp = new CrystalReport1();
DataSet ds = new DataSet();
string Conn = @"Data Source=ZLY;Initial Catalog=衡水市百货大楼管理系统;Integrated Security=True";
SqlConnection MyConn = new SqlConnection(Conn);
SqlCommand sqlCMD = new SqlCommand(sql, MyConn);
MyConn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCMD;
da.Fill(ds, "sql");
crp.Load(Application.StartupPath, "CrystalReport1.rpt");
crp.SetDataSource(ds.Tables["sql"]);
crystalReportViewer1.ReportSource = crp;
2, 试试
select MerchBill.*,ProfferBill.*
from MerchBill INNER JOIN ProfferBill
where(MerchID.profferID=ProfferBill.profferID)
from MerchBill a
left JOIN ProfferBill b on (b.profferID=a.profferID)
protected void Page_Load(object sender, System.EventArgs e)
{
using (SqlConnection connection = Common.ConnectionManager.GetConnectionNET())
{
string sqlString = string.Format(@"SELECT C.CODE AS COMNO, C.NAME AS COMNAME, B_1.pluno, B_1.PLUNAME,B_1.SPEC, B_1.PQTY, B_1.CQTY, B_1.ZQTY
FROM L11H AS A LEFT OUTER JOIN
(SELECT A.CODE, B.CODE AS pluno, MAX(B.NAME) AS PLUNAME, MAX(B.SPEC) AS spec, SUM(A.PQTY) AS PQTY, SUM(A.CQTY) AS CQTY,
SUM(A.ZQTY) AS ZQTY
FROM L11D AS A INNER JOIN
B01M AS B ON A.PLUID = B.PLUID
WHERE (A.CODE IN ({0}))
GROUP BY A.CODE, B.CODE) AS B_1 ON A.CODE = B_1.CODE INNER JOIN
B04M AS C ON A.COMID = C.COMID
WHERE (A.CODE IN ({0}))", Session["codes"]);
SqlDataAdapter da = new SqlDataAdapter(sqlString, connection);
DataTable dt = new DataTable("命令");
da.Fill(dt); rd = new ReportDocument();
String path = string.Empty;
switch (Request.QueryString["type"])
{
case "1":
path = Server.MapPath("~/report/L11-4.rpt");
break;
case "2":
path = Server.MapPath("~/report/L11-6.rpt");
break;
case "3":
path = Server.MapPath("~/report/L11-8.rpt");
break;
}
rd.Load(path);
rd.SetDataSource(dt);
CrystalReportViewer1.ReportSource = rd;
} private void Page_Unload(object sender, EventArgs e)
{
if (rd != null)
{
rd.Dispose();
}
}
参照下我写的复杂查询,可以显示,然后你找下自己错在哪里吧:
哥哥,我内个例子特别简单,高手一眼就能点出来我哪里出了问题,别在给我看更庞大的代码,我现在就想知道我的问题。
哪怕就是说 我哪的知识点错了或者说对于水晶报表 理解的不深入那那出的问题我都会特别高兴地。
我当初是直接在水晶报表里添加SQL命令来产生临时数据设计报表的,所以水晶报表自动给我产生了一个叫做“命令”的表(英文版的肯定不是那个名字),然后我DataTable dt = new DataTable("命令");
da.Fill(dt);
这样的DataTable水晶报表才会认可
CrystalReport1 crp = new CrystalReport1();
DataSet ds = new DataSet();
string Conn = @"Data Source=ZLY;Initial Catalog=衡水市百货大楼管理系统;Integrated Security=True";
SqlConnection MyConn = new SqlConnection(Conn);
SqlCommand sqlCMD = new SqlCommand(sql, MyConn);
MyConn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCMD;
da.Fill(ds, "sql"); crp.Load(Application.StartupPath, "CrystalReport1.rpt");
crp.SetDataSource(ds.Tables["sql"]); 假设我不看你主贴的描述,只从这段代码反推水晶报表模板,模板应该是这样子的。你的dataset里只应该有一个表,表的名称是:sql,里面的字段就是
select * from MerchBill INNER JOIN ProfferBill where(MerchID.profferID=ProfferBill.profferID)
这个语句查询出来的字段。好,我们再来看你的主贴描述,看问题出在哪里。
看到问题了吗?模板设计和代码不符合。解决方法1:
改模板,按照我前面说的那种,改成一个sql表的
代码不变
实际上也要变一下,因为你的两个表里有同名字段,所以需要做一下别名。解决方法2:
小改模板,设置两个表的关联关系,用profferID关联
改代码
CrystalReport1 crp = new CrystalReport1();
DataSet ds = new DataSet();
string Conn = @"Data Source=ZLY;Initial Catalog=衡水市百货大楼管理系统;Integrated Security=True";
SqlConnection MyConn = new SqlConnection(Conn);
//取表1
SqlCommand sqlCMD = new SqlCommand("select * from MerchBill" , MyConn);
MyConn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCMD;
da.Fill(ds, "MerchBill");
//取表2
SqlCommand sqlCMD2 = new SqlCommand("select * from ProfferBill " , MyConn);
MyConn.Open();
SqlDataAdapter da2 = new SqlDataAdapter();
da2.SelectCommand = sqlCMD;
da2.Fill(ds, "ProfferBill");
crp.Load(Application.StartupPath, "CrystalReport1.rpt");
crp.SetDataSource(ds);
crystalReportViewer1.ReportSource = crp;
你把结果填充的一个table中
datatable table = new datatable();
da.Fill(table); if (table.rows.count >0)
{
messagebox.show("有记录");
}如果没有记录看 你的数据或查询语句如果有记录在看水晶报表