The below is my retrieve data module image link.
https://ibb.co/dFNbnn
my sir told me to add extra fields:
Updated from and Updated to.
There is companyinfo table which contains columns like date(feed date) and lastupdateddate.
date(feed date):suppose i entered the new details on 5th march 2018.so this date will be creation of new details.
lastupdateddate means: suppose i updated the 5th march's entered details on today(6th march).so lastupdated date will be 6th march.
So i added updated from and updated to in my form as shown in image link.
Onclick of result button gives me the details from date to to date.i want the details from updateddate to To updateddate.i tried this in code but it displays me the result of from date to To date.
USE [expogroupdb]
GO
/****** Object: StoredProcedure [dbo].[testingRetrieveDaataNew] Script Date: 3/6/2018 2:13:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testingRetrieveDaataNew]
@currCat bigint,
@todate datetime,
@fromdate datetime,
@updatefromdate datetime,--added by chetan
@updatetodate datetime,--added by chetan
@user bigint,
@country bigint,
@region bigint,
@status bigint,
@isDateSelected nvarchar(200),
@isRemovesSelected nvarchar(200),
@keyword nvarchar(20),
@domain nvarchar(20),
@active tinyint
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@m_currCat bigint,
@m_todate datetime,
@m_fromdate datetime,
@m_updatefromdate datetime,--added by chetan
@m_updatetodate datetime,--added by chetan
@m_user bigint,
@m_country bigint,
@m_region bigint,
@m_status bigint,
@m_isDateSelected nvarchar(200),
@m_isRemovesSelected nvarchar(200),
@QUERY nvarchar(MAX) ,
@QUERY1 nvarchar(MAX) ,
@whereField nvarchar(MAX),
@whereField1 nvarchar(MAX),
@dateSelectedQuery nvarchar(MAX),
@criteriaSelected nvarchar(MAX),
@finalQuery nvarchar(MAX),
@m_keyword nvarchar(20),
@m_domain nvarchar(20),
@flag int,
@m_active tinyint
SET @m_keyword=@keyword
SET @m_domain=@domain
SET @m_currCat=@currCat
SET @m_todate=@todate
SET @m_fromdate=@fromdate
SET @m_updatefromdate=@updatefromdate--added by chetan
SET @m_updatetodate=@updatetodate--added by chetan
SET @m_user=@user
SET @m_country=@country
SET @m_region=@region
SET @m_status=@status
SET @m_isDateSelected=@isDateSelected
SET @m_isRemovesSelected=@isRemovesSelected
SET @QUERY='SELECT COMPANY.companyname as ''Company'',EMAIL.email,COMPANY.website,COMPANY.products,CATEGORY.categoryname as ''category'',COMPANY.urlorcatalog as ''Source'',COMPANY.contactperson as ''Contact'',COMPANY.designation as ''designation'',COMPANY.Telphone AS ''Tel'',COMPANY.mobile as ''Mobile'',COUNTRY.countryname as ''Country'',REGION.regionname as ''region'',COMPANY.comment as ''Comment'',COMPANY.Fax,STATUS.status,COMPANY.address,usr.name as ''lastupdatedBy'' FROM companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID LEFT OUTER JOIN userinfo usr ON COMPANY.userid=usr.ID LEFT OUTER JOIN statusinfo STATUS ON COMPANY.status=STATUS.ID LEFT OUTER JOIN category CATEGORY ON COMPANY.category=CATEGORY.ID LEFT OUTER JOIN countryinfo COUNTRY ON COMPANY.country=COUNTRY.ID LEFT OUTER JOIN regioninfo REGION ON COMPANY.region=REGION.ID '
SET @whereField=' WHERE '
SET @criteriaSelected=''
SET @finalQuery=''
SET @flag=0
SET @m_active=@active
IF @m_currCat=0
SET @m_currCat=null
IF @m_user=0
SET @m_user=null
IF @m_status=0
SET @m_status=null
IF @m_country=0
SET @m_country=null
IF @m_region=0
SET @m_region=null
IF @m_isRemovesSelected='true'
SET @m_isRemovesSelected=null
IF @m_keyword='false'
SET @m_keyword=null
IF @m_domain='false'
SET @m_domain=NULL
IF @m_isDateSelected='true'
BEGIN
IF @m_currCat IS NOT NULL
BEGIN
SET @criteriaSelected=@criteriaSelected + ' COMPANY.category ='+CAST(@m_currCat AS NVARCHAR(30)) +' AND '
END
IF @m_country IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.country ='+CAST(@m_country AS NVARCHAR(30)) +' AND '
IF @m_region IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.region ='+CAST(@m_region AS NVARCHAR(30)) +' AND '
IF @m_status IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status ='+CAST(@m_status AS NVARCHAR(30)) +' AND '
IF @m_user IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.userid ='+CAST(@m_user AS NVARCHAR(30)) +' AND '
IF @m_isRemovesSelected IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status !=8 AND '
IF @m_keyword IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select keyword from keywordinfo where EMAIL.email like ''%''+ keyword +''%'' )) AND '
IF @m_domain IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select domainname from domaininfo where EMAIL.email like ''%@''+ domainname +''%'' )) AND '
--SET @finalQuery=@QUERY + @whereField + @criteriaSelected + ' DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) >'''+ convert(varchar(50),@m_fromdate,101) +''' AND DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) <='''+ convert(varchar(50),@m_todate,101) +''' AND COMPANY.active ='+CAST(@m_active AS NVARCHAR(30))+' ORDER BY COMPANY.date DESC '
SET @finalQuery=@QUERY + @whereField + @criteriaSelected + ' DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) >'''+ convert(varchar(50),@m_fromdate,101) +''' AND DATEADD(day, DATEDIFF(day, 0, COMPANY.date), 0) <='''+ convert(varchar(50),@m_todate,101) +''' AND DATEADD(day, DATEDIFF(day, 0, COMPANY.lastupdateddate), 0) >'''+ convert(varchar(50),@m_updatefromdate,101) +''' AND DATEADD(day, DATEDIFF(day, 0, COMPANY.lastupdateddate), 0) <='''+ convert(varchar(50),@m_updatetodate,101) +''' AND COMPANY.active ='+CAST(@m_active AS NVARCHAR(30))+' ORDER BY COMPANY.date DESC,COMPANY.lastupdateddate DESC '
EXEC(@finalQuery)
--print @finalQuery
END
ELSE
BEGIN
IF @m_country IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.country ='+CAST(@m_country AS NVARCHAR(30)) +' AND '
IF @m_region IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.region ='+CAST(@m_region AS NVARCHAR(30)) +' AND '
IF @m_status IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status ='+CAST(@m_status AS NVARCHAR(30)) +' AND '
IF @m_user IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.userid ='+CAST(@m_user AS NVARCHAR(30)) +' AND '
IF @m_isRemovesSelected IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.status !=8 AND '
IF @m_keyword IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select keyword from keywordinfo where EMAIL.email like ''%''+ keyword +''%'' )) AND '
IF @m_domain IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' ( not exists( Select domainname from domaininfo where EMAIL.email like ''%@''+ domainname +''%'' )) AND '
IF @m_currCat IS NOT NULL
SET @criteriaSelected=@criteriaSelected + ' COMPANY.category ='+CAST(@m_currCat AS NVARCHAR(30)) +' AND '
-- IF @m_currCat IS NULL
-- SET @criteriaSelected=@criteriaSelected + ' 1=1 '
SET @finalQuery= @QUERY + @whereField + @criteriaSelected + ' COMPANY.active ='+CAST(@m_active AS NVARCHAR(30))+' ORDER BY COMPANY.date DESC,COMPANY.lastupdateddate DESC '
EXEC(@finalQuery)
-- print @finalQuery
END
END
