Testing if value exists in datacolumn

  • Thread starter Thread starter MS Newsgroups
  • Start date Start date
M

MS Newsgroups

Hi,

I am trying to build a function that returns a dataset.When populating the
dataset, is it possible to test if a value already exists in a column and
only add it if it is not already present ? I tried adding setting
mycolumn.unique=true, but this generates an exception when the first
duplicate occur.

Any suggestions would be appreciated

Niclas
 
Niclas,
I would still consider setting "mycolumn.unique=true", as it sounds like
that is a requirement of your dataset.

I would use DataView.Find to verify that the value does not exist.
Alternatively you could use DataTable.Select, however DataView.Find is
'cleaner' & probably faster.

If DataView.Find returns -1 the value was not found, if it returns 0 or
greater the value was found.

Something like:

Dim table As DataTable
Dim view As New DataView(table)
view.Sort = "mycolumn"

If view.Find(myValue) = -1 Then
Dim row As DataRow = table.NewRow()
...
table.Add(row)
End If

Note: DataView.Find returns -1 for not found, not null as the documentation
suggests, the return value is an Integer, so null is not really allowed for
a return value.
but this generates an exception when the first
duplicate occur.
Click to expand...
You could always trap for this exception and ignore it...

Dim row As DataRow = table.NewRow()
...
Try
table.Add(row)
Catch
' this space intentionally left blank
End Try

However you may be 'hiding' exceptions then.

If you don't have it, David Sceppa's book "Microsoft ADO.NET - Core
Reference" from MS Press covers ADO.NET in great details, including a
chapters on DataSets, DataTables, and DataViews.

Hope this helps
Jay
 
Many thanks !

Niclas
Jay B. Harlow said:
Niclas,
I would still consider setting "mycolumn.unique=true", as it sounds like
that is a requirement of your dataset.

I would use DataView.Find to verify that the value does not exist.
Alternatively you could use DataTable.Select, however DataView.Find is
'cleaner' & probably faster.

If DataView.Find returns -1 the value was not found, if it returns 0 or
greater the value was found.

Something like:

Dim table As DataTable
Dim view As New DataView(table)
view.Sort = "mycolumn"

If view.Find(myValue) = -1 Then
Dim row As DataRow = table.NewRow()
...
table.Add(row)
End If

Note: DataView.Find returns -1 for not found, not null as the documentation
suggests, the return value is an Integer, so null is not really allowed for
a return value.

You could always trap for this exception and ignore it...

Dim row As DataRow = table.NewRow()
...
Try
table.Add(row)
Catch
' this space intentionally left blank
End Try

However you may be 'hiding' exceptions then.

If you don't have it, David Sceppa's book "Microsoft ADO.NET - Core
Reference" from MS Press covers ADO.NET in great details, including a
chapters on DataSets, DataTables, and DataViews.

Hope this helps
Jay
Click to expand...
 
Back
Top