public List<Message> ReceiveDataSummaryPaging(SrchMessage info, int pageIndex, int pageSize, out int count)
{
var sqlCommand = new StringBuilder(@"select OrderStatusName as Type ,UserData.Items.ItemCode ,ItemDesc as ItemDesc,sum(Quantity) as Quantity
from UserData.NonAggregatedDemand ,UserData.Items ,UserData.OrderStatus
where UserData.NonAggregatedDemand.ItemCode = UserData.Items.ItemsId and OrderStatusId = UserData.NonAggregatedDemand.Type");
var condition = new List<string>(); #region 类型
if (!"ALL".Equals(info.Type))
{
this.Database.AddInParameter("@Type", DbType.AnsiString, info.Type);
condition.Add(@" OrderStatusName=@Type");
}
#endregion #region 查询时间范围
if (!string.IsNullOrEmpty(info.StartDate.ToString()))
{
this.Database.AddInParameter("@startTime", DbType.AnsiString, info.StartDate.Value.Date);
condition.Add(@" OrderDate >= @startTime");
} if (!string.IsNullOrEmpty(info.EndDate.ToString()))
{
this.Database.AddInParameter("@endTime", DbType.AnsiString, info.EndDate.Value.Date);
condition.Add(@" OrderDate <= @endTime");
}
#endregion #region 通过产品号查询
if (!string.IsNullOrEmpty(info.ItemNo))
{
this.Database.AddInParameter("@ItemCd", DbType.AnsiString, info.ItemNo);
condition.Add(@" UserData.Items.ItemCode=@ItemCd");
}
#endregion #region "条件组装"
if (condition.Count > 0)
{
for (int i = 0; i < condition.Count; i++)
{
sqlCommand.Append(" AND ").Append(condition[i]);
}
condition.Clear();
}
sqlCommand.AppendLine(" group by UserData.OrderStatus.OrderStatusName,UserData.Items.ItemCode,UserData.Items.ItemDesc order by UserData.Items.ItemCode");
#endregion "条件组装" return this.ExecuteReaderPaging<Message>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
{
var sqlCommand = new StringBuilder(@"select OrderStatusName as Type ,UserData.Items.ItemCode ,ItemDesc as ItemDesc,sum(Quantity) as Quantity
from UserData.NonAggregatedDemand ,UserData.Items ,UserData.OrderStatus
where UserData.NonAggregatedDemand.ItemCode = UserData.Items.ItemsId and OrderStatusId = UserData.NonAggregatedDemand.Type");
var condition = new List<string>(); #region 类型
if (!"ALL".Equals(info.Type))
{
this.Database.AddInParameter("@Type", DbType.AnsiString, info.Type);
condition.Add(@" OrderStatusName=@Type");
}
#endregion #region 查询时间范围
if (!string.IsNullOrEmpty(info.StartDate.ToString()))
{
this.Database.AddInParameter("@startTime", DbType.AnsiString, info.StartDate.Value.Date);
condition.Add(@" OrderDate >= @startTime");
} if (!string.IsNullOrEmpty(info.EndDate.ToString()))
{
this.Database.AddInParameter("@endTime", DbType.AnsiString, info.EndDate.Value.Date);
condition.Add(@" OrderDate <= @endTime");
}
#endregion #region 通过产品号查询
if (!string.IsNullOrEmpty(info.ItemNo))
{
this.Database.AddInParameter("@ItemCd", DbType.AnsiString, info.ItemNo);
condition.Add(@" UserData.Items.ItemCode=@ItemCd");
}
#endregion #region "条件组装"
if (condition.Count > 0)
{
for (int i = 0; i < condition.Count; i++)
{
sqlCommand.Append(" AND ").Append(condition[i]);
}
condition.Clear();
}
sqlCommand.AppendLine(" group by UserData.OrderStatus.OrderStatusName,UserData.Items.ItemCode,UserData.Items.ItemDesc order by UserData.Items.ItemCode");
#endregion "条件组装" return this.ExecuteReaderPaging<Message>(sqlCommand.ToString(), pageIndex, pageSize, out count);
}
SQL语句也能执行,但老是说关键字SELECT附近有语法错误
sum(Quantity) as Quantity后面加个空格。
每个单词后面都加个空格
另Group by 最什么要在最后面加上?
怪了,语法错误还能run ?
肯定不是这种问题。
肯定不是这种问题。
我SQL里语句是没问题,不分大小写,但我是在VS里读取sql数据就会出错,改成大写就好了