locked
Problem passing quotes to SQL RRS feed

  • Question

  • User1279376247 posted

    I'm trying to create this query:

    Select ServiceName from ServicesNameList where ServiceName in ('StringA','StringB')

    Here is my SqlDataSource and the parameters assignment in my Selecting event:

    

     

                    <asp:SqlDataSource ID="SqlDataSourceServiceList" runat="server" 
            ConnectionString="<%$ ConnectionStrings:HumanResourcesConnectionString %>" 
            SelectCommand="SELECT ServiceName FROM ServiceNameList 
            where ServiceName in (@ServiceNames)">
                            <SelectParameters>
                                <asp:Parameter Name="ServiceNames" Type = "String"/>
                            </SelectParameters>
        </asp:SqlDataSource>
    

     

            e.Command.Parameters("@ServiceNames").Value = "'StringA','StringB'"
    

    The SQL that gets generated is this:

    exec sp_executesql N'SELECT ServiceName FROM LU_ServiceNames 
            where ServiceName in (@ServiceNames)',N'@ServiceNames nvarchar(19)',@ServiceNames=N'''StringA'',''StringB'''

     

    and nothing gets returned from SQL, although it does when I type in the original SQL up above. 

    Does anyone see a problem somewhere?  I think there's something wrong with the number of quotes, although I don't know where.

     

    Tuesday, April 19, 2011 7:55 AM

Answers

  • User-1423995609 posted

    I think you don't need to use single quotes when defineing @ServiceNames parameter value. Did u try to remove single quotes around StringA and StringB?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 19, 2011 9:58 AM

All replies

  • User-1423995609 posted

    I think you don't need to use single quotes when defineing @ServiceNames parameter value. Did u try to remove single quotes around StringA and StringB?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 19, 2011 9:58 AM
  • User3866881 posted

    Hi:)

    Please remove the single quote first.

    If you cannot solve the problem. Just use SqlDataAdapter with DataTable instead of SqlDataSource, and do manually bind to the GridView

    Thx

    Thursday, April 21, 2011 9:48 PM