Thank you for your quick response. This is happening in production.
Here is the code . when click on the submit button it calls a webservice to retrieve the results. I am including the webmethod too.
webmethod
public DataSet GetSearchDetails(string searchBy,string tbValue)
{
string sSql="", sFormattedSql="", sConnectStr = "";
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
SqlDataAdapter da;
dpsUtils.GetNSTDConnectionStrings( sAppName , sAppName , out sConnectStr );
SqlConnection conn = new SqlConnection(sConnectStr);
try
{
if (conn.State.ToString().ToUpper() == "CLOSED")
conn.Open();
if(searchBy.ToLower().ToString()=="group" || searchBy.ToLower().ToString()=="user" )
sSql = "select distinct enterprise,customer,[group],groupname,fldenterpriseid,psr from vw_Get_details where [{0}] like '{1}' and [group] is not null and groupname is not null" ;
else
sSql = "select distinct enterprise,customer,[group],groupname,fldenterpriseid,psr from vw_Get_details where {0} like '{1}' and [group] is not null and groupname is not null";
sFormattedSql = String.Format(sSql, searchBy,tbValue);
cmd.Connection = conn;
cmd.CommandText = sFormattedSql;
cmd.CommandTimeout = 180;
// da = new SqlDataAdapter( sFormattedSql , conn );
da=new SqlDataAdapter(cmd);
da.Fill( ds , "SearchDetails" ) ;
da.Dispose();
conn.Close();
}
catch ( SqlException e )
{
conn.Close();
return ds;
}
return ds;
}
This is the class which calls the webservice
using System;
using System.IO;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Microsoft.Web.UI.WebControls;
using EVNTSYNCMTSLib;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Collections.Specialized;
using System.Xml;
using System.Web.Caching;
using gutils;
using BWwebservice;//this is a webservice
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class Query : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlTableCell celMainContentNavigationTopLevel;
protected System.Web.UI.HtmlControls.HtmlTableCell celLeftSideBar;
protected System.Web.UI.HtmlControls.HtmlGenericControl SearchCriteria;
private string XmenudataCache = "xmenudata" ;
public string sUserId = "";
public string sAccountId;
public string sCircuitId;
public string sVCID;
public string sIP;
public string sNSR;
public string sTBSPSR;
public string sAction;
public long xmlid;
public bool bDeveloper;
public string sWorkOrderId;
public string sStatus;
protected System.Web.UI.WebControls.Button btnResults;
protected System.Web.UI.WebControls.Button btnNewSearch2;
protected System.Web.UI.WebControls.DataGrid dataGrid2;
protected System.Data.DataSet dataSet1;
protected System.Data.DataSet dataSet2;
protected System.Web.UI.WebControls.Label lblSeed;
protected XOStandardControlLibrary.globalNavigationMenu GLOBALNAVIGATIONMENU;
protected XOStandardControlLibrary.firstNavigationMenu FIRSTNAVIGATIONMENU;
protected XOStandardControlLibrary.secondNavigationMenu SECONDNAVIGATIONMENU;
protected XOStandardControlLibrary.thirdNavigationMenu THIRDNAVIGATIONMENU;
protected System.Web.UI.WebControls.Label PopUpMessage;
protected System.Web.UI.WebControls.Label URLToOpen;
protected System.Web.UI.WebControls.Label Title;
protected System.Web.UI.WebControls.Label SubTitle;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label Label8;
protected System.Web.UI.WebControls.DropDownList ddlSearchBy;
protected System.Web.UI.WebControls.TextBox tbValue;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.Panel pSearch;
protected XOStandardControlLibrary.navigationMenuFooter NAVIGATIONMENUFOOTER;
protected BWLoggingWS.BWLoggingWS wsBW;
protected System.Web.UI.WebControls.LinkButton linkEnterprise;
public gutils.gUtils dpsUtils = new gUtils();
public gutils.Web gWeb;
string sAppName = "Route";
protected System.Web.UI.WebControls.Repeater rptBWDSearch;
protected System.Web.UI.WebControls.TextBox tbCurrPg;
protected System.Web.UI.WebControls.Label lblpgcount;
protected System.Web.UI.WebControls.ImageButton BtnFirst1;
protected System.Web.UI.WebControls.ImageButton BtnPrev;
protected System.Web.UI.WebControls.ImageButton BtnNext;
protected System.Web.UI.WebControls.ImageButton BtnEnd2;
protected System.Web.UI.WebControls.Panel pnlNavResults;
protected System.Web.UI.HtmlControls.HtmlTable Table3;
private PagedDataSource objPds;
private DataSet searchDS=null;
protected System.Web.UI.WebControls.Panel pnlrepeater;
protected System.Web.UI.HtmlControls.HtmlGenericControl GlobalButtons;
private string BWListCache = "BWList";
private void initPagedDataSource()
{
objPds = new PagedDataSource();
// Indicate that the data should be paged
objPds.AllowPaging = true;
// Set the number of items you wish to display per page
objPds.PageSize = 10;
if (Cache[BWListCache] != null)
{
DataSet ds = (DataSet) Cache[BWListCache];
// Populate the repeater control with the Items DataSet
objPds.DataSource = ds.Tables["SearchDetails"].DefaultView;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
wsBW = Global.CreateWebServiceInstance();
Page.RegisterHiddenField("__EVENTTARGET", "btnSearch");
gWeb = new Web(this);
lblError.Visible = false;
initPagedDataSource();
if (!IsPostBack)
{
pnlNavResults.Visible = false;
LoadSearchByList();
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnSearch.Click += new System.EventHandler(this.btnSearch_Click);
this.tbCurrPg.TextChanged += new System.EventHandler(this.tbCurrPg_TextChanged);
this.BtnFirst1.Click += new System.Web.UI.ImageClickEventHandler(this.BtnFirst_Click);
this.BtnPrev.Click += new System.Web.UI.ImageClickEventHandler(this.BtnPrev_Click);
this.BtnNext.Click += new System.Web.UI.ImageClickEventHandler(this.BtnNext_Click);
this.BtnEnd2.Click += new System.Web.UI.ImageClickEventHandler(this.BtnEnd_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
#region RepeaterNavagation
private void BtnFirst_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
// Move to first page.
DatasetPageAction(objPds, "rev" , -9999 );
}
private void BtnPrev_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
// Move to first page.
DatasetPageAction(objPds, "rev" , -1 );
}
private void BtnNext_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
// Move to first page.
DatasetPageAction(objPds, "fwd" , 1 );
}
private void BtnEnd_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
// Move to first page.
DatasetPageAction(objPds, "fwd" , 9999 );
}
private void tbCurrPg_TextChanged(object sender, System.EventArgs e)
{
int iTgPg = Convert.ToInt16( tbCurrPg.Text );
int iMaxPg = Convert.ToInt16 ( lblpgcount.Text );
if ( iTgPg >= iMaxPg )
{
// Move to max page and Reset the Typed value to the max pg.
DatasetPageAction(objPds, "fwd" , 9999 );
return;
}
if ( iTgPg < 0 )
{
DatasetPageAction(objPds, "rev" , -9999 );
return;
}
DatasetPageAction(objPds, "exact" , iTgPg );
}
/// <summary>
/// handles paging of a registerd dataset.
/// sdirection is the direction of the move ( fwd / rev ),
/// iCount is the number of pagest to move ( -9999 - first page / 9999 - last page )
/// </summary>
public void DatasetPageAction(PagedDataSource pds, string sdirection, int iCount)
{
URLToOpen.Text = "";
Int32 currentPage = 1;
Int32 pageCount = pds.PageCount;
try
{
currentPage = System.Convert.ToInt32(tbCurrPg.Text);
}
catch {}
// reset the Selected Item On Page change.
//pds.CurrentPageIndex = -1;
if ( sdirection == "fwd" )
{
if ( iCount == 9999 )
{
currentPage = pageCount;
}
else
{
if ( (currentPage + iCount) <= pageCount )
{
currentPage = currentPage + iCount;
}
else
currentPage = pageCount;
}
}
if ( sdirection == "rev" )
{
if ( iCount == -9999 )
{
currentPage = 1;
}
else
{
if ( (currentPage + iCount) >= 1 )
{
currentPage = currentPage + iCount;
}
else
currentPage = 1;
}
}
if ( sdirection == "exact" )
{
if ( iCount == 0 ) iCount = 1;
currentPage = iCount;
}
pds.CurrentPageIndex = currentPage - 1;
BindSearchDataToDG();
}
#endregion RepeaterNavagation
private void LoadSearchByList()
{
ddlSearchBy.Items.Clear();
ListItem listItem1 = new ListItem();
listItem1.Text = "Account Id";
listItem1.Value = "enterprise";
ddlSearchBy.Items.Add(listItem1);
ListItem listItem2 = new ListItem();
listItem2.Text = "Customer Name";
listItem2.Value = "customer";
ddlSearchBy.Items.Add(listItem2);
ListItem listItem3 = new ListItem();
listItem3.Text = "Site Id";
listItem3.Value = "group";
ddlSearchBy.Items.Add(listItem3);
ListItem listItem5 = new ListItem();
listItem5.Text = "Group Name";
listItem5.Value = "groupname";
ddlSearchBy.Items.Add(listItem5);
ListItem listItem4 = new ListItem();
listItem4.Text = "User Id";
listItem4.Value = "user";
ddlSearchBy.Items.Add(listItem4);
ListItem listItem6 = new ListItem();
listItem6.Text = "TN Number";
listItem6.Value = "number";
ddlSearchBy.Items.Add(listItem6);
ListItem listItem7 = new ListItem();
listItem7.Text = "PSR Number";
listItem7.Value = "psr";
ddlSearchBy.Items.Add(listItem7);
}
private void btnSearch_Click(object sender, System.EventArgs e)
{
PopUpMessage.Text = "";
URLToOpen.Text = "";
try
{
searchDS = wsBW.GetSearchDetails(ddlSearchBy.SelectedValue.ToString(),gutils.SQL.Utils.prepSearchField(tbValue.Text.ToString().Trim(),false));
}
catch(Exception ex)
{string str=ex.Message;
}
Cache[BWListCache] = searchDS;
objPds.DataSource = searchDS.Tables["SearchDetails"].DefaultView;
objPds.CurrentPageIndex = 0;
BindSearchDataToDG();
}
public void BindSearchDataToDG()
{
DataSet ds = (DataSet)Cache.Get(BWListCache);
if ( ds == null )
{
Cache[BWListCache] = wsBW.GetSearchDetails(ddlSearchBy.SelectedValue.ToString(),tbValue.Text.ToString().Trim());
ds = (DataSet)Cache.Get(BWListCache);
}
else
{
rptBWDSearch.DataSource = objPds;
rptBWDSearch.DataBind();
if(ds.Tables["SearchDetails"].Rows.Count<1)
{
lblError.Text = "No Data found";
lblError.Visible = true;
}
lblpgcount.Text = objPds.PageCount.ToString();
int iCurrPg = (objPds.CurrentPageIndex + 1);
tbCurrPg.Text = iCurrPg.ToString();
if (objPds.PageCount > 1 )
pnlNavResults.Visible = true;
else
pnlNavResults.Visible = false;
}
}
}
