Searching a Database

[FooFan]

Hi there,

I've got as program that has a Database incorportaed into it and im looking for a simple way to search that database using a text/input box. The name of the field that i would search is 'Furniture Number'. Displaying the found records in a the database table would be perfect.

Any help will be appreciated,
Thanks.

[Bill Crawley]

dim strSQL as string


strSQL = "Select * from tablename where [Furniture number] = " & textbox1.text

pass this as your sql statement to the recordset to obtain your Records.

replace table with your tablename

[PramodsNair]

Dear FooFan,

Try this

You haven't told the backend ie. if it's Access or MSSQl or anything. I assume it's MS Access and present you a solution.

In a from put a textbox and a button. name the textbox t1 and button b1 . I am calling the table sample and the field name fno (Furniture Number)

Private Sub b1_Click()
dim toSearch
toSearch = t1.text

If toSearch = "" Then msgbox "Nothing to search" : Exit Sub

Dim cc As New ADODB.Connection ' No need for a new connection if you have a global connection in the project
Dim r1 As New ADODB.Recordset
Dim sq As String

sq = ""

'Make the connection strings Data Source to meet your database name
cc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\smp.mdb"
cc.Open

'''''''''''''''''''''''''''''''
'NOTE
'IF you have declared the Furniture Number as string or text then comment the sq line given under
'the comment line and use the one which is commented just below this line
'sq = "select * from sample where fno ='" & tosearch & "'"
'''''''''''''''''''''''''''''''
sq = " select * from sample where fno =" & tosearch
'''''''''''''''''''''''''''''''
'NOTE
'''''''''''''''''''''''''''''''

r1.Open sq, cc

If Not ((r1.EOF = True) And (r1.BOF = True)) Then
'Do what ever you need to do with the record here
'I am just echoing the first 2 fields here
msgbox r1(0)
msgbox r1(1)

End If


End Sub

Hope this is what you need

Pramod S Nair

[Shuja Ali]

@Pramod: Couple of things that I would like to point out.

1. Never use New on the same line as Dim.
2. Use code tags whenever you post code, it makes your posts easily readable.
3. No need to check for both EOF and BOF when you open a recordset, you could just use one of them. Preferably BOF when you just open the recordset.
4. When you are sure that you are going to use String then declare it as string, don't use variants. When you declare something without specifying the type, VB will default it to variant. And Variants are little bit expensive when it comes to processing and memory.

And Happy Holi. Hope you are enjoying your day.

[PramodsNair]

Dear Shuja Ali,

Thankyou for correcting my small mistakes. I do indeed some times makes such small errors, and it's a nice experiance to have somebody to point that out.

Happy Holi to you too.

Pramod S Nair

[rudraksh]

If you are using SQL SERVER 2000, then to make your query fast you have to do certain things..
1.Normalize your tables
2.create appropiate non clustered indexes
3.Create the custered index if neccessary
4. Create the frequently used query as stored procedure Bcoz that will enhanced your system a lot
3.don't pass TSQL through recordset
4.use stored procedures
5.Create the queries as much possible with subqueries.That makes it fast.
Use a single interlooped subqueries instead of no of TSQL queries
6.Never use condituioned programming,always use set based approach.

there are many of which I stated some..I hope it would help you

Rudraksh

[exterminator]

If you are using SQL SERVER 2000, then to make your query fast you have to do certain things..
1.Normalize your tables
2.create appropiate non clustered indexes
3.Create the custered index if neccessary
4. Create the frequently used query as stored procedure Bcoz that will enhanced your system a lot
3.don't pass TSQL through recordset
4.use stored procedures
5.Create the queries as much possible with subqueries.That makes it fast.
Use a single interlooped subqueries instead of no of TSQL queries
6.Never use condituioned programming,always use set based approach.

there are many of which I stated some..I hope it would help you

Rudraksh

You could have had been a bit more specific but I don't that much is really required for a query that is a select with just one column in the where clause and no joins..

It's just that the columns coming in the where clause should be indexed.. and no scalar functions used on that column in the where clause.. else indexes are in-effective and there should be a clustered index on each table (usually an integer like record_id or something)...

To find out if a sub-query would be better or a join - it depends a lot on what is being actually done.. and no such decisions should be taken without profiling the query and analyzing their execution plan. There is no "one rule fits all" in the programming world.. Hope you get my point .. Regards.

[rudraksh]

You could have had been a bit more specific but I don't that much is really required for a query that is a select with just one column in the where clause and no joins..

It's just that the columns coming in the where clause should be indexed.. and no scalar functions used on that column in the where clause.. else indexes are in-effective and there should be a clustered index on each table (usually an integer like record_id or something)...

To find out if a sub-query would be better or a join - it depends a lot on what is being actually done.. and no such decisions should be taken without profiling the query and analyzing their execution plan. There is no "one rule fits all" in the programming world.. Hope you get my point .. Regards.

I think we should not specify everything here,its simply not needed.If anyone wants to grasp the knowledge its the best way to TEST...Test and TEST.
Well, I think you have mistaken my intension once again
It was some guidelines to make the queries more efficient, and I do believe that including queries into one generally boost up the execution time to 50 to 1000 times.I work with SQL SERVER 2000 and specifically I am now responsible for fine tuning the server .
I do agree with you that there is no fix rule in DBA fine tuning.But generally
if you follow those simple guidelines ,you could get the boost up easily,but there are exceptions...
And finally just want to add one more thing,I think searches are a bit more than a single line queries....and I know you don't use oneline query in your application

Rudraksh

[FooFan]

Thanks for the replies, but im having a bit of trouble getting this to work! I wondered if perhaps the use of the 'seek' method might work? i have read a little about this and wondered how, or even if, it could be used for what i need?
P.S i am using MS Access 2003 and its a table-type database.