jsteffler jsteffler - 7 months ago 12
SQL Question

How to reference a field value on a page in SQL Select statement

I know I can do this from code behind (I'm using VB), but I would think I should be able to do this in the ASP:sqldatasource.

All I want to do is select records where the value of field in the table is equal to the value of a hidden field on the page.

On selected node change of a tree view I set the value of a hidden field named: NLHF. I use this elsewhere so I know it works as expected (setting this value). The issue is I want to reference this value in a SQL select statement and I am failing miserably.

The code in question is:

<asp:SqlDataSource ID="SupervisorSQLDS" runat="server"
ConnectionString="<%$ myconnectionstring %>"
SelectCommand="SELECT DISTINCT ContactID, DisplayName AS ListItem FROM Contact WHERE LocationID = NLHF ORDER BY ListItem">
</asp:SqlDataSource>


I believe this is just the syntax... feeling not so bright but I giving up after a couple of hours of looking and trying different things.

Thanks for looking.

Answer

You can add parameters to your query, so the SQL becomes:

SELECT DISTINCT ContactID, DisplayName AS ListItem 
FROM Contact 
WHERE LocationID = @NLHF 
ORDER BY ListItem

Then bind a ControlParameter to your datasource's select command

<asp:SqlDataSource ID="SupervisorSQLDS" runat="server"
    ConnectionString="<%$ myconnectionstring %>" 
    SelectCommand="SELECT DISTINCT ContactID, DisplayName AS ListItem FROM Contact WHERE LocationID = @NLHF ORDER BY ListItem">

  <SelectParameters>
    <asp:ControlParameter Name="@NLHF" ControlID="NLHF" PropertyName="Value"/>
  </SelectParameters>

</asp:SqlDataSource>
Comments