Link to home
Start Free TrialLog in
Avatar of perrytaylor
perrytaylor

asked on

How to databind a combobox in wpf with Ado.net dataset

I have a combobox that I would like to fill with the column names of my dataset.  I tried three different methods and none worked correctly.  The first method I tried to bind the combobox and the program would not compile due to an error on making the window.  The second method fills my combo box with seemingly random numbers in place of the column name strings.  The third method was sort of a last ditch attempt and it didn't fill anything.  I'm sure all the methods are simple errors and I would prefer to have the first method of databinding fixed cause it allows a little more flexibility.

1st method 
 
            public Window1()
        {
            InitializeComponent();
 
            Binding drop_down = new Binding();
 
            drop_down.Source = sim1.Tables[0].Columns[0].ColumnName;
 
            //drop_down.Path = new PropertyPath("
 
            cmbXAxis.SetBinding(ComboBox.DisplayMemberPathProperty, drop_down); 
        }
 
2nd method
 
            int i = 0;
            cmbXAxis.Items.Clear();
            foreach (DataColumn column in myDataSet.Tables["data"].Columns)
            {
 
                string colname = column.ColumnName.ToString();
                cmbXAxis.Items.Insert(i, colname);
                i = ++i;
            }
 
3rd Method
            cmbXAxis.DataContext = myDataSet.Tables["data"].Columns;
            cmbXAxis.DisplayMemberPath = myDataSet.Tables["data"].Columns.ToString();
            cmbXAxis.SelectedValuePath = myDataSet.Tables["data"].Columns["Count"].ToString();

Open in new window

Avatar of ToddBeaulieu
ToddBeaulieu
Flag of United States of America image

Ok, two things. 1) I'd try to do as little binding through code as possible, but that's my opinion. I just don't like to see a lot of code-behind reaching into individual controls when 99/100 times you can do it in XAML at the control declaration.

I like your 3rd approach the best and it was close to being correct. You need to set the ItemsSource property, not the DC and the DisplayMember is being set to the string representation of the Columns collection itself.

I tried this code and it works.

I created a property to bind the ComboBox to. I set the DataContext of the page to itself, which I like to do. It lets me expose various properties and quickly bind to them.
XAML
----
        <ComboBox 
            Height="23" 
            HorizontalAlignment="Left" 
            Margin="10,10,0,0" 
            Name="comboBox1" 
            VerticalAlignment="Top" 
            Width="120" 
            ItemsSource="{Binding MyDataColumns}"
            DisplayMemberPath="ColumnName"
            />
 
CODE BEHIND
----
 
public partial class Window1 : Window
{
    public Window1()
    {
        InitializeComponent();
        LoadData();
        DataContext = this;
    }
 
    private DataTable _dataTable = null;
 
    private void LoadData()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        SqlDataAdapter adapter = null;
        DataSet dataSet = null;
 
        try
        {
            cn = new SqlConnection("Data Source=MyMachine;Initial Catalog=MyDb;Integrated Security=True");
            cmd = new SqlCommand("select top 1 * from MyTable", cn);
            adapter = new SqlDataAdapter(cmd);
            dataSet = new DataSet();
 
            adapter.Fill(dataSet);
 
            _dataTable = dataSet.Tables[0];
        }
        finally
        {
            if (cmd != null)
                cmd.Dispose();
 
            if (adapter != null)
                adapter.Dispose();
 
            if (dataSet != null)
                dataSet.Dispose();
 
            if (cn != null)
                cn.Dispose();
        }            
    }
 
    public IEnumerable MyDataColumns
    {
        get
        {
            return (IEnumerable)_dataTable.Columns;
        }
    }
}

Open in new window

Avatar of perrytaylor
perrytaylor

ASKER

I think I need to tweak your answer a little bit because the dataset does not load when the window loads.  It loads after the user selects an excel sheet which gets imported as a dataset.  I'm not sure how to handle the window method in this case.  My excel code is included below so you can see what I am talking about and have now to load the dropdown, I def like the direction your solution is heading better than what I have.
        private void btnGetFile6_Click(object sender, RoutedEventArgs e)
        {
            string filename;
            get_excel_sheet(out sim6, out filename);
            tbFileName6.Text = filename;
        }
        
        public void get_excel_sheet(out DataSet myDataSet, out string filename)
        {
            myDataSet = new DataSet();
            filename = "";
 
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            dlg.FileName = ""; // Default file name
            dlg.DefaultExt = ".xls"; // Default file extension
            dlg.Filter = "Excel documents (.xls)|*.xls"; // Filter files by extension
 
            // Show save file dialog box
            Nullable<bool> result = dlg.ShowDialog();
 
            string file;
            if (result == true)
            {
                // Open document
                file = dlg.FileName;
                int filedel = file.LastIndexOf("\\");
                filename = file.Remove(0, filedel+1);
            }
            //else end function
            else
            {
                return;
            }
 
 
            string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties= ";
        connectString = connectString+ @"""Excel 8.0;HDR=YES;""";
 
 
 
            OleDbConnection connection =
                    new OleDbConnection(connectString);
 
            string query = "SELECT * FROM [Lap Sim$A22:FG724] where (NOT ([Bank] IS NULL))";
 
            
            
            OleDbConnection myAccessConn = null;
            try
            {
                myAccessConn = new OleDbConnection(connectString);
            }
            catch(Exception ex)
            {
                  Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                  return;
            }
 
            try
            {
 
                OleDbCommand myAccessCommand = new OleDbCommand(query, myAccessConn);
                  OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
 
                  myAccessConn.Open();
                  myDataAdapter.Fill(myDataSet,"data");
 
            }
            catch (Exception ex)
            {
                  Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
                  return;
            }
            finally
            {
                myAccessConn.Close();
                connection.Close();
            }
 
  
            cmbXAxis.Items.Clear();
            foreach (DataColumn column in myDataSet.Tables["data"].Columns)
            {
                string colname = column.ColumnName.ToString();
                cmbXAxis.Items.Add(colname);
                cmbYAxis.Items.Add(colname);
            }
            
 
        }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ToddBeaulieu
ToddBeaulieu
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you know of anywhere that spells out the "Model/View Model/View Paradigm" that you referenced.  I'm trying to get things that work quickly, but also trying to figure out how to be more elegant and use the tools properly for the long run.  I don't have access to a computer this weekend to test out your code, but it seems like it makes sense.  If you would continue to monitor this thread I would appreciate it.  
thanks for your help