Issue when using ASP.Net web.config key as query parameter

Question

I found this question that seems to be related but didn't successfully resolve my issue: Defining ConnectionString in asp SqlDataSource

Most of the questions related to this are about the connection string. However, my issue is a little different.

In my test scenario, I'm creating a review website. I have two tables: reviews and sites. I'm creating a page that will allow the users to see their own reviews based on the id of the logged in user.

<asp:SqlDataSource ID="ReadReviewDataSource" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:Labor32_ECConnectionString %>" 
                   SelectCommand="<%="SELECT sites.name 
                   FROM sites 
                   INNER JOIN reviews ON sites.id = reviews.site_id 
                   WHERE reviews.usr_id =" +
                   ConfigurationManager.AppSettings["TestUserID"]%>">
</asp:SqlDataSource>

Whenever I try and test the site, I get and Parser Error Message: Server tags cannot contain <% ... %> constructs. I've found several results that talk about this issue in other contexts, but most of them are replacing the whole connection info or query with the web.config key value. In this case, I only want to replace my parameter with it.

When I followed some of the guidance found here: https://forums.asp.net/t/1471702.aspx?Using+web+config+settings+in+a+asp+SqlDataSource+ I got an error that said "The identifier that starts with....is too long. Maximum length is 128." I tried to follow the guidance on this page: https://www.sqlservercentral.com/Forums/208737/The-identifier-that-starts-withis-too-long-Maximum-length-is-128 but I'm not running a stored procedure (I suppose VS might be running my SQL statement through a SP on the back end, but I'm new and not familiar with how it works).

Can anyone provide any guidance?

You really should stay away from SqlDataSource. It's a terrible thing to use when architecting your application. Your UI code (ASPX) should be completely isolated from the data layer. Even your code behind (if you have one) should not directly communicate with the database. That sort of logic belongs in a data layer. — mason
– mason, Commented Aug 3, 2018 at 17:54
Can you provide clarification on what you mean? I'm rather new to ASP.NET and C#, and this project is solely for my own benefit and learning (not ever going to use it anywhere else at this point). How do I create a data source to populate a drop down menu on an ASPX page that pulls from SQL, if not through a SQLDataSource? — phroureo
– phroureo, Commented Aug 3, 2018 at 18:04
You would do that by writing actual C# code to query the database and return the data you need. That would go in your data layer. Then your code behind would call into the data layer to retrieve the data it needs. But let's back up even further. If this project is solely for your own learning, why are you learning ASP.NET Web Forms? It's dead/dying platform. If you're going to learn web development with C#, why are you starting with decades old technology that promotes a bad architecture? Why don't you look into ASP.NET Core? — mason
– mason, Commented Aug 3, 2018 at 18:14
Well, I've been asked to assist at work with maintenance of a legacy ASP.NET web forms application. And I figured the best way to learn it is to build with it. :P — phroureo
– phroureo, Commented Aug 3, 2018 at 18:38
Well in that case, I feel for you. I too have to maintain a legacy Web Forms app, and it still has a few SqlDataSource's left in it. When we come across them, we refactor the code to remove them. — mason
– mason, Commented Aug 3, 2018 at 18:43

Mohsin Mehmood · Accepted Answer · 2018-08-04 04:42:31Z

1

Here is what you can try: Add SelectParameter and also OnSelecting event handler. Set UserId parameter in code behind in OnSelecting event handler

<asp:SqlDataSource ID="ReadReviewDataSource" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:Labor32_ECConnectionString %>" 
                   SelectCommand="SELECT sites.name 
                   FROM sites 
                   INNER JOIN reviews ON sites.id = reviews.site_id 
                   WHERE reviews.usr_id = @UserId"
                   OnSelecting="ReadReviewDataSource_Selecting">
          <SelectParameters>  
              <asp:Parameter Name="UserId" />
          </SelectParameters>
</asp:SqlDataSource>

Code Behind:

  protected void ReadReviewDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    
    {    
         e.Command.Parameters["@UserId"].Value =  ConfigurationManager.AppSettings["TestUserID"];
    }

edited Aug 4, 2018 at 4:42

answered Aug 3, 2018 at 19:07

Mohsin Mehmood

4,2662 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

phroureo Over a year ago

Well, I'm at least getting a different error now!

System.IndexOutOfRangeException: 'An SqlParameter with ParameterName 'UserId' is not contained by this SqlParameterCollection.'

Mohsin Mehmood Over a year ago

Put a breakpoint inside OnSelecting event handler and check if e.Command.Parameters contains any parameter. I am assuming that you have added the <SelectParameters> section inside SqlDatasource element

phroureo Over a year ago

Yes, I copied and pasted your code. I also tried what this question: stackoverflow.com/questions/11275807/… said, but no dice. I'll try with breakpoints.

phroureo Over a year ago

I figured it out! Your "Code Behind" section should say e.Command.Parameters["@UserId"].Value. You're missing the @. Thanks for the help!~

Collectives™ on Stack Overflow

Issue when using ASP.Net web.config key as query parameter

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related