6 Replies - 7358 Views - Last Post: 07 October 2010 - 11:38 AM Rate Topic: -----

#1 jagman82  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 24
  • Joined: 24-February 10

Populate dropdown list containing 2 columns from MySQL

Posted 05 October 2010 - 11:09 AM

I am needing to pull two columns from MySQL into the Drop Down List which will activate the onselectedIndexChange for the control on postback. I am unable to hard code because the tool it is linked to depends on when the end user makes an entry. I am unable to get my columns to display in the dropdown. Obviously I am missing something.

        protected void Page_Load(object sender, EventArgs e)
        {
            
            DatabaseDAL databaseDal = new DatabaseDAL();
            String query;

            if (!Page.IsPostBack)
            {
              

                query = "SELECT we_date, notebook_name " +
                        "FROM donald_notebook.notebook " +
                        "WHERE @areaId = areaId ";



      databaseDal.createCommand(query);
      databaseDal.addParameter<Int32>("@areaId", areaId, DbType.Int32);
               
                databaseDal.openConnection();

                databaseDal.closeConnection();



Jagman

This post has been edited by jagman82: 05 October 2010 - 11:10 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Populate dropdown list containing 2 columns from MySQL

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Populate dropdown list containing 2 columns from MySQL

Posted 05 October 2010 - 11:54 AM

where is your code for putting the data into the dropdownlist?
Was This Post Helpful? 0
  • +
  • -

#3 sl4ck3r  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 285
  • Joined: 22-September 07

Re: Populate dropdown list containing 2 columns from MySQL

Posted 05 October 2010 - 01:00 PM

I usually put it in a function encase you need to use it somewhere else.. plus it makes it more readable.
Code in aspx.cs:
        private void populateSkills()
        {
            DataTable dtSkills = ((ScoringDAL)(Session["DAL"])).GetSkills();
            foreach (DataRow row in dtSkills.Rows)
            {
                ddlSkill.Items.Add(new ListItem(row["skill_ab"].ToString(), row["skill_id"].ToString()));
            }
        }


Code in DAL:
    public DataTable GetSkills()
    {
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(this._connectionString))
        using (SqlCommand comm = new SqlCommand("SELECT * FROM [tbl_skill]", conn))
        {
            conn.Open();
            dt.Load(comm.ExecuteReader());
        }
        return dt;
    }


Was This Post Helpful? 0
  • +
  • -

#4 jagman82  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 24
  • Joined: 24-February 10

Re: Populate dropdown list containing 2 columns from MySQL

Posted 06 October 2010 - 07:08 AM

What about using concatenate theory for this portion of the bringing the two columns into the drop down list?

Jagman
Was This Post Helpful? 3
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Populate dropdown list containing 2 columns from MySQL

Posted 06 October 2010 - 12:46 PM

View Postjagman82, on 06 October 2010 - 10:08 AM, said:

What about using concatenate theory for this portion of the bringing the two columns into the drop down list?

Jagman


Agreed. A DrowDownList doesn't have "columns". So either you can make your own control, or you can concatenate the two fields together into one field. This could be done in code, or it could be done in the SQL query.
Was This Post Helpful? 0
  • +
  • -

#6 jagman82  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 24
  • Joined: 24-February 10

Re: Populate dropdown list containing 2 columns from MySQL

Posted 07 October 2010 - 06:19 AM

I have tried the concatenate for the SQL and it did nothing. I am trying to populate the dropdown on page load. Is it possible to get some help here? This is my first bout with concatenation of anything in programming. The dropdown list needs to populate with two columns and be based upon a matching areaId related to the notebook being pulled from the database.

        protected void Page_Load(object sender, EventArgs e)
        {
            DatabaseDAL databaseDal = new DatabaseDAL();
            String query;

            if (!Page.IsPostBack)
            {

                // parse area id from URL
                Int32.TryParse(Request.QueryString["id"], out areaId);

                #region DisplaysDateAndNotebookNameFromAreaId

                query = "SELECT CONCAT(notebook_name, ' - ', CONVERT(we_date, char)) " +
                        "FROM donald_notebook.notebook " +
                        "WHERE id = @areaId ";


                databaseDal.createCommand(query);
                databaseDal.addParameter("@areaId", areaId, DbType.Int32);

                try
                {
                    databaseDal.openConnection();

                    DbDataReader reader = databaseDal.Command.ExecuteReader();

                    while (reader.Read())
                    {

                        reader.Close();
                    }
                }

                finally
                {
                    databaseDal.closeConnection();
                }


Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Populate dropdown list containing 2 columns from MySQL

Posted 07 October 2010 - 11:38 AM

try this...

protected void Page_Load(object sender, EventArgs e)
{
    DatabaseDAL databaseDal = new DatabaseDAL();
    String query;

    if (!Page.IsPostBack)
    {

        // parse area id from URL
        Int32.TryParse(Request.QueryString["id"], out areaId);

        #region DisplaysDateAndNotebookNameFromAreaId

        query = "SELECT notebook_name + ' - ' + CONVERT(we_date, char)) " +
                "FROM donald_notebook.notebook " +
                "WHERE id = @areaId ";

        databaseDal.createCommand(query);
        databaseDal.addParameter("@areaId", areaId, DbType.Int32);

        try
        {
            databaseDal.openConnection();

            using (DbDataReader reader = databaseDal.Command.ExecuteReader())
            {
                 while (reader.Read())
                 {
                      ddlSkill.Items.Add(new ListItem(reader.GetString(0)));
                 }
            }
        }

        finally
        {
            databaseDal.closeConnection();
        }

        #endregion
    }
}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1