Member Avatar for nagatron

Hello to all, I am always mad about this problem. If I type a name with two words in a textbox like "google corporation" it will be registered in the database ms access as "Google Corporation" now that is okay. What I want is, there should be no duplicate of names, but the problem is, when I enter "google corporation" it will be registered again in the database instead of displaying "No duplicate". This is my code:

Private Sub Command1_Click()

Dim rst As New ADODB.Recordset
Dim i As Integer
Dim R_Count As Currency

With rst
    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "SELECT * FROM records"
End With


If rst.AbsolutePosition > -1 Then
    rst.MoveLast
    R_Count = rst.RecordCount
    rst.MoveFirst
    
    For i = 1 To R_Count
        If rst.Fields("name") = Trim$(Text9.Text) Then
            MsgBox "No Duplicate", vbCritical, "Duplicate Name"
            Text1.SetFocus
            Exit Sub
            End If
            rst.MoveNext
            Next i
            Call saveData
            Else
            Call saveData
        End If
End Sub

This code only work like this:

TextBox: Database:

Google Corporation---->Google Corporation = OK
google Corporation---->Google Corporation = Not Working
Google corporation---->Google Corporation = Not Working
google corporation---->Google Corporation = Not Working

As you can see, the code only work if what you enter in the textbox is equal to the database. Is there a way where I can convert "google corporation or google Corporation or Google corporation" to "Google Corporation"?

Edited by nagatron because: n/a
  • 3 Contributors
  • 5 Replies
  • 235 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by nagatron

Dani AI

Generated

Short summary and why it happened: the code in the first post compares the textbox string to the recordset value with VB’s plain = operator, which is case-sensitive by default in a module (the default is a binary comparison). That makes "google corporation" different from "Google Corporation" even though Jet/ACE SQL comparisons are usually case-insensitive. See the VB Option Compare documentation. (learn.microsoft.com)

Fast fixes you can apply in code (no schema change)

  • Normalize both sides before comparing. Converting both strings to the same case is simple and predictable:

    If LCase$(rst.Fields("name").Value) = LCase$(Trim$(Text9.Text)) Then
    ' duplicate
End If

    Or use StrComp with a text (case-insensitive) comparison:

    If StrComp(rst.Fields("name").Value, Trim$(Text9.Text), vbTextCompare) = 0 Then
    ' duplicate
End If

    The LCase/UCase functions and StrComp are built-in VB/Access helpers; StrComp(..., vbTextCompare) is locale-aware, so LCase is often the simplest predictable choice. (support.microsoft.com)

A more robust, production-safe approach

  • Normalize once when you save and enforce uniqueness at the database level. Add a second field (for example name_norm) and populate it with a normalized value (trim + LCase$ or UCase$) when inserting/updating. Put a unique index on that field so the engine prevents duplicates (so two different capitalizations become a single value). That both prevents race conditions and avoids scanning the whole table in code. Microsoft documents how to prevent duplicates by creating a unique index. (support.microsoft.com)

Performance and security tips

  • Don’t loop the entire table to find a match. Use a parameterized query that compares normalized values (for example WHERE LCase([name]) = LCase(?) or WHERE UCase([name]) = UCase(?)) and request TOP 1 — this pushes the work into the engine and is much faster. Access/Jet supports LCase/UCase in SQL expressions. Also use parameterized ADODB.Command to avoid quoting bugs and SQL injection. (learn.microsoft.com)

Note about ’s solution: converting to proper case with StrConv(..., vbProperCase) is fine for display and helped solve the immediate problem, but proper-casing can change intended brand/formatting (acronyms, stylized names). Normalizing for uniqueness and enforcing a DB-level constraint is the cleanest long-term fix. (learn.microsoft.com)

Recommended Answers

All 5 Replies

Member Avatar for AndreRet

You need to determine or read between lower and upper case. First do the check, reads lower and then upper case, compares with your data entry. If matched, move on.

Have a look at the following links, which gives you sample code on how to determine the values of each first character in a letter.

http://www.dreamincode.net/forums/topic/12952-visual-basic-6-determining-each-character-in-vb/

Member Avatar for PoisonedHeart

why don't you just, convert the input text to lowercase and convert the data from the database into lowercase then compare the two of them...

I think you can optimize your code,

you may change your SELECT statment into

.Open "SELECT * FROM records WHERE name=" & Trim$(Text9.Text)

so that you don't have to use a loop just to compare all the records within your query result..

then you can check the result by:

If rst.BOF and rst.EOF = True Then 'returns true when the results are empty, meaning the input text has no matching records within the query.
     MsgBox "No Duplicate", vbCritical, "Duplicate Name"
     Text1.SetFocus
     Exit Sub
Edited by PoisonedHeart because: n/a
Member Avatar for nagatron

why don't you just, convert the input text to lowercase and convert the data from the database into lowercase then compare the two of them...

I think you can optimize your code,

you may change your SELECT statment into

.Open "SELECT * FROM records WHERE name=" & Trim$(Text9.Text)

so that you don't have to use a loop just to compare all the records within your query result..

then you can check the result by:

If rst.BOF and rst.EOF = True Then 'returns true when the results are empty, meaning the input text has no matching records within the query.
     MsgBox "No Duplicate", vbCritical, "Duplicate Name"
     Text1.SetFocus
     Exit Sub

I can convert all the text in the text input into lower case but the problem is, how can I convert the data from the database into lower case?

Member Avatar for nagatron

The only way to solve this either:

How to convert the first letter of each word into upper case> or How to convert data from database into lower case? Which is both I don't know.

Member Avatar for nagatron

I would like to apologize, problem solve. . I found out how to solve the problem. The answer is pretty simple. I am using

rst.Fields!name= StrConv(Text1, vbProperCase)

in saving data to database. Now to solve the problem I use this condition.

If rst.Fields("name") = StrConv(Text1.Text, vbProperCase)

Which capitalize all the 1st character in a word. Ex. i am now happy = I Am Now Happy

Edited by nagatron because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.