Sql – How to use Single Quotes in Eval Format String

asp.netevalsqlstring-formatting

I've got a Repeater and its SqlDatasource nested inside a Gridview TemplatedField.
The Repeater's datasource SelectCommand is set using the FormatString of an Eval from the Gridview.
The SelectCommand has a WHERE clause which is to compare a string.
Because I have already used the single and double quotes, I am having trouble delimiting the string in the SQL WHERE clause.

How do I add single quotes inside an Eval FormatString?

I have tried using 'Replace'.
I have tried using 'Special Characters' (… WHERE StringField = '{0}' …)

No luck so far. I appreciate any help you may be able to offer.

<asp:GridView ID="GridView1" runat="server" DataSourceID="DataSource1" DataKeyNames="Foo" AutoGenerateColumns="False" AllowSorting="true" >
    <Columns>
        <asp:BoundField DataField="Foo" HeaderText="Foo" SortExpression="Foo" />
        <asp:BoundField DataField="Bar" HeaderText="Bar" SortExpression="Bar" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Repeater ID="Repeater1" runat="server" DataSourceID="DataSourceNested">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("Blah") %>'></asp:Label>
                    </ItemTemplate>
                </asp:Repeater>
                <asp:SqlDataSource ID="DataSourceNested" runat="server" DataFile="~/App_Data/DatabaseName"
                    SelectCommand='<%# Eval("Bar", "SELECT Blah FROM TableName WHERE (StringField = {0})") %>' >
                </asp:SqlDataSource>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Best Answer

Don't forget that a .aspx page is simply XML. You just escape the quotes as you normally would.

For example:

<asp:Repeater ID="repeatTheLabel" runat="server">
    <ItemTemplate>
        <asp:Label ID="Label1" Text="<%# Eval(&quot;Id&quot;, &quot;This is item '{0}'.&quot;) %>" runat="server" />
    </ItemTemplate>
    <SeparatorTemplate>
        <br />
    </SeparatorTemplate>
</asp:Repeater>

When the above expression is databound the value between <%# and %> becomes:

Eval("Id", "This is item '{0}'.")

...which produces on the HTML page as output when databound with an array of objects with "Id" property values from 1 to 5:

This is item '1'.
This is item '2'.
This is item '3'.
This is item '4'.
This is item '5'.

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Jquery – How to format a Microsoft JSON date

eval() is not necessary. This will work fine:

var date = new Date(parseInt(jsonDate.substr(6)));

The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor.

I have intentionally left out the radix (the 2nd argument to parseInt); see my comment below.

Also, I completely agree with Rory's comment: ISO-8601 dates are preferred over this old format - so this format generally shouldn't be used for new development.

For ISO-8601 formatted JSON dates, just pass the string into the Date constructor:

var date = new Date(jsonDate); //no ugly parsing needed; full timezone support

Best Answer

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Jquery – How to format a Microsoft JSON date

Related Topic