Solved

Using a Drop down box with MySql

Posted on 2015-02-16
6
115 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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.

789 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