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:
Create proc [dbo].[DisplayRecord]
@pAddress varchar, @pDateStart date=null, @pDateTo date=null
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
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)
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)
select * from tblRecord where Address=@pAddress
else if (@pAddress=null and @pDateStart <> null and @pDateTo <> null)
select * from tblRecord where DateHired Between @pDateStart and @pDateTo
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