?
Solved

Using a Drop down box with MySql

Posted on 2015-02-16
6
Medium Priority
?
121 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:Michael Fowler
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:
Michael Fowler earned 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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 this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

864 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