Help with SQLDataSource query

misnstt
misnstt used Ask the Experts™
on
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>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
Thank you I will do as you instructed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial