Home » SQL & PL/SQL » SQL & PL/SQL » cannot find record
cannot find record [message #209688] Sat, 16 December 2006 21:36 Go to next message
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 #209694 is a reply to message #209688] Sun, 17 December 2006 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 21012
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is because queries are case sensitive. Search the board (or Internet) for "case insensitive" queries (I remember there was one here recently).

One possible solution would be using the UPPER function, so that your WHERE clause would look like

WHERE UPPER(column_name) = UPPER(search_criteria)

This would not use index on 'column_name' if you had one, so you'd have to create a function-based index (another term to search for).
Re: cannot find record [message #209695 is a reply to message #209688] Sun, 17 December 2006 01:35 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #209698 is a reply to message #209697] Sun, 17 December 2006 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 21012
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
matrik02
I use dbf file database IV.

I'm afraid you missed the door ... this is OraFAQ forum (Oracle, not dB IV).

@Frank: it is easy to answer simple questions; I'd like to beat you on tough ones ./fa/1637/0/
Re: cannot find record [message #210079 is a reply to message #209688] Tue, 19 December 2006 05:00 Go to previous message
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) & "'"
Previous Topic: scheduling a task
Next Topic: Using mod function on varchar2 field
Goto Forum:
  


Current Time: Sun Feb 19 13:00:03 CST 2017

Total time taken to generate the page: 0.10231 seconds