Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i need to Filter and display Google Map Markers from database based on DropDownList selection. for that I need to pass the selected value of DropDownList to the query.
What should i do in the following code to do the above task?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace trial2
{
    public partial class explore : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DropDownList1.DataBind();

                ListItem liMainArea = new ListItem("Select", "-1");
                DropDownList1.Items.Insert(0, liMainArea);

                DropDownList2.DataBind();

                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.DataBind();
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);

 
                DropDownList2.Enabled = false;
                DropDownList3.Enabled = false;

                
                
               
            }
            if(!this.IsPostBack)
            {
                DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA]");
                rptMarkers.DataSource = dt;
                rptMarkers.DataBind();
            }
            

            }

        private DataTable GetData(string query)
        {
            string conString = ConfigurationManager.ConnectionStrings["gisConnectionString"].ConnectionString;
            SqlCommand cmd = new SqlCommand(query);
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;

                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DropDownList1.SelectedIndex == 0)
            {            
                DropDownList2.Enabled = false;
                DropDownList2.DataBind();

                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.Enabled = false;
                DropDownList3.DataBind();

                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);
                 
            }
            else
            {
                
               
                DropDownList2.Enabled = true;

                
                DropDownList2.DataBind();
                
                ListItem liSubArea = new ListItem("Select", "-1");
                DropDownList2.Items.Insert(0, liSubArea);

                DropDownList3.SelectedIndex = 0;
                DropDownList3.Enabled = false;
 
            }

        }

       

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (DropDownList2.SelectedIndex == 0)
            {             
                DropDownList3.Enabled = false;

                DropDownList3.DataBind();
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);
            }
            else
            {
                DropDownList3.Enabled = true;
               
                
                DropDownList3.DataBind();
                
                ListItem liAmenities = new ListItem("Select", "-1");
                DropDownList3.Items.Insert(0, liAmenities);

            }
        }

        protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
        {

        }


    }

}
Posted

Never use string concatenation to pass parameters to SQL - you'll leave your code vulnerable to SQL Injection[^].

Instead, pass the parameters using the command's Parameters collection[^].

For example:
private DataTable GetData(string query, params object[] queryParameters)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["gisConnectionString"].ConnectionString))
    using (SqlCommand cmd = con.CreateCommand())
    {
        if (queryParameters != null && queryParameters.Length != 0)
        {
            IFormatProvider provider = CultureInfo.InvariantCulture;
            for (int index = 0; index < queryParameters.Length; index++)
            {
                string name = "@P" + index;
                string placeholder = "{" + index + "}";
                query = query.Replace(placeholder, name);
                cmd.Parameters.AddWithValue(name, queryParameters[index] ?? DBNull.Value);
            }
        }
        
        cmd.CommandText = query;
        
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
    }
}

DataTable dt = GetData(
    "select [Name], [Latitude], [Longitude] from [MAIN AREA]"
    + " where ({0} Is Null Or [Name] = {0})"
    + " And ({1} Is Null Or [Latitude] = {1})"
    + " And ({2} Is Null Or [Longitude] = {2})",
    DropDownList1.SelectedValue,
    DropDownList2.SelectedValue,
    DropDownList3.SelectedValue);
 
Share this answer
 
Comments
Member 13277556 25-Jun-17 5:19am    
values goes from Datatable dt to repeater ..... but it does not populate filtered marker .... plz help
we just need to add the following code in the selectedIndexChange event of the DropDownList1 so that it will return the filtered records.
C#
DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA] WHERE [Name] ='" + DropDownList1.SelectedItem.Text+"'");
            rptMarkers.DataSource = dt;
            rptMarkers.DataBind();
 
Share this answer
 
Comments
Richard Deeming 31-Mar-15 15:47pm    
Your code is still vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
Here is a solution using few if..else.. and StringBuilder -
Replace this section
C#
DataTable dt = this.GetData("select [Name], [Latitude], [Longitude] from [MAIN AREA]");
                rptMarkers.DataSource = dt;
                rptMarkers.DataBind();

with following-
C#
StringBuilder query=new StringBuilder("select [Name], [Latitude], [Longitude] from [MAIN AREA] where 1=1");
if(DropDownList1.SelectedIndex>0)
{
   query.Append(" and [Name]="+DropDownList1.SelectedValue.ToString());
}
if(DropDownList2.SelectedIndex>0)
{
   query.Append(" and [Latitude]="+DropDownList2.SelectedValue.ToString());
}
if(DropDownList3.SelectedIndex>0)
{
   query.Append(" and [Longitude]="+DropDownList3.SelectedValue.ToString());
}
DataTable dt = this.GetData(query);
rptMarkers.DataSource = dt;
rptMarkers.DataBind();


Hope, it helps !
If I have missed something, please let me know :)
 
Share this answer
 
v2
Comments
Richard Deeming 30-Mar-15 9:58am    
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900