Problem with DropDownList

**Miguel61** · Jan 27, 2012 07:10

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:

Code:

   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 :

left(?,2)
like '%?%'
like '%'?'%'

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

Can u help me?
Thanks in advance.

Code:

<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>

**imaginekitty** · Jan 27, 2012 07:30

What is the purpose of "WHERE 1"?

**Miguel61** · Jan 27, 2012 07:47

Originally Posted by imaginekitty

What is the purpose of "WHERE 1"?

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

Code:

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

**imaginekitty** · Jan 27, 2012 07:58

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.

**Miguel61** · Jan 27, 2012 08:09

Ok, thanks:

Code:

<%@ 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>

**Miguel61** · Jan 31, 2012 05:12

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

**Serenarules** · Jan 31, 2012 05:18

You're string concat is wrong. You'd be better off in any regard using the command.Parameters collection as described here:
http://msdn.microsoft.com/en-us/libr...arameters.aspx

**Miguel61** · Jan 31, 2012 05:22

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

**wwb_99** · Jan 31, 2012 06:55

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.

**Miguel61** · Jan 31, 2012 07:07

Sorry sir, but I still do not understand... this article http://msdn.microsoft.com/en-us/libr...arameters.aspx is reference for database SQLServer, my db is MySQL...

**wwb_99** · Jan 31, 2012 11:24

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

**Miguel61** · Jan 31, 2012 14:40

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

Code:

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 '?'...

**wwb_99** · Feb 1, 2012 11:05

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.

**Miguel61** · Feb 1, 2012 11:13

Thanks for your help, this is the solution:

Code:

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

User Tag List

Thread: Problem with DropDownList

Thread Tools

Display

Problem with DropDownList

Bookmarks

Bookmarks

Posting Permissions