asked on

Single Quotes in SqlDataSource Controls?????

Hi,

I have got some "IN ('String1','String') code for a WHERE clause for some SQL. Unfortunately the quotes are not been brought across correctly so the SQL is failing. See attached code.

I need the correct format for the SqlParameter Default Value property which I guess means escaping the Single Quote characters.

I have tried:

a) 2x Single Quotes
b) using '
c) /'

Nothing works.... If I type the 'String1','String' directly into the SQL text it works fine. However if I put this as a property value or in code then it fails.

Any help greatly appreciated,

Thanks,

Sam

<asp:SqlDataSource ID="_sds" runat="server" 
    ConnectionString="<%$ MyConnectionString %>" 
    SelectCommand="SELECT [ID], [Tag], [Description] FROM [Table] WHERE ([Tag] IN (@Tag))">
    <SelectParameters>
        <asp:Parameter Name="Tag" Type="String" DefaultValue="'String1','String2'" />
    </SelectParameters>
</asp:SqlDataSource>

Open in new window

ASKER CERTIFIED SOLUTION

rajquest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamJolly

ASKER

Thanks for the reply. Are you sure that doubling up the single quotes will work in the "defaultValue" property ie:

<asp:Parameter Name="Tag" Type="String" DefaultValue="''String1'',''String2''" />

Since I cannot get this to work.

Sam

SOLUTION

Kumaraswamy R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kumaraswamy R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamJolly

ASKER

Hi, Thanks rkworlds.... Tried that... Still no go. The only thought I had was that my parameter is defined as a string and my substitution is really a list of strings ie

tag in (@tag)

where @tag = 'string1','string2'

so perhaps asp.net cannot parse this?????

Sam

SamJolly

ASKER

OK, I have found that this issue is to do with the use of SqlParameter to populate a list like structure within an "IN (...)" clause. Basically the string datatype will not work. In the end I cut some ADO.NET code in my CBH and used the .REPLACE method which worked, but not terribly recommended for security etc. While the ADO.NET parameter object is more powerful than the SqlDataSource's I still could not get it to work. However I only tried the variant and VarChar Data types.

So is it possible to put in a list of strings via the SqlParameter object into the "IN (...)" clause?

Thanks,

Sam

SamJolly

ASKER

Hi,

I think it would be appropriate to allocate points to the folks who tried to help me. I know that a total solution was not arrived at, but the effort was there and appreciated.

Sam

SamJolly

ASKER

Hi,

I am allocating point in appreciated of the effort taken to attempt to solve my problem.

So thanks,

Sam

SamJolly

ASKER

Thanks