Solved

Using a Drop down box with MySql

Posted on 2015-02-16
6
114 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
Backup Your Microsoft Windows Server®

Backup 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to import sql database into mysql workbench 18 34
C# Desktop Application 3 31
.NET 2008 VB and C# 6 27
Incorrect definition of table mysql.proc 7 11
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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