myxiplx
asked on
Inserting relational data - datagrid error - it's not picking up primary key of parent
I'm creating an order entry form from scratch with very little experience in .NET 2003 so this may well be an obvious error ;-)
The idea is to have a simple order entry form: The user enters the main details for the order (date required, etc), then on the same form adds in all the items they would like to order and the quantities.
My order database essentially consists of two tables: Orders and OrderDetails. Orders has an auto-generated primary key (Order_ID) and there's a straighforward one-to-many relationship between the two tables in the SQL database using that key. The first part of my form which saves the entries in the orders table is now done and fully working, where I'm struggling is when I add a datagrid to allow the user to add the order details.
The datagrid just displays two fields for the user: ItemName and Quantity.
My first attempt used two separate dataadaptors and two datasets. This allowed the user to fill in the order details and as many items as they liked and saved fine except that the Order_ID field was not filled in for the detail items (which I expected about but this is the first time I've used a datagrid, I was happy to just get it saving something as a first step).
I attempted to fill in the Order_ID manually but quickly realised this would mean stepping through all the detail items the user has entered and completing it for each one. I can do this easily enough but I'm sure there's a better way.
Reading about this on the net I discovered that you can define relationships between tables in a dataset. That has the advantage of making the form very easy to use for viewing data, so I now have the same two dataadapters but one dataset containing both tables, and in the code I'm defining the relationship between the tables (which I called OrderDetails).
Now that I've done that as soon as I enter any data in the datagrid I get this error:
"Error when committing the row to the original data store.
ForeignKeyConstraint OrderDetails requires the child key values (0) to exist in the parent table. Do you want to correct the value?"
I can see that the datagrid isn't picking up a value for the primary key of the Order table, but with this application working with an offline dataset I'm not sure this isn't expected. Surely the primary key won't be known until the application commits the order to the database?
I know that when I save the data to the database I need to call the dataadapter update statements in order: firstly for the order, and then for the order details table, but I'm not even getting that far before I get this error.
Can someone tell me where I'm going wrong. Please ask if I've not given enough information.
thanks,
Ross
The idea is to have a simple order entry form: The user enters the main details for the order (date required, etc), then on the same form adds in all the items they would like to order and the quantities.
My order database essentially consists of two tables: Orders and OrderDetails. Orders has an auto-generated primary key (Order_ID) and there's a straighforward one-to-many relationship between the two tables in the SQL database using that key. The first part of my form which saves the entries in the orders table is now done and fully working, where I'm struggling is when I add a datagrid to allow the user to add the order details.
The datagrid just displays two fields for the user: ItemName and Quantity.
My first attempt used two separate dataadaptors and two datasets. This allowed the user to fill in the order details and as many items as they liked and saved fine except that the Order_ID field was not filled in for the detail items (which I expected about but this is the first time I've used a datagrid, I was happy to just get it saving something as a first step).
I attempted to fill in the Order_ID manually but quickly realised this would mean stepping through all the detail items the user has entered and completing it for each one. I can do this easily enough but I'm sure there's a better way.
Reading about this on the net I discovered that you can define relationships between tables in a dataset. That has the advantage of making the form very easy to use for viewing data, so I now have the same two dataadapters but one dataset containing both tables, and in the code I'm defining the relationship between the tables (which I called OrderDetails).
Now that I've done that as soon as I enter any data in the datagrid I get this error:
"Error when committing the row to the original data store.
ForeignKeyConstraint OrderDetails requires the child key values (0) to exist in the parent table. Do you want to correct the value?"
I can see that the datagrid isn't picking up a value for the primary key of the Order table, but with this application working with an offline dataset I'm not sure this isn't expected. Surely the primary key won't be known until the application commits the order to the database?
I know that when I save the data to the database I need to call the dataadapter update statements in order: firstly for the order, and then for the order details table, but I'm not even getting that far before I get this error.
Can someone tell me where I'm going wrong. Please ask if I've not given enough information.
thanks,
Ross
ASKER
Doesn't help at all I'm afraid, that's exactly what I have done. As I said in my question I have two dataadaptors, one for each table, and at the point I'm saving the data I will be calling the update command for the parent table first.
As I said in the post, the problem isn't with saving data to the database, it's before that. I get the error above while editing, as soon as I've entered one row in the datagrid and try to move to the next.
I could probably get around this by saving the parent table before the user tabs into the datagrid, but I don't really want to be updating the database until the user has entered both the order plus all the details. It should be done as a single operation. If I have to save the parent first I have the extra hassle of having to delete that if the user cancels the operation.
As I said in the post, the problem isn't with saving data to the database, it's before that. I get the error above while editing, as soon as I've entered one row in the datagrid and try to move to the next.
I could probably get around this by saving the parent table before the user tabs into the datagrid, but I don't really want to be updating the database until the user has entered both the order plus all the details. It should be done as a single operation. If I have to save the parent first I have the extra hassle of having to delete that if the user cancels the operation.
Hi
Are you sure that you don't have any constraint's added to your relation.
ds.Relations.Add(New DataRelation(relationName: ="relOrder sCompany", parentColumn:=ds.Tables("O rders").Co lumns("Com panyID"), _
childColumn:=ds.Tables("Co mpany").Co lumns("Com panyID"), _
createConstraints:=False))
Else perhaps you need to set one to the foreign key
ForeignKeyConstraint
A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint will determine what happens in the related tables.
The DeleteRule and UpdateRule properties of the ForeignKeyConstraint define the action to be taken when the user attempts to delete or update a row in a related table. The following table describes the different settings available for the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.
Rule Description
Cascade: Deletes or updates related rows. This is the default.
SetNull: Sets values in related rows to DBNull.
SetDefault: Sets values in related rows to the default value.
None: Specifies that no action be taken on related rows.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAddingConstraintsToDataSet.asp
vbturbo
Are you sure that you don't have any constraint's added to your relation.
ds.Relations.Add(New DataRelation(relationName:
childColumn:=ds.Tables("Co
createConstraints:=False))
Else perhaps you need to set one to the foreign key
ForeignKeyConstraint
A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint will determine what happens in the related tables.
The DeleteRule and UpdateRule properties of the ForeignKeyConstraint define the action to be taken when the user attempts to delete or update a row in a related table. The following table describes the different settings available for the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.
Rule Description
Cascade: Deletes or updates related rows. This is the default.
SetNull: Sets values in related rows to DBNull.
SetDefault: Sets values in related rows to the default value.
None: Specifies that no action be taken on related rows.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAddingConstraintsToDataSet.asp
vbturbo
Btw.
When you added a row in the parent table(succesfully) ,did you then collect the key value !, (To/for) the insert into the child table? (You have to!)
Else this will violate the rules.
<< it's not picking up primary key of parent
vbturbo
When you added a row in the parent table(succesfully) ,did you then collect the key value !, (To/for) the insert into the child table? (You have to!)
Else this will violate the rules.
<< it's not picking up primary key of parent
vbturbo
Since you use Auto increment
To address this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
It covers both Sql server and Acces database
To address this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
It covers both Sql server and Acces database
ASKER
Hiya,
Thanks for all this but I'm still struggling. I've gone through everything you've said, read tons of articles and still can't work this out.
Turning off constraints as you suggested means I don't get the error when adding rows to the datagrid, but the items are then saved to the database with the wrong key. I've also read the articles about fetching the identify value, but it appears that the dataadapter is doing this for me.
One change I've made which had some effect was to bind the dataset to the relation, not directly to the child table. The datagrid's dataMember property is now OrdersTable.RelationName as per:
http://msdn.microsoft.com/msdnmag/issues/02/02/cutting/
That had the effect of giving the same error but the value has changed to 1.
It definately binds between the parent and child tables now. While troubleshooting I added movefirst and movenext buttons to the orders form. The form cycles through the orders no problem and displays any child orderdetails in the datagrid. I didn't need to add any specific code to the datagrid, it's all working with just the relationship defined on the dataset.
I've added some more troubleshooting code and what I've found out so far is this:
When I create the new order record in the dataset, a temporary local order_ID is created. In my case the main order form is working with a temporary order_ID of 1. When I call the dataadapter's update command to save the record back to the database, a proper order_ID is generated by SQL server. Without me doing anything else the dataadapter also updates the local dataset with the correct order_ID.
Eg: Before the update the order_ID in memory for the new order will always be 1. After the update it will change to 14, 15, etc... matching the value saved to the database.
I've added code checking the order_ID throughout the form. As soon as I call AddNew() on the orders table this ID value of 1 is created, and it remains at that value right up to the point the data is saved to the database.
What's completely throwing me is that when editing in the datagrid, with constraints on I get an error saying the parent record for order_ID 1 doesn't exist. How can it not exist? I know for a fact that if I don't add the relationship between the tables the datagrid uses a default order_ID value of 0, so it wouldn't be using the ID of 1 if the record didn't exist.
I've even tried saving the parent record before entering any values in the child datagrid, but that creates yet another problem. After save is clicked the parent order_ID is updated to the proper value (eg. 14), but when I enter a row in the datagrid I still get the error saying it needs child key values (1) to exist in the parent table...
It's as though I'm missing some vital part of the link between the datagrid and the parent order, but I cannot see anything I've missed in the examples I've found online.
I also don't understand how the link can work for displaying data, but not for creating new records.
Ross
Thanks for all this but I'm still struggling. I've gone through everything you've said, read tons of articles and still can't work this out.
Turning off constraints as you suggested means I don't get the error when adding rows to the datagrid, but the items are then saved to the database with the wrong key. I've also read the articles about fetching the identify value, but it appears that the dataadapter is doing this for me.
One change I've made which had some effect was to bind the dataset to the relation, not directly to the child table. The datagrid's dataMember property is now OrdersTable.RelationName as per:
http://msdn.microsoft.com/msdnmag/issues/02/02/cutting/
That had the effect of giving the same error but the value has changed to 1.
It definately binds between the parent and child tables now. While troubleshooting I added movefirst and movenext buttons to the orders form. The form cycles through the orders no problem and displays any child orderdetails in the datagrid. I didn't need to add any specific code to the datagrid, it's all working with just the relationship defined on the dataset.
I've added some more troubleshooting code and what I've found out so far is this:
When I create the new order record in the dataset, a temporary local order_ID is created. In my case the main order form is working with a temporary order_ID of 1. When I call the dataadapter's update command to save the record back to the database, a proper order_ID is generated by SQL server. Without me doing anything else the dataadapter also updates the local dataset with the correct order_ID.
Eg: Before the update the order_ID in memory for the new order will always be 1. After the update it will change to 14, 15, etc... matching the value saved to the database.
I've added code checking the order_ID throughout the form. As soon as I call AddNew() on the orders table this ID value of 1 is created, and it remains at that value right up to the point the data is saved to the database.
What's completely throwing me is that when editing in the datagrid, with constraints on I get an error saying the parent record for order_ID 1 doesn't exist. How can it not exist? I know for a fact that if I don't add the relationship between the tables the datagrid uses a default order_ID value of 0, so it wouldn't be using the ID of 1 if the record didn't exist.
I've even tried saving the parent record before entering any values in the child datagrid, but that creates yet another problem. After save is clicked the parent order_ID is updated to the proper value (eg. 14), but when I enter a row in the datagrid I still get the error saying it needs child key values (1) to exist in the parent table...
It's as though I'm missing some vital part of the link between the datagrid and the parent order, but I cannot see anything I've missed in the examples I've found online.
I also don't understand how the link can work for displaying data, but not for creating new records.
Ross
Hi
Try setup/add this to your parent table in your form load event.
Dim pk(0) As DataColumn'define a primarykey collum
Dim cb As New OleDb.OleDbCommandBuilder( da) 'define the command builder
m_cn.Open() 'open the connection
ds.Clear()
'fill the dataset and setup the dataadapter
sSql = "SELECT Priority,Id,Firmname,Maila ddress,Fna me,Lname,K atagori FROM parenttable"
da.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
da.Fill(ds, "parenttable")
ds.Tables("parenttable").C olumns("Id ").AutoInc rement = True
pk(0) = ds.Tables("parenttable").C olumns("Id ") '<--- the collumname of your primary key
ds.Tables("parenttable").P rimaryKey = pk
========================== ========== =
Make sure that the foreign key IS not the auto increment column in the child table
Like this fields
Autono|foreignkey|bla|bla|
setup the second adapter (another one) DA1
Dim cb1 As New OleDb.OleDbCommandBuilder( DA1) 'define the command builder
sSql = "SELECT Priority,Id,Firmname,Maila ddress,Fna me,Lname,K atagori FROM parenttable"
DA1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
DA1.Fill(ds, "childtable")
ds.Tables("childtable").Co lumns("Id" ).AutoIncr ement = True
Add your relation here
hope this helps
vbturbo
Try setup/add this to your parent table in your form load event.
Dim pk(0) As DataColumn'define a primarykey collum
Dim cb As New OleDb.OleDbCommandBuilder(
m_cn.Open() 'open the connection
ds.Clear()
'fill the dataset and setup the dataadapter
sSql = "SELECT Priority,Id,Firmname,Maila
da.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
da.Fill(ds, "parenttable")
ds.Tables("parenttable").C
pk(0) = ds.Tables("parenttable").C
ds.Tables("parenttable").P
==========================
Make sure that the foreign key IS not the auto increment column in the child table
Like this fields
Autono|foreignkey|bla|bla|
setup the second adapter (another one) DA1
Dim cb1 As New OleDb.OleDbCommandBuilder(
sSql = "SELECT Priority,Id,Firmname,Maila
DA1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
DA1.Fill(ds, "childtable")
ds.Tables("childtable").Co
Add your relation here
hope this helps
vbturbo
btw . i answered a similar question a while ago though just setting a relation including a currencymanager to control parent /child, have a look here
https://www.experts-exchange.com/questions/22091126/vb-2005-Data-constraints.html
https://www.experts-exchange.com/questions/22091126/vb-2005-Data-constraints.html
correction
sSql = "SELECT Priority,Id,Firmname,Maila ddress,Fna me,Lname,K atagori FROM childtable"
sSql = "SELECT Priority,Id,Firmname,Maila
Hi
i made a finished solution for you which solves your above descriped problems
get the solution here
https://filedb.experts-exchange.com/incoming/ee-stuff/2167-Relation.zip
vbturbo
hope this helps
i made a finished solution for you which solves your above descriped problems
get the solution here
https://filedb.experts-exchange.com/incoming/ee-stuff/2167-Relation.zip
vbturbo
hope this helps
ASKER
I can't open that vbturbo, it says it's an unknown file. Checking my Help -> About box I'm running Microsoft Development Environment 2002, I'm guessing you have a newer version.
I've done still more testing and have some interesting results now.
My form and datagrid have now both been configured to display the Order_ID field so I can manully verify the link between the tables while editing data. With that in place I can confirm that everything works perfectly when editing existing records or adding new ones:
- When I edit a record and add a new detail line I can see the Order_ID field is given a default value automatically, matching the ID of the order I'm editing.
- When viewing records, I can scroll through my database and the datagrid automatically updates itself to display the records for the current order.
So ordinarily it would appear that the parent/child relationship is in place and working properly.
The only time I have a problem is when I'm adding a new order, and even then it appears that the links are working fine. When I create a new order the form shows that it is given an ID of 1. When adding child details in the datagrid, the Order_ID field is automatically filled in with the value 1 and so appears ok. However as soon as I have entered all the details for the current row and try to move to the next I get the error:
"ForeignKeyConstraint OrderDetails requires the child key values (1) to exist in the parent table."
I've even tried changing the autoincrementseed to 4 to double-check the relationship. If I do that the order is created with an ID of 5, and the related field in the child table is filled in automatically with this value, but I still get the error:
"ForeignKeyConstraint OrderDetails requires the child key values (5) to exist in the parent table."
I also get unexpected behaviour if I try to save the parent order first. If I click save without entering any child details I can see that the order is saved to the database and given a new Order_ID by the SQL server. The Order_ID field on the form updates itself automatically to show the new value.
However, if I now try to enter a row in the child datagrid, it attempts to use the old ID.
It looks like the datagrid is loosing it's connection to the parent at some point in this process, or is using out of date data?
I'm hoping that the two scenarios are linked and that this gives you an extra clue to what's going on here.
thanks,
Ross
I've done still more testing and have some interesting results now.
My form and datagrid have now both been configured to display the Order_ID field so I can manully verify the link between the tables while editing data. With that in place I can confirm that everything works perfectly when editing existing records or adding new ones:
- When I edit a record and add a new detail line I can see the Order_ID field is given a default value automatically, matching the ID of the order I'm editing.
- When viewing records, I can scroll through my database and the datagrid automatically updates itself to display the records for the current order.
So ordinarily it would appear that the parent/child relationship is in place and working properly.
The only time I have a problem is when I'm adding a new order, and even then it appears that the links are working fine. When I create a new order the form shows that it is given an ID of 1. When adding child details in the datagrid, the Order_ID field is automatically filled in with the value 1 and so appears ok. However as soon as I have entered all the details for the current row and try to move to the next I get the error:
"ForeignKeyConstraint OrderDetails requires the child key values (1) to exist in the parent table."
I've even tried changing the autoincrementseed to 4 to double-check the relationship. If I do that the order is created with an ID of 5, and the related field in the child table is filled in automatically with this value, but I still get the error:
"ForeignKeyConstraint OrderDetails requires the child key values (5) to exist in the parent table."
I also get unexpected behaviour if I try to save the parent order first. If I click save without entering any child details I can see that the order is saved to the database and given a new Order_ID by the SQL server. The Order_ID field on the form updates itself automatically to show the new value.
However, if I now try to enter a row in the child datagrid, it attempts to use the old ID.
It looks like the datagrid is loosing it's connection to the parent at some point in this process, or is using out of date data?
I'm hoping that the two scenarios are linked and that this gives you an extra clue to what's going on here.
thanks,
Ross
Hi Ross
Here is the entire class
Imports System.Data
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim daParent As OleDb.OleDbDataAdapter
Dim daChild As OleDb.OleDbDataAdapter
Dim sql As String
Dim myTable As DataTable
Dim myTableCol As DataColumn
Dim foreignkey As Integer
Private myCurrencyManager As CurrencyManager
Dim dsNewRow As DataRow
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.Jet.OL EDB.4.0;Da ta Source = C:\Test.mdb"
sql = "SELECT * FROM Parent"
con.Open()
daParent = New OleDb.OleDbDataAdapter(sql , con)
daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
daParent.Fill(ds, "Parent")
myTable = ds.Tables("Parent")
sql = "SELECT * FROM Child"
daChild = New OleDb.OleDbDataAdapter(sql , con)
daChild.FillSchema(ds, SchemaType.Mapped, "Child")
daChild.Fill(ds, "Child")
con.Close()
'SetdataRel()
BindControl(myTable)
Parentgrd.DataSource = ds.Tables("Parent")
Childgrd.DataSource = ds.Tables("Child")
ds.Tables("Parent").Column s("ID").Au toIncremen t = True
ds.Tables("Parent").Column s("ID").Au toIncremen tSeed = -1
ds.Tables("Parent").Column s("ID").Au toIncremen tSeed = -1
ds.Tables("Child").Columns ("ID").Aut oIncrement = True
SetdataRel()
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim tmp As Integer
tmp = ds.Tables("Parent").Rows.C ount
foreignkey = ds.Tables("Parent").Rows(t mp - 1).Item(0) + 1
dsNewRow = ds.Tables("Parent").NewRow ()
dsNewRow.Item(0) = foreignkey
dsNewRow.Item(1) = TextBox1.Text
dsNewRow.Item(2) = TextBox2.Text
TextBox3.Text = foreignkey
ds.Tables("Parent").Rows.A dd(dsNewRo w)
End Sub
Private Sub Addchild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Addchild.Click
dsNewRow = ds.Tables("Child").NewRow( )
dsNewRow.Item(1) = foreignkey
dsNewRow.Item(2) = TextBox4.Text
dsNewRow.Item(3) = TextBox3.Text
ds.Tables("Child").Rows.Ad d(dsNewRow )
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cb As New OleDb.OleDbCommandBuilder( daParent)
daParent.Update(ds, "Parent")
Dim cb1 As New OleDb.OleDbCommandBuilder( daChild)
daChild.Update(ds, "Child")
MsgBox("Data updated")
End Sub
Public Sub SetdataRel()
' Add the relation between Suppliers/Products
ds.Relations.Add(New DataRelation(relationName: ="relParCh i", parentColumn:=ds.Tables("P arent").Co lumns("ID" ), _
childColumn:=ds.Tables("Ch ild").Colu mns("Forei gnkey"), _
createConstraints:=False))
' Set the Suppliers grid
Parentgrd.DataSource = ds
Parentgrd.DataMember = "Parent"
' Set the Products grid
Childgrd.DataSource = ds
Childgrd.DataMember = "Parent.relParChi"
End Sub
Private Sub BindControl(ByVal myTable As DataTable)
' Bind a TextBox control to a DataTable column in a DataSet.
'TextBox1.DataBindings.Add ("text", myTable, "ID")
TextBox1.DataBindings.Add( "text", myTable, "FirstName")
TextBox2.DataBindings.Add( "text", myTable, "Surname")
' Specify the CurrencyManager for the DataTable.
myCurrencyManager = CType(Me.BindingContext(my Table), CurrencyManager)
' Set the initial Position of the control.
myCurrencyManager.Position = 0
End Sub
'Button event handlings
Private Sub btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprevious.Click
MovePrevious(myCurrencyMan ager)
End Sub
Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click
MoveNext(myCurrencyManager )
End Sub
Private Sub MoveNext(ByVal myCurrencyManager As CurrencyManager)
If myCurrencyManager.Position = myCurrencyManager.Count - 1 Then
'txtRecordPosition.Text = myCurrencyManager.Position
MessageBox.Show("You're at end of the records")
Else
myCurrencyManager.Position += 1
'txtRecordPosition.Text = myCurrencyManager.Position
End If
End Sub
Private Sub MoveFirst(ByVal myCurrencyManager As CurrencyManager)
myCurrencyManager.Position = 0
'txtRecordPosition.Text = myCurrencyManager.Position
End Sub
Private Sub MovePrevious(ByVal myCurrencyManager As CurrencyManager)
If myCurrencyManager.Position = 0 Then
'txtRecordPosition.Text = myCurrencyManager.Position
MessageBox.Show("You're at the beginning of the records.")
Else
myCurrencyManager.Position -= 1
'txtRecordPosition.Text = myCurrencyManager.Position
End If
End Sub
End Class
Hope this helps
vbturbo
Here is the entire class
Imports System.Data
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim daParent As OleDb.OleDbDataAdapter
Dim daChild As OleDb.OleDbDataAdapter
Dim sql As String
Dim myTable As DataTable
Dim myTableCol As DataColumn
Dim foreignkey As Integer
Private myCurrencyManager As CurrencyManager
Dim dsNewRow As DataRow
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.Jet.OL
sql = "SELECT * FROM Parent"
con.Open()
daParent = New OleDb.OleDbDataAdapter(sql
daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
daParent.Fill(ds, "Parent")
myTable = ds.Tables("Parent")
sql = "SELECT * FROM Child"
daChild = New OleDb.OleDbDataAdapter(sql
daChild.FillSchema(ds, SchemaType.Mapped, "Child")
daChild.Fill(ds, "Child")
con.Close()
'SetdataRel()
BindControl(myTable)
Parentgrd.DataSource = ds.Tables("Parent")
Childgrd.DataSource = ds.Tables("Child")
ds.Tables("Parent").Column
ds.Tables("Parent").Column
ds.Tables("Parent").Column
ds.Tables("Child").Columns
SetdataRel()
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim tmp As Integer
tmp = ds.Tables("Parent").Rows.C
foreignkey = ds.Tables("Parent").Rows(t
dsNewRow = ds.Tables("Parent").NewRow
dsNewRow.Item(0) = foreignkey
dsNewRow.Item(1) = TextBox1.Text
dsNewRow.Item(2) = TextBox2.Text
TextBox3.Text = foreignkey
ds.Tables("Parent").Rows.A
End Sub
Private Sub Addchild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Addchild.Click
dsNewRow = ds.Tables("Child").NewRow(
dsNewRow.Item(1) = foreignkey
dsNewRow.Item(2) = TextBox4.Text
dsNewRow.Item(3) = TextBox3.Text
ds.Tables("Child").Rows.Ad
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cb As New OleDb.OleDbCommandBuilder(
daParent.Update(ds, "Parent")
Dim cb1 As New OleDb.OleDbCommandBuilder(
daChild.Update(ds, "Child")
MsgBox("Data updated")
End Sub
Public Sub SetdataRel()
' Add the relation between Suppliers/Products
ds.Relations.Add(New DataRelation(relationName:
childColumn:=ds.Tables("Ch
createConstraints:=False))
' Set the Suppliers grid
Parentgrd.DataSource = ds
Parentgrd.DataMember = "Parent"
' Set the Products grid
Childgrd.DataSource = ds
Childgrd.DataMember = "Parent.relParChi"
End Sub
Private Sub BindControl(ByVal myTable As DataTable)
' Bind a TextBox control to a DataTable column in a DataSet.
'TextBox1.DataBindings.Add
TextBox1.DataBindings.Add(
TextBox2.DataBindings.Add(
' Specify the CurrencyManager for the DataTable.
myCurrencyManager = CType(Me.BindingContext(my
' Set the initial Position of the control.
myCurrencyManager.Position
End Sub
'Button event handlings
Private Sub btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprevious.Click
MovePrevious(myCurrencyMan
End Sub
Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click
MoveNext(myCurrencyManager
End Sub
Private Sub MoveNext(ByVal myCurrencyManager As CurrencyManager)
If myCurrencyManager.Position
'txtRecordPosition.Text = myCurrencyManager.Position
MessageBox.Show("You're at end of the records")
Else
myCurrencyManager.Position
'txtRecordPosition.Text = myCurrencyManager.Position
End If
End Sub
Private Sub MoveFirst(ByVal myCurrencyManager As CurrencyManager)
myCurrencyManager.Position
'txtRecordPosition.Text = myCurrencyManager.Position
End Sub
Private Sub MovePrevious(ByVal myCurrencyManager As CurrencyManager)
If myCurrencyManager.Position
'txtRecordPosition.Text = myCurrencyManager.Position
MessageBox.Show("You're at the beginning of the records.")
Else
myCurrencyManager.Position
'txtRecordPosition.Text = myCurrencyManager.Position
End If
End Sub
End Class
Hope this helps
vbturbo
And add this into the Form1.Designer.vb
Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Butto n
Me.Label4 = New System.Windows.Forms.Label
Me.Label3 = New System.Windows.Forms.Label
Me.btnprevious = New System.Windows.Forms.Butto n
Me.btnnext = New System.Windows.Forms.Butto n
Me.btnadd = New System.Windows.Forms.Butto n
Me.Childgrd = New System.Windows.Forms.DataG rid
Me.Parentgrd = New System.Windows.Forms.DataG rid
Me.TextBox2 = New System.Windows.Forms.TextB ox
Me.TextBox1 = New System.Windows.Forms.TextB ox
Me.Label5 = New System.Windows.Forms.Label
Me.Label6 = New System.Windows.Forms.Label
Me.Addchild = New System.Windows.Forms.Butto n
Me.TextBox4 = New System.Windows.Forms.TextB ox
Me.TextBox3 = New System.Windows.Forms.TextB ox
Me.Label8 = New System.Windows.Forms.Label
Me.TextBox5 = New System.Windows.Forms.TextB ox
CType(Me.Childgrd, System.ComponentModel.ISup portInitia lize).Begi nInit()
CType(Me.Parentgrd, System.ComponentModel.ISup portInitia lize).Begi nInit()
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(632, 412)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(75, 23)
Me.Button1.TabIndex = 41
Me.Button1.Text = "Update db "
Me.Button1.UseVisualStyleB ackColor = True
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(521, 118)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(55, 13)
Me.Label4.TabIndex = 37
Me.Label4.Text = "LastName"
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(521, 92)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(52, 13)
Me.Label3.TabIndex = 36
Me.Label3.Text = "Firstname"
'
'btnprevious
'
Me.btnprevious.Location = New System.Drawing.Point(17, 351)
Me.btnprevious.Name = "btnprevious"
Me.btnprevious.Size = New System.Drawing.Size(88, 24)
Me.btnprevious.TabIndex = 34
Me.btnprevious.Text = "Previous"
'
'btnnext
'
Me.btnnext.Location = New System.Drawing.Point(111, 352)
Me.btnnext.Name = "btnnext"
Me.btnnext.Size = New System.Drawing.Size(88, 24)
Me.btnnext.TabIndex = 33
Me.btnnext.Text = "Next"
'
'btnadd
'
Me.btnadd.Location = New System.Drawing.Point(632, 145)
Me.btnadd.Name = "btnadd"
Me.btnadd.Size = New System.Drawing.Size(88, 24)
Me.btnadd.TabIndex = 32
Me.btnadd.Text = "Add"
'
'Childgrd
'
Me.Childgrd.AllowNavigatio n = False
Me.Childgrd.CaptionBackCol or = System.Drawing.Color.FromA rgb(CType( CType(255, Byte), Integer), CType(CType(255, Byte), Integer), CType(CType(192, Byte), Integer))
Me.Childgrd.CaptionForeCol or = System.Drawing.Color.DarkO liveGreen
Me.Childgrd.CaptionText = "Child"
Me.Childgrd.DataMember = ""
Me.Childgrd.HeaderForeColo r = System.Drawing.SystemColor s.ControlT ext
Me.Childgrd.Location = New System.Drawing.Point(17, 192)
Me.Childgrd.Name = "Childgrd"
Me.Childgrd.PreferredColum nWidth = 101
Me.Childgrd.Size = New System.Drawing.Size(498, 144)
Me.Childgrd.TabIndex = 31
'
'Parentgrd
'
Me.Parentgrd.AllowNavigati on = False
Me.Parentgrd.CaptionBackCo lor = System.Drawing.Color.FromA rgb(CType( CType(255, Byte), Integer), CType(CType(255, Byte), Integer), CType(CType(192, Byte), Integer))
Me.Parentgrd.CaptionForeCo lor = System.Drawing.Color.DarkO liveGreen
Me.Parentgrd.CaptionText = "Parent"
Me.Parentgrd.DataMember = ""
Me.Parentgrd.HeaderForeCol or = System.Drawing.SystemColor s.ControlT ext
Me.Parentgrd.Location = New System.Drawing.Point(17, 25)
Me.Parentgrd.Name = "Parentgrd"
Me.Parentgrd.PreferredColu mnWidth = 101
Me.Parentgrd.Size = New System.Drawing.Size(498, 144)
Me.Parentgrd.TabIndex = 30
'
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point(632, 115)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.Size = New System.Drawing.Size(100, 20)
Me.TextBox2.TabIndex = 27
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(632, 89)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(100, 20)
Me.TextBox1.TabIndex = 26
'
'Label5
'
Me.Label5.AutoSize = True
Me.Label5.Location = New System.Drawing.Point(521, 276)
Me.Label5.Name = "Label5"
Me.Label5.Size = New System.Drawing.Size(52, 13)
Me.Label5.TabIndex = 48
Me.Label5.Text = "Postcode"
'
'Label6
'
Me.Label6.AutoSize = True
Me.Label6.Location = New System.Drawing.Point(521, 250)
Me.Label6.Name = "Label6"
Me.Label6.Size = New System.Drawing.Size(59, 13)
Me.Label6.TabIndex = 47
Me.Label6.Text = "Foreignkey"
'
'Addchild
'
Me.Addchild.Location = New System.Drawing.Point(632, 344)
Me.Addchild.Name = "Addchild"
Me.Addchild.Size = New System.Drawing.Size(88, 24)
Me.Addchild.TabIndex = 45
Me.Addchild.Text = "Add"
'
'TextBox4
'
Me.TextBox4.Location = New System.Drawing.Point(632, 273)
Me.TextBox4.Name = "TextBox4"
Me.TextBox4.Size = New System.Drawing.Size(100, 20)
Me.TextBox4.TabIndex = 44
'
'TextBox3
'
Me.TextBox3.Location = New System.Drawing.Point(632, 247)
Me.TextBox3.Name = "TextBox3"
Me.TextBox3.Size = New System.Drawing.Size(100, 20)
Me.TextBox3.TabIndex = 43
'
'Label8
'
Me.Label8.AutoSize = True
Me.Label8.Location = New System.Drawing.Point(521, 302)
Me.Label8.Name = "Label8"
Me.Label8.Size = New System.Drawing.Size(38, 13)
Me.Label8.TabIndex = 50
Me.Label8.Text = "Phone"
'
'TextBox5
'
Me.TextBox5.Location = New System.Drawing.Point(632, 299)
Me.TextBox5.Name = "TextBox5"
Me.TextBox5.Size = New System.Drawing.Size(100, 20)
Me.TextBox5.TabIndex = 49
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoS caleMode.F ont
Me.ClientSize = New System.Drawing.Size(841, 516)
Me.Controls.Add(Me.Label8)
Me.Controls.Add(Me.TextBox 5)
Me.Controls.Add(Me.Label5)
Me.Controls.Add(Me.Label6)
Me.Controls.Add(Me.Addchil d)
Me.Controls.Add(Me.TextBox 4)
Me.Controls.Add(Me.TextBox 3)
Me.Controls.Add(Me.Button1 )
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.btnprev ious)
Me.Controls.Add(Me.btnnext )
Me.Controls.Add(Me.btnadd)
Me.Controls.Add(Me.Childgr d)
Me.Controls.Add(Me.Parentg rd)
Me.Controls.Add(Me.TextBox 2)
Me.Controls.Add(Me.TextBox 1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.Childgrd, System.ComponentModel.ISup portInitia lize).EndI nit()
CType(Me.Parentgrd, System.ComponentModel.ISup portInitia lize).EndI nit()
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
Friend WithEvents Button1 As System.Windows.Forms.Butto n
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents btnprevious As System.Windows.Forms.Butto n
Friend WithEvents btnnext As System.Windows.Forms.Butto n
Friend WithEvents btnadd As System.Windows.Forms.Butto n
Friend WithEvents Childgrd As System.Windows.Forms.DataG rid
Friend WithEvents Parentgrd As System.Windows.Forms.DataG rid
Friend WithEvents TextBox2 As System.Windows.Forms.TextB ox
Friend WithEvents TextBox1 As System.Windows.Forms.TextB ox
Friend WithEvents Label5 As System.Windows.Forms.Label
Friend WithEvents Label6 As System.Windows.Forms.Label
Friend WithEvents Addchild As System.Windows.Forms.Butto n
Friend WithEvents TextBox4 As System.Windows.Forms.TextB ox
Friend WithEvents TextBox3 As System.Windows.Forms.TextB ox
Friend WithEvents Label8 As System.Windows.Forms.Label
Friend WithEvents TextBox5 As System.Windows.Forms.TextB ox
End Class
Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Butto
Me.Label4 = New System.Windows.Forms.Label
Me.Label3 = New System.Windows.Forms.Label
Me.btnprevious = New System.Windows.Forms.Butto
Me.btnnext = New System.Windows.Forms.Butto
Me.btnadd = New System.Windows.Forms.Butto
Me.Childgrd = New System.Windows.Forms.DataG
Me.Parentgrd = New System.Windows.Forms.DataG
Me.TextBox2 = New System.Windows.Forms.TextB
Me.TextBox1 = New System.Windows.Forms.TextB
Me.Label5 = New System.Windows.Forms.Label
Me.Label6 = New System.Windows.Forms.Label
Me.Addchild = New System.Windows.Forms.Butto
Me.TextBox4 = New System.Windows.Forms.TextB
Me.TextBox3 = New System.Windows.Forms.TextB
Me.Label8 = New System.Windows.Forms.Label
Me.TextBox5 = New System.Windows.Forms.TextB
CType(Me.Childgrd, System.ComponentModel.ISup
CType(Me.Parentgrd, System.ComponentModel.ISup
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(632, 412)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(75, 23)
Me.Button1.TabIndex = 41
Me.Button1.Text = "Update db "
Me.Button1.UseVisualStyleB
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(521, 118)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(55, 13)
Me.Label4.TabIndex = 37
Me.Label4.Text = "LastName"
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(521, 92)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(52, 13)
Me.Label3.TabIndex = 36
Me.Label3.Text = "Firstname"
'
'btnprevious
'
Me.btnprevious.Location = New System.Drawing.Point(17, 351)
Me.btnprevious.Name = "btnprevious"
Me.btnprevious.Size = New System.Drawing.Size(88, 24)
Me.btnprevious.TabIndex = 34
Me.btnprevious.Text = "Previous"
'
'btnnext
'
Me.btnnext.Location = New System.Drawing.Point(111, 352)
Me.btnnext.Name = "btnnext"
Me.btnnext.Size = New System.Drawing.Size(88, 24)
Me.btnnext.TabIndex = 33
Me.btnnext.Text = "Next"
'
'btnadd
'
Me.btnadd.Location = New System.Drawing.Point(632, 145)
Me.btnadd.Name = "btnadd"
Me.btnadd.Size = New System.Drawing.Size(88, 24)
Me.btnadd.TabIndex = 32
Me.btnadd.Text = "Add"
'
'Childgrd
'
Me.Childgrd.AllowNavigatio
Me.Childgrd.CaptionBackCol
Me.Childgrd.CaptionForeCol
Me.Childgrd.CaptionText = "Child"
Me.Childgrd.DataMember = ""
Me.Childgrd.HeaderForeColo
Me.Childgrd.Location = New System.Drawing.Point(17, 192)
Me.Childgrd.Name = "Childgrd"
Me.Childgrd.PreferredColum
Me.Childgrd.Size = New System.Drawing.Size(498, 144)
Me.Childgrd.TabIndex = 31
'
'Parentgrd
'
Me.Parentgrd.AllowNavigati
Me.Parentgrd.CaptionBackCo
Me.Parentgrd.CaptionForeCo
Me.Parentgrd.CaptionText = "Parent"
Me.Parentgrd.DataMember = ""
Me.Parentgrd.HeaderForeCol
Me.Parentgrd.Location = New System.Drawing.Point(17, 25)
Me.Parentgrd.Name = "Parentgrd"
Me.Parentgrd.PreferredColu
Me.Parentgrd.Size = New System.Drawing.Size(498, 144)
Me.Parentgrd.TabIndex = 30
'
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point(632, 115)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.Size = New System.Drawing.Size(100, 20)
Me.TextBox2.TabIndex = 27
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(632, 89)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(100, 20)
Me.TextBox1.TabIndex = 26
'
'Label5
'
Me.Label5.AutoSize = True
Me.Label5.Location = New System.Drawing.Point(521, 276)
Me.Label5.Name = "Label5"
Me.Label5.Size = New System.Drawing.Size(52, 13)
Me.Label5.TabIndex = 48
Me.Label5.Text = "Postcode"
'
'Label6
'
Me.Label6.AutoSize = True
Me.Label6.Location = New System.Drawing.Point(521, 250)
Me.Label6.Name = "Label6"
Me.Label6.Size = New System.Drawing.Size(59, 13)
Me.Label6.TabIndex = 47
Me.Label6.Text = "Foreignkey"
'
'Addchild
'
Me.Addchild.Location = New System.Drawing.Point(632, 344)
Me.Addchild.Name = "Addchild"
Me.Addchild.Size = New System.Drawing.Size(88, 24)
Me.Addchild.TabIndex = 45
Me.Addchild.Text = "Add"
'
'TextBox4
'
Me.TextBox4.Location = New System.Drawing.Point(632, 273)
Me.TextBox4.Name = "TextBox4"
Me.TextBox4.Size = New System.Drawing.Size(100, 20)
Me.TextBox4.TabIndex = 44
'
'TextBox3
'
Me.TextBox3.Location = New System.Drawing.Point(632, 247)
Me.TextBox3.Name = "TextBox3"
Me.TextBox3.Size = New System.Drawing.Size(100, 20)
Me.TextBox3.TabIndex = 43
'
'Label8
'
Me.Label8.AutoSize = True
Me.Label8.Location = New System.Drawing.Point(521, 302)
Me.Label8.Name = "Label8"
Me.Label8.Size = New System.Drawing.Size(38, 13)
Me.Label8.TabIndex = 50
Me.Label8.Text = "Phone"
'
'TextBox5
'
Me.TextBox5.Location = New System.Drawing.Point(632, 299)
Me.TextBox5.Name = "TextBox5"
Me.TextBox5.Size = New System.Drawing.Size(100, 20)
Me.TextBox5.TabIndex = 49
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!,
Me.AutoScaleMode = System.Windows.Forms.AutoS
Me.ClientSize = New System.Drawing.Size(841, 516)
Me.Controls.Add(Me.Label8)
Me.Controls.Add(Me.TextBox
Me.Controls.Add(Me.Label5)
Me.Controls.Add(Me.Label6)
Me.Controls.Add(Me.Addchil
Me.Controls.Add(Me.TextBox
Me.Controls.Add(Me.TextBox
Me.Controls.Add(Me.Button1
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.btnprev
Me.Controls.Add(Me.btnnext
Me.Controls.Add(Me.btnadd)
Me.Controls.Add(Me.Childgr
Me.Controls.Add(Me.Parentg
Me.Controls.Add(Me.TextBox
Me.Controls.Add(Me.TextBox
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.Childgrd, System.ComponentModel.ISup
CType(Me.Parentgrd, System.ComponentModel.ISup
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
Friend WithEvents Button1 As System.Windows.Forms.Butto
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents btnprevious As System.Windows.Forms.Butto
Friend WithEvents btnnext As System.Windows.Forms.Butto
Friend WithEvents btnadd As System.Windows.Forms.Butto
Friend WithEvents Childgrd As System.Windows.Forms.DataG
Friend WithEvents Parentgrd As System.Windows.Forms.DataG
Friend WithEvents TextBox2 As System.Windows.Forms.TextB
Friend WithEvents TextBox1 As System.Windows.Forms.TextB
Friend WithEvents Label5 As System.Windows.Forms.Label
Friend WithEvents Label6 As System.Windows.Forms.Label
Friend WithEvents Addchild As System.Windows.Forms.Butto
Friend WithEvents TextBox4 As System.Windows.Forms.TextB
Friend WithEvents TextBox3 As System.Windows.Forms.TextB
Friend WithEvents Label8 As System.Windows.Forms.Label
Friend WithEvents TextBox5 As System.Windows.Forms.TextB
End Class
I suppose you got the database download'd ?
ASKER
vbturbo, that doesn't appear to help at all, you're simply hard coding all the values and using an "Add Child" button to add child records.
You're also generating a primary key value by simply taking the key of the last value in memory, adding one, and manually applying that to a child item. I've already said that the value in the child records appears fine, how that's meant to help I'm not sure.
None of this addresses the question of why my DataGrid object is throwing ForeignKey errors.
You're also generating a primary key value by simply taking the key of the last value in memory, adding one, and manually applying that to a child item. I've already said that the value in the child records appears fine, how that's meant to help I'm not sure.
None of this addresses the question of why my DataGrid object is throwing ForeignKey errors.
Hi Ross
Yes that was only to demonstrate one of the two options i made in the solution.
1) the first of the two functionallity's i build is SOLEY for the grids - as your problem description, When i only use the grids is ! add,delete or alter in
parent grid , the same apply for the child grid ,add delete or alter as you like , there are no problems in the solution i uploaded , sorry but i only got
vb.net 2005 , but i does exacly as what you are asking.
2) Was only to demonstrate an alternative as you descripe in your last post.
Well i know this covers a different topic , but setting up the relation as descriped in the article makes exactly what you are looking for
http://support.microsoft.com/kb/310350
slightly modified
Public Function GetData() As DataSet
Dim con As New SqlConnection("server=<SQL Server>;ui d=<myid>;p wd=<mypwd> ;database= northwind" )
'Pull back the recent orders for the parent rows.
Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)
'Get only the appropriate child rows for the preceding parent rows.
Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)
Dim ds As New DataSet()
Dim dt As DataTable
Dim dc As DataColumn
Dim cb As SqlCommandBuilder
'
' Fill DataSet and set DataRelation for navigating in the DataGrid.
'
con.Open()
daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")
daOrders.Fill(ds, "Orders")
daDetails.FillSchema(ds, SchemaType.Mapped, "Details")
daDetails.Fill(ds, "Details")
ds.Relations.Add("OrdDetai l", ds.Tables!Orders.Columns!O rderID, ds.Tables!Details.Columns! OrderID)
'
' Set AutoNumber properties in the Orders DataTable.
'
dt = ds.Tables!Orders
dc = dt.Columns!OrderID
dc.AutoIncrement = True
dc.AutoIncrementSeed = -1
dc.AutoIncrementStep = -1
End Function
vbturbo
Yes that was only to demonstrate one of the two options i made in the solution.
1) the first of the two functionallity's i build is SOLEY for the grids - as your problem description, When i only use the grids is ! add,delete or alter in
parent grid , the same apply for the child grid ,add delete or alter as you like , there are no problems in the solution i uploaded , sorry but i only got
vb.net 2005 , but i does exacly as what you are asking.
2) Was only to demonstrate an alternative as you descripe in your last post.
Well i know this covers a different topic , but setting up the relation as descriped in the article makes exactly what you are looking for
http://support.microsoft.com/kb/310350
slightly modified
Public Function GetData() As DataSet
Dim con As New SqlConnection("server=<SQL
'Pull back the recent orders for the parent rows.
Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)
'Get only the appropriate child rows for the preceding parent rows.
Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)
Dim ds As New DataSet()
Dim dt As DataTable
Dim dc As DataColumn
Dim cb As SqlCommandBuilder
'
' Fill DataSet and set DataRelation for navigating in the DataGrid.
'
con.Open()
daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")
daOrders.Fill(ds, "Orders")
daDetails.FillSchema(ds, SchemaType.Mapped, "Details")
daDetails.Fill(ds, "Details")
ds.Relations.Add("OrdDetai
'
' Set AutoNumber properties in the Orders DataTable.
'
dt = ds.Tables!Orders
dc = dt.Columns!OrderID
dc.AutoIncrement = True
dc.AutoIncrementSeed = -1
dc.AutoIncrementStep = -1
End Function
vbturbo
Else create a new project and place 2 grids on it and name them like this
Parentgrd
Childgrd
and drop one button also.
Then use the database that i uploaded to you and modify the path in the connection string as it suits you
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim daParent As OleDb.OleDbDataAdapter
Dim daChild As OleDb.OleDbDataAdapter
Dim sql As String
Dim myTable As DataTable
Dim myTableCol As DataColumn
Dim foreignkey As Integer
Private myCurrencyManager As CurrencyManager
Dim dsNewRow As DataRow
'place this in your form load event
========================== ========== ========== ========== =======
con.ConnectionString = "PROVIDER=Microsoft.Jet.OL EDB.4.0;Da ta Source = C:\Test.mdb"
sql = "SELECT * FROM Parent"
con.Open()
daParent = New OleDb.OleDbDataAdapter(sql , con)
daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
daParent.Fill(ds, "Parent")
myTable = ds.Tables("Parent")
sql = "SELECT * FROM Child"
daChild = New OleDb.OleDbDataAdapter(sql , con)
daChild.FillSchema(ds, SchemaType.Mapped, "Child")
daChild.Fill(ds, "Child")
con.Close()
Parentgrd.DataSource = ds.Tables("Parent")
Childgrd.DataSource = ds.Tables("Child")
ds.Tables("Parent").Column s("ID").Au toIncremen t = True
ds.Tables("Parent").Column s("ID").Au toIncremen tSeed = -1
ds.Tables("Parent").Column s("ID").Au toIncremen tSeed = -1
ds.Tables("Child").Columns ("ID").Aut oIncrement = True
' Add the relation between Parent/Child
ds.Relations.Add(New DataRelation(relationName: ="relParCh i", parentColumn:=ds.Tables("P arent").Co lumns("ID" ), _
childColumn:=ds.Tables("Ch ild").Colu mns("Forei gnkey"), _
createConstraints:=False))
' Set the Suppliers grid
Parentgrd.DataSource = ds
Parentgrd.DataMember = "Parent"
' Set the Products grid
Childgrd.DataSource = ds
Childgrd.DataMember = "Parent.relParChi"
========================== ========== ========== ========== ========== ===
'Place this in button click event to save the modifications you made to the dataset
========================== ========== ========== ========== ========== ===
Dim cb As New OleDb.OleDbCommandBuilder( daParent)
daParent.Update(ds, "Parent")
Dim cb1 As New OleDb.OleDbCommandBuilder( daChild)
daChild.Update(ds, "Child")
MsgBox("Data updated")
Then you only have the grids to work with.
vbturbo
Parentgrd
Childgrd
and drop one button also.
Then use the database that i uploaded to you and modify the path in the connection string as it suits you
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim daParent As OleDb.OleDbDataAdapter
Dim daChild As OleDb.OleDbDataAdapter
Dim sql As String
Dim myTable As DataTable
Dim myTableCol As DataColumn
Dim foreignkey As Integer
Private myCurrencyManager As CurrencyManager
Dim dsNewRow As DataRow
'place this in your form load event
==========================
con.ConnectionString = "PROVIDER=Microsoft.Jet.OL
sql = "SELECT * FROM Parent"
con.Open()
daParent = New OleDb.OleDbDataAdapter(sql
daParent.FillSchema(ds, SchemaType.Mapped, "Parent")
daParent.Fill(ds, "Parent")
myTable = ds.Tables("Parent")
sql = "SELECT * FROM Child"
daChild = New OleDb.OleDbDataAdapter(sql
daChild.FillSchema(ds, SchemaType.Mapped, "Child")
daChild.Fill(ds, "Child")
con.Close()
Parentgrd.DataSource = ds.Tables("Parent")
Childgrd.DataSource = ds.Tables("Child")
ds.Tables("Parent").Column
ds.Tables("Parent").Column
ds.Tables("Parent").Column
ds.Tables("Child").Columns
' Add the relation between Parent/Child
ds.Relations.Add(New DataRelation(relationName:
childColumn:=ds.Tables("Ch
createConstraints:=False))
' Set the Suppliers grid
Parentgrd.DataSource = ds
Parentgrd.DataMember = "Parent"
' Set the Products grid
Childgrd.DataSource = ds
Childgrd.DataMember = "Parent.relParChi"
==========================
'Place this in button click event to save the modifications you made to the dataset
==========================
Dim cb As New OleDb.OleDbCommandBuilder(
daParent.Update(ds, "Parent")
Dim cb1 As New OleDb.OleDbCommandBuilder(
daChild.Update(ds, "Child")
MsgBox("Data updated")
Then you only have the grids to work with.
vbturbo
Hi
Have you got it going ?
vbturbo
Have you got it going ?
vbturbo
ASKER
Gaaaaah! Found it!!
Sorry for the delay - I left it a few days to concentrate on other areas of the application, thinking I'd be better off taking a break and coming back for a fresh look at this.
Well, today I took that fresh look and fixed it in 5 minutes. I've added a single line to the detail datagrid's Enter event:
EndCurrentEdit()....
I was thinking how it would work if I had two datagrids, and wandering what kind of error would occur if I was still editing a line in the parent datagrid when I created a child record... and as soon as I thought that something clicked. I'd never seen any mention of needing EndCurrentEdit() with plain controls on a form, but added it anyway and as soon as I did, everything worked perfectly.
Such an easy fix in the end, but took ages to spot.
I'm giving you the points for the effort you put into this. Without your help I wouldn't have had the confidence that what I had was actually right, and may never have fixed this problem. I also think this is a solution worth preserving for others to find.
all the best,
Ross
Sorry for the delay - I left it a few days to concentrate on other areas of the application, thinking I'd be better off taking a break and coming back for a fresh look at this.
Well, today I took that fresh look and fixed it in 5 minutes. I've added a single line to the detail datagrid's Enter event:
EndCurrentEdit()....
I was thinking how it would work if I had two datagrids, and wandering what kind of error would occur if I was still editing a line in the parent datagrid when I created a child record... and as soon as I thought that something clicked. I'd never seen any mention of needing EndCurrentEdit() with plain controls on a form, but added it anyway and as soon as I did, everything worked perfectly.
Such an easy fix in the end, but took ages to spot.
I'm giving you the points for the effort you put into this. Without your help I wouldn't have had the confidence that what I had was actually right, and may never have fixed this problem. I also think this is a solution worth preserving for others to find.
all the best,
Ross
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well there are rules (referencial integrity)
if a relation is established then rule is ! parent( 1 to ) table has the primary key , child ( many ) table has the foreign key
when adding rows to tables in a relation , then you have to add the first row to the parent.
Have a look at link http://www.vb-helper.com/howto_net_datagrid_master_child.html
Descripes the steps very simple , since the commandbuilder takes care of the job.
good luck
vbturbo