[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2002-04-08
9
Medium Priority
?
542 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

656 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