I am trying build a report using SRS. I am creating a DataTable using a SQL statement. The SQL after the “if” works but the SQL after the “else” returns no rows. Here is the code in my DataMethod:
public partial class WFSPackingLists
{
[AxSessionPermission(SecurityAction.Assert)]
[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static DataTable GetPackingLists(string PackingListsDS_SalesOrder)
{
DataSet ds;
if (PackingListsDS_SalesOrder == “*”)
{
ds = SqlHelper.ExecuteDataset(“Server=wfssqlsrv01;Database=AX2009_Carib_Dev;Integrated Security=SSPI”,
CommandType.Text, @“SELECT TransRefID, InvoiceID, PackingSlipID, COUNT(1) OVER (PARTITION BY TransRefID) as ‘Item Count’, StatusIssue, DatePhysical, Qty, COSTAMOUNTPOSTED, DateFinancial, DataareaID
FROM inventtrans WHERE transtype = 0 AND Transrefid > ‘SC00000000’
AND packingslipID > ‘PS000000’ AND StatusIssue IN (1, 2)
ORDER BY TransrefID”);
}
else
{
ds = SqlHelper.ExecuteDataset(“Server=wfssqlsrv01;Database=AX2009_Carib_Dev;Integrated Security=SSPI”,
CommandType.Text, @“SELECT TransRefID, InvoiceID, PackingSlipID, COUNT(1) OVER (PARTITION BY TransRefID) as ‘Item Count’, StatusIssue, DatePhysical, Qty, COSTAMOUNTPOSTED, DateFinancial, DataareaID
FROM inventtrans WHERE transtype = 0 AND Transrefid > ‘SC00000000’
AND packingslipID > ‘PS000000’ AND StatusIssue IN (1, 2)
AND TransrefID = ’ + PackingListsDS_SalesOrder + ’
ORDER BY TransrefID”);
}
return ds.Tables[0];
}
}
The first SQL statement works if I use “*” as my parameter value. But the SQL statement after the “else” never returns anything. I am entering a valid TransrefID. Not sure if it is a problem with my single quotes or the parameter. Can anyone help?
Thanks,
Bob