Creating datarelation on an expression column

Using ADO.NET I'm trying to create a data relation using an expression column, but I keep on getting the error message "Cannot create a constraint based on Expression column postcode.". Can anyone tell me what Im doing wrong? Code is below

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Data.sql

Imports System.Data.OleDb

Partial Class tbb
    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=xxxxx;UID=sebo27"
        Dim querystring As String = "SELECT blabla.`User`.UserName as USER, blabla.`User`.Postcode AS pcode, blabla.`User`.Tel, blabla.`User`.Email, blabla.`User`.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 dc As DataColumn = New DataColumn("postcode")
        dc.DataType = GetType(String)
        dt.Columns.Add(dc)


        dt.Columns("postcode").Expression = "trim(substring(pcode, 1,3))"



        Dim dv As New DataView
        dv.Table = dt

        dv.Sort = "postcode"

        Dim strPostCodes As String
        For Each drv As DataRowView In dv

            strPostCodes = Trim(strPostCodes & drv("postcode") & ",")
        Next
        'the following removes the trailing comma
        strPostCodes = Mid(strPostCodes, 1, strPostCodes.Length - 1)
        Dim strpostcode As String = Replace(LTrim(Replace(strPostCodes, ",", " ")), " ", "','")
        MsgBox(strpostcode)

        '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, latitude, longitude FROM [uk-postcodes] WHERE postcode IN (" & "'" & strpostcode & "'" & ")"


        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()
        Dim dc1 As DataColumn = New DataColumn("postcode")
        dc1.DataType = GetType(String)
        'dt1.Columns.Add(dc1)
        ds.Tables.Add(dt1)
        Dim dtrParent As DataRow
        Dim dtrChild As DataRow
       
        Dim dre As New DataRelation("CustomersOrders", _
          ds.Tables("Table1").Columns("postcode"), _
          ds.Tables("Table").Columns("postcode"))

        ds.Relations.Add(dre)
        'Display the Category and Child Products Within
        For Each dtrParent In ds.Tables("Categories").Rows
            lblDisplay.Text &= "<h3>" & dtrParent("CategoryName") & "</h3><ul>"
            For Each dtrChild In dtrParent.GetChildRows("Cat_Prod")
                lblDisplay.Text &= "<li>" & dtrChild("ProductName") & "</li>"
            Next
            lblDisplay.Text &= "</ul>"
        Next



        Dim dv1 As New DataView
        dv1.Table = dt1

        dv1.Sort = "Postcode"

        Try
            Me.GridView1.DataSource = dv1
            Me.GridView1.DataBind()
            Me.GridView1.Visible = True

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try


    End Sub
End Class
sebastizAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

SanclerCommented:
One thing you're doing wrong is trying to do what the error message tells you can't do: "Cannot create a constraint based on Expression column postcode."  A datarelation imposes a constraint: this column/field in this record in this table has to match a column/field in a record in that other table.  Constraints work on VALUES.  An Expression column doesn't contain a value.  It contains an "expression" or formula from which a value can be derived, but that's not good enough.

What you will need to do, I think, is revise your SQL statement so that it brings over from the database the Postcode already doctored into the pcode form you want.  I'm not an ODBC expert but perhaps, if you are not sure how to do that yourself, someone who is will chip in with the answer to that.

I should also point out that I'm a Forms man, not a Web man.  But although this is a Web Page I think the rules I've outlined above are applicable over both.  But I can't say whether there might be other issues - Web-specific - in the code you've posted.

Roger
0
sebastizAuthor Commented:
Thanks Sancler. I have a few questions- I have set the enforceconstraints property of the data set to false but still get the error message- So whats the point of this property?
Bringing in the original Postcode isnt a problem- however the original postcode column contains an extended character set (eg NW3 4BB) whereas in order to perform the lookup of longitutude etc I need a smaller character set, as the second database is based on the first half of the postcode only (ie NW3), so I need some way of trimming it. Would a workaround be to create the trimming in the SQL statement so that ADO.NET thinks it is a value?
0
SanclerCommented:
The point of the .EnforceConstraints property is so that it is possible to turn constraints which do exist on and off.  For instance, it is not possible to load data from two tables at the same time.  If table A requires one of its fields to be equal to a field in table B, and table A is loaded before table B is loaded, the contstraint would be violated.  So, to avoid that, .EnforceConstraints would be set to False before table A was loaded and set back to True after both tables had been loaded.  That, I stress, is just one example.

The point here is that what is concerned is not the enforcement of any constraint.  It is the creation of one.

>>
Would a workaround be to create the trimming in the SQL statement so that ADO.NET thinks it is a value?
<<

Yes.  That is what I was trying to suggest by "What you will need to do, I think, is revise your SQL statement so that it brings over from the database the Postcode already doctored into the pcode form you want."

Roger
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
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
.NET Programming

From novice to tech pro — start learning today.