I am trying to properly define the control parameters for @NumOfTransactions and I am getting an error "PropertyName must be set to a valid property name of the control named 'rptvwTransactionResearch' in Control Parameter 'NumOfTransactions'.
My aspx file
<table style="height: 400px; width: 100%;">
<tr>
<td style="vertical-align: top; text-align: left" >
<asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager>
<rsweb:ReportViewer ID="rptvwTransactionResearch" runat="server" Height="333px" Width="100%" Visible="False" SizeToReportContent="True">
<LocalReport ReportPath="Reports\rptTransactionResearch.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="SqlDataSource1" Name="TransactionResearch_ResidentResearch" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BANKMDFConnectionString %>"
SelectCommand="SELECT r.ResidentID, r.FirstName, r.LastName, rt.TransactionDate, CASE WHEN CharIndex(' From ', rt.TransactionDescription) > 0 THEN substring(rt.TransactionDescription, charindex(' From ', rt.TransactionDescription) + 6, (Len(rt.TransactionDescription) - charindex(' From ', rt.TransactionDescription) + 6)) ELSE ' ' END AS TransactionDescription,rt.Amount FROM Resident AS r LEFT OUTER JOIN ResidentTransactions AS rt ON r.FacilityID = rt.FacilityID AND r.ResidentID = rt.ResidentID WHERE (r.FacilityID = @FacilityID) AND ( @NumOfTransactions = (SELECT count(1) FROM ResidentTransactions WHERE (FacilityID = r.FacilityID) AND residentid = r.residentid)) AND (( (rt.TransactionDate = (SELECT MAX(TransactionDate) AS Expr1 FROM ResidentTransactions AS ResidentTransactions_1 WHERE (FacilityID = r.FacilityID) AND (ResidentID = r.ResidentID) AND (DepositWithdrawalCode = 'D'))) AND (rt.TransactionSeq = (SELECT MAX(TransactionSeq) AS Expr1 FROM ResidentTransactions AS ResidentTransactions_1 WHERE (FacilityID = r.FacilityID) AND (ResidentID = r.ResidentID) AND (TransactionDate = rt.TransactionDate) AND (DepositWithdrawalCode = 'D'))) ) OR Transactionseq is null) Order by ltrim(r.LastName),ltrim(r.FirstName) ">
<SelectParameters>
<asp:SessionParameter Name="FacilityID" SessionField="FacilityID" Type="Int32" />
<asp:ControlParameter Name="NumOfTransactions" ControlId="rptvwTransactionResearch" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Here is my aspx.cs code snippet
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
var numOfDays = Int32.Parse(txtParam.Text) * -1;
cmd.Parameters.AddWithValue("@FacilityID", Session["FacilityID"].ToString());
cmd.Parameters.AddWithValue("@NumOfDays", numOfDays);
cmd.Parameters.AddWithValue("@NumOfTransactions", txtParam.Text);
SqlDataAdapter adpReport = new SqlDataAdapter(cmd);
DataSet dsReport = new TransactionResearch();
dsReport = new DataSet("TransactionResearch_ResidentResearch");
var testing3 = dsReport.Tables;
adpReport.Fill(dsReport, "TransactionResearch_ResidentResearch");
rds.Name = "TransactionResearch_ResidentResearch";
rds.Value = dsReport.Tables;
rptvwTransactionResearch.LocalReport.DataSources.Add(rds);
rptvwTransactionResearch.LocalReport.ReportPath = "Reports/rptTransactionResearch.rdlc";
rptvwTransactionResearch.LocalReport.DataSources[0].DataSourceId = "SqlDataSource1";
SqlDataSource1.SelectParameters.Add("@NumOfTransactions", DbType.String, txtParam.Text);
SqlDataSource1.SelectCommand = "SELECT r.ResidentID, r.FirstName, r.LastName, rt.TransactionDate, CASE WHEN CharIndex(' From ', rt.TransactionDescription) > 0 THEN substring(rt.TransactionDescription, charindex(' From ', rt.TransactionDescription) + 6, (Len(rt.TransactionDescription) - charindex(' From ', rt.TransactionDescription) + 6)) ELSE ' ' END AS TransactionDescription,rt.Amount FROM Resident AS r LEFT OUTER JOIN ResidentTransactions AS rt ON r.FacilityID = rt.FacilityID AND r.ResidentID = rt.ResidentID WHERE (r.FacilityID = @FacilityID) AND ( @NumOfTransactions = (SELECT count(1) FROM ResidentTransactions WHERE (FacilityID = r.FacilityID) AND residentid = r.residentid)) AND (( (rt.TransactionDate = (SELECT MAX(TransactionDate) AS Expr1 FROM ResidentTransactions AS ResidentTransactions_1 WHERE (FacilityID = r.FacilityID) AND (ResidentID = r.ResidentID) AND (DepositWithdrawalCode = 'D'))) AND (rt.TransactionSeq = (SELECT MAX(TransactionSeq) AS Expr1 FROM ResidentTransactions AS ResidentTransactions_1 WHERE (FacilityID = r.FacilityID) AND (ResidentID = r.ResidentID) AND (TransactionDate = rt.TransactionDate) AND (DepositWithdrawalCode = 'D'))) ) OR Transactionseq is null) Order by ltrim(r.LastName),ltrim(r.FirstName)";
SqlDataSource1.DataBind();
}
The problem I'm facing is when I pass the query string in to SqlDataSource1 and set SelectParameters, it doesn't seem to register that parameter causing and error Must Declare Scarlar Variable '@NumOfTransactions . I also tried setting the controlID rptvwTransactionResearch$SqlDataSource1 and I get an error Could not find control 'rptvwTransactionResearch$SqlDataSource1' in ControlParameter 'NumOfTransactions'.
