The multi-part identifier "tbl_Profile.Gender" could not be bound.

The multi-part identifier "tbl_Profile.Gender" could not be bound.
The multi-part identifier "tbl_profile.BirthDate" could not be bound.
The multi-part identifier "tbl_profile.BirthDate" could not be bound.

{"The multi-part identifier \"tbl_Profile.Gender\" could not be bound.\r\nThe multi-part identifier \"tbl_profile.BirthDate\" could not be bound.\r\nThe multi-part identifier \"tbl_profile.BirthDate\" could not be bound."}

   public static DBProfileCollection GetAllProfilesBasicSearch(int PageSize, int PageIndex, out int TotalRecords, string sqlcountry, int sqlgender, int sqlage1, int sqlage2, bool sqlonlinenow)
        TotalRecords = 0;

        DBProfileCollection profileCollection = new DBProfileCollection();
        Database db = OmegaLoveSqlDataHelper.CreateConnection(ConnnectionString);
        DbCommand dbCommand = db.GetStoredProcCommand("prc_Profile_Search_Basic");

        db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
        db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
        db.AddInParameter(dbCommand, "Country", DbType.String, sqlcountry);
        db.AddInParameter(dbCommand, "Gender", DbType.Int32, sqlgender);
        db.AddInParameter(dbCommand, "Age1", DbType.Int32, sqlage1);
        db.AddInParameter(dbCommand, "Age2", DbType.Int32, sqlage2);
        db.AddInParameter(dbCommand, "IsOnLineNow", DbType.Boolean, sqlonlinenow);

        db.AddOutParameter(dbCommand, "TotalRecords", DbType.Int32, 0);
        using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            while (dataReader.Read())
                DBProfile profile = GetProfileFromReader(dataReader);
        TotalRecords = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecords"));
        return profileCollection;

USE [omegalove]
CREATE PROCEDURE [dbo].[prc_Profile_Search_Basic]
	@PageIndex				int = 0, 
	@PageSize				int = 2147483644,
	@Country				char(2) = null, --- Country Code Example CA is Canada
	@Gender					int = 0, --- 1 for male and 2 for female
	@Age1					int = 0,
	@Age2					int = 0,  --- Greater than Age1
	@IsOnlineNow			bit = false, -- false implies not online.
	@TotalRecords			int = null OUTPUT

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)	
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1
		IndexID int IDENTITY (1, 1) NOT NULL,
		ProfileID int NOT NULL,

	INSERT INTO #PageIndex (ProfileID)
	FROM tbl_profile a WITH (NOLOCK)

	SET @TotalRecords = @@rowcount	
a.ProfileID as ProfileID,
a.UserID as UserID,
a.UserName  as UserName,
a.HeadLine  as HeadLine,
a.EmailAddress as EmailAddress,
a.GenderID  as Gender,
b.Gender  as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
a.SearchAgeStartID as StartAge,
a.SearchAgeEndID as EndAge,
a.Birthdate as BirthDate,
dbo.fnAge_GetAgeNumber(a.Birthdate) AS Age,
dbo.fnZodiac_GetZodiacName(a.Birthdate) AS ZodiacName,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,

a.AppearanceID as Appearance,
e.Appearance as AppearanceName,

a.BodyTypeID as BodyType,
f.BodyType as BodyTypeName,

a.StyleID AS Style,
g.Style AS StyleName,

a.ComplexionID AS Complexion,
h.Complexion AS ComplexionName,

a.EyeColorID AS EyeColor,
i.EyeColor AS EyeColorName,

a.EyeWearID AS EyeWear,
j.EyeWear AS EyeWearName,

a.FacialHairID AS FacialHair,
k.FacialHair AS FacialHairName,

a.FacialShapeID AS FacialShape,
l.FacialShape AS FacialShapeName,

a.FashionSenseID AS FashionSense,
m.FashionSense AS FashionSenseName,

a.BestFeatureID AS BestFeature,
n.BestFeature AS BestFeatureName,

a.HairColorID AS HairColor,
o.HairColor AS HairColorName,

a.HairStyleID AS HairStyle,
p.HairStyle AS HairStyleName,

a.HeightID AS Height,
q.Height AS HeightName,

a.WeightID AS Weight,
r.Weight AS WeightName,

a.PiercingID AS Piercing,
s.Piercing AS PiercingName,

a.TattooID AS Tattoo,
t.Tattoo AS TattooName,

a.JewelryID AS Jewelry,
u.Jewelry AS JewelryName,

a.EthnicityID AS Ethnicity,
v.Ethnicity AS EthnicityName,

a.SmokingID AS Smoking,
w.Smoking AS SmokingName,

a.DrinkingID AS Drinking,
x.Drinking AS DrinkingName,

a.DrugID AS Drug,
y.Drug AS DrugName,

a.DietID AS Diet,
z.Diet AS DietName,

a.DisabilityID AS Disability,
aa.Disability AS DisabilityName,

a.ReligionID AS Religion,
ab.Religion AS ReligionName,

a.NationalityID AS Nationality,
ac.Nationality AS NationalityName,

a.EducationID AS Education,
ad.Education AS EducationName,

a.OccupationID AS Occupation,
ae.Occupation AS OccupationName,

a.EmploymentStatusID AS EmploymentStatus,
af.EmploymentStatus AS EmploymentStatusName,

a.HaveChildrenID AS HaveChildren,
ag.HaveChildren AS HaveChildrenName,

a.WantChildrenID AS WantChildren,
ah.WantChildren AS WantChildrenName,

a.MotherTongueID AS MotherTongue,
ai.MotherTongue AS MotherTongueName,

a.IncomeID AS Income,
aj.Income AS IncomeName,

a.Latitude AS Latitude,
a.Longitude AS Longitude,
a.IPAddress AS IPAddress,

a.SenseHumorID AS SenseHumor,
ak.SenseHumor AS SenseHumorName,

a.SpendingHabitID AS SpendingHabit,
al.SpendingHabit AS SpendingHabitName,

a.RelocateID AS Relocate,
am.Relocate AS RelocateName,

a.TransportationID AS Transportation,
an.Transportation AS TransportationName,

a.StatusID AS MaritalStatus,
ao.Status AS MaritalStatusName,

a.AboutMe AS AboutMeText,
a.SearchFor AS SearchForText,
a.IdealDate AS IdealDateText,
a.FearMeeting AS FearMeetingText,
a.MeHappy AS MeHappyText,

a.Casual_Shortterm AS CasualShortTermCheck,
a.Email AS EmailCheck,
a.Friends AS FriendsCheck,
a.LongTerm AS LongTermCheck,
a.Marriage AS MarriageCheck,
a.PhysicalIntimate AS PhysicalIntimateCheck,

a.PreviousLogin AS PreviousLogin,
a.LastLogin AS LastLogin,
a.LoginCount AS LoginCount,
a.LastOnline AS LastOnline,
a.UserSince  AS UserSince,
a.LastSessionID AS LastSessionID,
a.ConfirmationGUID AS ConfirmationGUID,
a.IsOnlineNow AS IsOnlineNow

--ap.BestDescribedID AS BestDescribed,
--ap.BestDescribedText AS BestDescribedName,
--ap.CheckBox AS BestDescribedCheckBox

--aq.FoodID AS Food,
--aq.FoodText AS FoodName,
--aq.CheckBox AS FoodCheckBox,
--ar.HobbyID AS Hobby,
--ar.HobbyText AS HobbyName,
--ar.CheckBox AS HobbyCheckBox,
--at.LanguageID AS Languages,
--at.LanguageText AS LanguagesName,
--at.CheckBox AS LanguagesCheckBox,
--au.LifeValuesID AS LifeValues,
--au.LifeValuesText AS LifeValuesName,
--au.CheckBox AS LifeValuesCheckBox,
--av.LookingForID AS LookingFor,
--av.LookingForText AS LookingForName,
--av.CheckBox AS LookingForCheckBox,
--aw.MusicID AS Music,
--aw.MusicText AS MusicName,
--aw.CheckBox AS MusicCheckBox,
--ax.ReadingID AS Reading,
--ax.ReadingText AS ReadingName,
--ax.CheckBox AS ReadingCheckBox,
--az.SportID AS Sport,
--az.SportText AS SportName,
--az.CheckBox AS SportCheckBox

#PageIndex [pi]
INNER JOIN  tbl_profile a on a.ProfileID = [pi].ProfileID
INNER JOIN  tbl_lookup_gender b  ON a.GenderID = b.GenderID
INNER JOIN  tbl_lookup_seeking c  ON a.SeekingID = c.SeekingID
INNER JOIN  CountryCodes d  ON a.CountryID = d.CountryID
INNER JOIN  tbl_lookup_appearance e  ON a.AppearanceID = e.AppearanceID
INNER JOIN  tbl_lookup_bodytype f  ON a.BodyTypeID = f.BodyTypeID
INNER JOIN  tbl_lookup_style g  ON a.StyleID = g.StyleID
INNER JOIN  tbl_lookup_complexion h  ON a.ComplexionID = h.ComplexionID
INNER JOIN  tbl_lookup_eyecolor i  ON a.EyeColorID = i.EyeColorID
INNER JOIN  tbl_lookup_eyewear j  ON a.EyeWearID = j.EyeWearID
INNER JOIN  tbl_lookup_facialhair k  ON a.FacialHairID = k.FacialHairID
INNER JOIN  tbl_lookup_facialshape l  ON a.FacialShapeID = l.FacialShapeID  
INNER JOIN  tbl_lookup_fashionsense m  ON a.FashionSenseID = m.FashionSenseID
INNER JOIN  tbl_lookup_bestfeature n  ON a.BestFeatureID = n.BestFeatureID  
INNER JOIN  tbl_lookup_haircolor o  ON a.HairColorID = o.HairColorID  
INNER JOIN  tbl_lookup_hairstyle p  ON a.HairStyleID = p.HairStyleID    
INNER JOIN  tbl_lookup_height q ON a.HeightID = q.HeightID  
INNER JOIN  tbl_lookup_weight r  ON a.WeightID = r.WeightID
INNER JOIN  tbl_lookup_piercing s  ON a.PiercingID = s.PiercingID
INNER JOIN  tbl_lookup_tattoo t ON a.TattooID = t.TattooID
INNER JOIN  tbl_lookup_jewelry u ON a.JewelryID = u.JewelryID
INNER JOIN  tbl_lookup_ethnicity v ON a.EthnicityID= v.EthnicityID
INNER JOIN  tbl_lookup_smoking w ON a.SmokingID= w.SmokingID
INNER JOIN  tbl_lookup_drinking x ON a.DrinkingID= x.DrinkingID
INNER JOIN  tbl_lookup_drug y ON a.DrugID= y.DrugID
INNER JOIN  tbl_lookup_diet z ON a.DietID= z.DietID
INNER JOIN  tbl_lookup_disability aa ON a.DisabilityID = aa.DisabilityID
INNER JOIN  tbl_lookup_religion ab ON a.ReligionID = ab.ReligionID
INNER JOIN  tbl_lookup_nationality ac ON a.NationalityID = ac.NationalityID
INNER JOIN  tbl_lookup_education ad ON a.EducationID = ad.EducationID
INNER JOIN  tbl_lookup_occupation ae ON a.OccupationID = ae.OccupationID
INNER JOIN  tbl_lookup_employmentstatus af ON a.EmploymentStatusID = af.EmploymentStatusID
INNER JOIN  tbl_lookup_havechildren ag ON a.HaveChildrenID = ag.HaveChildrenID
INNER JOIN  tbl_lookup_wantchildren ah ON a.WantChildrenID = ah.WantChildrenID
INNER JOIN  tbl_lookup_mothertongue ai ON a.MotherTongueID = ai.MotherTongueID
INNER JOIN  tbl_lookup_income aj ON a.IncomeID = aj.IncomeID
INNER JOIN  tbl_lookup_sensehumor ak ON a.SenseHumorID = ak.SenseHumorID
INNER JOIN  tbl_lookup_spendinghabit al ON a.SpendingHabitID = al.SpendingHabitID
INNER JOIN  tbl_lookup_relocates am ON a.RelocateID = am.RelocateID
INNER JOIN  tbl_lookup_transportation an ON a.TransportationID = an.TransportationID
INNER JOIN  tbl_lookup_status ao ON a.StatusID = ao.StatusID
--OUTER APPLY fnBestDescribed_SelectBestDescribedByProfileID(a.ProfileID) ap
--OUTER APPLY fnFood_SelectFoodByProfileID(a.ProfileID) aq
--OUTER APPLY fnHobby_SelectHobbyByProfileID(a.ProfileID) ar
--OUTER APPLY fnLanguage_SelectLanguageByProfileID(a.ProfileID) at
--OUTER APPLY fnLifeValues_SelectLifeValuesByProfileID(a.ProfileID) au
--OUTER APPLY fnLookingFor_SelectLookingForByProfileID(a.ProfileID) av
--OUTER APPLY fnMusic_SelectMusicByProfileID(a.ProfileID) aw
--OUTER APPLY fnReading_SelectReadingByProfileID(a.ProfileID) ax
--OUTER APPLY fnSport_SelectSportByProfileID(a.ProfileID) az
		[pi].IndexID > @PageLowerBound AND 
		[pi].IndexID < @PageUpperBound

And Country = @Country and 
tbl_Profile.Gender = @Gender 
and (DateDiff(YEAR, tbl_profile.BirthDate,getdate()) >= @Age1) 
and (DateDiff(YEAR,tbl_profile.BirthDate,getdate()) <= @Age2)



Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

At line 245 in the stored procedure (INNER JOIN  tbl_profile a on a.ProfileID = [pi].ProfileID), you see that tbl_profile is aliased as "a".  That means you can no longer use tbl_profile as the table name in the query, you need to use "a".  It looks like your have several of these issues starting at line 300.  Those should be more like "a.Gender = @Gender" and "YEAR, a.BirthDate, getdate()"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is the error:

and (DateDiff(YEAR, tbl_profile.BirthDate,getdate()) >= @Age1)
and (DateDiff(YEAR,tbl_profile.BirthDate,getdate()) <= @Age2)

must be:
and (DateDiff(YEAR, a.BirthDate,getdate()) >= @Age1)
and (DateDiff(YEAR, a.BirthDate,getdate()) <= @Age2)
omegaloveAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.