SQL Membership provider

Question

SQL Membership provider

rwheway

SSC Enthusiast

Points: 118
More actions
December 21, 2014 at 2:48 pm

#302048

I am trying to fill a Gridview utilizing the built in aspnet_Membership_FindUsersByName stored procedure. The SP works fine, however I would like to include additional fields from a custom table. I tried modifying the SP to include the additional table but the gridview returns the following error "A field or property with the name 'Contact1LastName' was not found on the selected data source." The stored procedure seems to run without error but does not return the additional field.
Is it possible to do this, or would I need to write a new stored procedure and call it via conventional programming calls?
The sql code is provided below. The additional table is dbo.UserProfiles and the fields that need to be added to the gridview are Contact1FirstName and Contact1LastName
ALTER PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
@ApplicationName nvarchar(256),
@UserNameToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m, dbo.UserProfiles up
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.UserId = up.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
ORDER BY u.UserName
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate,
up.Contact1FirstName,
up.Contact1LastName
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p, dbo.Userprofiles up
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND u.userId = up.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
Here is the vb code for populating the grid.
Private Sub BindUserAccounts()
Dim totalRecords As Integer
UserAccounts.DataSource = Membership.FindUsersByName(Me.UsernameToMatch + "%", Me.PageIndex, Me.PageSize, totalRecords)
UserAccounts.DataBind()
' Enable/disable the paging interface
Dim visitingFirstPage As Boolean = (Me.PageIndex = 0)
lnkFirst.Enabled = Not visitingFirstPage
lnkPrev.Enabled = Not visitingFirstPage
Dim lastPageIndex As Integer = (totalRecords - 1) / Me.PageSize
Dim visitingLastPage As Boolean = (Me.PageIndex >= lastPageIndex)
lnkNext.Enabled = Not visitingLastPage
lnkLast.Enabled = Not visitingLastPage
End Sub
And last, here is the grid on the aspx page
<asp:GridView ID="UserAccounts" runat="server"
AutoGenerateColumns="False">
<Columns>
<asp:HyperLinkField DataNavigateUrlFields="UserName"
DataNavigateUrlFormatString="UserInformation.aspx?user={0}" Text="Manage" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
<asp:BoundField DataField="Email" HeaderText="Email" />
<asp:CheckBoxField DataField="IsApproved" HeaderText="Approved?" />
<asp:CheckBoxField DataField="IsLockedOut" HeaderText="Locked Out?" />
<asp:CheckBoxField DataField="IsOnline" HeaderText="Online?" />
<asp:BoundField DataField="Comment" HeaderText="Comment" />
<asp:BoundField DataField="Contact1LastName" HeaderText="Last Name" />
</Columns>
</asp:GridView>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

I'm assuming that this is for an SQL 2012 database as its in the 2012 section.

One thing I noticed is that you are passing boundaries in for the result set, you might want to look at the NEW OFFSET command in SQL 2012 as an alternative for doing this (providing it doesn't need to be backward compatible).

DECLARE @PageNum INT =3

DECLARE @RowsPerPage INT =20

SELECT Object_NAME(object_iD) TableName, Name, column_id

FROM Sys.Columns

Order by

TableName

, column_id

OFFSET (@PageNum-1)* @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY

In regards to the actual question, my ASP is very flaky, but It seems you are missing a 'Comment' column that is bound in the grid, but I cant see it in the returned data set. It could be one of the MS issues that doesn't tell you the exact error.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

rwheway SSC Enthusiast Points: 118 More actions · Answer 2

Thanks for the reply. The comment field is listed in the first line of the select statement as "m.comment". The issue is with being able to add fields from the "UserProfiles" table, to the grid. Not sure how the offset command help.