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
what database are you using?
That sounds more like an issue with your DBMS as VB6 doesnt normally care about case
I am using MS Access, can you help me how to resolve this? Thank you so much. . .
SELECT * From table where LOWER(name) = 'neil'?
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 Subhope it helps... ;)
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
what's the exact error msg you are getting????
can you be more clear on this....
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.
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 Subget me a feed if this works out for you...
regards
Shouvik
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 SubOUTPUT: "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="<?>"?
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] & "'", gcnto the following ...
rst.Open "select * from event where eventname='" & [B]Trim(evtName)[/B] & "'", gcnin 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 Subtry this and get me your feed...
regards
Shouvik
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 Functionkeeping 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]
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
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.
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. . .
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.
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
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/09Input2:
Event Name: Tour --------------------> (primary key)
User: Neil
Schedule: 1/20/09Where 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/09This 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
okk......check out this code now...
i think this time it is 100% OK...
regards
Shouvik
sure, I will show it to you today. . .=) thank you =)
Give a comment or an advise what to do with this program. . .thank u so much
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
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....
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 & '"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. . .
*mumbles something about the SQL "LIKE" condition*
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
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.