This Code gives the Error "Parameter '@statecountryidand' must be defined when i search accroding to country,state,district,block.."
Pls help me....pls Thanks in advance
public DataTable SearchCampaignRecords(int countryid, int statecountryid, int districtstateid, int blockdistrictid, int campaignid,string startdate, string enddate)
{
CountryId = countryid;
StateCountryId = statecountryid;
DistrictStateId = districtstateid;
BlockDistrictId = blockdistrictid;
CampaignId = campaignid;
StartDate = startdate;
EndDate = enddate;
try
{
string qry = "select * from campaigndata camp left join country c on camp.countryid=c.countryid left join state s on camp.statecountryid=s.stateid left join district distt on camp.districtstateid=distt.districtid left join block b on camp.blockdistrictid=b.blockid left join campaign c1 on camp.campaignid=c1.campaignid where 1 = 1";
MySqlParameter countryidP, statecountryidP, districtstateidP, blockdistrictidP, campaignidP, startdateP, enddateP;
MySqlParameter[] p = { };
if (countryid != 0)
{
qry += " and camp.countryid = @countryid";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
countryidP = new MySqlParameter("@countryid", CountryId);
p[p.Length - 1] = countryidP;
}
if (statecountryid != 0)
{
qry += " and camp.statecountryid = @statecountryid";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
statecountryidP = new MySqlParameter("@statecountryid", StateCountryId);
p[p.Length - 1] = statecountryidP;
}
if (districtstateid != 0)
{
qry += "and camp.districtstateid = @districtstateid";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
districtstateidP = new MySqlParameter("@districtstateid", DistrictStateId);
p[p.Length - 1] = districtstateidP;
}
if (blockdistrictid != 0)
{
qry += " and camp.blockdistrictid = @blockid";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
blockdistrictidP = new MySqlParameter("@blockid", BlockDistrictId);
p[p.Length - 1] = blockdistrictidP;
}
if (campaignid != 0)
{
qry += "and camp.campaignid = @campaignid";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
campaignidP = new MySqlParameter("@campaignid",CampaignId);
p[p.Length - 1] = campaignidP;
}
if (startdate != "")
{
DateTime dt1 = DateTime.Parse(StartDate);
qry += " and camp.entrydate>=@dt1";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
startdateP = new MySqlParameter("@dt1", dt1);
p[p.Length - 1] = startdateP;
}
if (enddate != "")
{
DateTime dt2 = DateTime.Parse(EndDate);
qry += " and camp.entrydate<=@dt2";
Array.Resize<MySqlParameter>(ref p, p.Length + 1);
enddateP = new MySqlParameter("@dt2", dt2.AddDays(1));
p[p.Length - 1] = enddateP;
}
return MySqlHelper.ExecuteDataset(common.GetConnectionString(), qry, p).Tables[0];
}
catch
{
Exception ex = new Exception("Search CampaignRecords Error.");
throw ex;
}
}