Solved

Creating datarelation on an expression column

Posted on 2007-03-21
3
849 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:sebastiz
  • 2
3 Comments
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
 

Author Comment

by:sebastiz
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

17 Experts available now in Live!

Get 1:1 Help Now