Bind DataTable To BindingSource

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]);

Answer 1

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

Answer 2

@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());
}

Answer 3

@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