DataSet Relation is giving ERROR

[vishalonne]

Hi To All,

Once againg I'm in a big problem. I'm using C# 2008 and Access 2007. My DB contain 2 tables Customer and Booking. Both having Relation on CustomerID column.
Customer -> CustomerID (PK) and Booking -> CustomerID(FK)

Now I want to display the few columns from Customer and 2 columns from Booking records in C#, I tried and finally gave up. I have a confusion How can I use single Dataset for creating relation between 2 tables in C#. I saw pleanty of example on Net.

Here is my hard work which failed -

public partial class Form3 : Form
    {
        int f = 0;
        OleDbDataAdapter aAdapter;
        DataSet ds,ds1;
        OleDbCommand aCommand;
        OleDbConnection aConnection;
        
        public Form3()
        {
            InitializeComponent();
            try
            { 
                /*
                aConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Gas.mdb");
                aCommand = new OleDbCommand("select * from Customer INNER JOIN Booking ON Customer.CustomerID=Booking.CustomerID ORDER BY Customer.CustomerID", aConnection);
                aConnection.Open();
                aAdapter = new OleDbDataAdapter(aCommand);
                ds = new DataSet();
                aAdapter.Fill(ds, "bking_info");
                ds.Tables[0].Constraints.Add("pk_bkid", ds.Tables[0].Columns[0], true);
                DataRelation relation1 = ds.Relations.Add("percon", ds.Tables[0].Columns[0], ds.Tables[0].Columns[12]);
               
            sc = new SqlCommand("SELECT * FROM PersonalDetail INNER JOIN ContactDetail ON PersonalDetail.admission_no = ContactDetail.admission_no INNER JOIN ParentDetail ON PersonalDetail.admission_no = ParentDetail.admission_no order by PersonalDetail.admission_no;", scon);
            scon.Open();
            sda = new SqlDataAdapter(sc);
            ds = new DataSet();
            sda.Fill(ds, "personal_contact");
            ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);
            
            DataRelation relation2 = ds.Relations.Add("perpar", ds.Tables[0].Columns[0],ds.Tables[0].Columns[25]);
            */

                aConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Gas.mdb");
                
                
                //aCommand = new OleDbCommand("select * from Customer", aConnection);
                aAdapter = new OleDbDataAdapter("select * from Customer", aConnection);
                ds = new DataSet();
                aAdapter.Fill(ds, "custo_info"); Console.WriteLine(ds.Tables[0].Rows[0][0]);
                //aCommand = new OleDbCommand("select * from Booking", aConnection);
                aConnection.Open();
                aAdapter = new OleDbDataAdapter("select * from Booking", aConnection);
                ds = new DataSet();
                aAdapter.Fill(ds, "bking_info"); Console.WriteLine(ds.Tables[0].Rows[0][0]);
                //ForeignKeyConstraint idKeyRestraint = new ForeignKeyConstraint(ds.Tables[0].Columns[0],ds.Tables[0].Columns[]);

                DataRelation datare = ds.Relations.Add("percon", ds.Tables[0].Columns[0], ds.Tables[0].Columns[1]);
                foreach (DataRow drowCust in ds.Tables["Categories"].Rows)
                {
                    Console.Write("Column from Customer : " + drowCust[0]);
                     foreach(DataRow drowBook in drowCust.GetChildRows("percon"))
                     {
                         Console.WriteLine("Column from Booking : " + drowBook[1]);
                     }
                }

                Console.WriteLine("This is the returned data from UserMainTable table");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    comboBox1.Items.Add(ds.Tables[0].Rows[i][0].ToString());
                }
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }
        }

Please help me to get out of this mess. Show me some code snippet.

Regards
To All & Thanx in Advance

[lolafuertes]

In your code, line 44 you create a new dataset, overriding the previous. With this action, in your dataset you will have only one table. Instead youu need to have both.

I would suggest to remove this line.

Then, when you create the relation, in

DataRelation datare = ds.Relations.Add("percon", ds.Tables[0].Columns[0], ds.Tables[0].Columns[1]

You'll need to relate the table 0 columna 0 with the table 1 column 0
(assumming column 0 is the Customer id in both tables)

Hope this helps