SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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>

  2. #2
    Chopped Liver bronze trophy imaginekitty's Avatar
    Join Date
    Aug 2007
    Location
    Pennsyltucky
    Posts
    1,494
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What is the purpose of "WHERE 1"?

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by imaginekitty View Post
    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 ?"

  4. #4
    Chopped Liver bronze trophy imaginekitty's Avatar
    Join Date
    Aug 2007
    Location
    Pennsyltucky
    Posts
    1,494
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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>

  6. #6
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks but I do not understand your suggestion.... as it applies to DropDownList?

  9. #9
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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...

  11. #11
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Works exactly the same, just use MySql classes rather than Sql Server classes.

  12. #12
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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 '?'...

  13. #13
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help, this is the solution:

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •