数据读取器不能使用“选择上限”和“按上限分组”
本文关键字:按上限分组 读取 选择上限 不能 选择 数据 | 更新日期: 2023-09-27 18:10:47
我试图在visual Studios 2015 Express中使用c#从Microsoft SQL数据库获取数据。到目前为止,我使用的所有命令都可以正常工作。现在我试着用SqlDataReader:
执行这个命令SELECT UPPER(Company) FROM MY_TABLE GROUP BY UPPER(Company) HAVING COUNT(*) > 0;
下面是我的代码:
//open the sql connection
sqlConn.Open();
/*
* This Sql command will get the field entries from all the selected fields and group them into selection groups.
*
* This is important because it gives a list of arguments to group docId's with.
*/
string selectCommand = "SELECT UPPER(Company) FROM MY_TABLE GROUP BY UPPER(Company) HAVING COUNT(*) > 0;";
// Declare list for storing select groups. This will be returned
List <String> groupList = new List<string>();
// Set up table reader
SqlCommand sqlcmd = new SqlCommand(selectCommand, sqlConn);
dataRead = sqlcmd.ExecuteReader();
for (int i = 0; i < dataRead.FieldCount; i++)
{
Console.WriteLine("DATAREAD: '" + dataRead.GetName(i) + "' .");
}
while (dataRead.Read())
{
// select string for selecting groups
string select = "";
foreach (string colname in staple.fieldsToList())
{
// check for null
if(dataRead[colname] == DBNull.Value)
{
select = select + "NULL,";
}else{
select = select + dataRead[colname].ToString()
}
}
//store the select string in a list
groupList.Add(select);
}
return groupList;
当这段代码到达dataRead[colname]部分时,它抛出一个错误:
System.IndexOutOfRangeException: Company
所以我检查了dataRead中的名称,发现Company所在的索引应该是一个空字符串。我用Company替换了我的selectCommand字符串中的UPPER(Company),它工作得很好,所以不知何故使用UPPER()命令使DataReader初始化而没有适当的名称。
有谁知道修复方法吗?
将查询改为:
SELECT UPPER(Company) As Company FROM MY_TABLE GROUP BY UPPER(Company) HAVING COUNT(*) > 0;
如果您应用一个sql函数,如UPPER
,而不应用别名,它返回(No column name)
的结果。
试着把名字放回去:
SELECT UPPER(Company) AS Company ...