please help
ive been looking for the solution for days.
i have two date pickers (dbplite) one is for start date and one is end date.
also i have a dropdownlist with the list of addresses of persons records.
also a grid view to display record. i also have a button to filter records based on the chosen dates and dropdwown list.
now i created a stored procedure for that..that if the dropdownlist is null and startdate and enddate is not null, then
it will display the records based on date range. this one works.
but when i chose one item in dropdownlist and startdate and enddate is empty, there is no record being displayed but in reality, there is.

below is my stored proc:

1.

USE [EmpRecord_DB]
GO

Create proc [dbo].[DisplayRecord]

    @pAddress varchar,
    @pDateStart date=null,
    @pDateTo date=null

as

begin

select * from tblRecord where (Address = @pAddress or @pAddress is null)
and (StartDate between @pDateStart and @pDateTo or @pDateStart is null or @pDateTo is null)
and (EndDate between @pDateStart and @pDateTo or @pDateStart is null or @pDateTo is null) 
order by EmpID

end

  1. i also tried:

    select * from tblRecord where
    Address =COALESCE(@pAddress, Address)
    and StartDate between COALESCE(@pDateStart, StartDate) and COALESCE(@pDateTo, EndDate)
    and EndDate between COALESCE(@pDateStart, StartDate) and COALESCE(@pDateTo, EndDate)

  2. also i tried many if else statements to check if each parameter is empty or not, then
    it will execute the corresponding select statement. like:

    if (@pAddress<>null and @pDateStart = null and @pDateTo = null)

    begin
    select * from tblRecord where Address=@pAddress
    end

    else if (@pAddress=null and @pDateStart <> null and @pDateTo <> null)

    begin
    select * from tblRecord where DateHired Between @pDateStart and @pDateTo
    end

end

already tried many things but still no luck.

1 and 2 worked when i execute it directly from the server but when i called this on code (VB ASP.NET), it shows no record
when user chose no dates just Address from dropdown list..

3 also returns no record at all

please help..thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.