I am getting the error as "Procedure or function testingRetrieveDaataNew has too many arguments specified."
without that 2 newly added parameter, it is working fine
i newly added this:
SET @m_updatefromdate=@updatefromdate--added by chetan
SET @m_updatetodate=@updatetodate--added by chetan
and i got that error
private void displayDataInPaging(int startindex)
{
SqlConnection conn = null;
try
{
int gridCount;
Int64 currCat = Convert.ToInt64(cmbCategories.SelectedValue);
Int64 status = Convert.ToInt64(cmbAllStatus.SelectedValue);
Int64 country = Convert.ToInt64(cmbAllCountries.SelectedValue);
Int64 region = Convert.ToInt64(cmbRegions.SelectedValue);
string frmDate = dateTimePicker1.Value.ToString("MM-dd-yyyy");
string toDate = dateTimePicker2.Value.ToString("MM-dd-yyyy");
string updatefrmDate = dateTimePicker3.Value.ToString("MM-dd-yyyy");//added by chetan
string updatetoDate = dateTimePicker4.Value.ToString("MM-dd-yyyy");//added by chetan
Int64 user = Convert.ToInt64(cmbUsers.SelectedValue);
string isDateSelected = Convert.ToString(dateTimePicker1.Checked);
string isRemovesSelected = checkBoxRemoves.Checked.ToString();
string isKeywordSelected = checkBoxKeywords.Checked.ToString();
string isDomainSelected = checkBoxDomain.Checked.ToString();
int isActive = 0;
if (chkApproved.Checked)
{
isActive = 1;
}
else
{
isActive = 0;
}
MyConnect myCnn = new MyConnect(); String connString = myCnn.getConnect().ToString();
SqlDataAdapter adapter;
SqlCommand command;
DataSet ds;
DataTable dt = new DataTable();
conn = new SqlConnection(connString);
ds = new DataSet();
command = new SqlCommand();
conn.Open();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "testingRetrieveDaataNew";
command.Parameters.AddWithValue("@currCat", currCat);
command.Parameters.AddWithValue("@fromdate", frmDate);
command.Parameters.AddWithValue("@todate", toDate);
command.Parameters.AddWithValue("@updatefromdate", updatefrmDate);//added by chetan
command.Parameters.AddWithValue("@updatetodate", updatetoDate);//added by chetan
command.Parameters.AddWithValue("@user", user);
command.Parameters.AddWithValue("@country", country);
command.Parameters.AddWithValue("@region", region);
command.Parameters.AddWithValue("@status", status);
command.Parameters.AddWithValue("@isDateSelected", isDateSelected);
command.Parameters.AddWithValue("@isRemovesSelected", isRemovesSelected);
command.Parameters.AddWithValue("@keyword", isKeywordSelected);
command.Parameters.AddWithValue("@domain", isDomainSelected);
command.Parameters.AddWithValue("@active", isActive);
adapter = new SqlDataAdapter(command);
//adapter.Fill(ds, "userinfo");
adapter.Fill(ds, startindex, 20, "userinfo");
dt = ds.Tables["userinfo"];
gridCount = dt.Rows.Count;
if (gridCount < 1)
{
//pageIndex = pageIndex - 1;
SetPageIndex(typeIndex, 0);
MessageBox.Show("No Data Found");
index = startindex - 20;
}
else
{
SetPageIndex(typeIndex, 1);
gridData.DataSource = dt;
}
conn.Close();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
USE [expogroupdb]
GO
/****** Object: StoredProcedure [dbo].[testingRetrieveDaataNew] Script Date: 3/5/2018 2:22:39 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
