Hi I need a little help getting a substring to work. I am lost on the db call and the parts of the syntax. I want to nest a grid of the sizes purchased in this order per item type but to do that I am going to need to include just part of the SKU. All SKU data has SKUCode"-"SizeCode. The "PreSku" column should be everthing to the left of the "-"

I am getting an error "Invalid length parameter passed to the SUBSTRING function". I am assuming the charindex is not working?

ResultsetFields itemFields = new ResultsetFields( ; itemFields.DefineField(ProductFields.Sku, 0, "PreSku"); itemFields.DefineField(ProductFields.Name, 1, "ProductName"); itemFields.DefineField(ProductSeriesFields.Name, 2, "SeriesName"); itemFields.DefineField(ColorFields.Name, 3, "ColorName"); itemFields.DefineField(CustomerSalesOrderItemFields.ProductId, 4, "TTLQTY", AggregateFunction.Count); itemFields.DefineField(ProductFields.Msrp, 5, "MSRP"); itemFields.DefineField(CustomerSalesOrderItemFields.AmountCharged, 6, "Price"); itemFields.DefineField(CustomerSalesOrderItemFields.AmountCharged, 7, "RowTotal", AggregateFunction.Sum);

          itemFields[0].ExpressionToApply = new DbFunctionCall("substring({0},0,(charindex('-',{0}) - 1))", new object[] {"PreSku"});           

            IPredicateExpression itemFilter = new PredicateExpression();
            itemFilter.Add(CustomerSalesOrderItemFields.CustomerSalesOrderPartId == _orderPartId);

            IRelationCollection itemRelations = new RelationCollection();
            itemRelations.Add(CustomerSalesOrderItemEntity.Relations.CustomerSalesOrderPartEntityUsingCustomerSalesOrderPartId);
            itemRelations.Add(CustomerSalesOrderItemEntity.Relations.ProductEntityUsingProductId);
            itemRelations.Add(ProductEntity.Relations.ColorEntityUsingColorId);
            itemRelations.Add(ProductEntity.Relations.ProductSeriesEntityUsingProductSeriesId);


            IGroupByCollection itemGroupByClause = new GroupByCollection();
            itemGroupByClause.Add(itemFields[1]);
            itemGroupByClause.Add(itemFields[2]);
            itemGroupByClause.Add(itemFields[3]);
            itemGroupByClause.Add(itemFields[5]);
            itemGroupByClause.Add(itemFields[6]);


            DataTable dtOrderItems = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(itemFields, dtOrderItems, 0, null, itemFilter, itemRelations, true, itemGroupByClause, null, 0, 0);

            gvItems.DataSource = dtOrderItems;
            gvItems.DataBind();

I found another example but this attempt is not quite working either

DbFunctionCall dashIndex = new DbFunctionCall("charindex('-',{0}) -1)", new object[] {itemFields[0]}); DbFunctionCall SkuSubStr = new DbFunctionCall("substring({0},0,{1})", new object[] {itemFields[0],dashIndex}); itemFields[0].ExpressionToApply = SkuSubStr;

I think I am getting better at this, I found the problem this code below works I was using my ResultFields column rather than the ProductEntityFields call as the variable.

DbFunctionCall SkuSubStr = new DbFunctionCall("substring({0},0,(charindex('-',{0}) -1))", new object[] { ProductFields.Sku }); itemFields[0].ExpressionToApply = SkuSubStr;