Validating data.

A

Anibal

Hi everybody, this is my first post in this group.

I'm working with an Access DB and i have the following code to validate
fields with unique indexes (no duplicates):
'Field is a DataColumn
If Field.Unique Then
If Field.Table.Rows.Contains(NewData) Then
Return "Error: This field can't have duplicate values."
End If
End If

The problem that i found is that apparently the property 'Unique' works only
for Primary Keys fields.
So, how do i make a similar validation but with fields with unique indexes
only (no Primary Key)?

The other doubt that i have is that the property 'DataColumn.DefaultValue'
doesn't set itself to anything when i get the values from a field wich has a
default value.

Note: I fill a DataSet with Fill and FillSchema methods in order to do the
tests.

Thanks.
 
S

Sahil Malik

The problem that i found is that apparently the property 'Unique' works
only
for Primary Keys fields.
Click to expand...

<--- Nope, UniqueConstraint doesn't have to be a primary key. Esp. in a
dataset, which is completely disconnected from the d/b, a primary key even
can be specified as UniqueConstraint in a dataset.
The other doubt that i have is that the property 'DataColumn.DefaultValue'
doesn't set itself to anything when i get the values from a field wich has
a
default value.
Click to expand...

Fill will fill the data only, not schema. You'll have to set DefaultValue
yourself. FillSchema might or might not fill DefaultValue, and that is
specific to the .NET data provider you are using.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
A

Anibal

Hello, first thanks for the answer,
Second;
<--- Nope, UniqueConstraint doesn't have to be a primary key. Esp. in a
dataset, which is completely disconnected from the d/b, a primary key even
can be specified as UniqueConstraint in a dataset.
Click to expand...

But then i don't ger it. I am using an Access database and for some "unique"
fields (indexed and without duplicates) that are not primary keys, the
'Unique' property is set to 'False' at run-time when it should be set to
'True' when using my validation code (posted in the firt message).
Why?
FillSchema might or might not fill DefaultValue, and that is specific to
the .NET data provider you are using.
Click to expand...

Well, i am using a simple OleDb connection to an Access 2000 databas. So,
the 'DefaultValue' property doesn't set itself in this context?

Thank you again.
 
S

Sahil Malik

Anibal,

UniqueConstraint is something that is in ADO.NET, and that has no direct
connection/relation with setting a column's Indexed property to Yes (No
Duplicates).

Picture it this way, a datatable lives under System.Data (and not under
System.Data.OleDb). You use the same System.Data.DataTable for
SqlClient/OracleClient/OleDb/anything else; It is common to all
databases/.net providers, and logically so, it has no connection with what
you might set in your access database. It is *completely* disconnected.

In other words, setting a column's indexed properties to Yes (No Duplicates)
is a completely different animal, totally disconnected, from specifying
UniqueConstraint on a DataColumn.

You might ask WHY was ADO.NET designed this way? Well because DataSets and
DataTables are supposed to work in a disconnected fashion. They might be
filled from sources other than databases. They might need to work between a
webservice and a database. You might even write a ADO.NET provider on an
MSMQ queue as demonstrated in chapter #15 of my book. Being completely
disconnected is the only way to get all this flexibility, not to mention
other benefits of complete disconenctedness like betters scalability,
maintainability, pluggability, state management etc. So disconnected is
awesome. And for the connected scenarios sans ADO 2.x, you still have
datareaders or direct execution of commands (ExecuteQuery et.al.).

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik
 
A

Anibal

Ok, so i will have to define and set a UniqueConstraint object and then add
it to the ConstrainsCollections of a data table for the fields that i want.
But if the 'Unique' property of DataColumn is set automaticaly to 'TRUE' at
run-time for a primary key field of a DataTable (if there is one), then
there has to be some connection/relation between what i have in the database
and what i put in a DataTable in ADO.NET.
I mean, ADO.NET knows that this field has a restriction, but i don't put
that restriction by VB.NET code, i put it in Access (in this case) and
ADO.NET is identifing this restriction as a 'UniqueContrain', if there is no
relation how do ADO.NET do this?
 
S

Sahil Malik

Anibal,

Here is the code --- I tried this in .NET 2.0 and it worked fine for me
(populated the UniqueConstraint on a non primary key column with Indexed set
to Yes with no duplicates).
OleDbConnection conn= new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents
and Settings\\Sahil Malik\\Desktop\\db1.mdb;Persist Security Info=False") ;
DataSet ds = new DataSet() ;
OleDbCommand cmd = conn.CreateCommand() ;
cmd.CommandText = "Select * from Table1" ;
OleDbDataAdapter olda = new OleDbDataAdapter() ;
olda.SelectCommand = cmd ;
olda.FillSchema(ds, SchemaType.Mapped) ;
Console.WriteLine(ds.Tables[0].Columns[1].Unique); // Prints true

I don't have .NET 1.1 installed anywhere but the code should be the same.

I can't attach the db I used because the size limits exceed what I can post,
but the db had one table called Table1, with two columsn

One - text
Two - number - indexed with no duplis.

There were no primary keys.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
A

Anibal

Thanks for all the explications, but i still don't know how to do what i
need; wich is how to know by code if a given field has a unique index (no
duplicates) not being a primary key.
Thanks you again.
 
A

Anibal

Ok, it works,
Thanks you very much for your time.

Sahil Malik said:
Anibal,

Here is the code --- I tried this in .NET 2.0 and it worked fine for me
(populated the UniqueConstraint on a non primary key column with Indexed
set
to Yes with no duplicates).
OleDbConnection conn= new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Documents
and Settings\\Sahil Malik\\Desktop\\db1.mdb;Persist Security Info=False")
;
DataSet ds = new DataSet() ;
OleDbCommand cmd = conn.CreateCommand() ;
cmd.CommandText = "Select * from Table1" ;
OleDbDataAdapter olda = new OleDbDataAdapter() ;
olda.SelectCommand = cmd ;
olda.FillSchema(ds, SchemaType.Mapped) ;
Console.WriteLine(ds.Tables[0].Columns[1].Unique); // Prints true

I don't have .NET 1.1 installed anywhere but the code should be the same.

I can't attach the db I used because the size limits exceed what I can
post, but the db had one table called Table1, with two columsn

One - text
Two - number - indexed with no duplis.

There were no primary keys.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik




Anibal said:
Thanks for all the explications, but i still don't know how to do what i
need; wich is how to know by code if a given field has a unique index (no
duplicates) not being a primary key.
Thanks you again.
Click to expand...
Click to expand...
 
S

Sahil Malik

WHEW !!

:)

Anyway, the theory we discussed before this code is important too.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


Anibal said:
Ok, it works,
Thanks you very much for your time.

Sahil Malik said:
Anibal,

Here is the code --- I tried this in .NET 2.0 and it worked fine for me
(populated the UniqueConstraint on a non primary key column with Indexed
set
to Yes with no duplicates).
OleDbConnection conn= new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Documents
and Settings\\Sahil Malik\\Desktop\\db1.mdb;Persist Security Info=False")
;
DataSet ds = new DataSet() ;
OleDbCommand cmd = conn.CreateCommand() ;
cmd.CommandText = "Select * from Table1" ;
OleDbDataAdapter olda = new OleDbDataAdapter() ;
olda.SelectCommand = cmd ;
olda.FillSchema(ds, SchemaType.Mapped) ;
Console.WriteLine(ds.Tables[0].Columns[1].Unique); // Prints true

I don't have .NET 1.1 installed anywhere but the code should be the same.

I can't attach the db I used because the size limits exceed what I can
post, but the db had one table called Table1, with two columsn

One - text
Two - number - indexed with no duplis.

There were no primary keys.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik




Anibal said:
Thanks for all the explications, but i still don't know how to do what i
need; wich is how to know by code if a given field has a unique index
(no
duplicates) not being a primary key.
Thanks you again.


"Sahil Malik" <[email protected]> escribió en el mensaje
FillSchema is what is filling the UniqueConstraint per the Unique set
in
your d/b.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


Yes, I am using FillSchema.


"Sahil Malik" <[email protected]> escribió en el mensaje
Are you using FillSchema somewhere? That might be the connection.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


Ok, so i will have to define and set a UniqueConstraint object and
then
add it to the ConstrainsCollections of a data table for the fields
that
i want.
But if the 'Unique' property of DataColumn is set automaticaly to
'TRUE' at run-time for a primary key field of a DataTable (if there
is
one), then there has to be some connection/relation between what i
have
in the database and what i put in a DataTable in ADO.NET.
I mean, ADO.NET knows that this field has a restriction, but i don't
put that restriction by VB.NET code, i put it in Access (in this
case)
and ADO.NET is identifing this restriction as a 'UniqueContrain', if
there is no relation how do ADO.NET do this?


"Sahil Malik" <[email protected]> escribió en el
mensaje
Anibal,

UniqueConstraint is something that is in ADO.NET, and that has no
direct connection/relation with setting a column's Indexed property
to
Yes (No Duplicates).

Picture it this way, a datatable lives under System.Data (and not
under System.Data.OleDb). You use the same System.Data.DataTable
for
SqlClient/OracleClient/OleDb/anything else; It is common to all
databases/.net providers, and logically so, it has no connection
with
what you might set in your access database. It is *completely*
disconnected.

In other words, setting a column's indexed properties to Yes (No
Duplicates) is a completely different animal, totally disconnected,
from specifying UniqueConstraint on a DataColumn.

You might ask WHY was ADO.NET designed this way? Well because
DataSets
and DataTables are supposed to work in a disconnected fashion. They
might be filled from sources other than databases. They might need
to
work between a webservice and a database. You might even write a
ADO.NET provider on an MSMQ queue as demonstrated in chapter #15 of
my
book. Being completely disconnected is the only way to get all this
flexibility, not to mention other benefits of complete
disconenctedness like betters scalability, maintainability,
pluggability, state management etc. So disconnected is awesome. And
for the connected scenarios sans ADO 2.x, you still have
datareaders
or direct execution of commands (ExecuteQuery et.al.).

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik


Hello, first thanks for the answer,
Second;

<--- Nope, UniqueConstraint doesn't have to be a primary key.
Esp.
in a dataset, which is completely disconnected from the d/b, a
primary key even can be specified as UniqueConstraint in a
dataset.

But then i don't ger it. I am using an Access database and for
some
"unique" fields (indexed and without duplicates) that are not
primary
keys, the 'Unique' property is set to 'False' at run-time when it
should be set to 'True' when using my validation code (posted in
the
firt message).
Why?

FillSchema might or might not fill DefaultValue, and that is
specific to the .NET data provider you are using.

Well, i am using a simple OleDb connection to an Access 2000
databas.
So, the 'DefaultValue' property doesn't set itself in this
context?

Thank you again.


"Sahil Malik" <[email protected]> escribió en el
mensaje
The problem that i found is that apparently the property
'Unique'
works only
for Primary Keys fields.

<--- Nope, UniqueConstraint doesn't have to be a primary key.
Esp.
in a dataset, which is completely disconnected from the d/b, a
primary key even can be specified as UniqueConstraint in a
dataset.

The other doubt that i have is that the property
'DataColumn.DefaultValue'
doesn't set itself to anything when i get the values from a
field
wich has a
default value.

Fill will fill the data only, not schema. You'll have to set
DefaultValue yourself. FillSchema might or might not fill
DefaultValue, and that is specific to the .NET data provider you
are
using.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik



Hi everybody, this is my first post in this group.

I'm working with an Access DB and i have the following code to
validate
fields with unique indexes (no duplicates):
'Field is a DataColumn
If Field.Unique Then
If Field.Table.Rows.Contains(NewData) Then
Return "Error: This field can't have duplicate
values."
End If
End If

The problem that i found is that apparently the property
'Unique'
works only
for Primary Keys fields.
So, how do i make a similar validation but with fields with
unique
indexes
only (no Primary Key)?

The other doubt that i have is that the property
'DataColumn.DefaultValue'
doesn't set itself to anything when i get the values from a
field
wich has a
default value.

Note: I fill a DataSet with Fill and FillSchema methods in order
to
do the
tests.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQLDataAdapter.Fill fails after call to SQLDataAdapter.FillSchema 1
Fillschema does not import constraints 1
FillSchema, Constraints and Stored Procedures 5
AllowDBNull property 1
Losing Table Index 3
Constraint Problem 3
Reading large csv-file and removing duplicates 11
FillSchema and AutoIncrement 1

Top