An SqlParameter with ParameterName '@UserId' is not contained by this SqlParameterCollection

Question

I had a login page. once user successfuly logged in, they can view and manage their profile/information. This would be done by retrieving data from database and display on a formview.

However this following error appeared inside my userprofile.aspx.cs file:

Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName '@UserId' is not contained by this SqlParameterCollection.

Source Error: 

Line 44: 
Line 45:         // Assign the currently logged on user's UserId to the @UserId parameter
Line 46:         e.Command.Parameters["@UserId"].Value = currentUserId;
Line 47: 
Line 48:     }

Userprofile.aspx:

<asp:FormView ID="FormView1" runat="server" 
            DataSourceID="SqlDataSource1" DataKeyNames="UserId">
            <EditItemTemplate>
                UserId:
                <asp:Label ID="UserIdLabel1" runat="server" Text='<%# Eval("UserId") %>' />
                <br />
                Password:
                <asp:TextBox ID="PasswordTextBox" runat="server" 
                    Text='<%# Bind("Password") %>' />
                <br />
                Email:
                <asp:TextBox ID="EmailTextBox" runat="server" Text='<%# Bind("Email") %>' />
                <br />
                HomeTown:
                <asp:TextBox ID="HomeTownTextBox" runat="server" 
                    Text='<%# Bind("HomeTown") %>' />
                <br />
                HomepageUrl:
                <asp:TextBox ID="HomepageUrlTextBox" runat="server" 
                    Text='<%# Bind("HomepageUrl") %>' />
                <br />
                Signature:
                <asp:TextBox ID="SignatureTextBox" runat="server" 
                    Text='<%# Bind("Signature") %>' />
                <br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                    CommandName="Update" Text="Update" />
                &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </EditItemTemplate>
            <InsertItemTemplate>
                UserId:
                <asp:TextBox ID="UserIdTextBox" runat="server" Text='<%# Bind("UserId") %>' />
                <br />
                Password:
                <asp:TextBox ID="PasswordTextBox" runat="server" 
                    Text='<%# Bind("Password") %>' />
                <br />
                Email:
                <asp:TextBox ID="EmailTextBox" runat="server" Text='<%# Bind("Email") %>' />
                <br />
                HomeTown:
                <asp:TextBox ID="HomeTownTextBox" runat="server" 
                    Text='<%# Bind("HomeTown") %>' />
                <br />
                HomepageUrl:
                <asp:TextBox ID="HomepageUrlTextBox" runat="server" 
                    Text='<%# Bind("HomepageUrl") %>' />
                <br />
                Signature:
                <asp:TextBox ID="SignatureTextBox" runat="server" 
                    Text='<%# Bind("Signature") %>' />
                <br />
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                    CommandName="Insert" Text="Insert" />
                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </InsertItemTemplate>
            <ItemTemplate>
                UserId:
                <asp:Label ID="UserIdLabel" runat="server" Text='<%# Eval("UserId") %>' />
                <br />
                Password:
                <asp:Label ID="PasswordLabel" runat="server" Text='<%# Bind("Password") %>' />
                <br />
                Email:
                <asp:Label ID="EmailLabel" runat="server" Text='<%# Bind("Email") %>' />
                <br />
                HomeTown:
                <asp:Label ID="HomeTownLabel" runat="server" Text='<%# Bind("HomeTown") %>' />
                <br />
                HomepageUrl:
                <asp:Label ID="HomepageUrlLabel" runat="server" 
                    Text='<%# Bind("HomepageUrl") %>' />
                <br />
                Signature:
                <asp:Label ID="SignatureLabel" runat="server" Text='<%# Bind("Signature") %>' />
                <br />

            </ItemTemplate>
        </asp:FormView>
    </p>
<p>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:SecurityTutorialsConnectionString %>" 
            onselecting="SqlDataSource1_Selecting" 
            SelectCommand="SELECT UserProfiles.UserId, aspnet_Membership.Password, aspnet_Membership.Email, UserProfiles.HomeTown, UserProfiles.HomepageUrl, UserProfiles.Signature FROM aspnet_Membership INNER JOIN UserProfiles ON aspnet_Membership.UserId = UserProfiles.UserId">
        </asp:SqlDataSource>
    </p>
<p>
        &nbsp;</p>

</asp:Content>

Userprofile.aspx.cs:

 protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        // Get a reference to the currently logged on user
        MembershipUser currentUser = Membership.GetUser();

        // Determine the currently logged on user's UserId value
        Guid currentUserId = (Guid)currentUser.ProviderUserKey;

        // Assign the currently logged on user's UserId to the @UserId parameter
        e.Command.Parameters["@UserId"].Value = currentUserId;

    }

Lee O. · Accepted Answer · 2012-06-30 17:17:20Z

7

Create a new SqlParameter and add it to the collection.

SqlParameter param = new SqlParameter("@UserId", currentUserId);
e.Command.Parameters.Add(param);

answered Jun 30, 2012 at 17:17

Lee O.

3,3723 gold badges28 silver badges37 bronze badges

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

2 Comments

user1467175 Over a year ago

is there any namespace for sqlParameter? because the error-- The type or namespace name 'SqlParameter' could not be found (are you missing a using directive or an assembly reference?) occured

codingbiz Over a year ago

does that work for mysql? SqlParameter is meant for SqlServer (System.Data.SqlClient namespace)

codingbiz · Accepted Answer · 2012-06-30 17:45:45Z

5

Try the following:

DbParameter param = e.Command.CreateParameter();
param.ParameterName = "@UserId";
param.Value = currentUserId;
e.Command.Parameters.Add(param);

I didn't test this though

answered Jun 30, 2012 at 17:45

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

You must add the parameter with

e.Command.Parameters.AddWithValue("@UserId", currentUserId);

Once you have added it you could access it through the indexer as in your example.

UPDATE

If you are working with the System.Data.Common namespace, the AddWithValue method is not available. You will have to do something like

var param = e.Command.CreateParameter("@UserId", currentUserId);
e.Command.Parameters.Add(param);

This is a little bit more complicated but has the advantage that you do not have to implicitly create a parameter of a specific type like SqlParamter or OleDbParameter.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 30, 2012 at 16:44

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

2 Comments

user1467175 Over a year ago

Hi, i had tried your code. but a compilation error appeared. Compiler Error Message: CS1061: 'System.Data.Common.DbParameterCollection' does not contain a definition for 'AddWithValue' and no extension method 'AddWithValue' accepting a first argument of type 'System.Data.Common.DbParameterCollection' could be found (are you missing a using directive or an assembly reference?)

Olivier Jacot-Descombes Over a year ago

Apparantly the AddWithValue method only exists on specific types (SqlClient, ODBC, OleDb etc.); However you are working with System.Data.Common (which is perfectly OK).

Fandango68 · Accepted Answer · 2014-05-16 05:23:47Z

0

If you already have the parameter listed in your SqlDataSource, then simply point to it and change it's value ...

e.Command.Parameters["@UserId"].Value = currentUserId;

You do not need to create a parameter, unless you have not listed it back in your ASP page.

answered May 16, 2014 at 5:23

Fandango68

4,9755 gold badges50 silver badges88 bronze badges

Collectives™ on Stack Overflow

An SqlParameter with ParameterName '@UserId' is not contained by this SqlParameterCollection

4 Answers 4

2 Comments

Comments

UPDATE

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

UPDATE

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related