Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with SQLDataSource query

Posted on 2014-03-02
2
Medium Priority
?
294 Views
Last Modified: 2014-03-02
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>
0
Comment
Question by:misnstt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
Sammy Ageil earned 2000 total points
ID: 39899274
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
 

Author Closing Comment

by:misnstt
ID: 39899329
Thank you I will do as you instructed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question