CODEWITHSUNDEEP
sqlasp.net
sroonet
sroonet

Reputation: 65

Using user.identity.Name in SQL statement

In my ASP.NET app, I have the following SQL which works:

<asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], [Example] FROM [gridData] WHERE [Strategy]='Vocabulary'">

However, I need to add a user id check to the Where clause, and am hoping to use user.identity.Name to perform the check. I have tried the following, but it doesn't work:

<asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], [Example] FROM [gridData] WHERE [userid]= /'" + user.identity.Name + "/' AND [Strategy]='Vocabulary'">

Here is the error:

Parser Error Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: The server tag is not well formed.

Source Error: 

Line 46:             <asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], [Example] FROM [gridData] WHERE [userid]=/'" + User.Identity.Name + "/' AND [Strategy]='Vocabulary'">

What am I doing wrong?

Upvotes: 2

Views: 892

Answers (2)

Henry Liang
Henry Liang

Reputation: 137

To escape special char in string is to use backslash '\' not forward slash '/'. Try this and see if it works:

<asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], [Example] FROM [gridData] WHERE [userid]= \'" + user.identity.Name + "\' AND [Strategy]='Vocabulary'">

UPDATE: The answer is not this simple, as tested by sroonet. The actual answer is to use parameterized query in the SelectCommand. For details about using parameterized query in SelectCommand, please refer to the following webpage: https://msdn.microsoft.com/en-us/library/z72eefad.aspx

Upvotes: 0

James Balentine
James Balentine

Reputation: 212

I think you need to rethink your approach. Rather than trying to 'hard-code' the 'user.Identity.Name' property in the asp:SqlDataSource element, try creating a parameter to hold this value:

1) Replace 'user.Identity.Name' in your SelectCommand with a token for the parameter, such as '@Name'.

2) Then define a SelectParameter element for the asp:SqlDataSource that has a Name property of 'Name'. Set the Type property of the parameter to whatever is the data type of 'user.Identity.Name'.

3) Then, you can define the value that you want to select programmatically in an event handler for the Selecting event of the SqlDataSource.

Here's an example that works. The following code snippet is from the aspx page:

<asp:GridView ID="myGridView" runat="server" DataSourceID="myDataSource" DataKeyNames="ID"></asp:GridView>
<asp:SqlDataSource ID="myDataSource" runat="server"
    SelectCommand="SELECT * FROM [Users] WHERE [Name] = @Name"
    ConnectionString='Data Source=(LocalDB)\v11.0;AttachDbFilename="c:\users\windowsLogin\documents\visual studio 2012\Projects\WebApplication1\WebApplication1\App_Data\Database1.mdf";Integrated Security=True'>
    <SelectParameters>
        <asp:Parameter Name="Name" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Then, in the code-behind, you can define your parameter programmatically at run-time based on whatever criteria matter. (Here, I'm just assigning the value '2' to the @Name parameter; you could replace it with 'user.Identity.Name'.)

Private Sub myDataSource_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles myDataSource.Selecting
    e.Command.Parameters("@Name").Value = 2
End Sub

I hope this is clear... If not, refer to the documentation on MSDN. Here are a couple of pages to get you started:

SqlDataSource.SelectCommand Property

SqlDataSourceSelectingEventArgs Class

and, more generally,

Using Parameters with Data Source Controls for Filtering

Upvotes: 2

Related Questions