Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Single Quotes in SqlDataSource Controls?????

Posted on 2009-12-21
10
Medium Priority
?
740 Views
Last Modified: 2013-11-26
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

0
Comment
Question by:SamJolly
  • 6
  • 2
9 Comments
 
LVL 3

Accepted Solution

by:
rajquest earned 800 total points
ID: 26100546
where ever you have a single quotes in between data,. just another one
ex: if data is o'niel -> o''niel

single quote is an escape character. by adding another one sql knows to ignore the escape character
0
 

Author Comment

by:SamJolly
ID: 26100590
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
0
 
LVL 29

Assisted Solution

by:Kumaraswamy R
Kumaraswamy R earned 1200 total points
ID: 26100883
HI

if single quotes Means need to use  3 single quotes

ex: sample's --->  '''samples''s'''
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 29

Assisted Solution

by:Kumaraswamy R
Kumaraswamy R earned 1200 total points
ID: 26100911
0
 

Author Comment

by:SamJolly
ID: 26100943
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
0
 

Author Comment

by:SamJolly
ID: 26101456
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
0
 

Author Comment

by:SamJolly
ID: 26404095
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
0
 

Author Comment

by:SamJolly
ID: 26404100
Hi,

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

So thanks,

Sam
0
 

Author Closing Comment

by:SamJolly
ID: 31668755
Thanks
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question