| cannot find record [message #209688] |
Sat, 16 December 2006 21:36  |
matrik02
Messages: 64
Registered: November 2005
|
Member |
|
|
I use visual basic 6.0 . I want to find the owner name 'Yusuf Sardi'
So when i type this name 'Yusuf Sardi' i got the record. But when i try to type 'yusuf sardi' i there where no record found display in my datagrid.Why i cannot find the record?What is the problem with the SQL?
blockC is is dbf data file and Owner is the field name
I want to find the record in Owner field
Here is the code.
Private Sub Command3_Click()
With Data1
'Query to find owner
.RecordSource = "Select Plot, Area, Owner from blockC Where Owner Like '" & Text1.Text & "'"
.Refresh
End With
End Sub
|
|
|
|
|
|
| Re: cannot find record [message #209695 is a reply to message #209688] |
Sun, 17 December 2006 01:35  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I think you already guessed what is going on here: 'Yusuf Sardi' != 'yusuf sardi'.
Queries in Oracle (and, as far as I know, in most databases) are case-sensitive.
To work around that, you can convert both the databasecolumn and the user-input to uppercase prior to comparing:
upper(owner) = upper(:text)
This could cause a performance issue if you have an index on that particular databasecolumn. The use of the function renders the index useless. To overcome that, create a function based index (google for that if you don't know what it is) on upper(owner).
A few notes about your query:
1) like has no meaning if you don't use wildcards
2) this query is prone to sql-injection (again, google for that). You should use bindvariables.
[Edit: Aargh, again 'beaten' by Littlefoot. It's sunday morning, what are you doing here, get a life!]
[Updated on: Sun, 17 December 2006 01:36] Report message to a moderator |
|
|
|
| Re: cannot find record [message #209697 is a reply to message #209695] |
Sun, 17 December 2006 02:38 |
matrik02
Messages: 64
Registered: November 2005
|
Member |
|
|
You were correct. I mean this 'Yusuf Sardi' != 'yusuf sardi'. In my database the record is Yusuf Sardi.
Hi guy, I not query from Oracle. I use dbf file database IV.
I not understand what is mean WHERE UPPER(column_name) = UPPER(search_criteria) . Do you mean like this
WHERE UPPER(Owner) = UPPER(text1.text)
[Updated on: Sun, 17 December 2006 02:42] Report message to a moderator |
|
|
|
|
|
| Re: cannot find record [message #210079 is a reply to message #209688] |
Tue, 19 December 2006 05:00 |
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
|
Member |
|
|
If your data is in propercase that is like 'Yusuf Sardi' ( initial capitals ) then modify your query in vb as follows
.RecordSource = "Select Plot, Area, Owner from blockC Where Owner = '" & StrConv(Text1.Text,vbProperCase) & "'"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
