dataset relations (vb.net)

How can I import my database-relations in dataset-relations.
Now I typ them in as follows :
dim ds as new dataset
...
ds.relations.add("Order_detail", ds.tables("Order".colums("sernr"), ds.tables("Detail").colums("Order_sernr"))

But the same information already exists in my SQLserver !

Thanks.
dekeyzerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQLMasterCommented:

To populate a DataSet with existing primary key constraint information from a data source, you have to call FillSchema method of the DataAdapter, or set the MissingSchemaAction property of the DataAdapter to AddWithKey before calling Fill. Please remember that foreign key constraint information is not included and will need to be created explicitly :-(

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dekeyzerAuthor Commented:
Ok, the primary key i have now but the relation not.
The Table object has the property's Parentrelation and childrelation but these are not filled in.
Any idee how i can populate them ?
0
SQLMasterCommented:

parentRelations and childRelations are read only properties and they can only be read.

After you create relationships in DataSet, you can use these properties to get the required relations
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dekeyzerAuthor Commented:
Can I find my database relations in vb so that i can create the dataset relations with that data.
I want to create the relationships in the dataset with my sql-datarelation information.
In VB6 i can get that information via ado.connection.openschema(adSchemaForeignKeys, Array(Empty, Empty, TableName)).
I don't know if that is possible in ado.net.
0
SQLMasterCommented:
You can use DataTable.constraints property to see foreign and primary key relationships. This will return a constraintscollection that you can iterate to get the required info

Cheers
0
dekeyzerAuthor Commented:
Thanks, now i can make my primarykey constraints but the foreignkey constraints are missing.
Any idee how i can retrieve these form my SQLserver ?
0
SQLMasterCommented:
Use SQLDataAdapter class for connecting to SQL Server. You should get your foreign key constraints in ConstraintsCollection
0
dekeyzerAuthor Commented:
I use the SQLdataadapter for connecting the SQLserver but no foreignkeyconstrains ar in the collection.
Here folows my code :

        Dim da as SqlDataAdapter
        Dim ds as DataSet
        Dim da2 As SqlDataAdapter
        Dim oTable As DataTable
        Dim oConstraint As Constraint
        Dim oColumn As DataColumn
        Dim oFK As ForeignKeyConstraint
        Dim oUK As UniqueConstraint

        sConn = "....."
        da = New SqlDataAdapter
        da2 = New SqlDataAdapter
        ds = New DataSet
        da.SelectCommand = New SqlCommand("Select * from kop")
        da.SelectCommand.Connection = New SqlConnection(sConn)
        da.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da2.SelectCommand = New SqlCommand("Select * from det")
        da2.SelectCommand.Connection = New SqlConnection(sConn)
        da2.MissingSchemaAction = MissingSchemaAction.AddWithKey
        da.Fill(ds, "Order")
        da2.Fill(ds, "Detail")
        oTable = ds.Tables("Order")
        For Each oConstraint In oTable.Constraints
            MessageBox.Show(oConstraint.ConstraintName & " - " & oConstraint.ToString)
            If TypeOf oConstraint Is ForeignKeyConstraint Then
                oFK = CType(oConstraint, ForeignKeyConstraint)
                For Each oColumn In oFK.Columns
                    MessageBox.Show(oColumn.ColumnName())
                Next
            End If
            If TypeOf oConstraint Is UniqueConstraint Then
                oUK = CType(oConstraint, UniqueConstraint)
                For Each oColumn In oUK.Columns
                    MessageBox.Show(oColumn.ColumnName())
                Next
            End If
        Next

What do I wrong ?
0
dekeyzerAuthor Commented:
Sorry in the code above must the line
  da.SelectCommand = New SqlCommand("Select * from kop")
  da.SelectCommand = New SqlCommand("Select * from Order")
and
 da2.SelectCommand = New SqlCommand("Select * from det")
 da2.SelectCommand = New SqlCommand("Select * from Detail")
0
SQLMasterCommented:
Sorry it is not possible to get foreign key constraints :-(
0
dekeyzerAuthor Commented:
I found an solution : the table sysforeignkeys has all the information that i need.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.