使用左连接表时出错msg绑定到gridview [英] Having error msg while using left join table bind to gridview

问题描述

我在使用Left Join将数据绑定到gridview时遇到问题，也许我错过了一些问题。下面是我的编码和错误信息，请帮我看一下，谢谢



两个桌面详情

桌会员名单

<前lang =text> Member_no | Member_id | Member_status
1 |李|正常
2 |管理员| admin
3 |晒黑|正常

表GainPointList

 gpmember_id | gp_date_start | gp_date_end 
 lee | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 
 lee | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 
 lee | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 
 lee | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 
 tan | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 
 tan | 2013年4月20日下午9:18:17 | 4/20/201 9:18:17 PM 

编码部分

 SqlCommand Cmd =  new  SqlCommand（ 选择（ML .member_no）作为vmemberno，（ML.member_id）作为vmemberid，COUNT（DISTINCT GPL.gpmember_id）作为psmark，Min（GPL.gp_date_start）作为psdatestart，Min（GPL.gp_date_end）作为psdateend，（ML.member_status）作为vmemberstatus来自（memberlist ML LEFT JOIN gainpointlist GPL ON ML.member_id = GPL.gpmember_id）GROUP BY member_id，condatabase）; 
 
 DataTable Table =  new  DataTable（）; 
 
 SqlDataAdapter Adapter =  new  SqlDataAdapter（Cmd）; 
 
 Adapter.Fill（表）; 
 
 gvmemberinfo.DataSource = Table; 
 
 gvmemberinfo.DataBind（）; 
 

ERROR MSG

列''memberlist.member_no' '在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。 < br /> 
描述：执行当前Web请求期间发生了未处理的异常。请查看堆栈跟踪以获取有关错误及其源自代码的位置的更多信息。 < br /> 
< br /> 
异常详细信息：System.Data.SqlClient.SqlException：列''memberlist.member_no''在选择列表中无效，因为它不包含在任何一个中聚合函数或GROUP BY子句。

源错误：
第89行： DataTable Table = new DataTable（）; 
第90行：SqlDataAdapter适配器=新的SqlDataAdapter（Cmd）; 
第91行：Adapter.Fill（表）; //错误消息msg 
第92行：

解决方案

消息告诉你到底出了什么问题：你没有引用你的SQL中正确的列。将正确的列添加到group by子句中。提示：任何不属于聚合函数的列都应该在group by子句中。

Mark Merrens兴奋地告诉你该怎么做，但我建议你阅读这些：

汇总函数 [ ^ ]

使用聚合函数 [ ^ ]

聚合函数（Transact-SQL） [ ^ ]

在选择列表中使用聚合函数 [ ^

I having a problem while using Left Join to bind data to gridview, maybe i got miss out somethig. below is my coding and error msg, please help me take a look, thanks

two table detail
Table Memberlist

Member_no  |  Member_id  |  Member_status
1      |      lee      |    normal
2      |      admin    |    admin
3      |      tan      |    normal

Table GainPointList

gpmember_id   |   gp_date_start        |   gp_date_end
lee       |       4/20/2013 9:18:17 PM  |   4/20/201 9:18:17 PM
lee       |       4/20/2013 9:18:17 PM  |  4/20/201 9:18:17 PM
lee       |       4/20/2013 9:18:17 PM  |  4/20/201 9:18:17 PM
lee       |       4/20/2013 9:18:17 PM  |  4/20/201 9:18:17 PM
tan       |       4/20/2013 9:18:17 PM  |  4/20/201 9:18:17 PM
tan       |       4/20/2013 9:18:17 PM  |  4/20/201 9:18:17 PM

coding part

SqlCommand Cmd = new SqlCommand("Select (ML.member_no) as vmemberno, (ML.member_id) as vmemberid, COUNT(DISTINCT GPL.gpmember_id) as psmark, Min(GPL.gp_date_start) as psdatestart, Min(GPL.gp_date_end) as psdateend, (ML.member_status) as vmemberstatus From (memberlist ML LEFT JOIN gainpointlist GPL ON ML.member_id = GPL.gpmember_id) GROUP BY member_id", condatabase);

DataTable Table = new DataTable();

SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);

Adapter.Fill(Table);

gvmemberinfo.DataSource = Table;

gvmemberinfo.DataBind();

ERROR MSG

Column ''memberlist.member_no'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. <br />
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. <br />
<br />
Exception Details: System.Data.SqlClient.SqlException: Column ''memberlist.member_no'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Source Error: 
Line 89:         	    DataTable Table = new DataTable();
Line 90: 	            SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);
Line 91: 	            Adapter.Fill(Table); // error msg
Line 92:

解决方案

The message is telling you exactly what is wrong: you have not referenced the columns correctly in your sql. Add the correct columns to the group by clause. Hint: any column that is not part of an aggregate function should be in the group by clause.

Mark Merrens told you excatly what to do, but i would suggest you to read these:
Aggregate Functions[^]
Use Aggregate Functions[^]
Aggregate Functions (Transact-SQL)[^]
Using Aggregate Functions in the Select List[^]

这篇关于使用左连接表时出错msg绑定到gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！