Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • Last Modified:

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
0
sebastiz
Asked:
sebastiz
  • 2
1 Solution
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now