Adding Tables to a Dataset with Primary Key Relationships
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.
Code:
'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?
You can use this query to get all the data in one go:
Code:
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
You can use this query to get all the data in one go:
Code:
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?
Re: Adding Tables to a Dataset with Primary Key Relationships
In my example, I could have done this:
Code:
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.
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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
Code:
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 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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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:
Code:
.. FROM (TableMain Main INNER JOIN Table1LH LH ON LH.KeyLH = Main.MainKey) INNER JOIN Table1RH RH ON RH.KeyRH = Main.MainKey"
Re: Adding Tables to a Dataset with Primary Key Relationships
With the code corrected and the table row added via the DataGrid as shown
Code:
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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?
Re: Adding Tables to a Dataset with Primary Key Relationships
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
Last edited by ChrisE; Jun 9th, 2022 at 06:23 PM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Re: Adding Tables to a Dataset with Primary Key Relationships
Originally Posted by 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?
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
Here's a quick example of how it might look for two tables:
vb.net Code:
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}
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
Re: Adding Tables to a Dataset with Primary Key Relationships
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.
Code:
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"