By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Fast way of Searching & Dispalying thousands of Records

P: 10
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....
Jul 10 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
try to use LIKE search and use proper indexing on the database column.
Jul 10 '08 #2

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'this is searching for existing data in your database
  3. Private Sub seek_Click()                      ' your search button
  4. Dim my_database As Database           'dimension database as database so program knows where to look for data
  5. Dim my_record As Recordset
  6. Dim test As String
  7. test = Text1(1).Text
  8. Set my_database = OpenDatabase("K:\Projects\DBMOVIE.mdf")   
  9. Set my_record = my_database.OpenRecordset("select * from Movie_Data_Table where movie_title like '" & Text1(1).Text & "'")    ' this is used to search by name, only if data already exists
  10.    Do While Not my_record.EOF  'this function will keep searching for fields matching each textbox
  11.         Text1(0).Text = my_record.Fields("UserID")
  12.         Text1(1).Text = my_record.Fields("movie_title")
  13.         Text1(2).Text = my_record.Fields("movie_type")
  14.         Text1(3).Text = my_record.Fields("rating_scale")
  15.         Text1(4).Text = my_record.Fields("movie_location")
  16.         Text1(5).Text = my_record.Fields("movie_format")
  17.    my_record.MoveNext
  18.    Loop
  19.    my_database.Close
  20. End Sub
  21.  
Hope this help steer it in the right direction for you...
Jul 10 '08 #3

P: 10
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...
Jul 10 '08 #4

debasisdas
Expert 5K+
P: 8,127
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.
Jul 10 '08 #5

P: 10
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 ?
Jul 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.