?
Solved

Help with SQLDataSource query

Posted on 2014-03-02
2
Medium Priority
?
292 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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