Searching records

[jeffshex]

I've read a few posts where people build their query string in access via the code and then send that over to get the results. Is that the best way or is it better to come up with a stored procedure in SQL to handle that.

I guess is what I'm looking for is if I have an unbound form with a handful of fields and I only choose to fill in one or maybe even part of one or the other, that it will open up a new form that contains a list of results.
Has anyone had any expereince/luck with something like this working?

Example (unbound fields on form: FName, LName, Gender, etc..)
If I fill in:
FName: J
LName: Doe
Gender: M

Is that it returns:
John Doe
Jake Doer

and not Jenny Doe and the such.

Not sure how involved this actually is, but how have others handled this?

 

[jeffshex]

I guess I should have included that it disregards null fields and really doesn't factor them into the search at all.

 

[PaulBricker]

It would seem that you could make arguments for either method depending on what the actual construction of your query/stored procedure looked like. Generally I would say that if you are passing parameters to the object, and you have to develop additional code to handle the rest of the process, I would stick with building the query string in the code and forget the sp.

My 2 cents.

Paul

 

[danvlas]

Building the whole query in code is called 'dynamic sql' and is one of the most dangerous threats to a database. Google it up and you'll see what I mean.

If a limited and known number of filter criteria is expected, I'd go for a stored procedure with parameters.

If not, you can build a view or an inline function on the server and construct the filter in the client program.
Then you can use

"select * from viewname where 1=1 " & strFilter




Daniel Vlas
Systems Consultant

 

[jeffshex]

That's kind of what I ended up going with. I created a view on the server and the application just provides the where part. I tried doing some injection on the test db that I have like dropping tables, inserting records, etc... but couldn't get any of that to work (which is a good thing I would guess).

The purpose was to search the view and return those filtered to a list box on a form