Solved

Using a Drop down box with MySql

Posted on 2015-02-16
6
110 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now