[RESOLVED] Key Constraint Problem

[Megalith]

Hi all,

Ok i have 3 tables in a dataset

Table1 contains items for sale (referenced)
Table2 contains Stores (Parent)
Table3 Contains items in a particular store (Child)

the problem is the data in this case. Table 2 contains a StoreId field as a primary key. Table1 Contains an ItemId field. My problem is in table 3 this contains 3 fields, StoreId, Name and Quantity.

Now my Table1 contains a field called Name and one called PluralName, I need to determine which field in table one relates to the Name field in table3.

The following 2 constraints exist currently in my app

Table2 - StoreId (one) to Table3 - StoreId (many)
Table1 - Name (one) to Table3 - Name (one)

can i add another constraint :-
Table1 - PluralName (one) to Table3 - Name (one) ???

I am presuming if i do i will get a constraint error as the property does not relate on a one to one level if the store has more than one item in stock and vice versa when it has only one in stock.

Any ideas how to get round this?

[eatmycode]

Based on your example, Table 3 is unnecessary.

[Megalith]

table 3 is the important table, it references table 1 and table 2 to display items from a particular store, table1 is all items every store sells and table 2 is each store.

[eatmycode]

You have the format you need then. I'd create a dual primary key based on the StoreID and ItemID from the other 2 tables, and you can add other miscellaneous information into that table.

[Megalith]

the problem is that the data that makes table3 doesn't contain the ItemId. I suppose i could parse Table1 for each item and obtain the ItemId but i was looking for a way to do this without parsing this table 1000's of times to populate table3

[eatmycode]

I'm not sure why you are concerned about parsing; that is what a database is for. If you are using SQL Server then the server will take a snapshot of the query anyway to reduce reads. If you then create a VIEW of the necessary data, that will reduce read times again.

Besides which, someone wanting ALL of the data in the table is not very likely, and they will only want a subset of the data.

[Pradeep1210]

Fill Table3 using this query:

Select * From Table3
inner join Table1 on Table1.Name = Table3.Name
inner join Table2 on Table2.StoreId = Table3.StoreId

can i add another constraint :-
Table1 - PluralName (one) to Table3 - Name (one) ???

You can, but you already have a join condition on Table3.Name. So that would effectively not work.
In case it was a typo and you meant Table3.PluralName then this is how you would do:

Select * From Table3
inner join Table1 on Table1.Name = Table3.Name AND Table1.PluralName = Table3.PluralName 
inner join Table2 on Table2.StoreId = Table3.StoreId

[Megalith]

hey again, appreciate the bouncing of ideas, sadly with this app i am not using sql, the dataset is formed from DataObjects obtained from an xml source using LINQ, the xml is beyond my control.

I could use a view as you suggested and i can use DataSet to LINQ for querying. All the data is resident in memory which again will make the parsing simple. The data in Table1 columns for ItemId, Name and PluralName are Unique so to obtain the ItemID i can query either the Name or Plural Name, depending on Quantity in Table3. shouldn't be too much load thinking about it, and should be only a few lines of code to write. Will have a go, would sooner my tables related via ID anyway as it will allow other ideas to be easier to implement further down the road.

[Megalith]

Fill Table3 using this query:

Select * From Table3
inner join Table1 on Table1.Name = Table3.Name
inner join Table2 on Table2.StoreId = Table3.StoreId

You can, but you already have a join condition on Table3.Name. So that would effectively not work.
In case it was a typo and you meant Table3.PluralName then this is how you would do:

Select * From Table3
inner join Table1 on Table1.Name = Table3.Name AND Table1.PluralName = Table3.PluralName 
inner join Table2 on Table2.StoreId = Table3.StoreId

Problem with that is my field Name on Table3 depends on the Quantity, if Quantity is 1 then it is the same as Name in table1, but if it is over one it is the same as PluralName from Table1.

Thinking i could use RegEx so that it will work with Name whether it is plural or not.

[Pradeep1210]

ok.. so you already have all the data loaded into datasets and you just want to filter data of your interest.
That's easy. Just use the DataTable.Select method, to filter out records of your interest.

Have a look here for more information:
http://msdn.microsoft.com/en-us/library/det4aw50.aspx

[eatmycode]

I'm starting to see the problem. You are using the Name column as an index column, and that is slowing down the system. My only suggestion is to change the Name column to a less intensive type, such as an auto-incrementing column. It really is unnecessary to have the Name replicated in both tables.

[Megalith]

@ eatmycode Yeah this datasource for table3 essentially says that store x has y quantities of item z but i need more information about item z which is available in Table1 (like cost and quantity in storage). Whoever designed the initial system needed a rocket up something but i have no influence over this and it is from a remote server completely beyond my control, if the initial developer had normalised the tables properly then it would be easy

@ pradeep thanks for confirming my initial concern that i cannot constrain 2 columns as i was considering.

[eatmycode]

If all you want is the data in your set, try DataSet.EnforceConstraints = False

Short-term solution I suppose.

[Megalith]

ok.. so you already have all the data loaded into datasets and you just want to filter data of your interest.
That's easy. Just use the DataTable.Select method, to filter out records of your interest.

Have a look here for more information:
http://msdn.microsoft.com/en-us/library/det4aw50.aspx

perfect thanks for the help.

@ eatmycode i guess i can do that while i fill the child table and then re-enable contraints after the datatables are populated.

thread resolved thanks guys

[Megalith]

Ok have not resolved this, how do i mark a thread unresolved again?

Thought this would be a simple task but i am getting errors like (Cannot find column [pairs of Red Socks].... here is the function i am using, the idea is it takes the description of the item (Name) and returns the ItemId

    Private Function getGlam(ByVal Name As String) As Integer
        ' We use Select to get the Id from the glam table
        Dim dr As DataRow()
        Dim Items As DataTable = ds.Tables("Table1") ' which contains the items
        dr = Items.Select("[" + Name + "]")
        If dr.GetUpperBound(0) = 1 Then
            Return CInt(dr(0)(0))
        Else
            MsgBox("Not Unique Error")
        End If
    End Function

I have tried using the following as parameters for the select property

Name - keyword 'of' conflicted so added square brackets.
"[" + Name "]" - Cannot find column [pairs of Red Socks].
"Name LIKE [" + Name + "]" - Cannot find column [pairs of Red Socks].
"Name Or PluralName Like [" + Name + "]" - Cannot find column [pairs of Red Socks].

Any idea how i can get this to search all the columns for the Name value rather than searching for a column. The msdn documentation says "Gets an array of all DataRow objects that match the filter criteria in order of primary key (or lacking one, order of addition.)"

I presumed this meant it would search each column looking for matches on the rows, would my presumption be wrong and therefore this wont fix my issue?

[Megalith]

Ok i have decided to go with using a LINQ to DataSet Query and then parse the result to find the GlamId, i then plan to populate the Table with the GlamId rather than the Name value. Not used Linq with dataSet and already have issues in formulating the query to return a result. i have tried using 2 queries (one compares Name to Name the other compares Name to PluralName) but neither query returned a result.

[eatmycode]

Yeah, you're a bit off with your iteration. Don't bother typing out the datarow objects, that's unnecessary. You can examine the row values directly using the following:

For j As Integer = 0 To table.Rows.Count - 1
            Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}", _
                table.Rows(j).Item(0), table.Rows(j).Item(1), table.Rows(j).Item(2))

That will return all my rows from all my columns. Strangely the Item property needs a column value, so if the [Name] column is the 2nd column in the table, you would use 'Item(1)', you could also put the column name in the Item property if that helps.

[Pradeep1210]

Ok have not resolved this, how do i mark a thread unresolved again?

Thought this would be a simple task but i am getting errors like (Cannot find column [pairs of Red Socks].... here is the function i am using, the idea is it takes the description of the item (Name) and returns the ItemId

    Private Function getGlam(ByVal Name As String) As Integer
        ' We use Select to get the Id from the glam table
        Dim dr As DataRow()
        Dim Items As DataTable = ds.Tables("Table1") ' which contains the items
        dr = Items.Select("[" + Name + "]")
        If dr.GetUpperBound(0) = 1 Then
            Return CInt(dr(0)(0))
        Else
            MsgBox("Not Unique Error")
        End If
    End Function

I have tried using the following as parameters for the select property

Name - keyword 'of' conflicted so added square brackets.
"[" + Name "]" - Cannot find column [pairs of Red Socks].
"Name LIKE [" + Name + "]" - Cannot find column [pairs of Red Socks].
"Name Or PluralName Like [" + Name + "]" - Cannot find column [pairs of Red Socks].

Any idea how i can get this to search all the columns for the Name value rather than searching for a column. The msdn documentation says "Gets an array of all DataRow objects that match the filter criteria in order of primary key (or lacking one, order of addition.)"

I presumed this meant it would search each column looking for matches on the rows, would my presumption be wrong and therefore this wont fix my issue?

It should be in the form of Field=Value.
e.g. myRows = MyDataTable.Select("FieldName = ValueBeingSearched")

From what you have shown, it looks like you are only specifying the field name and not the value you are looking for.

[Megalith]

It should be in the form of Field=Value.
e.g. myRows = MyDataTable.Select("FieldName = ValueBeingSearched")

From what you have shown, it looks like you are only specifying the field name and not the value you are looking for.

Ok will try that, have a feeling i tried already. How would i go about this when i need to compare against 2 columns, is

MyDataTable.Select("Name OR PluralName = [" + Name + "]")

a valid query for this?

Just tried using .Select("Name = [" + Name + "]") gets the following error.

System.Data.EvaluateException was unhandled
Message="Cannot find column [pair of Red Socks]."
Source="System.Data"
StackTrace:
at System.Data.NameNode.Bind(DataTable table, List`1 list)
at System.Data.BinaryNode.Bind(DataTable table, List`1 list)
at System.Data.DataExpression.Bind(DataTable table)
at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
at System.Data.DataTable.Select(String filterExpression)
at SLApp.JobsPage.getGlam(String Name) in C:\Documents and Settings\Megalith\My Documents\Visual Studio 2008\Projects\http example\http example\JobsPage.vb:line 119
at SLApp.JobsPage.jobslist(String xml) in C:\Documents and Settings\Megalith\My Documents\Visual Studio 2008\Projects\http example\http example\JobsPage.vb:line 104
at SLApp.MainSLApp.btnJobs_Click(Object sender, EventArgs e) in C:\Documents and Settings\Megalith\My Documents\Visual Studio 2008\Projects\http example\http example\MainSLApp.vb:line 414
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at SLApp.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
InnerException:

I Guess that using LINQ i will have the same issues?

[Pradeep1210]

Try this:

RowsReturned = MyDataTable.Select("Name = '" & Name & "'")

or

RowsReturned = MyDataTable.Select("Name = '" & Name & "' OR PluralName = '"  & Name & "'")

[Megalith]

Try this:

RowsReturned = MyDataTable.Select("Name = '" & Name & "'")

or

RowsReturned = MyDataTable.Select("Name = '" & Name & "' OR PluralName = '"  & Name & "'")

Tried the second example and it returned no results, thats better than before, will look into why and let you know.

[Pradeep1210]

Tried the second example and it returned no results, thats better than before, will look into why and let you know.

If that caused no error and simply returned zero rows, that means you need to check that the record is present in the datatable.

[Megalith]

ok looked into it and the issue is that the Name columns are not the same in both tables :-s My table1 value has Red Socks whareas my table3 value has Pair of Red Socks. I tried using "Where Name LIKE %'" & Name & "'" but obviously it is the wrong way round as per the table data. Any ideas?

[eatmycode]

Maybe your Select statement isn't returning any rows based on your expression. Don't be over-complicated in your select statement, and your match needs to be EXACT. So if socks has a capital S, you need to specify that.

Equally, your iterations may not be typing out properly.
Call a Select statement with an expression; simply: "Name = Socks". Try the statment without the single quotes! I don't know the make-up of DataObject querying.

Dim foundRows() as DataRow = DataTable.Select(expression)

Always check the count of your rows collection. You will then have to type out the rows. Try working with a console application before you set it up on a form; it's a great timesaver sometimes.

        Dim row As DataRow
        Dim col As DataColumn
        For Each row In foundRows
            For Each col In row.Table.Rows

            Next col
        Next row

[Megalith]

I'm going to try to remove part of the string and use LIKE, not sure how successful it will be so will let you guys know if i have any issues

[eatmycode]

You need the LIKE clause when using the % wildcard:

...
WHERE Name LIKE '%socks';

[Megalith]

Ok guys here it is, this works but it is not fast.

    Private Function getGlam(ByVal Name As String) As Integer
        If Name.Contains("pair of") Then
            Name = Name.Substring(7)
        End If
        Name = Name.Replace("'", "''")
        Dim dr As DataRow()
        Dim Items As DataTable = ds.Tables("Table1") ' which contains the items
        If Name.Length > 8 Then
            dr = Items.Select("PluralName LIKE '%" & Name & "%' OR Name LIKE '%" & Name & "%'")
        Else
            dr = Items.Select("Name = '" & Name & "' OR PluralName = '" & Name & "'")
        End If

        If dr.Length = 1 Then
            Return CInt(dr(0)(0))
        ElseIf dr.Length > 1 Then
            MsgBox("Not Unique Error")
        Else
            MsgBox("Invalid Data Error")
        End If
    End Function

As i said it work without any errors and without throwing one of my MsgBoxes. My only issue is it takes 20 seconds to run which is far too slow for my needs and would lead to the user thinking the app had crashed.

[eatmycode]

If the Name & PluralName columns are large it will take a long time to run because they are text columns. Also you are searching every possible combination with the wildcard clause. Which is why you should try and update your own database to use an integer foreign key between tables. And this will radically improve performance.

[Pradeep1210]

ok looked into it and the issue is that the Name columns are not the same in both tables :-s My table1 value has Red Socks whareas my table3 value has Pair of Red Socks. I tried using "Where Name LIKE %'" & Name & "'" but obviously it is the wrong way round as per the table data. Any ideas?

The % should be inside the quotes.

So it would look like this (or something similar):

RowsReturned = MyDataTable.Select("Name LIKE '%" & Name & "'")   ' e.g. "Name LIKE '%Red Socks'"

[Megalith]

I have just realised there is an image url column which will be a unique column for both tables. this should halve the time i guess. got a few things i need to do but will look at that option a lil later just to confirm the columns are indeed matching.

[Megalith]

Just to confirm the ImageUrl field is identical in both tables which has simplified things a lot but i'm still not happy with the performance on this part of the code. Also when i ran this code i got an error because one store had an item which wasn't in the main inventory of items, not much i can do about that but i have worked around it by manually adding a dummy row to Table1.