Case Sensitivity

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

what database are you using?

That sounds more like an issue with your DBMS as VB6 doesnt normally care about case

jbennet

Moderator

18,523 posts since Apr 2005

Reputation Points: 1,826

Solved Threads: 601

I am using MS Access, can you help me how to resolve this? Thank you so much. . .

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

SELECT * From table where LOWER(name) = 'neil'

Comatose

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

hi, try this subroutine.. and use the final data to process to your database...

Sub zz()

Dim inputx, inputy, inputz, inputfinal As String
Dim i As Integer

inputx = InputBox("enter data")

i = Len(inputx)


inputy = UCase(Mid(inputx, 1, 1))

inputz = LCase(Mid(inputx, 2, i - 1))

inputfinal = inputy + inputz

MsgBox inputfinal 'use inputfinal data to process to your database


End Sub

hope it helps... ;)

cguan_77

Nearly a Posting Virtuoso

1,317 posts since Apr 2007

Reputation Points: 19

Solved Threads: 115

Here is my sample code fragment, are there anything to be added in this code? please help me. . .thank you so much. . .
The highlight part is where the upper case and lower case error.

Private Sub cmdAdd_Click()
chec:

Set rst = New ADODB.Recordset

With rst

    .ActiveConnection = Con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "event"

End With

If rst.AbsolutePosition > -1 Then
    rst.MoveLast
    R_Count = rst.RecordCount
    rst.MoveFirst
    For i = 1 To R_Count
        <strong>If rst.Fields("eventname") = Trim(txtEName.Text) Then</strong>
            Label20.Caption = "Event Name Already Exist. Type a new one"
            txtEName.SetFocus
            Exit Sub
            End If
            rst.MoveNext
            Next i
            Call dataupdate2
            Else
            Call dataupdate2
End If

rst.Close
Set rst = Nothing
Call dload2
End Sub

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

what's the exact error msg you are getting????
can you be more clear on this....

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

If the name already exits in the database, the message "Event Name Already Exist. Type a new one" will supposedly appear. But it only works if I input "Neil" but if I input "neil" or "NEIL", error message appear, "you can't store item because it's already exist in the database" then the 3 buttons appear "end, debug and the 3rd one (i forgot the name) will appear. What do you think is wrong with the code? Why is it this message (Event Name Already Exist. Type a new one) will not appear if I input the same name but with different letter case?

All I want is no matter what case you use, the message "Event Name Already Exist. Type a new one" should appear if it exist in the database. . .help me, thank you so much.

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

If the name already exits in the database, the message "Event Name Already Exist. Type a new one" will supposedly appear. But it only works if I input "Neil" but if I input "neil" or "NEIL", error message appear, "you can't store item because it's already exist in the database" then the 3 buttons appear "end, debug and the 3rd one (i forgot the name) will appear. What do you think is wrong with the code? Why is it this message (Event Name Already Exist. Type a new one) will not appear if I input the same name but with different letter case?

All I want is no matter what case you use, the message "Event Name Already Exist. Type a new one" should appear if it exist in the database. . .help me, thank you so much.

well....frnd...msaccess doesn't care for case at all....whether you use uppercase or lowercase or mixmatched letters....msaccess recognizes all as same....but to avoid getting this error you can modify your code....just a sample here....you can check this out...

just write a private function with return type of boolean, pass the event name which you need to check for duplication as parameter

now on a button_click event, accept an event name from a textbox, call the function and if it returns true then show your msg....that's it....so simple...

Private Function IsEventExists(ByVal evtName As String) As Boolean
Dim gcn As New ADODB.Connection
Dim rs As New ADODB.Recordset

On Error GoTo err1

If gcn.State = adStateOpen Then gcn.Close
Set gcn = Nothing

gcn.ConnectionString = "<your connection string>"
gcn.Open

rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open "select * from <your tablename> where <your field name>='" & Trim(evtName) & "'", gcn

If rs.RecordCount > 0 Then
    IsEventExists = True
Else
    IsEventExists = False
End If

If rs.State = adStateOpen Then rs.Close
Set rs = Nothing

Exit Function

err1:
    Err.Clear
    IsEventExists = True
    Exit Function
End Function

Private Sub Command1_Click()
If IsEventExists(Text1.Text) = True Then
    MsgBox "Event Name Already Exist. Type a new one", vbInformation, "Duplicate Event"
    Text1.SetFocus
Else
    '''insert it to the database...put the code here
End If
End Sub

get me a feed if this works out for you...

regards
Shouvik

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

This is what I did:

Private Function IsEventExists(ByVal evtName As String) As Boolean Dim gcn As New ADODB.Connection Dim rst As New ADODB.Recordset On Error GoTo err1 If gcn.State = adStateOpen Then gcn.Close Set gcn = Nothing gcn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\facility.mdb" gcn.Open rst.CursorLocation = adUseClient rst.CursorType = adOpenDynamic rst.LockType = adLockOptimistic rst.Open "select * from event where eventname='" & Trim(txtEName) & "'", gcn If rst.RecordCount > 0 Then IsEventExists = True Else IsEventExists = False End If If rst.State = adStateOpen Then rst.Close Set rst = Nothing Exit Function err1: Err.Clear IsEventExists = True Exit Function End Function

------------------------------------------------------------------------------------- Private Sub cmdAdd_Click() chec: Set rst = New ADODB.Recordset With rst .ActiveConnection = Con .CursorLocation = adUseClient .CursorType = adOpenDynamic .LockType = adLockOptimistic .Open "event" End With If IsEventExists(txtEName.Text) = True Then MsgBox "Event Name Already Exist. Type a new one", vbInformation, "Duplicate Event" txtEName.SetFocus Else With rst .AddNew .Fields!fnumber = StrConv(txtFNumber, vbProperCase) .Fields!eventname = StrConv(txtEName, vbProperCase) .Fields!fname = StrConv(txtFName, vbProperCase) .Fields!fincharge = StrConv(txtFIncharge, vbProperCase) .Fields!fschedules = StrConv(Text2, vbProperCase) .Fields!fschedulee = StrConv(Text3, vbProperCase) .Fields!sitcapacity = StrConv(txtCapacity, vbProperCase) .Fields!userschede = StrConv(Combo4, vbProperCase) .Fields!userscheds = StrConv(Combo3, vbProperCase) .Fields!fuser = StrConv(txtFUser, vbProperCase) .Fields!destination = StrConv(txtDestination, vbProperCase) .Fields!condition = StrConv(cboCondition, vbProperCase) .Fields!enddate = dtpDate(0).Value .Fields!startdate = dtpDate(0).Value .Fields!transaction = StrConv(cboTransaction, vbProperCase) .Fields!fsh = StrConv(txtfsh, vbProperCase) .Fields!ush = StrConv(txtush, vbProperCase) .Update Label20.Caption = "Successfully Saved. . ." End With End If Call dload2 End Sub

OUTPUT: "Event Name Already Exist. Type a new one" this messge always pop-up even I put a new event name. what do you think is wrong with my code?
The "dload2" there is where the data display in the MSFlexGrid. What am I going to put in the "gcn.ConnectionString="<?>"?

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

you have done some mistakes in the code....do the modifications as I mentioned...

in the function definition change

rst.Open "select * from event where eventname='" & <strong>Trim(txtEName)</strong> & "'", gcn

to the following ...

rst.Open "select * from event where eventname='" & <strong>Trim(evtName)</strong> & "'", gcn

in your cmdAdd_click() change the entire code...like the following...

Private Sub cmdAdd_Click()
dim rst as New ADODB.Recordset

on error goto chec

If IsEventExists(txtEName.Text) = True Then
    MsgBox "Event Name Already Exist. Type a new one", vbInformation, "Duplicate Event"
    txtEName.SetFocus
    exit sub
endif	

rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

if rst.state=adstateopen then  rst.close
set rst=nothing

rst.open "select * from event",con,1,2
rst.AddNew

rst!fnumber = StrConv(txtFNumber, vbProperCase)
rst!eventname = StrConv(txtEName, vbProperCase)
rst!fname = StrConv(txtFName, vbProperCase)
rst!fincharge = StrConv(txtFIncharge, vbProperCase)
rst!fschedules = StrConv(Text2, vbProperCase)
rst!fschedulee = StrConv(Text3, vbProperCase)
rst!sitcapacity = StrConv(txtCapacity, vbProperCase)
rst!userschede = StrConv(Combo4, vbProperCase)
rst!userscheds = StrConv(Combo3, vbProperCase)
rst!fuser = StrConv(txtFUser, vbProperCase)
rst!destination = StrConv(txtDestination, vbProperCase)
rst!condition = StrConv(cboCondition, vbProperCase)
rst!enddate = dtpDate(0).Value
rst!startdate = dtpDate(0).Value
rst!transaction = StrConv(cboTransaction, vbProperCase)
rst!fsh = StrConv(txtfsh, vbProperCase)
rst!ush = StrConv(txtush, vbProperCase)

rst.Update
if rst.state=adstateopen then  rst.close
set rst=nothing

Label20.Caption = "Successfully Saved. . ."
Call dload2

exit sub

chec:
err.clear
msgbox "some error occured..."
txtEName.SetFocus
exit sub	
End Sub

try this and get me your feed...

regards
Shouvik

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

Please help me how to ignore case sensitivity in VB. My program is to store data in the database, though I use small letters or big letters, the database store the data starting with a capital letter followed by small letters. Ex.

Input: Neil, neil, NEIL

In the database: Neil

So if I input Neil, the program response is ok but if I input neil or NEIL, the program results an error in the database and the program exits.

Please I need your kind help. Thank you so much.

Regards, Neil

try formatting the data before actually submitting it to the database initially... its been a fairly long time since i've done that much with formatting text in visual basic, so i'm not really sure if there's a default way to force name style capitalization of words, but either way... you could do it like this...

Private Function PropperName(stDATA As String) as String
 'holds returned word array, just incase ya know?
 Dim stWords() As String
 'just a loop variable
 Dim iLoop As Integer
 
 'split the submitted data into individual words, after setting
 'all characters to lower case
 stWords = getWords(LCase(stDATA))
 'loop the words, set them the first character to upper case
 For iLoop = LBound(stWords) To UBound(stWords)
  Mid$(stWords(iLoop), 1, 1) = UCase$(Left$(stWords(iLoop), 1))
 Next iLoop
 
 PropperName = Join(stWords, " ")
End Function

Private Function getWords(stDATA) As String()
 getWords = Split(stDATA, " ", , vbTextCompare)
End Function

keeping in mind that the getwords isn't really needed, but if your user enters the name NEIL NORBERT - for instance - then it would submit Neil Norbert to the database, vs Neil norbert

hope that helps :)

[edit]
ok, so i didn't notice the vbPropperCase in StrConv before :"> but this is a good way to do it if you don't want to use the built in way - lol
[/edit]

kain_mcbride

Light Poster

25 posts since Nov 2008

Reputation Points: 22

Solved Threads: 3

Hello.......i have attached the sample code...
just check this out....and get me your feedback...

hope this will be useful for you...

regards
Shouvik

Attachments Sample.zip (12.2KB)

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

hallo friend, I have tried your code about the case sensetivity. I works great but there's a little bit problem. If I input like this:

Input1:
Event Name: Fieldtrip --------------------> (primary key)
User: Neil
Schedule: 1/20/09

Input2:
Event Name: Tour --------------------> (primary key)
User: Neil
Schedule: 1/20/09

Where the event name is my primary key in the database. It results an error if "User" and "Schedule" in input1 and input2 are the same. I was expecting that there will be no error coz my primary key is "Event Name" not the user or schedule. "some error occured..." this message will appear. But if I input another entry in input2 like:

Input2:
Event Name: Tour --------------------> (primary key)
User: James
Schedule: 1/21/09

This works fine.

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

By the way, thank you so much for the sample. I will give the feedback. I will try to study your code. As soon as I finished it, I will share the program to you if you want. It is for my thesis. hehehe thanks. . .

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

These are the errors I see in the sample you sent to me. I was not able to view the running part. =) I will try to understand your code.

Attachments error.zip (113.62KB)

Junior Poster

107 posts since Aug 2008

Reputation Points: 10

Solved Threads: 2

These are the errors I see in the sample you sent to me. I was not able to view the running part. =) I will try to understand your code.

what did you mean by this --->I was not able to view the running part

did u mess up with vb???....it seems that some of your activex files (.ocx) are missing.....the snaps that you are attached are fully system related....these are not related to the program at all....to avoid getting this error you have the following options :-

1. uninstall and then re-install vb
2. just register "mscomctl.ocx" and "mscomct2.ocx" in your system...
might be you are not familer with registering activex controls manually...so i'm giving you the steps :-

1. if you haven't yet messed up with vb, you will have these two ocx files in your "system32" directory of your root drive..
2. use the command --> regsvr32 to register the files...like to register "mscomctl.ocx" you must type....

regsvr32 mscomctl.ocx....similarly....for the other one
regsvr32 mscomct2.ocx..........issue this command from start->run

just register these two ocx files and the errors will be simultaneously solved....

if you donot have these files...just download from net....these are freely distributable and available for download...

regards
Shouvik

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

hallo friend, I have tried your code about the case sensetivity. I works great but there's a little bit problem. If I input like this:

Input1: Event Name: Fieldtrip --------------------> (primary key) User: Neil Schedule: 1/20/09

Input2: Event Name: Tour --------------------> (primary key) User: Neil Schedule: 1/20/09

Where the event name is my primary key in the database. It results an error if "User" and "Schedule" in input1 and input2 are the same. I was expecting that there will be no error coz my primary key is "Event Name" not the user or schedule. "some error occured..." this message will appear. But if I input another entry in input2 like:

Input2: Event Name: Tour --------------------> (primary key) User: James Schedule: 1/21/09

This works fine.

can you upload your database(only) here...so that i can investigate.....according to the code...there is not a single line of error at all....i have tested it several times....but i tested it with a sample database....ofcourse your db structure is 100% diff. than this...so if you give me the actual database i can check for the error more thoroughly........now the choice is yours...

btw thanks for using the code and for urs feedback...

regards
Shouvik

Posting Pro

553 posts since May 2007

Reputation Points: 30

Solved Threads: 49

okk......check out this code now...
i think this time it is 100% OK...

regards
Shouvik

Attachments Sample.zip (12.53KB)