Fast way of Searching & Dispalying thousands of Records

[praveenhathwar]

Hi, Im new to this Forum. Im facing a problem from long time.
I have a text box. If user types a character, I should display the itemname (records/data) into Flexgrid starting from those characters. Note that User only types the character and he doesn't press Enter key.

I ve return the code in Change event of the textox. But it is taking too much time. For every character I press, im calling search functioin where im searching and displaying the records starting from those characters....

Im Using VB6.0 and SQL Server 2000


Can anyone help me to get out from this problem....
Thanks in Advance....

[debasisdas]

try to use LIKE search and use proper indexing on the database column.

[Dököll]

Greetings and salutations!

A simple way to start could be to set up an ODBC, DAO, or ADO connnection to your SQL Server database, assuming you already have a database built with fields needed, say the field to search is called ID, on your VB6 form, you would have a textbox searching just that field (ID), here's an example using DAO, you'll need to add Microsoft DAO 3.6 Library. It search:

[CODE=VB]

'this is searching for existing data in your database
Private Sub seek_Click() ' your search button
Dim my_database As Database 'dimension database as database so program knows where to look for data
Dim my_record As Recordset
Dim test As String
test = Text1(1).Text
Set my_database = OpenDatabase("K :\Projects\DBMO VIE.mdf")
Set my_record = my_database.Ope nRecordset("sel ect * from Movie_Data_Tabl e where movie_title like '" & Text1(1).Text & "'") ' this is used to search by name, only if data already exists
Do While Not my_record.EOF 'this function will keep searching for fields matching each textbox
Text1(0).Text = my_record.Field s("UserID")
Text1(1).Text = my_record.Field s("movie_title" )
Text1(2).Text = my_record.Field s("movie_type ")
Text1(3).Text = my_record.Field s("rating_scale ")
Text1(4).Text = my_record.Field s("movie_locati on")
Text1(5).Text = my_record.Field s("movie_format ")
my_record.MoveN ext
Loop
my_database.Clo se
End Sub
[/CODE]

Hope this help steer it in the right direction for you...

[praveenhathwar]

try to use LIKE search and use proper indexing on the database column.

Thanks Debasisdas, for Ur reply...
Indexing means...? Im new to VB. Plz bare with me...

[debasisdas]

creating index is part of database not VB.

You need to create index on the fields which are frequently used in the where clause of the query in the tables.

[praveenhathwar]

creating index is part of database not VB.

You need to create index on the fields which are frequently used in the where clause of the query in the tables.

K...What is the purpose of View Tables (Views) in SQL SERVER? Can I use that.. Will it be helpful and become faster ?