• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

Find out if a field in an ADO Recordset contains unique values.

Hello all.
Is there a way to find out if field in an ADO recordset contains unique values (No Duplicates).
Thanks
Dean.
0
Dwhite
Asked:
Dwhite
  • 2
  • 2
  • 2
  • +3
1 Solution
 
Ryan ChongCommented:
I think there is no way to do it except a loop will do..
0
 
skhorshidCommented:
you should really do that in your SQL that you use to generate the recordset, ie

Select distinct field from table
 

I would perform this operation in SQL for sure.

I dont know exactly what your are trying to do and your table structures however you I imagine you should use  count(), group by and distinct to perform your evaluations
0
 
DwhiteAuthor Commented:
Because the "Filter property" in ADO does not support Column to column comparison's e.g. Column1 > Column2.
I want to write a function that will do the same thing.
like.
Do until rst.EOF
if Column1 > Column2 then
 strFilter = strFilter & " And " & myUniqueColumnValue....

rst.MoveNext
loop
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
skhorshidCommented:
why dont you change your sql ??

select distinct  col1, col2 from table where col1>col2
0
 
deightonCommented:
apply the following sql query when you open a recordset - of course your table & field are not grading and name, so you need to change that bit.

.eof = true  indicates that there are no duplicates, .eof = false then you have duplicates

SELECT grading.name
FROM grading group by grading.name having count(grading.name) > 1
0
 
riaz9Commented:
You can execute 2 sql statements and compare the count(*) values like :

1. select count(*) from table - get count of records in recordset.

2. select count(distinct unique_column_name) from table - get count of records.

If both count() values are same,then unique ,else not.
0
 
Chandramouli kArchitectCommented:
Pls try this.

The following code uses two recordsets.

Use the First recordset to get all columns in the table. Then for each fld in the recordset run a Distinct-Count(*) query. If the Recordset Contains any records it is not unique Otherwise it is.


ALL THE BEST
KCM.

************SOURCE CODE*******************
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim strFldName As String, strQry As String
Private Sub Command1_Click()
rs.Open "Select * From Rb_format_ro", cn, 0, 1
If Not (rs.BOF And rs.EOF) Then
    For Each fld In rs.Fields
        MsgBox fld.Name
        strFldName = fld.Name
        strQry = "SELECT DISTINCT (" & strFldName & "), Count(*) AS TotCount" _
        & " From RB_FORMAT_RO " _
        & " GROUP BY " & strFldName _
        & " HAVING Count(*) > 1 "
       
        If rs1.State = 1 Then rs1.Close
        rs1.Open strQry, cn, 0, 1
        If Not (rs1.BOF And rs1.EOF) Then
            MsgBox "The Field " & strFldName & " Does not Contain Unique Records"
        Else
            MsgBox "The Field " & strFldName & " Contains Unique Records"
        End If
    Next fld
End If
rs1.close
set rs1 = nothing
rs.close
set rs = nothing
End Sub

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\Exp\Vb\test.mdb"
End Sub

*******************
0
 
Chandramouli kArchitectCommented:
to: riaz
My logic is completly different from urs.

KCM
0
 
DwhiteAuthor Commented:
Hank you all for the help.
Dean.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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