Solved

Using a Drop down box with MySql

Posted on 2015-02-16
6
116 Views
Last Modified: 2015-02-17
I'm trying to add a drop down box to a web page that displays the orderID field from the database. I'm getting an error message: "Error      1      An object reference is required for the non-static field, method, or property 'System.Web.UI.WebControls.BaseDataBoundControl.DataSource.get'"

public static bool orderDropDown(string orderID)
    {
        {

            using (MySqlConnection connection = new MySqlConnection("server=*****;user id=*****;database=tcamach_WSC;password=******;persistsecurityinfo=True"))
            {

                connection.Open();
                //string query = "Insert into Employee (empID, empPermissions, userName, empEmail, password, empLName, empFName, empPhone) values (@empFName, @empLName, @userName, @empEmail, @password, @empPermissions, @empID, @empPhone);";
                using (var cmd = new MySqlCommand("SELECT * FROM Order", connection))
                // set the command text of the command object0
                using (var reader = cmd.ExecuteReader())
                {

                    if (reader.HasRows)
                    {
                        DropDownList.DataSource = reader;
                        DropDownList.DataValueField = "orderID";
                        DropDownList.DataTextField = "orderID";
                        DropDownList.DataBind();

                        
                        connection.Close();
                        

                    }



                }





            }


        }

    }

Open in new window

0
Comment
Question by:Robin Harris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 40613420
Try loading the data into a datatable for binding

   DataTable dt  = new DataTable();
   dt.Load(cmd.ExecuteReader());
   connection.Close();

   DropDownList1.DataSource = dt;

Open in new window

0
 

Author Comment

by:Robin Harris
ID: 40613447
I added that to my code and have a different error message: Error      1      'System.Web.UI.WebControls.DropDownList' does not contain a definition for 'Datasource'

      
public static bool orderDropDown(string orderID)
    {
        {

            using (MySqlConnection connection = new MySqlConnection("server=****;user id=******;database=******;password=*****;persistsecurityinfo=True"))
            {

                connection.Open();
                //string query = "Insert into Employee (empID, empPermissions, userName, empEmail, password, empLName, empFName, empPhone) values (@empFName, @empLName, @userName, @empEmail, @password, @empPermissions, @empID, @empPhone);";
                using (var cmd = new MySqlCommand("SELECT * FROM Order", connection))
                // set the command text of the command object0
                using (var reader = cmd.ExecuteReader())
                {

                    if (reader.HasRows)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(cmd.ExecuteReader());
                        connection.Close();


                        DropDownList.Datasource = dt;
                        return true;
                        //DropDownList1.DataSource = dt;
                        //DropDownList.DataSource = reader;
                        //DropDownList.DataValueField = "orderID";
                        //DropDownList.DataTextField = "orderID";
                        //DropDownList.DataBind();

                        
                        //connection.Close();
                        

                    }



                }





            }


        }

    }

Open in new window

0
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 40613478
You still need to set the fields DataValueField and DataTextField as well DataBind

To get this working I have removed the "using" commands and there is no error checking. Once you are happy this is working I would add them again


public static bool orderDropDown(string orderID)
{
         MySqlConnection connection = new MySqlConnection("server=****;user id=******;database=******;password=*****;persistsecurityinfo=True"))
         connection.Open();
         var cmd = new MySqlCommand("SELECT * FROM Order", connection))
         var reader = cmd.ExecuteReader()
         if (reader.HasRows)
         {
              DataTable dt = new DataTable();
              dt.Load(reader);
              DropDownList.Datasource = dt;
              DropDownList.DataValueField = "orderID";
              DropDownList.DataTextField = "orderID";
              DropDownList.DataBind();
        }
        connection.Close();
        return true;
    }

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Robin Harris
ID: 40614425
Ok I no longer receive the error mentioned above. Now I'm getting a MySQL syntax error at the line var reader = cmd.ExcuteReader();

 public static bool orderDropDown(DropDownList ddlOrderID)
{
    MySqlConnection connection = new MySqlConnection("server=****;user id=******;database=*****;password=****;persistsecurityinfo=True");
         connection.Open();
         var cmd = new MySqlCommand("SELECT * FROM Order", connection);
         var reader = cmd.ExecuteReader();
        
        if (reader.HasRows)
         {
              DataTable dt = new DataTable();
              dt.Load(reader);
              ddlOrderID.DataSource = dt;
              ddlOrderID.DataValueField = "orderID";
              ddlOrderID.DataTextField = "orderID";
              ddlOrderID.DataBind();
       }
        connection.Close();
        return true;
    }

Open in new window

0
 

Author Comment

by:Robin Harris
ID: 40614431
Sorry here is a portion of the error message:


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 'Order' at line 1.

Error number 1064
 public static bool orderDropDown(DropDownList ddlOrderID)
{
    MySqlConnection connection = new MySqlConnection("server=****;user id=******;database=*****;password=****;persistsecurityinfo=True");
         connection.Open();
         var cmd = new MySqlCommand("SELECT * FROM Order", connection);
         var reader = cmd.ExecuteReader();
        
        if (reader.HasRows)
         {
              DataTable dt = new DataTable();
              dt.Load(reader);
              ddlOrderID.DataSource = dt;
              ddlOrderID.DataValueField = "orderID";
              ddlOrderID.DataTextField = "orderID";
              ddlOrderID.DataBind();
       }
        connection.Close();
        return true;
    }

Open in new window

0
 

Author Comment

by:Robin Harris
ID: 40614606
I figured it out. My Query was causing the issue. I changed it to "SELECT orderID FROM `Order`". Thanks for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Where is this file? 3 33
Formating field inside mysql query 2 31
How can I do Unit Testing with Session ? 1 23
Using sample Autorize.net c# simple example 1 26
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question