String Search in VB4.0

String Search in VB4.0

Post by Pascal Vermeule » Thu, 09 Jan 1997 04:00:00



1. Open a recordset with as recordsource an SQL statement like this :
Select * from CLIENTS where CLIENT_Name like "Smith*".
The asterisk after Smith will return also the records where the name BEGINS
with Smith.
2. Do a loop on the recordset :
While rs.eof = false then
        do something with the record
wend
You can use also a SQL statement to specify the recordsource of a
datacontrol.

THIS IS THE HELP FILE TEXT FROM VB4 :
Syntax

Expression Like "pattern"

The Like operator syntax has these parts:

Part    Description

expression      SQL expression used in a WHERE clause.
pattern String or character string literal against which expression is
compared.

Remarks

You can use the Like operator to find values in a field that match the
pattern you specify.  For pattern, you can specify the complete value (for
example, Like "Smith"), or you can use wildcard characters like those
recognized by the operating system to find a range of values (for example,
Like "Sm*").
In an expression, you can use the Like operator to compare a field value to
a string expression.  For example, if you enter Like "C*" in an SQL query,
the query returns all field values beginning with the letter C.  In a
parameter query, you can prompt the user for a pattern to search for.  
The following example returns data that begins with the letter P followed
by any letter between A and F and three digits:

Like "P[A-F]###"

In Visual Basic, if expression matches pattern, result is True (-1); if
there is no match, result is False (0); and if either expression or pattern
is a Null, result is also a Null.
THIS IS AN EXAMPLE FROM THE VB4 DOC

This example returns a list of employees whose names begin with the letters
A through D.

SELECT * FROM Employees WHERE LastName Like "[A-D]*"

Hope this helps.
--
Pascal Vermeulen

Visit the Visual Basic Central!
http://www.ping.be/Visual_Basic_Central



Quote:> I have battled with a problem for almost 4 months now.  I need to search

a database field for a particular word
Quote:> within the field...

> Example, I need to search a clients name field (Smith, John, E.) buy

using the search string of "Smith".  I
Quote:> want the program to search for the first occurance of the word "Smith"

and move the record pointer to the first
Quote:> occurence.

> Right now, I am using FINDFIRST to locate a clients record.  However, I

have to type the full name of the
Quote:> client (Smith, John, E.) in order to locate the record.  I have tried

using the "INSTR" with the FINDFIRST but
Quote:> I can not get the program to work.

> I know there is a way to search for a string within a field!  I have

scaned the newsgroups, AOL and all the VB
Quote:> books I own.  Could someone PLEASE tell me how to search for a string?  

> Aaron J. Pommier
> Harvey & Stuckel, Chtd.

 
 
 

String Search in VB4.0

Post by Aaron J. Pommie » Thu, 09 Jan 1997 04:00:00


I have battled with a problem for almost 4 months now.  I need to search a database field for a particular word
within the field...

Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
occurence.

Right now, I am using FINDFIRST to locate a clients record.  However, I have to type the full name of the
client (Smith, John, E.) in order to locate the record.  I have tried using the "INSTR" with the FINDFIRST but
I can not get the program to work.

I know there is a way to search for a string within a field!  I have scaned the newsgroups, AOL and all the VB
books I own.  Could someone PLEASE tell me how to search for a string?  

Aaron J. Pommier
Harvey & Stuckel, Chtd.

 
 
 

String Search in VB4.0

Post by Faustino E. Osun » Fri, 10 Jan 1997 04:00:00




Quote:> I have battled with a problem for almost 4 months now.  I need to search

a database field for a particular word
Quote:> within the field...

> Example, I need to search a clients name field (Smith, John, E.) buy

using the search string of "Smith".  I
Quote:> want the program to search for the first occurance of the word "Smith"

and move the record pointer to the first
Quote:> occurence.

> Right now, I am using FINDFIRST to locate a clients record.  However, I

have to type the full name of the
Quote:> client (Smith, John, E.) in order to locate the record.  I have tried

using the "INSTR" with the FINDFIRST but
Quote:> I can not get the program to work.

> I know there is a way to search for a string within a field!  I have

scaned the newsgroups, AOL and all the VB

Quote:> books I own.  Could someone PLEASE tell me how to search for a string?  

> Aaron J. Pommier
> Harvey & Stuckel, Chtd.

One solution is to create a loop that loops thru every single record and
see if the partial string is found in the record

For i = 0 to Recordset.RecordCount -1
  if instr(1, Recordset("Name"), "John") > 0 then
    'Data was found do your thing
  end if
next i

Note i dont know the proper syntax and properties of the recordset off the
top of my head but you can get the general clue.  The down fall to this
method is that once your database starts picking up in size the speed of
this procedure is greatly decreased.

I hope this will shed some light on your situation.

Enjoy!
Faustino

 
 
 

String Search in VB4.0

Post by maestr » Fri, 10 Jan 1997 04:00:00



> I have battled with a problem for almost 4 months now.  I need to search a database field for a particular word
> within the field...

> Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
> want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
> occurence.

You should use "LIKE" in your criteria

eg.
myDB.FindFirst "[Client Name] Like '*Smith*'"

-----------------------------------------------------------------
  ,-._|\      Mark Smith (Computer Support Specialist)

 \_,--.x/     (Cobram Victoria Australia)      
       v      Phone: (+61 58) 711 662

 
 
 

String Search in VB4.0

Post by W.P. Mus » Fri, 10 Jan 1997 04:00:00



Quote:>Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
>want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
>occurence.

well, i use in vb sql-statements to search for something. I always
extend the search-string with an asteriks (*). So when i'm looking for
smith my application looks for smith*.

But you get also the smithson, smiths and smith names.

so (on the fly) the code looks like

Make a form with
a listbox = List1
an textbox = txtSearch
a commandbutton = cmdGo

'on Form1.general
Dim ds as dynaset, db as database
Dim criteria$

Const databasenaam="c:\win\test.mdb"
Const tablename="testing"

Sub Form1_load
        set db=opendatabase(databasename)
end sub

Sub cmdGo_Click
        Dim SQL$

        criteria$= "*" & txtSearch & "*"
        SQL$= "SELECT * FROM " & tablename & " WHERE name ='" & _
                criteria$ & "'"
        set ds = db.createdynaset(SQL$)
        do while not ds.eof
                List.additem ds("name") & " " & ds("adress")
                ds.movenext
        loop
        ds.close
end Sub

Now all you have to do is put some code under the List1.click event to
show all the other fields off the choosen person.

good luck
gr. walther

----- afknijpen -----
Walther Musch
Tilburg University
http://pi1438.kub.nl:2080/KATHER/

 
 
 

String Search in VB4.0

Post by Bengt A. Johanso » Fri, 10 Jan 1997 04:00:00




Quote:> I have battled with a problem for almost 4 months now.  I need to search

a database field for a particular word
Quote:> within the field...

> Example, I need to search a clients name field (Smith, John, E.) buy

using the search string of "Smith".  I
Quote:> want the program to search for the first occurance of the word "Smith"

and move the record pointer to the first
Quote:> occurence.

> Right now, I am using FINDFIRST to locate a clients record.  However, I

have to type the full name of the
Quote:> client (Smith, John, E.) in order to locate the record.  I have tried

using the "INSTR" with the FINDFIRST but
Quote:> I can not get the program to work.

> I know there is a way to search for a string within a field!  I have

scaned the newsgroups, AOL and all the VB

Quote:> books I own.  Could someone PLEASE tell me how to search for a string?  

> Aaron J. Pommier
> Harvey & Stuckel, Chtd.

Try this:  Datactl.Recordset.FindFirst "Name LIKE 'Smith*'"

HTH,

Bengt.

 
 
 

String Search in VB4.0

Post by Hibo » Fri, 10 Jan 1997 04:00:00




Quote:> I have battled with a problem for almost 4 months now.  I need to search

a database field for a particular word

Quote:> within the field...

Here is a solution:
SQL = "Select * from Clients where Name like '*" & SearchString & "*'"

you can either open the recordset with then sql statement or use the last
part as your find criteria.
I just tried it on my database and it works so good that I'm going to find
somplace in my code to use it. :)

 
 
 

String Search in VB4.0

Post by Alan Dav » Fri, 10 Jan 1997 04:00:00



Hello Aaron,

Have a look at the Like operator

YourRecordSet.FindFirst "FieldName like "Smith*"

Quote:>I have battled with a problem for almost 4 months now.  I need to search a database field for a particular word
>within the field...

>Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
>want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
>occurence.

>Right now, I am using FINDFIRST to locate a clients record.  However, I have to type the full name of the
>client (Smith, John, E.) in order to locate the record.  I have tried using the "INSTR" with the FINDFIRST but
>I can not get the program to work.

>I know there is a way to search for a string within a field!  I have scaned the newsgroups, AOL and all the VB
>books I own.  Could someone PLEASE tell me how to search for a string?  

         Alan Davis, Southampton, England
 
 
 

String Search in VB4.0

Post by Dan MacDonal » Fri, 10 Jan 1997 04:00:00



> I have battled with a problem for almost 4 months now.  I need to search a database field for a particular word
> within the field...

> Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
> want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
> occurence.

The SQL "LIKE" clause is key here:

        SELECT * FROM clients WHERE clientname LIKE "*Smith*"

Of course, indexing will not be used because the field may or may not
begin with Smith. The database essentially has to perform a sequential
scan of the table to find appropriate records. But it's still faster
than retrieving all the records into VB and searching yourself...

 
 
 

String Search in VB4.0

Post by Julie Wats » Sat, 11 Jan 1997 04:00:00


On Wed, 08 Jan 1997 10:56:32 -0800, "Aaron J. Pommier"


>>I have battled with a problem for almost 4 months now.  I need to search a database field for a particular word
>>within the field...

>>Example, I need to search a clients name field (Smith, John, E.) buy using the search string of "Smith".  I
>>want the program to search for the first occurance of the word "Smith" and move the record pointer to the first
>>occurence.

>>Right now, I am using FINDFIRST to locate a clients record.  However, I have to type the full name of the
>>client (Smith, John, E.) in order to locate the record.  I have tried using the "INSTR" with the FINDFIRST but
>>I can not get the program to work.

>>I know there is a way to search for a string within a field!  I have scaned the newsgroups, AOL and all the VB
>>books I own.  Could someone PLEASE tell me how to search for a string?  

>>Aaron J. Pommier
>>Harvey & Stuckel, Chtd.

Could you do something like creating a query with the criteria being
LIKE "*Smith*" and then sorting in ascending order.  I think you would
find the first occurance that way.  Using the like with a * in front
and back will let you find the string within the field.

I hope this helps you out!

Julie

 
 
 

String Search in VB4.0

Post by Daniel P Huds » Sat, 11 Jan 1997 04:00:00





>> I have battled with a problem for almost 4 months now.  I need to
>>search a database field for a particular word within the field...

 I've battled the one I'm about to mention for over 3 years, so...

 I need you to post your question in a group where it is ON-TOPIC like
 the one any follow-up's to this thread will be directed to, namely,
 comp.lang.basic.visual.misc, and notice that little 'visual' keyword.

 
 
 

String Search in VB4.0

Post by Stuart McLachl » Wed, 22 Jan 1997 04:00:00




>Subject: String Search in VB4.0
>Date: Wed, 08 Jan 1997 10:56:32 -0800
>I have battled with a problem for almost 4 months now.  I need to search a
>database field for a particular word
>within the field...
>Example, I need to search a clients name field (Smith, John, E.) buy using the
>search string of "Smith".  I
>want the program to search for the first occurance of the word "Smith" and move
>the record pointer to the first
>occurence.
>Right now, I am using FINDFIRST to locate a clients record.  However, I have to
>type the full name of the
>client (Smith, John, E.) in order to locate the record.  I have tried using the
>"INSTR" with the FINDFIRST but
>I can not get the program to work.
>I know there is a way to search for a string within a field!  I have scaned the
>newsgroups, AOL and all the VB
>books I own.  Could someone PLEASE tell me how to search for a string?  
>Aaron J. Pommier
>Harvey & Stuckel, Chtd.

Check out  "LIKE" in on-line help.   ie LIKE "Smith*"
 
 
 

String Search in VB4.0

Post by John Guarnier » Wed, 22 Jan 1997 04:00:00


Programming tip: You should have 3 seperate fields FirstName, MI, LastName
it will make life less complicated down the road.

                                          John :)

 
 
 

String Search in VB4.0

Post by Daniel P Huds » Thu, 23 Jan 1997 04:00:00



>Programming tip: You should have 3 seperate fields FirstName, MI,
>LastName it will make life less complicated down the road.

 Posting tip, you should have the keyword visual in that newsgroup name
 somewhere like comp.lang.basic.visual.misc. It will make your posts
 On-Topic.
 
 
 

1. Searching for a string in a VB4 database

Does anyone know if it is possible to search a field in a database for part of
a string:

For example, if I search for "hotels" or "hotel"  under a field name of
"business" , I should return "Sheraton Hotel" , "Holiday Inn Hotel" ,
"Surfside Hotels" etc.

(Sort of an intelligent search engine)


Thanks

2. D3 - ODBC - Excel - Mac

3. Parsing to search an apostrophe within a string search field

4. Perfomance: Informix vs. InterBase

5. Parse ASP search string into query string?

6. Transfering tables

7. problems - database searches with vb4.0

8. HOW: SQL Server receives XML from SAP

9. I search a fast Sprite Funktion for VB4.0

10. Zero length strings, SQL Server6.0/VB4.0a and spaces

11. vb4.0 searching dBase III database

12. Search on a DBList in VB4 Pro

13. Searching Access DB in VB4