dynamically build sql based on text in textbox and dropdownbox

[Edward F]

I have a web form that contains textboxes for first name and last name. I
also have a dropdown list that contains companies.

I would like the user to enter criteria in any manner in the control and get
results from the database.

For example if the user enters information in the firstname field a dataset
be returned. If the user selects all three controls then a dataset should
also be returned and the text of the controls will be the search criteria.

The base sql is : select * from Name where where"

if the txtID contains a value then the sql string will be "select * FROM
Name where ID = TXTID.TEXT"

if txtid and txtfirstname have values the sql string will be "select * FROM
Name where ID = TXTID.TEXT and FIRST_NAME = txtfirstname.text"


if all three are selected txtid,txtfirstname and txtlastname and have values
the sql string will be "select * FROM Name where ID = TXTID.TEXT and
FIRST_NAME = txtfirstname.text and LAST_NAME = txtlastname.txt"
Also, is it possible to loop through all the controls find the textboxes
that contains values and build the controls that way.

Pls help my job is on the line.








I understand how to use datasets, grid, data adapter and connection objects.

would like to determine the user

 

[souri challa]

Edward,

A good way to do this would be to encapsulate the search logic into
a stored procedure with default parameters. some thing like

Create procedure GetResults(
@FirstName VarChar(255) = null,
@LastName VarChar(255) = null,
@Id int = null
)
As
Begin
Select *
From <table>
Where (firstname = @FirstName or @FirstName is null)
And (LastName = @LastName or @LastName is null)
And (Id = @Id or @Id is null)
End

Now in your data access layer, you call the stored procedure with only
the parameters that have a value.

Hope this sets you on track.
-Souri