Hi there,
Is it possible to add a unique constraint on two columns in a table, so that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?
i.e.
column1 column2
1 1
1 2
2 1
1 1 <-- should not be able to add this record as combination
already exists,
thanks for any help
regds
Brian 7 6617
Brian,
Yes, it is. When creating the UniqueConstraint instance to apply to a
DataTable, you can pass in an array of DataColumn instances representing the
columns, that together, should be unique.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com... Hi there,
Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...?
i.e. column1 column2 1 1 1 2 2 1 1 1 <-- should not be able to add this record as combination already exists,
thanks for any help regds Brian
Brian,
Have you tried something like:
try
{
DataTable table = new DataTable();
table.Columns.Add("column1", typeof(int));
table.Columns.Add("column2", typeof(int));
Constraint constraint = new UniqueConstraint("constraint1",
new DataColumn[] {table.Columns["column1"],
table.Columns["column2"]}, false);
table.Constraints.Add(constraint);
table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {1,1});
}
catch (Exception ex)
{
Debug.WriteLine(ex, "Exception");
}
Hope this helps
Jay
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:E6**********************************@microsof t.com...
| Hi there,
|
| Is it possible to add a unique constraint on two columns in a table, so
that
| the constraint is a composite of the two? i.e. these two columns together
| should be unique...?
|
| i.e.
| column1 column2
| 1 1
| 1 2
| 2 1
| 1 1 <-- should not be able to add this record as combination
| already exists,
|
| thanks for any help
| regds
| Brian
Hi Jay,
thanks for your help, i had tried this but that actually ensures that that
each of the columns is unique, not that the combination of both is unique.
found a solutions anyways, i added a new key on the dataset that had the two
columns i was interested in.
thanks for your help
brian
"Jay B. Harlow [MVP - Outlook]" wrote: Brian, Have you tried something like:
try { DataTable table = new DataTable(); table.Columns.Add("column1", typeof(int)); table.Columns.Add("column2", typeof(int)); Constraint constraint = new UniqueConstraint("constraint1", new DataColumn[] {table.Columns["column1"], table.Columns["column2"]}, false); table.Constraints.Add(constraint);
table.Rows.Add(new object[] {1,1}); table.Rows.Add(new object[] {1,2}); table.Rows.Add(new object[] {2,1}); table.Rows.Add(new object[] {1,1}); } catch (Exception ex) { Debug.WriteLine(ex, "Exception"); }
Hope this helps Jay
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message news:E6**********************************@microsof t.com... | Hi there, | | Is it possible to add a unique constraint on two columns in a table, so that | the constraint is a composite of the two? i.e. these two columns together | should be unique...? | | i.e. | column1 column2 | 1 1 | 1 2 | 2 1 | 1 1 <-- should not be able to add this record as combination | already exists, | | thanks for any help | regds | Brian
hi nicholas
thanks for your help, i had tried this but that actually ensures that that
each of the columns is unique, not that the combination of both is unique.
found a solutions anyways, i added a new key on the dataset that had the two
columns i was interested in and this works just fine
thanks for your help
brian
"Nicholas Paldino [.NET/C# MVP]" wrote: Brian,
Yes, it is. When creating the UniqueConstraint instance to apply to a DataTable, you can pass in an array of DataColumn instances representing the columns, that together, should be unique.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard.caspershouse.com
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message news:E6**********************************@microsof t.com... Hi there,
Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...?
i.e. column1 column2 1 1 1 2 2 1 1 1 <-- should not be able to add this record as combination already exists,
thanks for any help regds Brian
Brian,
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
Have you? What flavor of the framework?
In VS.NET 2003 (.NET 1.1 SP1) it ensures that the combination of both
columns are unique! In your example it throws an exception when you try to
add the second "1, 1" pair. If it was ensuring each column was unique then
it would fail when you attempted to add "1,2", as "1,1" already exists.
| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
Note the third parameter to the UniqueConstraint construtor I used indicates
if this constraint is the primary key or not...
Try the following adds, which line throws the constraint exception?
table.Rows.Add(new object[] {1,1});
table.Rows.Add(new object[] {1,2});
table.Rows.Add(new object[] {1,3});
table.Rows.Add(new object[] {1,4});
table.Rows.Add(new object[] {2,1});
table.Rows.Add(new object[] {2,2});
table.Rows.Add(new object[] {2,3});
table.Rows.Add(new object[] {2,4});
table.Rows.Add(new object[] {1,1});
It again should be:
table.Rows.Add(new object[] {1,1});
As that line has the duplicate.
Hope this helps
Jay
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message
news:9D**********************************@microsof t.com...
| Hi Jay,
|
| thanks for your help, i had tried this but that actually ensures that that
| each of the columns is unique, not that the combination of both is unique.
|
| found a solutions anyways, i added a new key on the dataset that had the
two
| columns i was interested in.
|
| thanks for your help
| brian
|
| "Jay B. Harlow [MVP - Outlook]" wrote:
|
| > Brian,
| > Have you tried something like:
| >
| > try
| > {
| > DataTable table = new DataTable();
| > table.Columns.Add("column1", typeof(int));
| > table.Columns.Add("column2", typeof(int));
| > Constraint constraint = new UniqueConstraint("constraint1",
| > new DataColumn[] {table.Columns["column1"],
| > table.Columns["column2"]}, false);
| > table.Constraints.Add(constraint);
| >
| > table.Rows.Add(new object[] {1,1});
| > table.Rows.Add(new object[] {1,2});
| > table.Rows.Add(new object[] {2,1});
| > table.Rows.Add(new object[] {1,1});
| > }
| > catch (Exception ex)
| > {
| > Debug.WriteLine(ex, "Exception");
| > }
| >
| > Hope this helps
| > Jay
| >
| > "Brian Keating" <Br**********@discussions.microsoft.com> wrote in
message
| > news:E6**********************************@microsof t.com...
| > | Hi there,
| > |
| > | Is it possible to add a unique constraint on two columns in a table,
so
| > that
| > | the constraint is a composite of the two? i.e. these two columns
together
| > | should be unique...?
| > |
| > | i.e.
| > | column1 column2
| > | 1 1
| > | 1 2
| > | 2 1
| > | 1 1 <-- should not be able to add this record as
combination
| > | already exists,
| > |
| > | thanks for any help
| > | regds
| > | Brian
| >
| >
| >
Hi Jay,
When i think back yoiu are more than likely right, acually yes i would
agree, you are right.
My mistake was that i didn't really have 2 columns on the unique key, i had
3! and it was only when i created the extra key that i added this column, so
basically my unique constraint with with 2 coulmn had duplicated when i went
to load it and so i said... shit ... not working and went back to the drawing
board.
thanks again,
Brian
"Jay B. Harlow [MVP - Outlook]" wrote: Brian, | thanks for your help, i had tried this but that actually ensures that that | each of the columns is unique, not that the combination of both is unique. Have you? What flavor of the framework?
In VS.NET 2003 (.NET 1.1 SP1) it ensures that the combination of both columns are unique! In your example it throws an exception when you try to add the second "1, 1" pair. If it was ensuring each column was unique then it would fail when you attempted to add "1,2", as "1,1" already exists.
| found a solutions anyways, i added a new key on the dataset that had the two | columns i was interested in. Note the third parameter to the UniqueConstraint construtor I used indicates if this constraint is the primary key or not...
Try the following adds, which line throws the constraint exception?
table.Rows.Add(new object[] {1,1}); table.Rows.Add(new object[] {1,2}); table.Rows.Add(new object[] {1,3}); table.Rows.Add(new object[] {1,4}); table.Rows.Add(new object[] {2,1}); table.Rows.Add(new object[] {2,2}); table.Rows.Add(new object[] {2,3}); table.Rows.Add(new object[] {2,4});
table.Rows.Add(new object[] {1,1});
It again should be: table.Rows.Add(new object[] {1,1});
As that line has the duplicate.
Hope this helps Jay "Brian Keating" <Br**********@discussions.microsoft.com> wrote in message news:9D**********************************@microsof t.com... | Hi Jay, | | thanks for your help, i had tried this but that actually ensures that that | each of the columns is unique, not that the combination of both is unique. | | found a solutions anyways, i added a new key on the dataset that had the two | columns i was interested in. | | thanks for your help | brian | | "Jay B. Harlow [MVP - Outlook]" wrote: | | > Brian, | > Have you tried something like: | > | > try | > { | > DataTable table = new DataTable(); | > table.Columns.Add("column1", typeof(int)); | > table.Columns.Add("column2", typeof(int)); | > Constraint constraint = new UniqueConstraint("constraint1", | > new DataColumn[] {table.Columns["column1"], | > table.Columns["column2"]}, false); | > table.Constraints.Add(constraint); | > | > table.Rows.Add(new object[] {1,1}); | > table.Rows.Add(new object[] {1,2}); | > table.Rows.Add(new object[] {2,1}); | > table.Rows.Add(new object[] {1,1}); | > } | > catch (Exception ex) | > { | > Debug.WriteLine(ex, "Exception"); | > } | > | > Hope this helps | > Jay | > | > "Brian Keating" <Br**********@discussions.microsoft.com> wrote in message | > news:E6**********************************@microsof t.com... | > | Hi there, | > | | > | Is it possible to add a unique constraint on two columns in a table, so | > that | > | the constraint is a composite of the two? i.e. these two columns together | > | should be unique...? | > | | > | i.e. | > | column1 column2 | > | 1 1 | > | 1 2 | > | 2 1 | > | 1 1 <-- should not be able to add this record as combination | > | already exists, | > | | > | thanks for any help | > | regds | > | Brian | > | > | >
Hi Nicholas,
you were correct, i actually had 3 coulmns so when i went to load it i got
duplicates... dough!! i didn't design the database so i only noticed there
were 3 columns in the key at a later stage. (creating the extra key actually
but it's only hit me now after seeing the Jay B. Harlow told me that
UniqueContatrian was really what i though it was.
thanks brian
"Nicholas Paldino [.NET/C# MVP]" wrote: Brian,
Yes, it is. When creating the UniqueConstraint instance to apply to a DataTable, you can pass in an array of DataColumn instances representing the columns, that together, should be unique.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard.caspershouse.com
"Brian Keating" <Br**********@discussions.microsoft.com> wrote in message news:E6**********************************@microsof t.com... Hi there,
Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...?
i.e. column1 column2 1 1 1 2 2 1 1 1 <-- should not be able to add this record as combination already exists,
thanks for any help regds Brian This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marty |
last post by:
Hi,
Ok I use the OLEDBConnector and dataset to retrieve data from my Access DB.
I have a problem to read/parse the dataset and I would like to know if I
am using the right object to reach my...
|
by: TechNoHick |
last post by:
I am using a dataset that I fill with XML data. When I try and use the
Dim curRow As DataRow = dtGalleries.Rows.Find(GalleryId)
I keep getting an error that the primary key doesn’t exist. I...
|
by: nima |
last post by:
Hi
I have two tables in my database. PRODUCT and PRODUCT_GROUP. PRODUCT has a foreign key to PRODUCT_GROUP. I generated a typed dataset using VS.net and added a relationship between the two...
|
by: Christopher Weaver |
last post by:
I'm trying to insert a new Row within an existing Table within an existing
DataSet using the following:
DataRow NewTaskRow = dsTaskActivities.Tables.NewRow();...
|
by: Paul |
last post by:
I'm trying to use the Duwamish 7.0 Framework to make a
distributed application.
I re-wrote parts of the application to work with Students
instead of Customers. So instead of a unique email...
|
by: Roy Lawson |
last post by:
I am having no problems connecting to a DB, creating a DataAdapter,
and creating a dataset...and connecting to the data. Using the
builtin data objects to do all this.
My only problem now is...
|
by: Geoff Jones |
last post by:
Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new column
to the beginning of the table
and to fill...
|
by: Monty M. |
last post by:
Hello;
I was wondering if anyone can assist me with this problem. Here are
the tools I am using:
Language: C#
Database: MS SQL Server 2000
Application: Visual Studio 2005
1. I have a...
|
by: BostonNole |
last post by:
Is there a way to create an index (non-unique) constraint on a
DataTable column?
Here is some code sample I would like to run, but the constraint can
not be unique.
Dim IDDataColumn As...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |