Topic: Linked table in database

[Keith™]

I have a relation built into the database, but I cannot be sure it exists at runtime because I need the ability to create the tables programmatically.

Are you telling me that I can fill a dataset with data from two tablesthen display it as if it were a single table?

[fixo]

Sorry, Keith
Now is is too late in my village (01:40 local)
Check private message I need sleep

~'J'~

[fixo]

Here is a working code from lesson:
(tested on VS2005)
borrwed from "PRO C# 2005 and the .NET 2.0 Platform",
page 999 (russian edition) by Andrew Troelsen

Code: [Select]

         private void btnFill_Click(object sender, EventArgs e)
        {
            //int id = (int)this.DataGridViewInventory.SelectedRows[0].Cells[0].Value;//ok
 
            SqlCommand cmdPivot = new SqlCommand();
            cmdPivot.Connection = cn;
            //cmdPivot.CommandText=
            //    "SELECT e.FirstName, e.LastName FROM Customers e " +
            //    "JOIN Inventory h " +
            //    "ON e.custID = h.carID " +
            //    "WHERE h.carID = '" + id + "'";//ok also with single record
            cmdPivot.CommandText =
    "SELECT e.custID, e.FirstName, e.LastName FROM Customers e " +
    "JOIN Inventory h " +
    "ON e.custID = h.carID " +
    "WHERE h.carID = e.custID";
            pivotTableAdapter = new SqlDataAdapter(cmdPivot);
            sqlCBPivot = new SqlCommandBuilder(pivotTableAdapter);
            pivotTableAdapter.Fill(pivotDS,"PivotTable");
            DataGridViewPivot.DataSource = pivotDS.Tables["PivotTable"];
            SaveData();//
        }
HTH

~'J'~

[Keith™]

Ok, I have put this one to bed ... finally I think ...

I'll not post code because I am not at liberty to do so .. however, I have resolved the issue of linking tables, updating multiple tables with commandbuilder and generally all of the things that were making my life miserable over the last several months. The resolution was so simple, that I can't believe I didn't think of it along time ago.

I'll go through the steps in case anyone else would like a simple method to link multiple tables 1-to-1 in access.

1) Build each table with the requisite autonumber fields and foreign key fields, either programmatically or manually in the database

2) Using a single SQL statement, retrieve the fields from your tables with a left join i.e.

SELECT Table1.Field1, Table1.Field2, Table2.* FROM(Table1) LEFT JOIN Table2 ON Table1.Field1=Table2.Table1Field2

3) display and/or edit the records as desired

4) Now you have columns in the format of Table1.Field1 etc ... Rename Table1.Field1 to Field1 and update the records in Table1
5) Rename Field1 (from the previous action) to Table1Field1 and rename Table2.Field1 to Field1 and update again ... this time to table2

As long as the records are 1-to-1 and you have the requisite columns it will be very easy... it took me 6 months to figure it out ... hopefully it won't be so difficult for everyone else now.

[Draftek]

Congrats.
Nothing like a little blood letting to make you appreciate it.

[Keith™]

Congrats.
Nothing like a little blood letting to make you appreciate it.

and hair pulling