none
Bind DataTable To BindingSource RRS feed

  • Question

  • I am querying an access table and returning the data to a DataTable - I then want to bind that DataTable to a BindingSource, I see my rows in my DataTable look like this 

    [0] = "Jorge"
    [1] = "Active"
    [2] = "Maine"
    [3] = "Year 3"

    and the DataTable is being populated like this:

    string sqlQuery = "Select * from [Get Data];";

    dataTable1 = //results of this query above

    What I am unable to figure out is How would I bind the rows of the DataTable to fields using my binding source? This does not work, gives an error, but should illustrate my question

    txtField1.DataBindings.Add("Text", _bs, [0]);

    Wednesday, January 16, 2019 4:12 PM

Answers

  • Hello,

    Given the following query.

    public DataTable Products()
    {
        mHasException = false; // ignore
    
        var dt = new DataTable();
    
        var selectStatement = "SELECT P.ProductID , P.ProductName , S.CompanyName AS SupplierName , " +
                              "P.SupplierID , P.CategoryID , P.UnitPrice , P.UnitsInStock , " +
                              "P.UnitsOnOrder " + 
                              "FROM Products AS P INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID " + 
                              "ORDER BY P.ProductName;";
    
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
            {
                try
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
                catch (Exception e)
                {
                    mHasException = true; // ignore
                    mLastException = e; // ignore
                }
            }
        }
    
        return dt;
    }

    The DataTable is passed back to the calling form.

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form2 : Form
        {
            readonly BindingSource _bs = new BindingSource();
            readonly DataOperations _ops = new DataOperations();
            public Form2()
            {
                InitializeComponent();
                Shown += Form2_Shown;
            }
    
            private void Form2_Shown(object sender, EventArgs e)
            {
                _bs.DataSource = _ops.Products();
    
                bindingNavigator1.BindingSource = _bs;
    
                productNameTextBox.DataBindings.Add("Text", _bs, "ProductName");
                supplierNameTextBox.DataBindings.Add("Text", _bs, "SupplierName");
                unitPriceTextBox.DataBindings.Add("Text", _bs, "UnitPrice");
            }
        }
    }

    The last three lines binding three fields to TextBox controls. The BindingNavigator is optional but this permits moving row to row in the underlying DataTable.

    If you want to get field information for the current row or the underlying DataTable we can use extension methods.

    public static class BindingSourceExtensions
    {
        public static DataTable DataTable(this BindingSource sender) => 
            (DataTable)sender.DataSource;
    
        public static DataRow DataRow(this BindingSource sender) => 
            ((DataRowView)sender.Current).Row;
    }

    Add a button to the form to get the current row fields.

    private void currentRow_Click(object sender, EventArgs e)
    {
        var currentRow = _bs.DataRow();
        MessageBox.Show($"Product id: {currentRow.Field<int>("ProductID")} for {currentRow.Field<string>("ProductName")}");
    }


    If you need to integrate a TextBox binding

    var bindingFieldName = productNameTextBox.DataBindings[0]
        .BindingMemberInfo.BindingField;
    Console.WriteLine(bindingFieldName);

    Hopefully the above is helpful.

    This will not work

    txtField1.DataBindings.Add("Text", _bs, [0]);

    Choices


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, January 17, 2019 1:07 AM
    Moderator

All replies

  • Hello,

    Given the following query.

    public DataTable Products()
    {
        mHasException = false; // ignore
    
        var dt = new DataTable();
    
        var selectStatement = "SELECT P.ProductID , P.ProductName , S.CompanyName AS SupplierName , " +
                              "P.SupplierID , P.CategoryID , P.UnitPrice , P.UnitsInStock , " +
                              "P.UnitsOnOrder " + 
                              "FROM Products AS P INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID " + 
                              "ORDER BY P.ProductName;";
    
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
            {
                try
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                }
                catch (Exception e)
                {
                    mHasException = true; // ignore
                    mLastException = e; // ignore
                }
            }
        }
    
        return dt;
    }

    The DataTable is passed back to the calling form.

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form2 : Form
        {
            readonly BindingSource _bs = new BindingSource();
            readonly DataOperations _ops = new DataOperations();
            public Form2()
            {
                InitializeComponent();
                Shown += Form2_Shown;
            }
    
            private void Form2_Shown(object sender, EventArgs e)
            {
                _bs.DataSource = _ops.Products();
    
                bindingNavigator1.BindingSource = _bs;
    
                productNameTextBox.DataBindings.Add("Text", _bs, "ProductName");
                supplierNameTextBox.DataBindings.Add("Text", _bs, "SupplierName");
                unitPriceTextBox.DataBindings.Add("Text", _bs, "UnitPrice");
            }
        }
    }

    The last three lines binding three fields to TextBox controls. The BindingNavigator is optional but this permits moving row to row in the underlying DataTable.

    If you want to get field information for the current row or the underlying DataTable we can use extension methods.

    public static class BindingSourceExtensions
    {
        public static DataTable DataTable(this BindingSource sender) => 
            (DataTable)sender.DataSource;
    
        public static DataRow DataRow(this BindingSource sender) => 
            ((DataRowView)sender.Current).Row;
    }

    Add a button to the form to get the current row fields.

    private void currentRow_Click(object sender, EventArgs e)
    {
        var currentRow = _bs.DataRow();
        MessageBox.Show($"Product id: {currentRow.Field<int>("ProductID")} for {currentRow.Field<string>("ProductName")}");
    }


    If you need to integrate a TextBox binding

    var bindingFieldName = productNameTextBox.DataBindings[0]
        .BindingMemberInfo.BindingField;
    Console.WriteLine(bindingFieldName);

    Hopefully the above is helpful.

    This will not work

    txtField1.DataBindings.Add("Text", _bs, [0]);

    Choices


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, January 17, 2019 1:07 AM
    Moderator
  • @Kareninstructor

    I get an error of -

    An action query can not be used as a row source.


    //and i set-up my catch block to read
    catch (System.Exception z)
    {
       MessageBox.Show(z.ToString());
    }


    Thursday, January 17, 2019 9:28 PM
  • @Kareninstructor

    I get an error of -

    An action query can not be used as a row source.


    //and i set-up my catch block to read
    catch (System.Exception z)
    {
       MessageBox.Show(z.ToString());
    }


    First off, in this last reply I have no clue to what you mean by action query. Secondly, in your original post there is nothing mentioning an action query (and have no clue what that means).

    I've highlighted parts that I have addressed and nothing to do with whatever an action query is.

    So unless you fully explain what an action query is I can't help you past what I've provide so far which as stated does work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 11:30 PM
    Moderator