How do I get google map to display specific results based on a preference criteria

Hi,

I am doing a final year project and I am a complete novice on how to get my site to support certain features. I am doing a website on Microsoft Visual Web developer with ASP.Net and C# and a database on SQL server.

a. I am designing a car share scheme website. I only need to implement a few features - Search Page so that users can view results in the form of icons on the google map and a list of results.

b. A hyperlink beside the returned results so that the user can contact that specific member and,

c. An automated email alert function when a user contacts a member.

I have currently designed a database which has 4 tables. I have attached a document which lists the use cases, entity attributes and SQL statement of my database.

And I have found that an XML file can help me put the icons on the map. But how do I get the XML file to output different addresses based on the preference criteria. The preference criteria will be automatically selected by the website based on a user who is using the system. I am guessing I probably need some sort of session variable or cookie here?

If you need codes of what I have done so far, then I will be happy to provide them.

Guys Please help!!! Its due in 2 weeks time and I am hoping I can get those 3 features done.

Formal-System-Use-Case.docx
xmalcolmAsked:
Who is Participating?
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.

Anthony PerkinsCommented:
You may want to get a Moderator to place this question in more appropriate zones.  SQL Server not being one of them.
0
Jens FiedererTest Developer/ValidatorCommented:
As ac said, the fact you are using SQL Server doesn't really make this an SQL Server question, but ASP.NET and C# may be appropriate.

It sounds like you can already get the wanted effect with an XML file - but you don't one a single unchangeable XML file, you want to generate that XML file based on the user.

That implies you have to know who the user is - there are many ways of doing this, but some of the easiest use the infrastructure VS provides for you.  For example, if you use the web site starter kit, login is handled for you and you can find the user in the credentials of the request.
0
xmalcolmAuthor Commented:
thank for the info guys - I will look into it!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

xmalcolmAuthor Commented:
Hi Jens, I made a profile now. How do I now use it in my query?

Will it be something like this

SELECT * FROM tbl_Member
WHERE JourneyPref = @JourneyPref
AND SmokingType = @SmokingType
AND Gender = @Gender

if so how do I declare the parameters?
0
Jens FiedererTest Developer/ValidatorCommented:
Part 6 in this series http://www.4guysfromrolla.com/articles/120705-1.aspx goes into how to use profile information.
0
xmalcolmAuthor Commented:
Thanks I'll look into that
0
xmalcolmAuthor Commented:
Hi Jens,

I had a look in it but it only shows what I have already done so far. What I want is to use the stored profile info for searches but it doesnt say how to. thanks anyway
0
Jens FiedererTest Developer/ValidatorCommented:
You haven't told me what you have done so far, or how you have done it, so it is hard for me to tell you how to use what you have done.

If you used the default sql profile provider, as that site explained, all property values are stored in a single column, so you are somewhat limited in doing SQL searches in any meaningful way.  If you want your values stored in a more queryable table automatically, you'd want to write a custom profile provider that works with your tbl_member structure.

If you want to use the existing infrastructure, and your login is the same as the user's email, you could just execute sql statements against your table using System.Web.HttpContext.Current.User.Identity.Name

sort of like

        SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ClubSiteDB"].ConnectionString);
        string qry = "select * from tbl_member where Member_Email =@memberemail";
        SqlCommand command = new SqlCommand(qry, connection);

        SqlParameter param0 = new SqlParameter("@memberemail", SqlDbType.VarChar);
        param0.Value = System.Web.HttpContext.Current.User.Identity.Name;
        command.Parameters.Add(param0);

        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

to find the current user's preferences and something similar with

Select * from tbl_member where Member_Smoker_Pref = @pref and ....

to do the matching (although this would give you ALL matches, not the ones in a radius...not clear how to judge distance from your data, but maybe google will clip the extraneous ones for you...)
0
xmalcolmAuthor Commented:
Hi Jens,

Sorry I haven't given you what I have done so far. I am attaching my web config file, my aspx and aspx.cs file.

And with regards to radius feature, I think I am not going to implement it as I dont have time and getting results based on a preference is much important.

Could you have a look at the code that I have attached and let me know where I could add the code you stated above and how can I use it with the code that I have now as my output is all put to an XML file.
Web Config File:

<profile enabled="true" defaultProvider="AspNetSqlProfileProvider">
      <properties>
        <add name="SmokeP" type="String"/>
        <add name="GenderP" type="String"/>
        <add name="JourneyP" type="String"/>
      </properties>
    </profile>

ASPX File:
<script type="text/javascript">

        var map;
        var geocoder;
        var xml;
        var markers;
        var address;
        
        function createMarker(point, html) {
            var marker = new GMarker(point);
            GEvent.addListener(marker, "click", function() {
            // instructs the marker to open an info window
            marker.openInfoWindowHtml(html);
            });
            return marker;
        }

        function initialize() {
            if (GBrowserIsCompatible()) {
                var map = new GMap2(document.getElementById("map_canvas"));
                map.setCenter(new GLatLng(51.066573,-0.313765), 09);
                map.setUIToDefault();
                
                // Create new geocoding object
                geocoder = new GClientGeocoder();

                // Download the data in out2.xml and load it on the map.
                GDownloadUrl("./out2.xml", function(data) {
                    xml = GXml.parse(data);
                    markers = xml.documentElement.getElementsByTagName("marker");
                    for (var i = 0; i < markers.length; i++) {
                        address = markers[i].getAttribute("address");
                        geocoder.getLocations(address, addToMap);
                    }
                });
            }
            
            // This function adds the point to the map

            function addToMap(response) {
                // Retrieve the object
                place = response.Placemark[0];

                // Retrieve the latitude and longitude
                point = new GLatLng(place.Point.coordinates[1],
                          place.Point.coordinates[0]);

                // Create a marker
                marker = new GMarker(point);
                var marker = createMarker(point,'<div style="width:240px">Contact This Person <a href="MessagingPage.aspx">Link<\/a><\/div>')

                // Add the marker to map
                map.addOverlay(marker);
            }
        }

    </script>
    <br />
  <body onload="initialize()" onunload="GUnload()">
    <div id="map_canvas" style="width: 500px; height: 300px"></div>
  </body>

ASPX.CS File

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.Xml;
using System.IO;
using System.Xml.Xsl;
using System.Text;

public partial class SearchPage2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT * FROM tbl_Member WHERE Member_Gender = @GenderP AND Member_Smoker_Status = @SmokeP AND Journey_Offering = @JourneyP";

//the sql statement above doesnt work as I have not declared the parameters and I dont know how to - this is where i am stuck.

        command.CommandType = CommandType.Text;
        command.Connection = myConn;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbl_Member");

        // Get a FileStream object
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("./Data2.xml"), false);
        // WriteXml method to write an XML document
        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
        myConn.Close();

        lblSelSmokeP.Text = Profile.SmokeP.ToString();
        lblSelGenderP.Text = Profile.GenderP.ToString();
        lblSelJourneyP.Text = Profile.JourneyP.ToString(); 

        }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT Member_Address FROM tbl_Member";
        command.CommandType = CommandType.Text;
        command.Connection = myConn;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbl_Member");

        // Get a FileStream object
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("./out1.xml"), false);
        // WriteXml method to write an XML document
        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
        myConn.Close();

        XmlDocument sourceDocument = new XmlDocument();
        sourceDocument.Load(Server.MapPath("./out1.xml"));

        XmlDocument outputDocument = new XmlDocument();
        XmlElement markers = outputDocument.CreateElement("markers", "");
        outputDocument.AppendChild(markers);
        // use ChildNodes[1] if a <?xml version='1.0' encoding='utf-8'?> is in data2.xml, otherwise ChildNodes[0]
        foreach (XmlNode node in sourceDocument.ChildNodes[0].ChildNodes)
        {
            XmlElement child = outputDocument.CreateElement("marker", "");
            XmlAttribute att = outputDocument.CreateAttribute("address", "");
            att.Value = node.ChildNodes[0].InnerText;
            child.Attributes.Append(att);
            markers.AppendChild(child);
        }
        outputDocument.Save(Server.MapPath("./out2.xml"));
    }
    
}

Open in new window

0
Jens FiedererTest Developer/ValidatorCommented:
Much nicer to see what you are really doing!

To execute:

command.CommandText = "SELECT * FROM tbl_Member WHERE Member_Gender = @GenderP AND Member_Smoker_Status = @SmokeP AND Journey_Offering = @JourneyP";

You want to add (as in my previous example, assuming your Profile code works)
        SqlParameter param0 = new SqlParameter("@GenderP", SqlDbType.VarChar);
        param0.Value = Profile.GenderP.ToString();
        command.Parameters.Add(param0);

        SqlParameter param1 = new SqlParameter("@SmokeP ", SqlDbType.VarChar);
        param1.Value = Profile.SmokeP .ToString();
        command.Parameters.Add(param1);

        SqlParameter param2 = new SqlParameter("@ JourneyP", SqlDbType.VarChar);
        param2.Value = Profile. JourneyP.ToString();
        command.Parameters.Add(param2);
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
xmalcolmAuthor Commented:
Hi Jens,

That helped a lot. I got it work now. I have attached my code to this response. Now the only thing I have left to do in terms of returning results is how do I design the query if I want non-matching values to be selected. For example

A user may have gender preference as Both so the current query will only return results which has 'both' as their preference instead of people who are Male and Female.
Similarly a user saying he/she is offering a lift means the query should return people who fall under the category 'both' or 'seeking' a lift. Any ideas how I could do that?
public partial class SearchPage2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT * FROM tbl_Member WHERE Member_Gender LIKE @GenderP AND Member_Smoker_Status LIKE @SmokeP AND Journey_Offering LIKE @JourneyP";

        SqlParameter param0 = new SqlParameter("@GenderP", SqlDbType.VarChar);
        param0.Value = Profile.GenderP.ToString();
        command.Parameters.Add(param0);

        SqlParameter param1 = new SqlParameter("@SmokeP", SqlDbType.VarChar);
        param1.Value = Profile.SmokeP.ToString();
        command.Parameters.Add(param1);

        SqlParameter param2 = new SqlParameter("@JourneyP", SqlDbType.VarChar);
        param2.Value = Profile.JourneyP.ToString();
        command.Parameters.Add(param2);

        command.CommandType = CommandType.Text;
        command.Connection = myConn;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbl_Member");

        // Get a FileStream object
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("./Data2.xml"), false);
        // WriteXml method to write an XML document
        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
        myConn.Close();

        lblSelSmokeP.Text = Profile.SmokeP.ToString();
        lblSelGenderP.Text = Profile.GenderP.ToString();
        lblSelJourneyP.Text = Profile.JourneyP.ToString(); 

        }

Open in new window

0
Jens FiedererTest Developer/ValidatorCommented:
You are using a "LIKE" comparison already, so when they don't care, just set the parameter to '%', which matches anything.
0
xmalcolmAuthor Commented:
Hi Jens,

I will do that but how do I account for non-matching values? E.g. I dont want the query to return results matching whose preferences are to seek a lift to the user who is also seeking a lift. That info is of no use to the user. Instead the query needs to return members who have selected either both or offering a lift if the user using the system has a preference of only seeking a lift.
0
Jens FiedererTest Developer/ValidatorCommented:
You can use or clauses with extra parameters.

So if your CommandText has "....AND (Journey_Offering  = @JourneyP1 OR Journey_Offering = @JourneyP2)..."

(you don't really need LIKE with this approach)

You can do, if the user needs to find an offer
        SqlParameter JourneyP1= new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "OFFER";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2= new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "OFFER"
        command.Parameters.Add(JourneyP2);

or if the user needs to find a seeker

        SqlParameter JourneyP1= new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "SEEK";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2= new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "SEEK"
        command.Parameters.Add(JourneyP2);

or if the user is flexible

        SqlParameter JourneyP1= new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "SEEK";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2= new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "OFFER"
        command.Parameters.Add(JourneyP2);

(obviously this assumes the values are "SEEK" and "OFFER", they might be "S" and "O" or whatever, but the approach is the same)
0
xmalcolmAuthor Commented:
So are you suggesting having an If statement and does this SQL statement and code look alrite? I know I am doing something wrong here.

     SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open();
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT * FROM tbl_Member WHERE (Member_Gender = @GenderP1 OR Member_Gender = @GenderP2) AND (Member_Smoker_Status = @SmokeP1 OR Member_Smoker_Status = @SmokeP2) AND (Journey_Offering  = @JourneyP1 OR Journey_Offering = @JourneyP2)";

        SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "Offering";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "Offering";
        command.Parameters.Add(JourneyP2);

SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "Seeking";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "Seeking";
        command.Parameters.Add(JourneyP2);

SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "Both";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "Both";
        command.Parameters.Add(JourneyP2);
0
Jens FiedererTest Developer/ValidatorCommented:
Exactly, although to deal with "Both" on BOTH sides, you either need THREE parameters or to stick with the LIKE(instead of '=') and '%'as below:

if (lblSelJourneyP.Text == "Both") {
        SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "%";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "%";
        command.Parameters.Add(JourneyP2);
} else if (lblSelJourneyP.Text == "Seeking") {
        SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "Offering";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "Both";
        command.Parameters.Add(JourneyP2);
} else if (lblSelJourneyP.Text == "Offering") {
        SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
        JourneyP1.Value = "Seeking";
        command.Parameters.Add(JourneyP1);

        SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
        JourneyP2.Value = "Both";
        command.Parameters.Add(JourneyP2);

}
0
xmalcolmAuthor Commented:
Hey Jens,

Thanks very much for your help it all works now.

Now I am facing a different problem. My google map doesnt pick up on the data produced by the query. It used to work before but i obviously made a few changes since then. Could you possibly look at the code and let me know if I am doing anything silly which is causing the google map not to update?

Could it be related to saving the password wen logging through firefox or related to session or cookies?
aspx code

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="SearchPage2.aspx.cs" Inherits="SearchPage2" Title="UoB Search Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
<h3>Welcome to the Search Page</h3>
  <!DOCTYPE html "-//W3C//DTD XHTML 1.0 Strict//EN" 
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=ABQIAAAAU9bBU2DSiOtbnM-fTucRPBR77MpkYO6YK8ATw9mBbNJauBgDHxQm2lVmfBMI4NVCkSskT1X-N4T_EQ"
            type="text/javascript"></script>

    <asp:Label ID="lblSearchRadius" runat="server" Text="Search Radius: " Font-Size="8" Font-Bold="true"></asp:Label>
    <asp:DropDownList ID="ddlSelectRadius" runat="server" Font-Size="8">
    <asp:listitem Enabled="true">Within 1 mile</asp:listitem>
            <asp:listitem>Within 10 mile</asp:listitem>
            <asp:listitem>Within 20 mile</asp:listitem>
    </asp:DropDownList>
    <br />
    <br />
    Smoking Preference Currently Selected: <asp:Label ID="lblSelSmokeP" runat="server" Font-Size="8" Font-Bold="true"></asp:Label>
    <br />
    <br />
    Gender Preference Currently Selected: <asp:Label ID="lblSelGenderP" runat="server" Text="" Font-Size="8" Font-Bold="true"></asp:Label>
    <br />
    <br />
    Journey Preference Currently Selected: <asp:Label ID="lblSelJourneyP" runat="server" Text="" Font-Size="8" Font-Bold="true"></asp:Label>
    <br />
    <br />
    <asp:HyperLink Font-Underline="true" Text="Change Your Preference" ID="hy1" runat="server" NavigateUrl="~/SearchPage.aspx"></asp:HyperLink>
    <br />
    <br />
    <asp:Button ID="btnSearch" runat="server" Text="Search" Font-Size="8" OnClick="btnSearch_Click"/>
    <br />
    <br />
  
    <script type="text/javascript">

        var map;
        var geocoder;
        var xml;
        var markers;
        var address;
        
        function createMarker(point, html) {
            var marker = new GMarker(point);
            GEvent.addListener(marker, "click", function() {
            // instructs the marker to open an info window
            marker.openInfoWindowHtml(html);
            });
            return marker;
        }

        function initialize() {
            if (GBrowserIsCompatible()) {
                var map = new GMap2(document.getElementById("map_canvas"));
                map.setCenter(new GLatLng(51.066573,-0.313765), 09);
                map.setUIToDefault();
                
                // Create new geocoding object
                geocoder = new GClientGeocoder();

                // Download the data in out2.xml and load it on the map.
                GDownloadUrl("./out2.xml", function(data) {
                    xml = GXml.parse(data);
                    markers = xml.documentElement.getElementsByTagName("marker");
                    for (var i = 0; i < markers.length; i++) {
                        address = markers[i].getAttribute("address");
                        geocoder.getLocations(address, addToMap);
                    }
                });
            }
            
            // This function adds the point to the map

            function addToMap(response) {
                // Retrieve the object
                place = response.Placemark[0];

                // Retrieve the latitude and longitude
                point = new GLatLng(place.Point.coordinates[1],
                          place.Point.coordinates[0]);

                // Create a marker
                marker = new GMarker(point);
                var marker = createMarker(point,'<div style="width:240px">Contact This Person <a href="MessagingPage.aspx">Link<\/a><\/div>')

                // Add the marker to map
                map.addOverlay(marker);
            }
        }

    </script>
    <br />
  <body onload="initialize()" onunload="GUnload()">
    <div id="map_canvas" style="width: 500px; height: 300px"></div>
  </body>
   <br />
</asp:Content>



aspx.cs code


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.Xml;
using System.IO;
using System.Xml.Xsl;
using System.Text;

public partial class SearchPage2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        lblSelSmokeP.Text = Profile.SmokeP.ToString();
        lblSelGenderP.Text = Profile.GenderP.ToString();
        lblSelJourneyP.Text = Profile.JourneyP.ToString();

        SqlConnection myConn = new SqlConnection();
        myConn.ConnectionString = "Data Source=XG7;Initial Catalog=Car_Share_XG;Integrated Security=True";
        myConn.Open(); // this statement allows myConn (which is the name I gave to my connection) to connect
        SqlCommand command = new SqlCommand(); 

        command.CommandText = "SELECT Member_Address FROM tbl_Member WHERE (Member_Gender LIKE @GenderP1 OR Member_Gender LIKE @GenderP2) AND (Member_Smoker_Status LIKE @SmokeP1 OR Member_Smoker_Status LIKE @SmokeP2) AND (Journey_Offering  LIKE @JourneyP1 OR Journey_Offering LIKE @JourneyP2)";
        
  
        // Declaration of parameters and if statements for Journey preference selection

        if (lblSelJourneyP.Text == "Both")
        {
            SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
            JourneyP1.Value = "%";
            command.Parameters.Add(JourneyP1);

            SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
            JourneyP2.Value = "%";
            command.Parameters.Add(JourneyP2);
        }

        else if (lblSelJourneyP.Text == "Seeking")
        {
            SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
            JourneyP1.Value = "Offering";
            command.Parameters.Add(JourneyP1);

            SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
            JourneyP2.Value = "Both";
            command.Parameters.Add(JourneyP2);
        }
        else if (lblSelJourneyP.Text == "Offering")
        {
            SqlParameter JourneyP1 = new SqlParameter("@JourneyP1", SqlDbType.VarChar);
            JourneyP1.Value = "Seeking";
            command.Parameters.Add(JourneyP1);

            SqlParameter JourneyP2 = new SqlParameter("@JourneyP2", SqlDbType.VarChar);
            JourneyP2.Value = "Both";
            command.Parameters.Add(JourneyP2);

        }
        
        // End of parameter declaratation and if statement for Journey Preference selection

        if (lblSelSmokeP.Text == "Both")
        {
            SqlParameter SmokeP1 = new SqlParameter("@SmokeP1", SqlDbType.VarChar);
            SmokeP1.Value = "%";
            command.Parameters.Add(SmokeP1);

            SqlParameter SmokeP2 = new SqlParameter("@SmokeP2", SqlDbType.VarChar);
            SmokeP2.Value = "%";
            command.Parameters.Add(SmokeP2);
        }
        else if (lblSelSmokeP.Text == "Smoker")
        {
            SqlParameter SmokeP1 = new SqlParameter("@SmokeP1", SqlDbType.VarChar);
            SmokeP1.Value = "Smoker";
            command.Parameters.Add(SmokeP1);

            SqlParameter SmokeP2 = new SqlParameter("@SmokeP2", SqlDbType.VarChar);
            SmokeP2.Value = "Both";
            command.Parameters.Add(SmokeP2);
        }
        else if (lblSelSmokeP.Text == "Non-Smoker")
        {
            SqlParameter SmokeP1 = new SqlParameter("@SmokeP1", SqlDbType.VarChar);
            SmokeP1.Value = "Non-Smoker";
            command.Parameters.Add(SmokeP1);

            SqlParameter SmokeP2 = new SqlParameter("@SmokeP2", SqlDbType.VarChar);
            SmokeP2.Value = "Both";
            command.Parameters.Add(SmokeP2);

        }

        // End of parameter declaration and if statement for Smoking preference selection
        // Declaration of parameters and if statement for Gender Preference selection

        if (lblSelGenderP.Text == "Both")
        {
            SqlParameter GenderP1 = new SqlParameter("@GenderP1", SqlDbType.VarChar);
            GenderP1.Value = "%";
            command.Parameters.Add(GenderP1);

            SqlParameter GenderP2 = new SqlParameter("@GenderP2", SqlDbType.VarChar);
            GenderP2.Value = "%";
            command.Parameters.Add(GenderP2);
        }
        else if (lblSelGenderP.Text == "Male")
        {
            SqlParameter GenderP1 = new SqlParameter("@GenderP1", SqlDbType.VarChar);
            GenderP1.Value = "Male";
            command.Parameters.Add(GenderP1);

            SqlParameter GenderP2 = new SqlParameter("@GenderP2", SqlDbType.VarChar);
            GenderP2.Value = "Both";
            command.Parameters.Add(GenderP2);
        }
        else if (lblSelGenderP.Text == "Female")
        {
            SqlParameter GenderP1 = new SqlParameter("@GenderP1", SqlDbType.VarChar);
            GenderP1.Value = "Female";
            command.Parameters.Add(GenderP1);

            SqlParameter GenderP2 = new SqlParameter("@GenderP2", SqlDbType.VarChar);
            GenderP2.Value = "Both";
            command.Parameters.Add(GenderP2);

        }

        // End of parameter declaration and if statement for Gender preference selection

        // We are going to use the sql DataAdapterClass to build a new dataset which will then be filled with data
        // from tbl_Member to the method called ds

        command.CommandType = CommandType.Text;
        command.Connection = myConn;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        da.Fill(ds, "tbl_Member");


        // Get a FileStream object

        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("./Out1.xml"), false);

        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
        myConn.Close();

        XmlDocument sourceDocument = new XmlDocument();
        sourceDocument.Load(Server.MapPath("./out1.xml"));

        XmlDocument outputDocument = new XmlDocument();
        XmlElement markers = outputDocument.CreateElement("markers", "");
        outputDocument.AppendChild(markers);
        // use ChildNodes[1] if a <?xml version='1.0' encoding='utf-8'?> is in data2.xml, otherwise ChildNodes[0]
        foreach (XmlNode node in sourceDocument.ChildNodes[0].ChildNodes)
        {
            XmlElement child = outputDocument.CreateElement("marker", "");
            XmlAttribute att = outputDocument.CreateAttribute("address", "");
            att.Value = node.ChildNodes[0].InnerText;
            child.Attributes.Append(att);
            markers.AppendChild(child);
        }
        outputDocument.Save(Server.MapPath("./out2.xml"));

    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        
    }

}

Open in new window

0
xmalcolmAuthor Commented:
Dont worry I sorted it out now - thanks for all your help Jens!
0
Jens FiedererTest Developer/ValidatorCommented:
Great...and congrats on sorting out that other issue.
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.