Advance Search Problem

Hi. Im building a contact manager and i have the table Contact, Category and ContactInCategory.

I don't know what to do a advanced search, in one page i can search by Name, Last Name, Phone or Category. I generate the querystring and in other page show the results in a gridview.

The problem is that i don't know how to do the category search. The search work fine with the Name, Last Name and Phone but how i do the search for the category?.

I can create a datasource like <data:Contact.... or something like that?

Thanks in advance guys.

You could either create a view which pulls all of the fields you wish to search together and then do any filtering using the Filter features of .netTiers or create a custom SP where you do the filtering.

hth

swin 

I create a custom sp and try to do that search but is a dynamic SQL. And aperently nettiers do not support that kind of sp.

Im new in nettiers so what do you suggest i should do?

This is the SP.

CREATE PROCEDURE [dbo].[_Negocios_Search]
(
@Neg_NegocioID varchar(10)='',
@Neg_Nombre varchar(100)='',
@Neg_Calle varchar(80)='',
@Neg_Altura varchar(8)='',
@Neg_BarrioID varchar(8)='',
@Neg_Telefono1 varchar(20)='',
@Neg_Delivery varchar(2)='',
@Neg_Reservas varchar(2)='',
@Neg_DiaAtencionID varchar(8)='',
@Neg_HorarioAtencionID varchar(8)='',
@Neg_Activo varchar(8)='',
@Rne_RubroID varchar(8)=''

) AS
 SET DATEFORMAT dmy
DECLARE @Where varchar(3000)
DECLARE @Select varchar(3000)
SELECT @Where =""
IF @Rne_RubroID <> "" and @Rne_RubroID is not null
    SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID, Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID, Neg_HorarioAtencionID FROM Negocios, RubrosNegocio "
Else
    SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID, Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID, Neg_HorarioAtencionID FROM Negocios "
SELECT @Where =""
if @Neg_NegocioID <> "" and @Neg_NegocioID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_NegocioID = " + @Neg_NegocioID
  Else
     SELECT @Where = @Where + " AND  Neg_NegocioID = " + @Neg_NegocioID
End
if @Neg_Nombre <> "" and @Neg_Nombre is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Nombre like '" + RTRIM(@Neg_Nombre) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Nombre like '" + RTRIM(@Neg_Nombre) + "%'"
End
if @Neg_Calle <> "" and @Neg_Calle is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Calle like '" + RTRIM(@Neg_Calle) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Calle like '" + RTRIM(@Neg_Calle) + "%'"
End
if @Neg_Altura <> "" and @Neg_Altura is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Altura = " + @Neg_Altura
  Else
     SELECT @Where = @Where + " AND  Neg_Altura = " + @Neg_Altura
End
if @Neg_BarrioID <> "" and @Neg_BarrioID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_BarrioID = " + @Neg_BarrioID
  Else
     SELECT @Where = @Where + " AND  Neg_BarrioID = " + @Neg_BarrioID
End
if @Neg_Telefono1 <> "" and @Neg_Telefono1 is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Telefono1 like '" + RTRIM(@Neg_Telefono1) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Telefono1 like '" + RTRIM(@Neg_Telefono1) + "%'"
End
if @Neg_Delivery <> "" and @Neg_Delivery is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Delivery like '" + RTRIM(@Neg_Delivery) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Delivery like '" + RTRIM(@Neg_Delivery) + "%'"
End
if @Neg_Reservas <> "" and @Neg_Reservas is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Reservas like '" + RTRIM(@Neg_Reservas) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Reservas like '" + RTRIM(@Neg_Reservas) + "%'"
End
if @Neg_DiaAtencionID <> "" and @Neg_DiaAtencionID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_DiaAtencionID = " + @Neg_DiaAtencionID
  Else
     SELECT @Where = @Where + " AND  Neg_DiaAtencionID = " + @Neg_DiaAtencionID
End
if @Neg_HorarioAtencionID <> "" and @Neg_HorarioAtencionID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_HorarioAtencionID = " + @Neg_HorarioAtencionID
  Else
     SELECT @Where = @Where + " AND  Neg_HorarioAtencionID = " + @Neg_HorarioAtencionID
End
if @Neg_Activo <> "" and @Neg_Activo is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Activo = " + @Neg_Activo
  Else
     SELECT @Where = @Where + " AND  Neg_Activo = " + @Neg_Activo
End
if @Rne_RubroID <> "" and @Rne_RubroID is not null
Begin
    if @Where = ""
        SELECT @Where = " Where Rne_RubroID = " + @Rne_RubroID + " AND Neg_NegocioID = Rne_NegocioID "
    Else
        SELECT @Where = @Where + " AND Rne_RubroID = " + @Rne_RubroID + " AND Neg_NegocioID = Rne_NegocioID "
End
Execute(@Select + @Where )
Select @@ROWCOUNT
SET NOCOUNT ON
RETURN