?
Solved

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

Posted on 2002-04-08
9
Medium Priority
?
489 Views
Last Modified: 2008-02-26
Hello all.
Is there a way to find out if field in an ADO recordset contains unique values (No Duplicates).
Thanks
Dean.
0
Comment
Question by:Dwhite
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6924916
I think there is no way to do it except a loop will do..
0
 
LVL 2

Expert Comment

by:skhorshid
ID: 6924925
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
 

Author Comment

by:Dwhite
ID: 6924947
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!

 
LVL 2

Expert Comment

by:skhorshid
ID: 6924962
why dont you change your sql ??

select distinct  col1, col2 from table where col1>col2
0
 
LVL 18

Expert Comment

by:deighton
ID: 6924967
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
 
LVL 1

Expert Comment

by:riaz9
ID: 6925002
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
 
LVL 5

Accepted Solution

by:
kcm76 earned 800 total points
ID: 6925802
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
 
LVL 5

Expert Comment

by:kcm76
ID: 6925810
to: riaz
My logic is completly different from urs.

KCM
0
 

Author Comment

by:Dwhite
ID: 6927328
Hank you all for the help.
Dean.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question