SqlDataSource - get data with a simple query based on session

Question

I have a dropdown in my application, which should be filled with values which are based on a session variable.

<asp:SqlDataSource
      id="SqlDataSource3"
      runat="server"
      ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
      ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
      SelectCommand="SELECT ID_SHOP
                     FROM SHOP
                     WHERE ID_SHOP_CITY = ?">
      <SelectParameters>
          <asp:SessionParameter
            Name="selectedCityId"
            SessionField="selectedCityId"
            DefaultValue="5" />
      </SelectParameters>
  </asp:SqlDataSource>

based on this example: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter.aspx

I get syntax error - invalid character.

If I change my code:

<asp:SqlDataSource
      id="SqlDataSource3"
      runat="server"
      ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
      ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
      SelectCommand="SELECT ID_SHOP
                     FROM SHOP
                     WHERE ID_SHOP_CITY = 1">
      <SelectParameters>
          <asp:SessionParameter
            Name="selectedCityId"
            SessionField="selectedCityId"
            DefaultValue="5" />
      </SelectParameters>
  </asp:SqlDataSource>

and it works. However I don't need that where clause, I need to use the session variable. How can I fix it?

I think all you need to do here is change the = 1 to = @selectedCityId — crthompson
– crthompson, Commented Jan 11, 2014 at 19:11
The other thing the example in the link does is end the sql statement with a ; — crthompson
– crthompson, Commented Jan 11, 2014 at 19:13

afzalulh · Accepted Answer · 2014-01-12 06:07:27Z

2

The example you are pointing is using Odbc Provider, which uses ? for parameter. But you are using SqlClient provider, which expects @ParamName for parameter. You can check the differences here: Using Parameters with the SqlDataSource Control.

You have to change your markup to this:

<asp:SqlDataSource
      id="SqlDataSource3"
      runat="server"
      ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
      ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
      SelectCommand="SELECT ID_SHOP
                     FROM SHOP
                     WHERE ID_SHOP_CITY = @selectedCityId">
    <SelectParameters>
        <asp:SessionParameter
            Name="selectedCityId"
            SessionField="selectedCityId"
            DefaultValue="5" />
    </SelectParameters>
</asp:SqlDataSource>

answered Jan 12, 2014 at 6:07

afzalulh

7,9432 gold badges28 silver badges37 bronze badges

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

1 Comment

jlnorsworthy Over a year ago

+1 for pointing out the difference in specifying parameters with Odbc provider vs SqlClient provider

Collectives™ on Stack Overflow

SqlDataSource - get data with a simple query based on session

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related