2

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?

Improve this question
2
  • Why are you surrounding the usename with /s? Commented Sep 25, 2015 at 22:11
  • Removed and get following 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. Line 46: <asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], minimal reproducible example FROM [gridData] WHERE [userid]='" + User.Identity.Name + "' AND [Strategy]='Vocabulary'"> Commented Sep 25, 2015 at 23:32

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, James. You rock!
0

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

Improve this answer

2 Comments

made your suggested change, now get: 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. Line 46: <asp:SqlDataSource ID="ISESDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [id], [Word], [Definition], minimal reproducible example FROM [gridData] WHERE [userid]=\'" + User.Identity.Name + "\' AND [Strategy]='Vocabulary'" >
Ok, I did some research and the problem is not so simple. Basically, you can not do string concatenation in SelectCommand property, as per this link (stackoverflow.com/questions/1112371/…). What you may want to try is to follow this post: msdn.microsoft.com/en-us/library/z72eefad.aspx to use parameterized query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.