Problem with DropDownList

Hi there, I need your appreciated help.

I need populate the dropdownlist #2 with the value selected in the dropdownlist #1.

But I have problem with this query in MySQL:


   SelectCommand="SELECT * FROM tbl_1 WHERE 1 AND _SU LIKE ?"

I need extract with query only first two characters in the string DataValueField.
If DataValueField it’s XQ00 I need in the query where XQ.

I try this in the SelectCommand query :

  1. left(?,2)
  2. like ‘%?%’
  3. like ‘%’?‘%’

But the aspnet page response with error and the dropdownlist #2 not populate.

Can u help me?
Thanks in advance.


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnMySQL %>"
    ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>"
    SelectCommand="SELECT * FROM tbl_1 WHERE 1 LIMIT 0,4"
    DataSourceMode="DataSet">
</asp:SqlDataSource>

<p>dropdownlist #1
<asp:DropDownList ID="DropDownList1" runat="server" Visible="true"
   DataSourceID="SqlDataSource1"
   AutoPostBack="True"
   DataTextField="_DU"
   DataValueField="_SU">
<asp:ListItem>SELECT ONE VALUE</asp:ListItem>
</asp:DropDownList>
    </p>


<asp:sqldatasource ID="SqlDataSource2" runat="server"
   ConnectionString="<%$ ConnectionStrings:ConnMySQL %>"
   ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>"
   SelectCommand="SELECT * FROM tbl_1 WHERE 1 AND _SU like ?"
   DataSourceMode="DataSet">
<selectparameters>
<asp:controlparameter Name="_SZ" ControlID="DropDownList1" PropertyName="SelectedValue" Direction="Input" />
</selectparameters>
</asp:sqldatasource>

<p>dropdownlist #2
<asp:DropDownList ID="DropDownList2" runat="server" Visible="true"
   DataSourceID="SqlDataSource2"
   AutoPostBack="True"
   DataTextField="_DU"
   DataValueField="_SZ">
</asp:DropDownList>
</p>


What is the purpose of “WHERE 1”? :confused:

I try this, but I don’t have changes in the output:


SelectCommand="SELECT * FROM tbl_1 WHERE _SU LIKE ?"

Why was it there in the first place? Is there a reason for it?

Can you show one of your final SQL statements? That may help to make sense of this.

Ok, thanks:



<%@ Page Language="C#" AutoEventWireup="false" %>
<%@ Import Namespace="System.IO" %> 
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%@ Import Namespace="System.Configuration" %>

<script runat="server">
    
    protected void Button1_Click(Object sender, EventArgs e)  { 
        if (Page.IsValid)  { 
            
            OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
            myConnectionString.Open();
            string cmdstr = "INSERT INTO tbl_insert (DropDownList1)VALUES('" + DropDownList1.Text + "')";
            OdbcCommand aCommand = new OdbcCommand(cmdstr, myConnectionString);
            OdbcDataReader aReader = aCommand.ExecuteReader();
            aReader.Close();
            myConnectionString.Close();

            Label1.Text = "OK";
                        
            }
    }


</script>

<form id="form1" runat="server">
    <div>
    
    <p>Date
    <asp:TextBox ID="myDate" runat=server>
    </asp:TextBox>
        <img src="/mySite/images/CALEN.GIF" alt="Open" align="absmiddle" style="cursor:hand" 
         onClick='popUpCalendar(this, document.forms[0].myDate, "dd/mm/yyyy")'>

    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
         ControlToValidate="myDate"
         ErrorMessage="Campo data" 
         ValidationExpression="^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\\d\\d$">
    </asp:RegularExpressionValidator>

    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
         ControlToValidate="myDate" 
         ErrorMessage="KO" >
    </asp:RequiredFieldValidator>
    </p>



<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnMySQL %>"
    ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>"
    SelectCommand="SELECT * FROM tbl_1 WHERE 1 LIMIT 0,4"
    DataSourceMode="DataSet">
</asp:SqlDataSource>

<p>DropDownList1
<asp:DropDownList ID="DropDownList1" runat="server" 
   Visible="true"
   DataSourceID="SqlDataSource1"
   AutoPostBack="True" 
   DataTextField="_DU" 
   DataValueField="_SZ">
<asp:ListItem>Values</asp:ListItem>
</asp:DropDownList>
    </p>




<asp:sqldatasource ID="SqlDataSource2" runat="server"
   ConnectionString="<%$ ConnectionStrings:ConnMySQL %>"
   ProviderName="<%$ ConnectionStrings:ConnMySQL.ProviderName %>"
   SelectCommand="SELECT * FROM tbl_1 WHERE _SZ LIKE ?" 
   DataSourceMode="DataSet">

<selectparameters>
<asp:controlparameter Name="_SZ" 
     ControlID="DropDownList1" 
     PropertyName="SelectedValue" 
     Direction="Input" />
</selectparameters>

</asp:sqldatasource>

<p>DropDownList2
<asp:DropDownList ID="DropDownList2" runat="server" 
   Visible="true"
   DataSourceID="SqlDataSource2"
   AutoPostBack="True" 
   DataTextField="_DU"
   DataValueField="_DU">
</asp:DropDownList>
</p>


    <asp:ImageButton ID="Button1" ImageUrl="/mySite/images/but_confirm.gif" runat=server Text="submit" OnClick="Button1_Click" />

    <br />
    <br />

    <asp:ValidationSummary ID="ValidationSummary1" runat=server HeaderText="errori" />

    <asp:Label ID="Label1" runat=server></asp:Label>
    </div>

</form>

I try this, but I have error… why?

SelectCommand=“SELECT * FROM tbl_1 WHERE _SU like ‘%’ + ? + ‘%’”

Server Error in ‘/’ Application.

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘+ ‘XQ00’ + ‘%’’ at line 1
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘+ ‘XQ00’ + ‘%’’ at line 1

You’re string concat is wrong. You’d be better off in any regard using the command.Parameters collection as described here:

thanks but I do not understand your suggestion… as it applies to DropDownList?

No, it refers to every time you touch a database. Using parameters in this case would force your string to be properly escaped and would prevent your database exception.

Sorry sir, but I still do not understand… this article http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx is reference for database SQLServer, my db is MySQL…

Works exactly the same, just use MySql classes rather than Sql Server classes.

Thanks, I try your suggestion but I don’t have resolved my problem.


using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class _Default : System.Web.UI.Page 
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlContinents.AppendDataBoundItems = true;
            OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
            String strQuery = "select ID, nome from tbl_login";
            OdbcCommand objCmd = new OdbcCommand(strQuery, myConnectionString);
            objCmd.CommandType = CommandType.Text;
            objCmd.CommandText = strQuery;

            try
            {
                myConnectionString.Open();
                ddlContinents.DataSource = objCmd.ExecuteReader();
                ddlContinents.DataTextField = "nome";
                ddlContinents.DataValueField = "ID";
                ddlContinents.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnectionString.Close();
                myConnectionString.Dispose();
            }
        }
    }


    protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(new ListItem("--Select Country--", ""));
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));
        ddlCountry.AppendDataBoundItems = true;    
        OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
        String strQuery = "select email from tbl_login " +
                           "where ID=?";
        OdbcCommand objCmd = new OdbcCommand(strQuery, myConnectionString);
        objCmd.Parameters.AddWithValue("ID", ddlContinents.SelectedItem.Value);
        objCmd.CommandType = CommandType.Text;
        objCmd.CommandText = strQuery;
        objCmd.Connection = myConnectionString;

        try
        {
            myConnectionString.Open();
            ddlCountry.DataSource = objCmd.ExecuteReader();
            ddlCountry.DataTextField = "email";
            ddlCountry.DataValueField = "email";
            ddlCountry.DataBind();
            if (ddlCountry.Items.Count > 1)
            {
                ddlCountry.Enabled = true;
            }
            else
            {
                ddlCountry.Enabled = false;
                ddlCity.Enabled = false;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConnectionString.Close();
            myConnectionString.Dispose();
        }
    }


    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCity.Items.Clear();
        ddlCity.Items.Add(new ListItem("--Select City--", ""));
        ddlCity.AppendDataBoundItems = true;
        OdbcConnection myConnectionString = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString);
        String strQuery = "select IP_Address from tbl_login " +
                                    "where email=?";
        OdbcCommand objCmd = new OdbcCommand(strQuery, myConnectionString);
        objCmd.Parameters.AddWithValue("email", ddlCountry.SelectedItem.Value);
        objCmd.CommandType = CommandType.Text;
        objCmd.CommandText = strQuery;
        objCmd.Connection = myConnectionString;

        try
        {
            myConnectionString.Open();
            ddlCity.DataSource = objCmd.ExecuteReader();
            ddlCity.DataTextField = "IP_Address";
            ddlCity.DataValueField = "IP_Address";
            ddlCity.DataBind();
            if (ddlCity.Items.Count > 1)
            {
                ddlCity.Enabled = true;
            }
            else
            {
                ddlCity.Enabled = false;
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConnectionString.Close();
            myConnectionString.Dispose();
        }
    }


    protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblResults.Text = "You Selected " +
                          ddlContinents.SelectedItem.Text + " -----> " +
                          ddlCountry.SelectedItem.Text + " -----> " +
                          ddlCity.SelectedItem.Text;
    }  


}

The problem it’s the ‘String strQuery’… you can not manipulate the variable ‘?’… :frowning:

Well, you didn’t name the parameter. I think you’d just have to use Parameter.Add() to handle things declared as ?.

If you made the parameter ?ID it might work better.

Thanks for your help, this is the solution:

String strQuery = "SELECT name FROM tbl_login" +
                  "WHERE name LIKE CONCAT('%',Left(?,2),'%');

:wink: