sebastiz
asked on
Datarelation causing odd result
I am getting odd results which I suspect is related to a datarelation problem.
I am trying to get the longitude and latitude for various postcodes which are supplied in customer addresses. The results i get are all rather odd with the location for the Birmingham postcode being in Scotland!
I think this is related to the constraints in the datarelationship I set up particularly as when i set enableFocre Constraints to true it generates a fault (and not if set to false)- "This constraint cannot be enabled as not all values have corresponding parent values."
Any ideas
Seb
I am trying to get the longitude and latitude for various postcodes which are supplied in customer addresses. The results i get are all rather odd with the location for the Birmingham postcode being in Scotland!
I think this is related to the constraints in the datarelationship I set up particularly as when i set enableFocre Constraints to true it generates a fault (and not if set to false)- "This constraint cannot be enabled as not all values have corresponding parent values."
Any ideas
Seb
ASKER
Ive sorted this- it was related to the for each statement and the fact the dtrChild was being used before it was defined. Can I get the points refunded?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Data.sql
Imports System.Data.OleDb
Partial Class NHSmap3
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionstring As String = "DATABASE=blabla;DESCRIPTI
Dim querystring As String = "SELECT blabla.`User`.UserName as USER, blabla.`User`.Postcode AS pcode_orig, LEFT(blabla.`User`.Postcod
Dim connection As New OdbcConnection(connections
Dim dcom As New OdbcCommand(querystring, connection)
connection.Open()
Dim dbr As OdbcDataReader = dcom.ExecuteReader()
Dim dt As New Data.DataTable("Table")
Dim ds As New DataSet
'ds.EnforceConstraints = False
ds.Tables.Add(dt)
dt.Load(dbr)
dbr.Close()
Dim dv As New DataView
dv.Table = dt
dv.Sort = "pcode"
'Use the Access database to translate postcodes into longitudes and latitudes
Dim conn As String = "Provider=Microsoft.Jet.OL
Dim cmd As String = "SELECT postcode AS pcode, latitude, longitude FROM [uk-postcodes]"
Dim conn1 As New OleDbConnection(conn)
Dim dcom1 As New OleDbCommand(cmd, conn1)
conn1.Open()
Dim dbr1 As OleDbDataReader = dcom1.ExecuteReader()
Dim dt1 As New Data.DataTable("Table1")
dt1.Load(dbr1)
conn1.Close()
ds.Tables.Add(dt1)
ds.EnforceConstraints = False
Dim dre As New DataRelation("Connectpcode
ds.Tables("Table").Columns
ds.Tables("Table1").Column
Dim dv1 As New DataView
ds.Relations.Add(dre)
'Display the Category and Child Products Within
Dim dtrParent As DataRow
Dim dtrChild As DataRow
Dim dt2 As New DataTable("Merge")
Dim dc2 As New DataColumn("USER")
Dim dc3 As New DataColumn("pcode")
Dim dc4 As New DataColumn("longitude")
Dim dc5 As New DataColumn("latitude")
dt2.Columns.Add(dc2)
dt2.Columns.Add(dc3)
dt2.Columns.Add(dc4)
For Each dtrParent In ds.Tables("Table").Rows
lblDisplay.Text &= "<h3>" & dtrParent("pcode") & dtrParent("USER") & " </h3><ul>"
For Each dtrChild In dtrParent.GetChildRows(dre
lblDisplay.Text &= "<li>" & dtrChild("pcode") & dtrChild("latitude") & "</li>"
Next
lblDisplay.Text &= "</ul>"
Next
Dim i As Integer
For Each dtrParent In ds.Tables("Table").Rows
For i = 1 To 3
dt2.Rows().Add(dtrParent("
Next i
Next
End Sub
End Class