Solved

Creating datarelation on an expression column

Posted on 2007-03-21
3
862 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
[X]
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
  • 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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