Retrieve items from one column in a list using CAML and bind that data to dropdownlist

Question

I want to retrieve a column items from the list. My list contains two columns. I want to retrieve first column items and those items should bind to the drop down list. I am doing this in a web part. I want to extract first column details using CAML (if any better way please suggest me). This is my list

 Course Name   Duration   
-----------------------
  Sharepoint   60days 
  MSBI         45days 
  .Net         90days 
  Java         50days 

How to achieve it? I tried like this.. but not succeeded.. :(

        DropDownList drpList;
        protected override void CreateChildControls()
        {
            drpList = new DropDownList();
            SPSite site = SPContext.Current.Site;
            SPWeb web = site.RootWeb;
            ArrayList myarr = new ArrayList();
            myarr.Add(1);
            myarr.Add(2);
            SPSiteDataQuery dataquery = new SPSiteDataQuery();
            dataquery.Query = "<OrderBy><FieldRef Name='Course Name' /></OrderBy>";
            DataTable dt = web.GetSiteData(dataquery);
            drpList.DataTextField = "Course Name";
            drpList.DataValueField = "Course Name";
            drpList.DataSource = dt;
            drpList.DataBind();
            Controls.Add(drpList);
        }

Answer 1

I would say you are missing parameters from SPSiteDataQuery, as I'm wondering how would you target your list/content type - because the current query simply attempts to select it from all lists in the current SPWeb. Beside it is recomanded to use InternalName for fields not DisplayName as this could change! Use Nullable=TRUE for every field which could be Empty as otherwise is not returned in results.

You should do it as follows:

     //Ask for all lists of a certain template (here PAGES from Publishing Sites)
     query.Lists = "<Lists ServerTemplate=\"850\" />";

     // Get the Title (Last Name) and FirstName fields.
     query.ViewFields = "<FieldRef Name=\"Duration\" />" +
                        "<FieldRef Name=\"CourseName\" Nullable=\"TRUE\" Type=\"Text\"/>";

// Set the sort order.
         query.Query = "<OrderBy>" + 
                           "<FieldRef Name=\"CourseName\" />" + 
                       "</OrderBy>";

Answer 2

 SPList objProjectList = objWeb.Lists["ProjectItems"];                        
 SPQuery getProject = new SPQuery();
 getProject.ViewXml = "<Query><Where><IsNotNull><FieldRef Name='Title' /></IsNotNull></Where></Query>";
 SPListItemCollection getCollection = objProjectList.GetItems(getProject);
 if (getCollection != null && getCollection.Count > 0)
 {
       ddlCurrentPro.DataSource = getCollection.GetDataTable();
       ddlCurrentPro.DataTextField = "Title";
       ddlCurrentPro.DataBind();
 }