I'm trying to inner join asp_Users & asp_Membership tables so that i can retrieve the CreateDate field from asp_Membership in a BoundField of a GridView i have.
I have tried the following which seems to make sense to me but it keeps throwing the error:
A field or property with the name 'CreateDate' was not found on the selected data source.
ALTER PROCEDURE [dbo].[stp_Customer_Fetch_Paged]
(
@sortExpression nvarchar(100),
@startRowIndex int,
@maximumRows int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
SET @sortExpression = 'aspnet_Users.UserId'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = '
SELECT userid, username, first_name, last_name, town,postcode, rowrank FROM (
SELECT aspnet_Users.UserId, aspnet_Membership.UserId as MembershipID, aspnet_Membership.CreateDate, aspnet_Users.UserName, Customer.title, Customer.first_name, Customer.last_name, Customer.telephone,
Customer.dpa_consent, Customer.billing_address_id, Address.friendly_name, Address.address_line_1, Address.address_line_2,
Address.address_line_3, Address.town, Address.postcode, ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ' ) AS RowRank
FROM Customer INNER JOIN
aspnet_Users ON Customer.userid = aspnet_Users.UserId
INNER JOIN
Address ON Customer.billing_address_id = Address.address_id AND Customer.userid = Address.user_id
INNER JOIN
aspnet_Membership ON
aspnet_Membership.UserId = aspnet_Users.UserId
) AS ProductsWithRowNumbers
WHERE RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex)+ ' + ' +
CONVERT(nvarchar(10), @maximumRows) + ')'
print @sql
-- Execute the SQL query
EXEC sp_executesql @sql
END
Despite me selecting it in the stored procedure. I'm very new to SQL so it might be something simple i've overlooked? Any help is greatly appreciated.
Thank you.
