-1
SqlDataAdapter adpReport;
      
DataSet dsReport;
        
ReportDataSource rds = new ReportDataSource();

strSQL = "SELECT ********* WHERE Facility.FacilityId = @FacilityID and DirectDepositRejections.TransactionDate >= @txtFromDate And DirectDepositRejections.TransactionDate <= @txtToDate";

using (SqlConnection conn = new SqlConnection(DBConnection.connectionString()))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand(strSQL, conn))
    {
        cmd.Parameters.AddWithValue("@FacilityID", Session["facilityID"].ToString());
        cmd.Parameters.AddWithValue("@txtFromDate", txtFromDate.Text);
        cmd.Parameters.AddWithValue("@txtToDate", txtToDate.Text);

        adpReport = new SqlDataAdapter(cmd);

        dsReport = new DataSet("TransactionHistory_ResidentTransactions");
        adpReport.Fill(dsReport, "TransactionHistory_ResidentTransactions");

        rds.Name = "RejectionReport_RejectionDataSet";
        rds.Value = dsReport.Tables[0];
                
        rptvwRejectionTransactions.LocalReport.DataSources.Add(rds);
        rptvwRejectionTransactions.LocalReport.DataSources[0].DataSourceId = "SqlDataSource1";
SqlDataSource1.InsertParameters.Add("@r.FacilityID", System.Data.DbType.String, Session["FacilityID"].ToString());

        SqlDataSource1.SelectCommand = strSQL;
        SqlDataSource1.DataBind();
    }

    ReportParameter[] params1 = new ReportParameter[1];
    params1[0] = new ReportParameter("FacilityID", Session["FacilityID"].ToString(), false);
    rptvwRejectionTransactions.LocalReport.SetParameters(params1);

    rptvwRejectionTransactions.LocalReport.Refresh();

When I tried to add SelectParameters to my SqlDataSource, I get an error:

Must declare the scalar variable "@FacilityID"

I've manually input the Select Parameters in the aspx page and it works fine with the other 2 values that I've parameterized. I want to try doing it within my code. Do anyone have any insights?

This is my aspx page for my SqlDataSource1. This works fine, but I can't do it the other way.

<SelectParameters>
    <%--<asp:SessionParameter Name="FacilityID" SessionField="FacilityID" Type="Int32" />--%>
    <asp:ControlParameter Name="txtFromDate" ControlID="txtFromDate" Type="String"/>
    <asp:ControlParameter Name="txtToDate" ControlID="txtToDate" Type="String"/>
</SelectParameters>
brianw921
  • 1
  • 2
  • Its seems like you do a nice job of creating a parameterized query, and then later on you just ignore all that and use a raw string without the proper parameterization; `SqlDataSource1.SelectCommand = strSQL;` so that just won't work. – topsail Dec 11 '22 at 02:42
  • Is FaciltityId in database a string or number? If it is a string your strSQL must have single quotes around the parameter : Facility.FacilityId = '@FacilityID' – jdweng Dec 11 '22 at 03:00
  • 2
    @jdweng What? You don't put quotes around an sql parameter. – LarsTech Dec 11 '22 at 03:21
  • SqlDataSource1.SelectCommand = strSQL. The strSQL string that I set to SqlDataSource1.SelectCommand is a parameterized string. I thought the line above it would parametrize it. How would you suggest I do it? – brianw921 Dec 11 '22 at 03:26
  • When I debug it, I can see the dataset. under "dsReport", but when I try to render it on the page, that's when I get the error that I must declare the Scalar Variable. – brianw921 Dec 11 '22 at 03:36
  • By using AddWithValue with strings, the type of those parameters is string (varchar) while they should probably be int and date. This may lead to a full table scan converting those columns to varchar. Please specify their types – Hans Kesting Dec 11 '22 at 11:19
  • @LarsTech : The strSQL gets run in the SQL Server and must be consistent with the server requirements. The parameter are used so the driver properly converts the variable to the correct type. If you do not use a parameter than the driver guesses the type and sometimes guesses wrong. You have to understand the different layers of the of Networking. – jdweng Dec 11 '22 at 11:23
  • @hansKesting When I run the SQL command in SQL server it seems to work fine. I'm able to call my query from the database. I can see a my results when I debug it, and I can step through the whole function without an error. The error comes when I render it on the page. – brianw921 Dec 11 '22 at 15:32
  • First you execute the query by filling `dsReport`, then you are databinding `SqlDataSource1` with a fresh copy of the query-string - which executes the query a second time, this time without parameters (SqlDatasource1 doesn't know about cmd). Can you databind against `dsReport.Tables[0]` instead? – Hans Kesting Dec 11 '22 at 15:45
  • @jdweng Maybe we're talking about different things, but you don't put quotes around a parameter. If you are passing a string value, the driver will properly put quotes around it for you. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/719186), ignoring the `AddWithValue` issue. – LarsTech Dec 11 '22 at 15:46
  • @hanskesting. I have to add my dataset in to my ReportDataSource and then add that report to my LocalReport in order for it to render on the page. – brianw921 Dec 11 '22 at 19:08
  • @LarsTech : See following webpage. If you have a constant string it must have a single quote around the parameter : https://smallbusiness.chron.com/build-query-string-tsql-28188.html The parameter in c# is to ensure the driver interprets the variable properly, not the query string. – jdweng Dec 11 '22 at 19:35
  • 2
    @jdweng That's just bad advice. The whole point of parameters is so you don't have to do that. – LarsTech Dec 11 '22 at 19:47
  • @HansKesting I tried to parameterize it the 2nd time with “SelectParamters.add(@facilityId)”so that when it gets binded to sql data souce the second time, it would be parameterized. – brianw921 Dec 11 '22 at 23:00
  • @LarsTech : You are wrong. – jdweng Dec 12 '22 at 02:01
  • 1
    @jdweng No, he's not. You do not enclose parameters with single quotes -- not even string/varchar parameters -- because the database isn't doing a simple replacement on the parameter placeholder. If you've been putting single quotes around your string parameters, you probably haven't been using real parameterized queries and may have insecure code out there. – Joel Coehoorn Dec 12 '22 at 04:41
  • 1
    @jdweng The code in that link uses values from parameters to build a dynamic SQL query in a way that is vulnerable to SQL-injection (try inserting a lastname `O'Brien`). This is *not* a proper parameterized query – Hans Kesting Dec 12 '22 at 08:02
  • @LarsTech; @JoelCoehoorn; @HansKesting We are dealing with different layers of the interface and you do not mix requirements between layers. It is the blunder that Microsoft did when developing the Net Library and had to rewrite the entire library when developing CORE so the library would work with both Linux, Mac, and Windows. – jdweng Dec 12 '22 at 09:50
  • @LarsTech I think the problem is when Insert the Parameters into the SQLDatasource. It doesn't seem to pick up on it once I bind the data. I've also tried ```SqlDatasource.SelectParameters.add("@facilityID,....)``` – brianw921 Dec 12 '22 at 12:35
  • @jdweng Parmeterized queries and ADO.Net do not use the `execute(...)` method. They use `sp_executesql(...)`, which sends the parameter data separately from the command string and NEVER use single quotes around parameter names. I have a Microsoft MVP award in this area, and I've met the developers of the platform. I know of what I speak. There may be other libraries out there that do this incorrectly (EF is not one of them), but the code in the question is raw ADO.Net and web forms. – Joel Coehoorn Dec 12 '22 at 18:36
  • @JoelCoehoorn : That really confusing that you use one connection string for EF and other when you do the query in SSMS. If single quotes work for EF I would always use the single so you do not confuse the world. – jdweng Dec 12 '22 at 18:47
  • @jdweng But the single quotes only **SEEM** to work for EF. In reality, you are leaving yourself open to sql injection. – Joel Coehoorn Dec 12 '22 at 18:49
  • @jdweng Here's an example direct from the MS documentation, no quotes: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand – Joel Coehoorn Dec 12 '22 at 18:51
  • @JoelCoehoorn : Just because it works doesn't mean it is right. This is a perfect example of mingling the low level interface with the application layer. When you learn network layer you are always taught not to mingle layers. It is the big differences between the Software Scientists at Bell Lab who developed UNIX and the junior programmers at Microsoft the did everything wrong. – jdweng Dec 12 '22 at 20:08

1 Answers1

0

The <%-- bee sting means the FacilityID parameter is commented out. It is not declared when used with the data source.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • I commented it out because I want to try and set the SelectParameters within my code. I also have a line ```rptvwTransactionResearch.LocalReport.DataSources.Clear();``` above where if I set the SelectParameters in my aspx page, it will give me an error ```Cannot create dataset for XXXXX" – brianw921 Dec 12 '22 at 05:13
  • `I want to try and set the SelectParameters within my code`. You can set parameter values from code, but you still need to have a placeholder declared in the markup and the in the question is using a completely different SqlCommand object from the datasource. – Joel Coehoorn Dec 12 '22 at 14:43
  • Can you provide some examples on syntax so I can get a clearer picture? I've tried to declare it in the placeholder using Control Parameters. Nothing is sticking. – brianw921 Dec 12 '22 at 16:15