ASP.NET SqlDataSource lookup variable column name

Question

I have a database of items that are available, but the availability varies by company. There are about 10 companies, each with their own column and a bit value if it is available to them.

This is being done on a SharePoint site in Designer, and I don't have access to the code behind.

|  PhoneMake   |  PhoneModel   |  CompanyA   |  CompanyB   |
|--------------|---------------|-------------|-------------|
|    Apple     |   iPhone 5c   |   True      |   False     |
|    Apple     |   iphone 5s   |   True      |   True      |
|    Android   |   Note 3      |   False     |   False     |
|    Android   |   Note 4      |   False     |   True      |

On my webpage, I have a dropdown to select your company. I then want it to select the next level of filtering (ie. manufacturer)

<asp:DropDownList runat="server" id="Company" AutoPostBack="True" EnableViewState="False">
    <asp:ListItem></asp:ListItem>
    <asp:ListItem Value="CompanyA">Choose A Company</asp:ListItem>
    <asp:ListItem Value="CompanyB">Choose B Company</asp:ListItem>
</asp:DropDownList>

I need to take the selection of this dropdown and input it as a column to filter on in my SqlDataSource.

<asp:SqlDataSource id="SqlDataSource_PhoneMake" runat="server" __designer:commandsync="true" ProviderName="System.Data.SqlClient" ConnectionString="--yada yada--"
SelectCommand="SELECT DISTINCT [PhoneMake] FROM [table] WHERE (([PhoneMake] IS NOT NULL) AND ([@Company] = 'True'))">
    <SelectParameters>
        <asp:controlparameter ControlID="Company" PropertyName="SelectedValue" Name="Company" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

What is the problem you are facing with the attempt you made? — Giorgos Betsos
– Giorgos Betsos, Commented Jan 5, 2015 at 22:36
It does not work. When you make a selection an error gets thrown. — user3320324
– user3320324, Commented Jan 5, 2015 at 22:37
The reason is you can't use a parameter in order to define the column used in the query. Parameters can only be used to define column values in the predicates of the where clause. — Giorgos Betsos
– Giorgos Betsos, Commented Jan 5, 2015 at 22:45
That's what I was afraid of. Sounds like I need to re-do the Select statement somehow then — user3320324
– user3320324, Commented Jan 5, 2015 at 22:49

Giorgos Betsos · Accepted Answer · 2015-01-05 23:21:09Z

1

Use the following SelectCommand instead:

SELECT DISTINCT [PhoneMake]
FROM [table]
WHERE ([PhoneMake] IS NOT NULL) AND
      ([CompanyA] = (CASE @Company WHEN 'CompanyA' THEN 1 ELSE [CompanyA] END)) AND 
      ([CompanyB] = (CASE @Company WHEN 'CompanyB' THEN 1 ELSE [CompanyB] END))

To explain the above a little bit, if, e.g. @Company = 'CompanyA' then the 3rd predicate in the WHERE clause:

 ([CompanyB] = (CASE @Company WHEN 'CompanyB' THEN 1 ELSE [CompanyB] END))

becomes:

([CompanyB] = [CompanyB])

and is thus ignored. What is left is exactly the conditions required:

 ([PhoneMake] IS NOT NULL) AND ([CompanyA] = 1)

edited Jan 5, 2015 at 23:21

answered Jan 5, 2015 at 23:13

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

1 Comment

user3320324 Over a year ago

This does exactly what I wanted. Thank you!!

prospector · Accepted Answer · 2015-01-05 23:13:27Z

0

If someone else knows a better way, then please chime in.

But without access to the code behind I can only think of something like this:

 <% SqlDataSource_PhoneMake.SelectCommand = String.Format("SELECT DISTINCT [PhoneMake] FROM [table] WHERE (([PhoneMake] IS NOT NULL) AND ({0} = 'True'))", Company.SelectedValue); %>
<asp:SqlDataSource id="SqlDataSource_PhoneMake" runat="server" __designer:commandsync="true" ProviderName="System.Data.SqlClient" ConnectionString="--yada yada--"
SelectCommand="">
</asp:SqlDataSource>

I Think someone could technically change your dropdown values in Firebug and you'd be open to SQL Injection would be a possible concern.

So a SQL Query would be best with a parameter if possible.

answered Jan 5, 2015 at 23:13

prospector

3,4791 gold badge25 silver badges42 bronze badges

2 Comments

Giorgos Betsos Over a year ago

What about the query proposed by my answer?

prospector Over a year ago

@GiorgosBetsos if he can get it to work then you're solution is much better than mine. I'm just not sure if the DataSource will accept it without giving the same error he's already facing.

Collectives™ on Stack Overflow

ASP.NET SqlDataSource lookup variable column name

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related