Solved

Help with SQLDataSource query

Posted on 2014-03-02
2
281 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
2 Comments
 
LVL 27

Accepted Solution

by:
Sammy earned 500 total points
Comment Utility
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
Comment Utility
Thank you I will do as you instructed.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now