Using SelectParameters in ASP.NET gives 'Must declare variable' errors

Question

Hi,

I have an ASP SqlDataSource connected to Sybase DB query, using Select Parameters that are populated by a dropdown:

Dropdown (works OK):

<asp:SqlDataSource ID="dsBondIDList" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
    SelectCommand="SELECT [name], [bondID] FROM [bonds]">
</asp:SqlDataSource>
<asp:DropDownList ID="lstBondID" runat="server" DataSourceID="dsBondIDList" DataTextField="name" DataValueField="bondID">

SqlDataSource with parameter:

    <asp:SqlDataSource ID="dsBonds" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        SelectCommand="SELECT [ticker], [name], [isin], [currency], 
                       [stock], [maturity], [bid], [ask] 
                       FROM [bonds] where [bondID] = @bondID">
        <SelectParameters>
            <asp:ControlParameter Name="bondID" ControlID="lstBondID" PropertyName="SelectedValue" DefaultValue="-1" />
        </SelectParameters>    
    </asp:SqlDataSource>

But I get an error when I try to run it:

ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Must declare  variable '@bondID'.

Which of course I would get it I ran the sql literally as displayed. I expect the ASP engine to do the substitution before sending the query (and to use the default value of lstBondID if necessary)

Does anybody know why the @bondID is not being substituted with the lstBondID.SelectedValue?

Thanks in advance

Ryan

Answer 1

I just went through this problem 10 minutes ago and here's the fix:

If you need to keep using the ODBC driver you have, change your query to use ? instead of Named Parameters.

SELECT [ticker], [name], [isin], [currency], 
[stock], [maturity], [bid], [ask] 
FROM [bonds] where [bondID] = ?

The parameters are used in the order they are added to the parameters collection (This gets hairy when you need to use the parameter more than once in your query etc).

Otherwise you can change drivers. Look for sybdrvodb.dll and regsvr32 it. Then setup your DSN and use that.