Help with SQLDataSource query

Hello I have a search page that searches users profiles based on their City of Residence.  This data table is called User Profiles.

I have another Table called locations and in that table the user has entered other locations of residence.  This table includes the Columns Id, UserId, City,

Currently the search button queries only the users and retrieves from the UserProfiles table.  I want to be able to query from both tables and show results on the DataList.  

I am not sure how to set up the SQL datasource to query users from both tables and show results on one Datalist.  

The code behind on the submit button is:

Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        SqlDataSource3.DataBind()
        DataList4.DataBind()

    End Sub


Here is the current SQL Datasource which queries users in the UserProfiles Table:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"
            ConnectionString="<%$ ConnectionStrings:dbMyCMSConnectionString %>"
           SelectCommand="SELECT UserId, UserName, FirstName, tagline, cookingstyle, cookingexperience, howmuchmoney, minimumhours, workdistance, contactnumber, email, howmany, groups, languages, City, State, Country, LastName, webaddress, Image_Path, timestamp, ipAddress, facebook, twitter, emailpersonal, website, linkedin, zip FROM UserProfiles WHERE (City LIKE '%' + @City + '%')">
           <SelectParameters>
               <asp:ControlParameter ControlID="city" Name="City" PropertyName="Text" Type="String"  />
           </SelectParameters>
                      </asp:SqlDataSource>
misnsttAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

SammyCommented:
Don't take it personal but you have a bad design here.
There is no relationship (foreign key) between the tables, No join will allow you to get valid results.
you can eliminate the profile table and use ASp,NET profile provider then get query based on the city from the Location table

http://www.codeproject.com/Articles/281602/ASP-NET-Profile-Provider
0

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
misnsttAuthor Commented:
Thank you I will do as you instructed.
0
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
ASP.NET

From novice to tech pro — start learning today.