Adding Tables to a Dataset with Primary Key Relationships

[Richard Friedman]

Some History:

I have an access database that has over 120 fields. When I try to save changes I get an error that the table is too complex. My smaller tables save with no problems.

I have decided to break the table into three smaller tables, with the same Primary Key name and corresponding values preserved.

I wish to combine them in code and treat it as one table so that I do not have to change my many lines of code that edit the records.

Hopefully when I go to save (update) each of the three tables, they will preserve the changes and save properly.

Here is what I have so far.

'The array of datatables (gdt(a),b,c) is valid
			'Each of them have a valid primary key PBKey
			Dim ds As New DataSet
			ds = New DataSet()
			ds.Tables.Add("PORT_PLATES_PROPS")
			ds.Tables.Add("PORT_PLATES_DS")
			ds.Tables.Add("PORT_PLATES_ADS")
			ds.Relations.Add(gdt(a).Columns("PBKey"), gdt(b).Columns("PBKey"))
			ds.Relations.Add(gdt(a).Columns("PBKey"), gdt(c).Columns("PBKey"))

When it runs I get an error on the Relations.Add statement

"Cannot create DataRelation if Parent or Child Columns are not in a Dataset."

I check during runtime and the Data Tables have show that they have the PBKey column.

Ultimately it I get this to work is there a way that I can treat it as one DataTable?

Thanks
Richard

[jmcilhinney]

If you want to combine the tables in code then you should just have one query. Let's say that you started out with this table:

Table1:
Table1Id (PK)
Column1
Column2
Column3

and you broke it up into these tables:

Table1:
Table1Id (PK)
Column1

Table1A:
Table1Id (PK, FK)
Column2

Table1B:
Table1Id (PK, FK)
Column3

You can use this query to get all the data in one go:

SELECT t1.Table1Id, t1.Column1, t1a.Column2, t1b.Column3
FROM Table1 t1
INNER JOIN Table1A t1a ON t1a.Table1Id = t1.Table1Id
INNER JOIN Table1B t1b ON t1b.Table1Id = t1.Table1Id

One call to Fill on a data adapter with that query and you'll get all your data into one DataTable. You can then use three data adapters to save changes to the three different tables, with UPDATE statements that only refer to the columns in that table. Just be sure to set AcceptChangesOnUpdate to False on the first two data adapters, so that there are still changes to save after you call Update on them. You can leave it set to True for the third or else set it to False on all three and call AcceptChanges on the DataTable yourself.

[jmcilhinney]

You can do it as three separate DataTables if you want to, so that the code matches the database, but then you will have to change the code you already have. The issue with what you're currently doing is exactly as the error message says. You're trying to create DataRelations between DataColumns that don;t exist. You need to create the schema in those DataTables before you can create the DataRelations. You can either create the schema manually, call FillSchema on a data adapter to create the schema without retrieving data or call Fill to create the schema and retrieve data. Once the DataColumns exist in the DataTables, you can create the DataRelations.

[Richard Friedman]

If you want to combine the tables in code then you should just have one query. Let's say that you started out with this table:

Table1:
Table1Id (PK)
Column1
Column2
Column3

and you broke it up into these tables:

Table1:
Table1Id (PK)
Column1

Table1A:
Table1Id (PK, FK)
Column2

Table1B:
Table1Id (PK, FK)
Column3

You can use this query to get all the data in one go:

SELECT t1.Table1Id, t1.Column1, t1a.Column2, t1b.Column3
FROM Table1 t1
INNER JOIN Table1A t1a ON t1a.Table1Id = t1.Table1Id
INNER JOIN Table1B t1b ON t1b.Table1Id = t1.Table1Id

One call to Fill on a data adapter with that query and you'll get all your data into one DataTable. You can then use three data adapters to save changes to the three different tables, with UPDATE statements that only refer to the columns in that table. Just be sure to set AcceptChangesOnUpdate to False on the first two data adapters, so that there are still changes to save after you call Update on them. You can leave it set to True for the third or else set it to False on all three and call AcceptChanges on the DataTable yourself.

That's what I was looking for. I have 10 columns in my first table and about 60 columns in each of the other two. Do I have to list the columns explicitly or is there a global expression that I can use. Also each table contains a primary key of the same name, do I include that in the list of columns for each table?

[jmcilhinney]

In my example, I could have done this:

SELECT t1.*, t1a.Column2, t1b.Column3

and got the same result. If you use just a star then you will get the PK column from all three tables. That's probably not an issue as far as the data is concerned, although you would have to hide those columns if you're displaying the data in a grid. I'm not 100% sure what the resulting columns will be named, given that the source columns all have the same name. You can suck it and see and then fix it if it's an issue.

Note that you will have to write out each column explicitly and add appropriate parameters when creating INSERT and UPDATE commands. You're probably also going to want to use a transaction when saving, to make sure that changes get saved to all tables or none.

[techgnome]

Using * isn't a problem as far as the data is concerned, but it could be a problem as far as code goes.
consider this:

Table1:
Table1Id (PK)
Column1

Table1A:
Table1Id (PK, FK)
Column2

Table1B:
Table1Id (PK, FK)
Column3

If you use select * and get all fields of all tables... you end up with this:
Table1Id, Column1, Table1Id, Column2, Table1Id, Column3 ....

You see how Table1Id is in there trhee times? Now what happens when you try to access that field? Which one do you get? So the data itself isn't a problem, but your use of it might be.

-tg

[Richard Friedman]

I have returned to this problem that I have not managed to solve with a simplified example.

Here is the code that I am currently trying

Private Sub ThreeTables()

		Dim con As New OleDb.OleDbConnection

		Dim gdt As DataTable
		Dim gda As OleDbDataAdapter
		Dim gcb As OleDbCommandBuilder


		Dim Sql As String

		Try

			PBcon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\RhLhTest.mdb"
			PBcon.Open()
			Sql = "SELECT Main.*, LH.*, RH.* FROM TableMain Main INNER JOIN Table1LH LH ON LH.KeyLH = Main.MainKey INNER JOIN Table1RH RH ON RH.KeyRH = Main.MainKey"
			gda = New OleDb.OleDbDataAdapter(Sql, PBcon)
			gcb = New OleDbCommandBuilder(gda) With {.QuotePrefix = "[", .QuoteSuffix = "]"}
			gdt = New DataTable
			gda.MissingSchemaAction = MissingSchemaAction.AddWithKey
			gda.Fill(gdt)
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try
		con.Close()

	End Sub

This is my Error

"Syntax error (missing operator) in query expression 'LH.KeyLH = Main.MainKey INNER JOIN Table1RH RH ON RH.KeyRH = Main.MainKey'."


The simplified database looks like

Attachment 185052

[jmcilhinney]

The simplified database looks like

Attachment 185052

The Insert Image function doesn't work properly so no one can see that attachment. You have to use the Manage Attachments function like you did for the other one.

[Richard Friedman]

Attached is the image that I did not upload properly before.
The database has Three tables

Main: Has the main properties for the two other tables
LH: Has the left side properties
RH: Has the Right side properties.

The field names are meaningless in this example except for the primary key

In the main program with I add a row to the combined set, all three tables have a row added (or deleted) with identical primary key numbers.

[jmcilhinney]

I'm not sure what's wrong with your SQL but, on a different note, I hope you're not really using AutoNumbers in all three tables when those columns are supposed to be related. While it will usually be the case, there's no guarantee that the values for all three tables will stay in sync and, once out of sync, they will never resync. You should be using an AutoNumber for the main table and then manually inserting that value into the other tables.

[si_the_geek]

For Access-based databases you need to write the joins in a slightly different way to most database systems, and put brackets around each join, like this:

.. FROM (TableMain Main INNER JOIN Table1LH LH ON LH.KeyLH = Main.MainKey) INNER JOIN Table1RH RH ON RH.KeyRH = Main.MainKey"

[Richard Friedman]

With the code corrected and the table row added via the DataGrid as shown

	

Private Sub ThreeTables()




		Dim Sql As String

		Try

			PBcon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\RhLhTest.mdb"
			PBcon.Open()
			Sql = "SELECT Main.*, LH.*, RH.*, LH.KeyLH FROM (Main INNER JOIN LH ON Main.KeyMain = LH.KeyLH) INNER JOIN RH ON Main.KeyMain = RH.KeyRH;"
			gda = New OleDb.OleDbDataAdapter(Sql, PBcon)
			gcb = New OleDbCommandBuilder(gda) With {.QuotePrefix = "[", .QuoteSuffix = "]"}

			gda.MissingSchemaAction = MissingSchemaAction.AddWithKey
			gda.Fill(gdt)
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try

		With DataGridView1
			DataGridView1.AutoGenerateColumns = True
			DataGridView1.DataSource = gdt
			DataGridView1.DataMember = gdt.TableName
		End With


		'con.Close()

	End Sub

	Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
		gda.Update(gdt)
	End Sub

I get the following error on the gda(update(gdt) statement

Dynamic SQL generation is not supported against multiple base tables.'

If I create this query inside of Microsoft Access, I can add rows successfully.

any thoughts?

thanks

[wes4dbt]

Have you created the UpdateCommand? You can't use a CommandBuilder when your "Select" command retrieves multiple tables. You have to manually create the Insert/Update/Delete commands.

[Richard Friedman]

I have to admit that I am lost here.
My table RH has about 45 fields. My table LH also has about 45 fields and my table Main has about 20 fields.
If I add a row and correctly populate all 100+ fields, what would the update command look like?

[ChrisE]

that Image in Post#9 doesn't look correct I can't see any relation,
go back to Post#2 an look again what JMC done

[jmcilhinney]

I have to admit that I am lost here.
My table RH has about 45 fields. My table LH also has about 45 fields and my table Main has about 20 fields.
If I add a row and correctly populate all 100+ fields, what would the update command look like?

There wouldn't be a single UPDATE command. That's the point. You need one INSERT, UPDATE or DELETE statement per table. For a real database, e.g. SQL Server, you can use a single command object with multiple SQL statements. Access doesn't support multiple SQL statements in a single command so you have to use multiple commands. If you have a DataTable containing data for three tables then you need three data adapters to save those changes, each with one SQL UPDATE statement in the UpdateCommand. You would have to set AcceptChangesDuringUpdate to False on at least two of them. You would call Update on one to save changes to one table while not affecting the RowState of the DataRows. You would then call Update on the second to do the same for the second table. Wen you call Update on the third to save changes to the third table, then you want AcceptChanges called to set all the RowStates to Unchanged, so you can either have that done implicitly by the data adapter or you can do it explicitly.

[jmcilhinney]

Here's a quick example of how it might look for two tables:

Using connection As New OleDbConnection("connection string here"),
      parentCommand As New OleDbCommand("UPDATE Parent SET ParentName = @ParentName WHERE ID = @ID", connection),
      parentAdapter As New OleDbDataAdapter("SELECT p.*, c.ChildName FROM Parent p INNER JOIN Child c ON p.ID = c.ID", connection) With {.UpdateCommand = parentCommand, .AcceptChangesDuringUpdate = False},
      childCommand As New OleDbCommand("UPDATE Child SET ChildName = @ChildName WHERE ID = @ID", connection),
      childAdapter As New OleDbDataAdapter With {.UpdateCommand = childCommand, .AcceptChangesDuringUpdate = False}
    With parentCommand.Parameters
        .Add("@ParentName", OleDbType.VarChar, 50, "ParentName")
        .Add("@ID", OleDbType.Integer, 0, "ID")
    End With
 
    With childCommand.Parameters
        .Add("@ChildName", OleDbType.VarChar, 50, "ChildName")
        .Add("@ID", OleDbType.Integer, 0, "ID")
    End With
 
    Dim table As New DataTable
 
    connection.Open()
    parentAdapter.Fill(table)
 
    Dim row = table.Rows(0)
 
    row("ParentName") = "Modified"
    row("ChildName") = "Modified"
 
    parentAdapter.Update(table)
    childAdapter.Update(table)
 
    table.AcceptChanges()
End Using

Note that the same connection object is used in all cases. You might also want to consider using a transaction for the save, given that it has multiple parts. I'm not going to go into that here though.

[Richard Friedman]

I must admit my ignorance on this subject. I am way over my head and do not want to waste your time.

I do not understand commands and parameters, but it seems to me that I would need to add a parameter for each field in each table which I am reluctant to do for all 100+ fields. If I am incorrect then that displays my lack of understanding.

My original attempt (at the beginning of the thread) was to bring in the three tables and join them in the data set. Then somehow treat that joined dataset as one table. When it came to saving back to the database the simple update command would hopefully suffice.

If you agree that with my apparent lack of understand, I will never get there, then I thank you for your efforts.

[jmcilhinney]

If you want to save data to three separate tables then you're better off doing three separate queries to fill three separate DataTables in a DataSet. You could then use a command builder to generate all the action commands for you, if you want to avoid writing your own SQL. Better still, create a typed DataSet, which you would initiate from the Data Sources window.

[Richard Friedman]

In my fantasy world I would build the dataset and add relations that would behave like an inner join and call "something?" that would act like the whole table.

Sub loadDataset()
		Dim da As OleDb.OleDbDataAdapter
		Dim Sql As String
		Dim con As New OleDb.OleDbConnection
		con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\RhLhTest.mdb"
		con.Open()

		dsPP=New dataset

		Sql = "Select * from Main "
		da = New OleDb.OleDbDataAdapter(Sql, con)
		da.Fill(dsPP, "Main")

		Sql = "Select * from RH "
		da = New OleDb.OleDbDataAdapter(Sql, con)
		da.Fill(dsPP, "RH")

		Sql = "SELECT * From LH "
		da = New OleDb.OleDbDataAdapter(Sql, con)
		da.Fill(dsPP, "LH")
		con.Close()

		dsPP.Relations.Add("rel1", dsPP.Tables.Add("Main").Columns("KeyMain"), dsPP.Tables.Add("RH").Columns("KeyRH"))
		dsPP.Relations.Add("rel1", dsPP.Tables.Add("Main").Columns("KeyMain"), dsPP.Tables.Add("LH").Columns("KeyLH"))

	End Sub

Failing that I will now look up the following topics "Typed Dataset" and "Data Sources Window"

Thanks