Solved

Datarelation causing odd result

Posted on 2007-03-29
3
213 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:sebastiz
  • 2
3 Comments
 

Author Comment

by:sebastiz
ID: 18819648
Sorry.... This is the code:


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;DESCRIPTION=blabla;DSN=blabla;OPTION=0;PORT=0;SERVER=xxxxxxx;UID=sebo27"
        Dim querystring As String = "SELECT blabla.`User`.UserName as USER, blabla.`User`.Postcode AS pcode_orig, LEFT(blabla.`User`.Postcode, LENGTH(blabla.`User`.Postcode) - LOCATE(' ', REVERSE(blabla.`User`.Postcode))) as pcode,blabla.`User`.Tel, blabla.`User`.Email, blabla.`User`.Address AS Address, blabla.Customer.CustomerID FROM blabla.Customer, blabla.`User` WHERE blabla.Customer.UserID = blabla.`User`.UserID AND blabla.`User`.Postcode IS NOT NULL"


        Dim connection As New OdbcConnection(connectionstring)
        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.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\UKPostcodes.mdb;Persist Security Info=False"

        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("Connectpcodes", _
        ds.Tables("Table").Columns("pcode"), _
        ds.Tables("Table1").Columns("pcode"))
        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("USER"), dtrParent("pcode"), dtrChild("longitude"))
               
               

               


            Next i
        Next
    End Sub
End Class

0
 

Author Comment

by:sebastiz
ID: 18869955
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?

0
 

Accepted Solution

by:
EE_AutoDeleter earned 0 total points
ID: 18962744
sebastiz,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now