Hi,

I suppose that this thread is duplicate with case Ref. CAS-79891-TV28VK.

Maybe one possible approach to solve your task could be:

Option 1:

 

namespace UltraGridDataBindingWithSQLDataAdapter
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
 
            string sqlq = "SHAPE { SELECT ID, sText FROM dbItems  WHERE ID =10}   APPEND ({SELECT ID, sDescription FROM dbRows }  RELATE ID TO ID)";
            DataSet ds = new DataSet();
 
            System.Data.OleDb.OleDbConnection nwindConn = new System.Data.OleDb.OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=IGBGSOFDS22;Integrated Security=SSPI;Initial Catalog=Test");
            System.Data.OleDb.OleDbDataAdapter adap = new System.Data.OleDb.OleDbDataAdapter(sqlq,nwindConn); 
            adap.Fill(ds);
 
            ds.Tables[1].TableName = "BBB";
            ds.Tables[0].TableName = "AAA";
            ds.Relations[0].RelationName = "HHH";
            ultraGrid1.DataSource = ds;
        }
 
        private void ultraButton1_Click(object sender, EventArgs e)
        {
            MessageBox.Show(ultraGrid1.DisplayLayout.Bands[0].Key + " --- " + ultraGrid1.DisplayLayout.Bands[1].Key);
        }
    }
}
 

Please note that in this approach I set the name of the tables and releation outside of the SQL statement.

Option 2:
In this scenario I used a InnerJOin between both tables.
[code]

namespace UltraGridWithTablesRelationsSQLSelect

{

    public partial class Form1 : Form

    {

        private  DataSet ds;

        private  DataTable dtParent;

        private DataTable dtChild;

        public Form1()

        {

            InitializeComponent();

            ds = new DataSet();

            dtParent = new DataTable("Parent");

            dtChild = new DataTable("Child");

            dtParent.Columns.Add("ID",typeof(int));

            dtParent.Columns.Add("sItem",typeof(string));

            dtChild.Columns.Add("ID",typeof(int));

            dtChild.Columns.Add("sDescription",typeof(string));

            dtChild.Columns.Add("sNote",typeof(string));

            ds.Tables.Add(dtParent);

            ds.Tables.Add(dtChild);

            DataRelation relation = new DataRelation("Relationship", dtParent.Columns["ID"], dtChild.Columns["ID"], true);

            ds.Relations.Add(relation);

            this.ultraGrid1.DataSource = this.ds.Tables["Parent"];

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = "Data Source=IGBGSOFDS22;Initial Catalog=Test;Integrated Security=True";

            SqlCommand cmd = new SqlCommand("select * from [dbItems] inner join [dbRows] ON [dbItems].ID =[dbRows].ID", conn);

            conn.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())

            {

                dtParent.Rows.Add(dr[0], dr[1]);

                dtChild.Rows.Add(dr[2], dr[3], dr[4]);

            }

        }

    }

}

[code]

Please try these approaches and I`ll wait your feedback. Please if you have any questions, do not hesitate to write me

Hi,

Well, you could fill the tables separately, I think. But I'm really not an expert on the DataSet and DataAdapter. You would need to contact Microsoft for more detailed support on those components.

Personally, I would just to a Find/Replace and change the string to the new name. Or better yet, change the current string to a constant and then define the constant with the new name so that if you run into the same kind of issue in the future, you only have to change it in one place.

> I guess the Fill method must be automatically generating the name of the Relationship. So you may be stuck with it.

Do you have a better data fetch - populate code suggestion besides the fill?

> Why does it matter what the key is?

Because the keys are used in 230+ _InitalizeLayout events.  Along with my custom per-user grid 'column' 'what is visible - width - sorted' settings.

UltraWinGrid also appears to have dropped the functionality of Column.BaseTableName and Column.BaseColumnName which told you exactly which sql table/column a field represented.  

I guess the Fill method must be automatically generating the name of the Relationship. So you may be stuck with it.

You cannot change the key on the grid band. You can try changing the relationship name on the DataSet.

ds.RelationShips(index or key).Key = newKey

But I probably wouldn't do that if I were you. It might cause you some trouble later on when you try to write the changes back to the database. The DataAdapter might get confused.

Why does it matter what the key is?