Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Creating datarelation on an expression column

Posted on 2007-03-21
3
860 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
ID: 18768264
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
ID: 18769574
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
ID: 18769731
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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