Help With Search Function, I don't know where to start!!

[markmcgookin]

Hi Folks,

I have a VB app, and I have been working at it for a while, and I am now at the stage where I want to create a search function. Now don't be scared! It is in the .Net compact framework, and uses SQL Server CE as the database (This seems to scare off people trying to help! lol) but the connection and reading of data etc is all handled, and I think it is going to be a "relatively " simple function.

My database has a number of fields (Below is the code for creating the table I am referring to (edited for ease of reading)

tblDetails
PRIMARY KEY, Flower_ID NTEXT, Name_Eng NTEXT, Name_Lat NTEXT, 
Location_ID NTEXT, Habitat_ID NTEXT, Season_ID NTEXT, Family_ID NTEXT, 
Height NTEXT, Flower_Shape NTEXT, Leaf_Shape NTEXT, Colour NTEXT, 
Plant_Type NTEXT, Bloom_Size NTEXT, Cluster_Type NTEXT, Description 
NTEXT
 

So you can see my values

Flower_ID, Name_Eng, Name_Lat etc etc..

Now, I am going to create a form with a series of dropdown/combo boxes for the fields I want the user to be able to select from, these will be

cmbLocation, cmbHabitat, cmbSeason, cmbFamily, cmdHeight, cmbFlowerShape, cmbLeafShape, cmbColour, cmbPlantType, cmbBloomSize, cmbClusterType

Now, on a "Submit" esq Button I want the program to take in these values, and search through the records in (just) one table in the database and output the resulting rows Eng_Name to a listbox.

I already have an A-Z search function done, where the user selects a letter, and all the flowers starting with that letter are displayed.

It is this "composite" search key that I have NO IDEA where to start with, I have toyed with the idea of using a load of IF or CASE statements to form a concatenated SQL query, as one approach. But I thought that might bring me trouble when the user does not know a field because in this situation the search must search all values in that field.

e.g. - FlowerShape = 4petals, 5petals, 6petals, I Don't Know (Search all flower shapes)

Which will obviously return more results.

The idea of the search is to narrow the results with each piece of data the user reads in.

So really what I think some pseudocode would be is

Select * FROM tblDetails
Read in value1
IF value1 = "I Don't Know"
THEN
Move on...
ELSE
Remove all data from list where field1 != value1

Read in value2
IF value1 = "I Don't Know"
THEN
Move on...
ELSE
Remove all data from list where field2 != value2

However, this might be totally wrong.

I know this sounds like a scary request, but If you read the code you will see it is quite simple, the data reader reads in the whole line from the table and all the values are in order of position relative to their position in the statement above

e.g.
Temp = MyReader.GetStr ing(2) Is the flowers name
Temp = MyReader.GetStr ing(1) Is the primary key, Flower_ID

Ok, so I hope this hasn't bored you all, but I know it's better to be concise here than post too little.

I really need help with this folks, so please help me out!

Thanks very much for all your time and any responses!

Just a suggestion of a technique and some relevant classes/functions would be EXCELLENT, not looking for a code solution, just some help, or a similar exaple from somewhere!

[markmcgookin]

Cheers for the Edit Killer42!

Mark

[markmcgookin]

Hey folks,

anyone got any help for me on this one? I really can't get my head around it.

I think what might suit me at this stage is simply a function that adds all the flowers to a list and simply eliminates the ones where

"valueX != InputX"

I know this method may be slow, and woefully poor for large amounts of data, but I can't seem to think of another way of acchieving what I want (NB: If someone has an idea that would achieve the same results but with a different method, I would REALLY appreciate some help!)

I think what I will do is put the search code in the loading of the results form, and on the submit of the search form, simply pass all the data rerieved to a module.vb file so all forms can see it.

Pleeeeeeeease help me out with some code here folks, I'm totally stuck.

Thanks!

[markmcgookin]

Possibly creating a recordset with all the data in it and then eliminating the ones that dont match?

Does anyone know if the compact framework supports recordsets?

lol help please <-- Doing some oliver twist style begging here