Case Sensitivity

[nagatron]

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

[jbennet]

what database are you using?

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

[nagatron]

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

[Comatose]

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

?

[cguan_77]

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... ;)

[nagatron]

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
        [B]If rst.Fields("eventname") = Trim(txtEName.Text) Then[/B]
            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

[choudhuryshouvi]

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

[nagatron]

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.

[choudhuryshouvi]

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

[nagatron]

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="<?>"?

[choudhuryshouvi]

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='" & [B]Trim(txtEName)[/B] & "'", gcn

to the following ...

rst.Open "select * from event where eventname='" & [B]Trim(evtName)[/B] & "'", 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

[kain_mcbride]

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

hi

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]

[choudhuryshouvi]

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

[nagatron]

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.

[nagatron]

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. . .

[nagatron]

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.

[choudhuryshouvi]

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 <ocx file name> 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

[choudhuryshouvi]

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

[choudhuryshouvi]

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

regards
Shouvik

[nagatron]

sure, I will show it to you today. . .=) thank you =)

[nagatron]

Give a comment or an advise what to do with this program. . .thank u so much

[nagatron]

the you gave works, thank you so much, but there are two errors from the code. the .value and the dim one. . try to see the image. . .thank u so much

[choudhuryshouvi]

did you register the activex components as I said...

if yes then goto project->components and check on "Microsoft Windows Common Control 6.0" and click ok....

[jireh]

Hi,

If the problem is duplication then you must check first the primary keys. How many primary keys in your table, one, two, or more? OK, suppose you have a table name Employee and have three fields named EmployeeID, Name, Address, where EmployeeID has the primary key.

In your code to avoid the error, the very common is to check the data first before saving it, right? so...

Select EmployeeID From Employee Where EmployeeID = "' & txtEmployeeID.Text & '"

[nagatron]

The Microsoft Windows Common Control 6.0 is already checked and I only got 1 Primary key each table. It is there in the zip. . .

[Comatose]

*mumbles something about the SQL "LIKE" condition*

[choudhuryshouvi]

The Microsoft Windows Common Control 6.0 is already checked and I only got 1 Primary key each table. It is there in the zip. . .

the sample code that i gave you also contains one primary key in the database....so there is nothing wrong with the code or the database structure....this code is running absolutely ok in my system....the errors that you snapped in is completely system related....it is not related with your code....not logically or not in compilation level...

make sure that you have referenced "microsoft windows common control 6.0" and "microsoft windows common control 6.0 (sp3)" in your project->components list..

the reason for these errors are....this code uses two controls "datepicker" and "listview" which are included in "microsoft windows common control 6.0" and "microsoft windows common controls 6.0 (sp3)" components...so unless you register and add these into your project, the code won't compile....

i hope you understood the logic behind the errors now....so just do it and get me your feeds when you are done...

and ofcourse whether your table contains one primary key or more than one, that doesn't be a matter at all....coz according to your database this code was written in such a way that you won't face any problem in case if you accidentally inserts any duplicate value to primary key field...there is a validation for checking duplicate value already in the code....so just proceed with it....

regards
Shouvik

[nagatron]

Thank you so much for the help, I finally solve the case sensitive problem. What I did, since in the database, the string starts with capital letter, I use string control like this.

Text2.text = UCase(Left$(Text1, 1)) & LCase(Mid(Text1, 2))
if rst.Field("fname") = Trim(Text2.text) then
.
.
.
.
end if

so even i input NEIL or neil or NeIL, it will appear as Neil. Thank you so much for the help, the if condition you gave to me if very helpful and easy to understand.