TableRequestInfoV5 subselect param

Data Abstract
1

Hi,

I want to use a TableRequestInfoV5 with a WhereClause and DataParameters, but one of the parameters is used in a subselect:
SELECT
[art_ID],
Article.[DateCreated],
Article.[DateModified],
Article.[UserIDCreated],
Article.[UserIDModified],
[art_ArticleCode],
[art_CustomerReference],
[art_SupplArtReference],
[art_SupplColorReference],
[art_Description],
[art_MainGroupID],
[art_SubGroupID],
[art_GroupID],
ISNULL(Qty, 0) as StockQty
FROM
Article
LEFT JOIN
(
SELECT stk_ArticleID, COUNT(*) AS Qty
FROM Stock
WHERE (stk_ShopID = :ShopID)
GROUP BY stk_ArticleID
)stk ON art_ID = stk_ArticleID
WHERE
{WHERE}
order by art_ArticleCode desc

BUT instead of replacing the {WHERE} with my WhereExpression, it gets replaced by (1=1):
exec sp_executesql N’SELECT
[art_ID],
Article.[DateCreated],
Article.[DateModified],
Article.[UserIDCreated],
Article.[UserIDModified],
[art_ArticleCode],
[art_CustomerReference],
[art_SupplArtReference],
[art_SupplColorReference],
[art_Description],
[art_MainGroupID],
[art_SubGroupID],
[art_GroupID]
ISNULL(Qty, 0) as StockQty
FROM
Article
LEFT JOIN
(
SELECT stk_ArticleID, COUNT(*) AS Qty
FROM Stock
WHERE (stk_ShopID = @ShopID)
GROUP BY stk_ArticleID
)stk ON art_ID = stk_ArticleID
WHERE
(1=1)
order by art_ArticleCode desc’,N’@ShopID int’,@ShopID=1

This is the C# code:
WhereExpression _expr = new BinaryExpression(new FieldExpression(“art_ArticleCode”), artCode, BinaryOperator.Equal);`
TableRequestInfoV5 reqInfo = new TableRequestInfoV5();
reqInfo.WhereClause = _expr.ToXmlNode();
reqInfo.Parameters = new DataParameter[] { new DataParameter(“ShopID”, shopID) };
Instance.remoteDataAdapter.Fill(_ds, new string[] { _ds.Article.TableName }, new TableRequestInfo[] { reqInfo });

Isn’t this possible?

Thx

2

Hello

I’ve just created a Schema table with statement like

SELECT
    O.Id,
    OrderStatus,
    C.Name
FROM
    Orders O
    LEFT JOIN
    (
    SELECT
        Id,
     	Name
    FROM
        Customers
    WHERE
    	Id = :CustomerId
    ) C ON C.Id = O.CustomerId
WHERE
	{WHERE}

and accessed it using code like

WhereExpression expr = new BinaryExpression(new FieldExpression("OrderStatus"), 2, BinaryOperator.Equal);

TableRequestInfoV5 request = new TableRequestInfoV5();
request.WhereClause = expr.ToXmlNode();
request.Parameters = new DataParameter[] { new DataParameter("CustomerId", 100) };
var ds = new WinFormsApplication7Dataset();

fDataModule.DataAdapter.Fill(ds, new string[] { "TestTable" }, new TableRequestInfo[] { request });

The DB query was

SELECT
    O.Id,
    OrderStatus,
    C.Name
FROM
    Orders O
    LEFT JOIN
    (
    SELECT
        Id,
     	Name
    FROM
        Customers
    WHERE
    	Id = @CustomerId
    ) C ON C.Id = O.CustomerId
WHERE
	("OrderStatus" = @DAPARAM_1)

I.e. both parameter and WHERE conditions were processed correctly.

So I’ll have to ask you for a testcase (pls send it to support@ ).
Also please specify exact Data Abstract veriosn you’re using.

Thanks in advance

3

Hey,

We use a Schema Command to fill the DataSet. Maybe that’s the difference with your example, because we can’t get it to work.
We captured the resulted SELECT in SQL Profiler (You can see the (1=1) at the end, but we definitely use a WhereExpression and a TableRequestInfoV5 with a WhereClause and Parameters):

exec sp_executesql N’SELECT
[art_ID],
Article.[DateCreated],
Article.[DateModified],
Article.[UserIDCreated],
Article.[UserIDModified],
[art_ArticleCode],
[art_CustomerReference],
[art_SupplArtReference],
[art_SupplColorReference],
[art_Description],
[art_MainGroupID],
[art_SubGroupID],
[art_GroupID],
[art_Param1], [art_Param2], [art_Param3], [art_Param4], [art_Param5],
[art_Param6], [art_Param7], [art_Param8], [art_Param9], [art_Param10],
[art_Param11], [art_Param12], [art_Param13], [art_Param14], [art_Param15],
[art_Param16], [art_Param17], [art_Param18], [art_Param19], [art_Param20],
[art_Param21], [art_Param22], [art_Param23], [art_Param24], [art_Param25],
[art_ColorID], [art_SubColorID], [art_SupplierID],
[art_BrandID], [art_SizeBarID], [art_SeasonID],
[art_VATID], [art_LabelType], [art_LabelDescr],
[art_StartSize], [art_StopSize], [art_InventoryTableID],
[art_StockLocationID], [art_LastCommandDate], [art_LastDeliveryDate],
[art_PublishToWeb], [art_CanReorder], [art_PictureName], [art_AnimationName],
[art_ModelDescription],
[art_Comment],
IsNull(vat_Perc,0) as VATPercent,
art_IsActive,
art_AccountingID,
art_Theme1ID,
art_Theme2ID,
art_Design,
Article.WebTrans,
Article.WebDate,
art_IsRent,
art_RentDay,
art_EverPublishedToWeb,
art_PurchasePriceNet,
art_SalePrice1,
art_SalePrice2,
art_SalePrice3,
art_SalePrice4,
art_LocationShop,
art_LocationWareHouse,
art_SupplRef1,
art_SupplRef2,
art_SupplRef3,
art_SupplRef4,
art_SupplRef5,
art_ReductionActive,
art_CostumePrice,
art_WebModel1,
art_WebModel2,
art_WebModel3,
art_WebSubGroup1,
art_WebSubGroup2,
art_WebSubGroup3,
art_WebSite,
art_CanReorderWeb,
art_WebSubGroupType1,
art_WebSubGroupType2,
art_WebSubGroupType3,
– MainGroup
IsNull(MainGroup.arg_Description1,’’’’) as MainGroupDescr1,
IsNull(MainGroup.arg_Description2,’’’’) as MainGroupDescr2,
IsNull(MainGroup.arg_Description3,’’’’) as MainGroupDescr3,
IsNull(MainGroup.arg_Description4,’’’’) as MainGroupDescr4,

-- SubGroup
IsNull(SubGroup.arg_Description1,'''') as SubGroupDescr1,
IsNull(SubGroup.arg_Description2,'''') as SubGroupDescr2,
IsNull(SubGroup.arg_Description3,'''') as SubGroupDescr3,
IsNull(SubGroup.arg_Description4,'''') as SubGroupDescr4,

-- Group
IsNull(ArtGroup.arg_Description1,'''') as GroupDescr1,
IsNull(ArtGroup.arg_Description2,'''') as GroupDescr2,
IsNull(ArtGroup.arg_Description3,'''') as GroupDescr3,
IsNull(ArtGroup.arg_Description4,'''') as GroupDescr4,

-- MainColor
IsNull(MainColor.col_Description1,'''') as ColorDescr1,
IsNull(MainColor.col_Description2,'''') as ColorDescr2,
IsNull(MainColor.col_Description3,'''') as ColorDescr3,
IsNull(MainColor.col_Description4,'''') as ColorDescr4,

-- SubColor
IsNull(SubColor.col_Description1,'''') as SubColorDescr1,
IsNull(SubColor.col_Description2,'''') as SubColorDescr2,
IsNull(SubColor.col_Description3,'''') as SubColorDescr3,
IsNull(SubColor.col_Description4,'''') as SubColorDescr4,

-- Supplier
IsNull(sup_Name,'''') as SupplierName,

-- Brand
IsNull(bra_BrandName,'''') as BrandName,

-- SizeBeamHeader
IsNull(sbh_Description,'''') as SizeBeamHeader,

-- Season
IsNull(sea_Description,'''') as Season,

-- theme 1
IsNull(theme1.tmh_Description1,'''') as Theme1Descr1,
IsNull(theme1.tmh_Description2,'''') as Theme1Descr2,
IsNull(theme1.tmh_Description3,'''') as Theme1Descr3,
IsNull(theme1.tmh_Description4,'''') as Theme1Descr4,
-- theme 2
IsNull(theme2.tmh_Description1,'''') as Theme2Descr1,
IsNull(theme2.tmh_Description2,'''') as Theme2Descr2,
IsNull(theme2.tmh_Description3,'''') as Theme2Descr3,
IsNull(theme2.tmh_Description4,'''') as Theme2Descr4,
ISNULL(Qty, 0) as StockQty,
Isnull(SaleQty,0) as SaleQty,
Isnull(lbl_Description,'''') as LabelDescription,
Isnull(pri_SalePrice,0) as SalePrice,
Isnull(pri_PurchasePrice,0) as PurchasePrice,
Isnull(pri_PurchasePriceOrg,0) as PurchasePriceOrig

FROM
Article
left join ArticleGroup MainGroup on MainGroup.arg_ID = art_MainGroupID
left join ArticleGroup SubGroup on SubGroup.arg_ID = art_SubGroupID
left join ArticleGroup ArtGroup on ArtGroup.arg_ID = art_GroupID
left join Color MainColor on MainColor.col_ID = art_ColorID
left join Color SubColor on SubColor.col_ID = art_SubColorID
Left join ArticlePrice on pri_ArticleID = art_ID and pri_Position = 1
left join Supplier on sup_ID = art_SupplierID
left join Brand on bra_ID = art_BrandID
left join SizeBeamHeader on sbh_ID = art_SizeBarID
left join Season on sea_ID = art_SeasonID
Left Join Labeltype on lbl_ID = art_LabelType
Left join VAT on vat_ID = art_VatID
Left join ArticleTheme theme1 on art_theme1ID = theme1.thm_ID
Left join ArticleTheme theme2 on art_theme2ID = theme2.thm_ID
LEFT JOIN
(
SELECT stk_ArticleID, COUNT(*) AS Qty
FROM Stock
WHERE ((@ShopID = 0 AND stk_ShopID > 0) OR (stk_ShopID = @ShopID))
GROUP BY stk_ArticleID
)stk ON art_ID = stk_ArticleID
LEFT JOIN
(
SELECT sld_ArticleID, COUNT(sld_SaleQty) AS SaleQty
FROM SalesDetail
GROUP BY sld_ArticleID
)sale ON art_ID = sld_ArticleID
WHERE
(1=1)
order by art_ArticleCode desc’,N’@ShopID int’,@ShopID=1

4

Hello

Please provide a testcase (preferable a complete client and server apps, not just code snippets). Without it it is not possible to tell what exactly causes this issue.

Regards