ADO.NET Relationships

  • Thread starter Thread starter Mario Acevedo-Bengoechea
  • Start date Start date
M

Mario Acevedo-Bengoechea

I have two tables, Students and Citizenship. The tables
are related by the Citizenship.CitiNum as the Primary Key
table and Students.Citizenship as the Child. Both are
defined in SQL Server 2000 as tinyint.


Dim StAdapter As SqlDataAdapter = Nothing
sql = "SELECT * FROM Citizenship; SELECT * FROM
Students"
conn = New SqlConnection
conn.ConnectionString = ConnectionString
conn.Open()
StAdapter = New SqlDataAdapter(sql, conn)
StDS = New DataSet("StData")
StAdapter.Fill(StDS, "Citizenship")
StAdapter.Fill(StDS, "Students")
Dim ParentCol As DataColumn = New DataColumn
Dim ChildCol As DataColumn = New DataColumn
ChildCol = StDS.Tables("Students").Columns
("Citizenship")
ParentCol = StDS.Tables("Citizenship").Columns
("CitiNum")
Dim StudentToCitizen As DataRelation = New
DataRelation("St2Cit", ParentCol, ChildCol, True)
StDS.Relations.Add(StudentToCitizen)

When running the code I get that the fields are not of
the same data type. When I check the ChildCom and
ParentCol in the watch windows I see that the system is
bringing Students.Citizenship as System.String instead of
System.Byte. The ParentCol, however is being brought in
as System.Byte without any problems. I tried loading the
schema using:

StDataAdapter.fillschema(StDs.Tables
("Students"),SchemaType.Mapped)
StDataAdapter.fillschema(StDs.Tables
("Citizenship"),SchemaType.Mapped)

I also tried SchemaType.Source but did not work either.

I have no clue of what is going on and help will be
appreciated.

Thank you,

Mario Acevedo-Bengoechea
 
What happens if you change the datatype to Int? Do you have a small project that repros the problem and script to create the tables that you can send to me to try to repro this
inhouse?...

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------
 
I changed the Data Type to Int and I am still having the
problem. I have not created the SQL Scripts yet because
I am still at a very early stage and just did a quick
table using SQL Enterprise Manager. I did the following
code changes to the VB.NET code in which I use multiple
adapters and then merge the resulting tables and it
worked. I think that the way I was using should work, at
least according to some books I've been reading but I
can't be absolutely certain: (keep in mind that objects
not Dim'd here were declared at the form level)

Dim StAdapter As SqlDataAdapter = Nothing
Dim CitiAdapter As SqlDataAdapter = Nothing
Dim RelAdapter As SqlDataAdapter = Nothing
Dim CitiDS As DataSet = Nothing
Dim RelDS As DataSet = Nothing
Dim sql2 As String = "SELECT * FROM Citizenship"
Dim sql3 As String = "SELECT * FROM Religion"
sql = "SELECT * FROM Students"
conn = New SqlConnection
conn.ConnectionString = ConnectionString
conn.Open()
StAdapter = New SqlDataAdapter(sql, conn)
CitiAdapter = New SqlDataAdapter(sql2, conn)
RelAdapter = New SqlDataAdapter(sql3, conn)
StDS = New DataSet("Students")
CitiDS = New DataSet("Citizenship")
RelDS = New DataSet("Religion")
CitiAdapter.Fill(CitiDS, "Citizenship")
StAdapter.Fill(StDS, "Students")
RelAdapter.Fill(RelDS, "Religion")
StDS.Merge(CitiDS)
StDS.Merge(RelDS)
CitiDS.Dispose()
CitiAdapter.Dispose()
RelDS.Dispose()
RelAdapter.Dispose()
Dim ParentCol As DataColumn = New DataColumn
Dim ChildCol As DataColumn = New DataColumn
ChildCol = StDS.Tables("Students").Columns
("Citizenship")
ParentCol = StDS.Tables("Citizenship").Columns
("CitiNum")
Dim StudentToCitizen As DataRelation = New
DataRelation("St2Cit", ParentCol, ChildCol, True)
ParentCol = StDS.Tables("Religion").Columns
("ChurchID")
ChildCol = StDS.Tables("Students").Columns
("Church")
Dim StudentToChurch As DataRelation = New
DataRelation("St2Rel", ParentCol, ChildCol, True)
StDS.Relations.Add(StudentToCitizen)
StDS.Relations.Add(StudentToChurch)

I'll try to prepare a "repro package" to send you.

Greetings,

Mario Acevedo
-----Original Message-----
What happens if you change the datatype to Int? Do you
Click to expand...
have a small project that repros the problem and script
to create the tables that you can send to me to try to
repro this
inhouse?...

Want to know more? Check out the MSDN Library at
Click to expand...
http://msdn.microsoft.com or the Microsoft Knowledge
Base at http://support.microsoft.com
 
Oh, I see I just looked over your previous code again and missed that... Now for the relationships to work right in.Net you would need to use a dataadapter for each table as you
have in the second sample... the Why it is changing the datatype using one method and not when using the other seems to be the real question... However to do the relationships
with ADO.Net you will have to use teh method you employed in this code...

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------
 
Back
Top