Problem passing quotes to SQL

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.

 

Answer 1

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?

Answer 2

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