Link to home
Start Free TrialLog in
Avatar of myxiplx
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
Avatar of vbturbo
vbturbo
Flag of Denmark image

Hi

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
Avatar of myxiplx
myxiplx

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.
Hi

Are you sure that you don't have any constraint's added to your relation.

        ds.Relations.Add(New DataRelation(relationName:="relOrdersCompany", parentColumn:=ds.Tables("Orders").Columns("CompanyID"), _
         childColumn:=ds.Tables("Company").Columns("CompanyID"), _
         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



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
Avatar of myxiplx

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
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,Mailaddress,Fname,Lname,Katagori FROM parenttable"
        da.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
        da.Fill(ds, "parenttable")

        ds.Tables("parenttable").Columns("Id").AutoIncrement = True
        pk(0) = ds.Tables("parenttable").Columns("Id")   '<--- the collumname of your primary key
        ds.Tables("parenttable").PrimaryKey = 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,Mailaddress,Fname,Lname,Katagori FROM parenttable"
        DA1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
        DA1.Fill(ds, "childtable")
        ds.Tables("childtable").Columns("Id").AutoIncrement = True


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
correction

sSql = "SELECT Priority,Id,Firmname,Mailaddress,Fname,Lname,Katagori FROM childtable"
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
Avatar of myxiplx

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
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.OLEDB.4.0;Data 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").Columns("ID").AutoIncrement = True
        ds.Tables("Parent").Columns("ID").AutoIncrementSeed = -1
        ds.Tables("Parent").Columns("ID").AutoIncrementSeed = -1

        ds.Tables("Child").Columns("ID").AutoIncrement = 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.Count
        foreignkey = ds.Tables("Parent").Rows(tmp - 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.Add(dsNewRow)
    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.Add(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:="relParChi", parentColumn:=ds.Tables("Parent").Columns("ID"), _
         childColumn:=ds.Tables("Child").Columns("Foreignkey"), _
         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(myTable), 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(myCurrencyManager)
    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
And add this into the Form1.Designer.vb

    Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.Label4 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.btnprevious = New System.Windows.Forms.Button
        Me.btnnext = New System.Windows.Forms.Button
        Me.btnadd = New System.Windows.Forms.Button
        Me.Childgrd = New System.Windows.Forms.DataGrid
        Me.Parentgrd = New System.Windows.Forms.DataGrid
        Me.TextBox2 = New System.Windows.Forms.TextBox
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.Label5 = New System.Windows.Forms.Label
        Me.Label6 = New System.Windows.Forms.Label
        Me.Addchild = New System.Windows.Forms.Button
        Me.TextBox4 = New System.Windows.Forms.TextBox
        Me.TextBox3 = New System.Windows.Forms.TextBox
        Me.Label8 = New System.Windows.Forms.Label
        Me.TextBox5 = New System.Windows.Forms.TextBox
        CType(Me.Childgrd, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.Parentgrd, System.ComponentModel.ISupportInitialize).BeginInit()
        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.UseVisualStyleBackColor = 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.AllowNavigation = False
        Me.Childgrd.CaptionBackColor = System.Drawing.Color.FromArgb(CType(CType(255, Byte), Integer), CType(CType(255, Byte), Integer), CType(CType(192, Byte), Integer))
        Me.Childgrd.CaptionForeColor = System.Drawing.Color.DarkOliveGreen
        Me.Childgrd.CaptionText = "Child"
        Me.Childgrd.DataMember = ""
        Me.Childgrd.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.Childgrd.Location = New System.Drawing.Point(17, 192)
        Me.Childgrd.Name = "Childgrd"
        Me.Childgrd.PreferredColumnWidth = 101
        Me.Childgrd.Size = New System.Drawing.Size(498, 144)
        Me.Childgrd.TabIndex = 31
        '
        'Parentgrd
        '
        Me.Parentgrd.AllowNavigation = False
        Me.Parentgrd.CaptionBackColor = System.Drawing.Color.FromArgb(CType(CType(255, Byte), Integer), CType(CType(255, Byte), Integer), CType(CType(192, Byte), Integer))
        Me.Parentgrd.CaptionForeColor = System.Drawing.Color.DarkOliveGreen
        Me.Parentgrd.CaptionText = "Parent"
        Me.Parentgrd.DataMember = ""
        Me.Parentgrd.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.Parentgrd.Location = New System.Drawing.Point(17, 25)
        Me.Parentgrd.Name = "Parentgrd"
        Me.Parentgrd.PreferredColumnWidth = 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.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(841, 516)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.TextBox5)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.Addchild)
        Me.Controls.Add(Me.TextBox4)
        Me.Controls.Add(Me.TextBox3)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.btnprevious)
        Me.Controls.Add(Me.btnnext)
        Me.Controls.Add(Me.btnadd)
        Me.Controls.Add(Me.Childgrd)
        Me.Controls.Add(Me.Parentgrd)
        Me.Controls.Add(Me.TextBox2)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.Childgrd, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.Parentgrd, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents btnprevious As System.Windows.Forms.Button
    Friend WithEvents btnnext As System.Windows.Forms.Button
    Friend WithEvents btnadd As System.Windows.Forms.Button
    Friend WithEvents Childgrd As System.Windows.Forms.DataGrid
    Friend WithEvents Parentgrd As System.Windows.Forms.DataGrid
    Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents Addchild As System.Windows.Forms.Button
    Friend WithEvents TextBox4 As System.Windows.Forms.TextBox
    Friend WithEvents TextBox3 As System.Windows.Forms.TextBox
    Friend WithEvents Label8 As System.Windows.Forms.Label
    Friend WithEvents TextBox5 As System.Windows.Forms.TextBox

End Class
I suppose you got the database download'd ?
Avatar of myxiplx

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.

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=<SQLServer>;uid=<myid>;pwd=<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("OrdDetail", ds.Tables!Orders.Columns!OrderID, 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
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.OLEDB.4.0;Data 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").Columns("ID").AutoIncrement = True
        ds.Tables("Parent").Columns("ID").AutoIncrementSeed = -1
        ds.Tables("Parent").Columns("ID").AutoIncrementSeed = -1

        ds.Tables("Child").Columns("ID").AutoIncrement = True

        ' Add the relation between Parent/Child

        ds.Relations.Add(New DataRelation(relationName:="relParChi", parentColumn:=ds.Tables("Parent").Columns("ID"), _
         childColumn:=ds.Tables("Child").Columns("Foreignkey"), _
         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
Hi

Have you got it going ?

vbturbo
Avatar of myxiplx

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
ASKER CERTIFIED SOLUTION
Avatar of vbturbo
vbturbo
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial