advice needed - "Search"

[eramgarden]

OK, this app i'm working with has "search" pages. For example, a page has 10 textboxes on it and users can fillout only one box. It calls a stored proc and the stored proc takes into account the text boxes that were left blank

So, it puts defaults values in there. For example, it defaults the date range from 1900 to 2999!

Has anyone else done "search" pages? What's the best way to do build a search criteria and pass it to the database?

 

[kahlua001]

Not sure what you mean by takes into account boxes left blank, can you give an example?

 

[eramgarden]

for example, if there are 3 textboxes:

Firstbox --> user enters a value
second box ---> user enters nothing
third box ---> user enters nothing

Clicks Search. Code calls Stored Proc. In the "where" clause of the SQL, they also have "second box" and "third box" as critieria. Instead of only searching based on what the user entered...they search by whatever textboxes there are on the screen and default the empty textboxes to a value.. For example, if users dont enter a date range, they dafault the date to 1900-2999.

I want to change that to only search based on what the user has entered and ignore the empty textboxes. I guess that would requre a dynamic SQL and building of the search string up-front in the code??

 

[kahlua001]

Yes, I would say dynamically build your SQL string, I dont see any use of a Stored Procedure for this. That must be poor performance to filter where the date is 1900-2999. If there arent too many scenarios, you can build the logic in your SP.

If @param1 IS NOT NULL
select * from table where param1 = @param1
END IF

 

[Joe Mamma]

example. . .

execute this SQL create proc agains Northwind on your local machine. . .

create proc GetSysObjects @name as sysname = null , @LowDate as DateTime = null , @HighDate as DateTime = null 
as
set @LowDate = isnull(@LowDate, cast('1/1/1900' as datetime)) 
set @HighDate = isnull(@HighDate, cast('12/31/2999' as datetime)) 
set @name = isnull(@name, '') 
select * from sysobjects where name like @name +'%' 
and crdate between @lowDate and @HighDate

this code works. . .
C# cause vb sux

using (SqlConnection conn = new SqlConnection("integrated security=SSPI;initial catalog=Northwind"))
{
 conn.Open();
 using (SqlDataAdapter da = new SqlDataAdapter("GetSysObjects", conn))
 { 
  da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
  SqlCommandBuilder.DeriveParameters(da.SelectCommand);
  da.SelectCommand.Parameters["@name"].Value = textBox1.Text.Trim() == "" ? 
       System.DBNull.Value : (object) textBox1.Text.Trim();
  try
  {
   da.SelectCommand.Parameters["@LowDate"].Value = textBox2.Text.Trim() == "" ? 
    System.DBNull.Value : (object) Convert.ToDateTime(textBox2.Text.Trim());
  }
  catch (Exception ex)
  {
   if (ex is FormatException) throw new ApplicationException("Invalid Begin Date");
   throw ex;
  }
  try
  {
   da.SelectCommand.Parameters["@HighDate"].Value = textBox3.Text.Trim() == "" ? 
    System.DBNull.Value : (object) Convert.ToDateTime(textBox3.Text.Trim());
  }
  catch (Exception ex)
  {
   if (ex is FormatException) throw new ApplicationException("Invalid End Date");
   throw ex;
  }
  da.Fill(dataSet1);
  dataGrid1.DataSource = dataSet1.Tables[0];
 }
}