protected void btnSearch_Click(object sender, EventArgs e)
{
sqlInjectionChecking();
String sql = @"
SELECT
Mem_Member.Mem_MemberID,
Mem_Member.MemberShipNo,
--Mem_Member.MemberShipNoDigit,
--Mem_Member.Mem_MemberTypeID,
--Mem_MemberType.Mem_MemberTypeName,
Mem_Member.Name,
--Mem_Member.DateOfBirth,
--Mem_Member.Age,
--Comn_Nationality.Comn_NationalityName,
--Mem_Member.Comn_NationalityID,
--Mem_Member.PlaceOfBrith,
Mem_Member.MailingAddress1,
Mem_Member.MailingAddress2,
Mem_Member.MailingAddress3,
Mem_Member.MailingAddress,
--Mem_Member.PermanentAddress,
--Mem_Member.PermanentAddress1,
--Mem_Member.PermanentAddress2,
--Mem_Member.PermanentAddress3,
--Mem_Member.PhoneOffice,
--Mem_Member.PhoneResidence,
Mem_Member.Mobile,
Mem_Member.Email,
--Mem_Member.Fax,
--Mem_Member.OtherContactInfo,
--Mem_Member.Comn_GenderID,
--Comn_Gender.Comn_GenderName,
--Mem_Member.PresentIEBMembershipNo,
--Mem_Member.Mem_SubDivisionID,
Mem_SubDivision.Mem_SubDivisionName,
--Mem_SubDivision.Mem_DivisionID,
--Mem_SubDivision.FullName AS SubDivisionFullName,
--Mem_Division.Mem_DivisionName,
--Mem_Division.FullName,
--Mem_Member.DeclarationDate,
--Mem_Member.ScrollNo,
--Mem_Member.ReceiptDate,
--Mem_Member.Comn_StatusID,
--Comn_Status.Comn_StatusName,
Mem_Member.PictureURL,
--Mem_Member.SignatureURL,
--Mem_Member.Comn_UniversityID,
Comn_University.Comn_UniversityName,
Mem_EducationalInfo.YearOfPassing as PassingYear,
--Mem_Member.Comn_OfficeID,
Comn_Office.Comn_OfficeName,
--Mem_Member.Comn_BloodGroup,
--Mem_Member.PassportNo,
--Mem_Member.NationalIDNo,
--Mem_Member.BirthRegistrationID,
--Mem_Member.Comn_RowStatusID,
--Mem_Member.ExtraField20,
--Mem_Member.ExtraField19,
--Mem_Member.ExtraField18,
--Mem_Member.ExtraField17,
--Mem_Member.ExtraField16,
--Mem_Member.ExtraField15,
--Mem_Member.ExtraField14,
--Mem_Member.ExtraField13,
--Mem_Member.ExtraField12,
--Mem_Member.ExtraField11,
--Mem_Member.ExtraField10,
--Mem_Member.ExtraField9,
--Mem_Member.ExtraField8,
--Mem_Member.ExtraField7,
--Mem_Member.ExtraField6,
--Mem_Member.ExtraField5,
--Mem_Member.ExtraField4,
--Mem_Member.ExtraField3,
--Mem_Member.ExtraField2,
--Mem_Member.ExtraField1,
--Comn_RowStatus.Comn_RowStatusName,
'' as PictureUrl
,'' as href
FROM
Mem_Member inner JOIN
Mem_MemberType ON Mem_Member.Mem_MemberTypeID = Mem_MemberType.Mem_MemberTypeID inner JOIN
Mem_MemberCategory ON Mem_MemberType.Mem_MemberCategoryID = Mem_MemberCategory.Mem_MemberCategoryID inner JOIN
Comn_Nationality ON Mem_Member.Comn_NationalityID = Comn_Nationality.Comn_NationalityID inner JOIN
Comn_Gender ON Mem_Member.Comn_GenderID = Comn_Gender.Comn_GenderID inner JOIN
Mem_SubDivision ON Mem_Member.Mem_SubDivisionID = Mem_SubDivision.Mem_SubDivisionID inner JOIN
Mem_Division ON Mem_SubDivision.Mem_DivisionID = Mem_Division.Mem_DivisionID INNER JOIN
Mem_ApprovedCouncilMeeting ON
Mem_Member.Mem_ApprovedCouncilMeetingID = Mem_ApprovedCouncilMeeting.Mem_ApprovedCouncilMeetingID INNER JOIN
Comn_Status ON Mem_Member.Comn_StatusID = Comn_Status.Comn_StatusID left outer JOIN
Mem_EducationalInfo ON Mem_Member.Mem_MemberID = Mem_EducationalInfo.Mem_MemberID left outer JOIN
Comn_University ON Mem_EducationalInfo.Comn_UniversityID = Comn_University.Comn_UniversityID left outer JOIN
Comn_Office ON Mem_Member.Comn_OfficeID = Comn_Office.Comn_OfficeID INNER JOIN
Comn_RowStatus ON Mem_Member.Comn_RowStatusID = Comn_RowStatus.Comn_RowStatusID
";
bool CriteriaUsed = false;
if (txtMemebershipNo.Text != "")
{
if (txtMemebershipNo.Text.Contains("/"))
{
txtMemebershipNo.Text = txtMemebershipNo.Text.Split('/')[1];
}
sql += where (sql) + "Mem_Member.MemberShipNoDigit=" + txtMemebershipNo.Text + @"
";
CriteriaUsed = true;
}
if (rbtnLMembershipType.SelectedValue != "0")
{
sql += where (sql) + "Mem_Member.Mem_MemberTypeID=" + rbtnLMembershipType.SelectedValue + @"
";
CriteriaUsed = true;
}
if (ddlComn_BloodGroup.SelectedValue != "")
{
sql += where (sql) + "Mem_Member.Comn_BloodGroup='" + ddlComn_BloodGroup.SelectedValue + @"'
";
CriteriaUsed = true;
}
if (txtName.Text != "")
{
sql += where (sql) + "Mem_Member.Name like '%" + txtName.Text + @"%'
";
CriteriaUsed = true;
}
if (txtAddress.Text != "")
{
sql += where (sql) + "(Mem_Member.MailingAddress1 like '%" + txtAddress.Text + @"%'
or Mem_Member.MailingAddress2 like '%" + txtAddress.Text + @"%'
or Mem_Member.MailingAddress3 like '%" + txtAddress.Text + @"%'
or Mem_Member.MailingAddress like '%" + txtAddress.Text + @"%'
or Mem_Member.PermanentAddress like '%" + txtAddress.Text + @"%'
or Mem_Member.PermanentAddress1 like '%" + txtAddress.Text + @"%'
or Mem_Member.PermanentAddress2 like '%" + txtAddress.Text + @"%'
or Mem_Member.PermanentAddress3 like '%" + txtAddress.Text + @"%'
) ";
CriteriaUsed = true;
}
if (txtPresentIEBMembershipNo.Text != "")
{
sql += where (sql) + "Mem_Member.PresentIEBMembershipNo like '%" + txtPresentIEBMembershipNo.Text + @"%'
";
CriteriaUsed = true;
}
if (txtDateOfBirth.Text != "")
{
sql += where (sql) + "Mem_Member.DateOfBirth ='" + CommonManager.DateReformation(txtDateOfBirth.Text) + @"'
";
CriteriaUsed = true;
}
if (ddlMem_Division.SelectedValue != "0")
{
sql += where (sql) + "Mem_Division.Mem_DivisionID=" + ddlMem_Division.SelectedValue + @"
";
CriteriaUsed = true;
}
if (ddlComn_University.SelectedValue != "0")
{
sql += where (sql) + "Mem_EducationalInfo.Comn_UniversityID=" + ddlComn_University.SelectedValue + @"
";
CriteriaUsed = true;
}
if (ddlComn_Office.SelectedValue != "0")
{
sql += where (sql) + "Mem_Member.Comn_OfficeID=" + ddlComn_Office.SelectedValue + @"
";
CriteriaUsed = true;
}
if (txtPassingYear.Text != "")
{
sql += where (sql) + "Mem_EducationalInfo.YearOfPassing =" + txtPassingYear.Text + @"
";
CriteriaUsed = true;
}
if (txtMobile.Text != "")
{
sql += where (sql) + "Mem_Member.Mobile like '%" + txtMobile.Text + @"%'
";
CriteriaUsed = true;
}
if (txtEmail.Text != "")
{
sql += where (sql) + "Mem_Member.Email like '%" + txtEmail.Text + @"%'
";
CriteriaUsed = true;
}
if (CriteriaUsed)
{
DataSet ds = DatabaseManager.ExecSQL(sql + " and Mem_Member.Comn_RowStatusID=1");
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["MailingAddress"].ToString() == "")
{
dr["MailingAddress"] = dr["MailingAddress1"].ToString() + "<br/>" + dr["MailingAddress2"].ToString() + "<br/>" + dr["MailingAddress3"].ToString() + "<br/>";
}
if (dr["PictureURL"].ToString() == "")
{
dr["PictureUrl"] = "../MembersArea/MemberPicture/" + dr["MemberShipNo"].ToString().Split('/')[0] + "-" + dr["MemberShipNo"].ToString().Split('/')[1] + ".jpg";
}
else
{
dr["PictureURL"] = "../MembersArea/MemberPicture/" + dr["PictureURL"];
}
if (HttpContext.Current.Request.Url.AbsoluteUri.Contains("MembersDirectory.aspx"))
{
dr["href"] = "../page/MemberDetails.aspx?memberno=" + dr["MemberShipNo"].ToString().Replace("/", "_");
}
else
{
dr["href"] = "../MembersArea/MembershipInfo.aspx?mem_MemberID=" + dr["Mem_MemberID"].ToString();
}
}
gvMember.DataSource = ds.Tables[0];
gvMember.DataBind();
lblCount.Text = ds.Tables[0].Rows.Count + " Memeber(s)";
}
else
{
lblCount.Text = "Plesae Select any Criteria";
gvMember.DataSource = null;
gvMember.DataBind();
}
}