I have a help popup page, which fills data from sql database located on a different server than the application hosted machine. generally it works fine, but some time it throws "Service not available" message on the page. and show the following Eo.Web window error
Eo.Web.clientside debug message
eo.web.control "Callbackpanek1" error message (set client side on error......): The call back on 'Callbackpanel1' has failed because the server did not recognize this callback and process it as a normal request. This can occur if multiple call back /call back panel controls in the page, and this call back control was initially dynamically loaded and was not loaded for this call back.
etc....etc... i am using the following code
-------------- HTML --------------------
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmHelpWin.aspx.vb" Inherits="App_Common_frmHelpWin"
ValidateRequest="false" %>
<%@ Register Assembly="EO.Web" Namespace="EO.Web" TagPrefix="eo" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title id="pageCaption" runat="server">Help Window</title>
<link href="../App_Css/NIStyle1.css" rel="stylesheet" type="text/css" visible="true">
<base target="_self" />
<script language='javascript' src="../App_Js/NIJS1.js"></script>
<script language="javascript" type="text/javascript">
function GridItemOnDoubleClick()
{
// read the primary key from the grid
var item = Grid1.getSelectedItem();
var PKeyID = item.getKey();
window.returnValue=PKeyID;
window.close();
return;
}
function on_checkbox_clicked(objChkBox, pKey)
{
var txt = document.getElementById("txtSelectedValues");
if (objChkBox.checked == true)
{
txt.value = txt.value + pKey + ";";
}
else
{
var str = txt.value;
str = str.replace(pKey + ";","");
txt.value = str;
}
}
function returnValues()
{
var txt = document.getElementById("txtSelectedValues");
window.returnValue=txt.value;
window.close();
}
function ShowChecked()
{
//var oneTime = document.getElementById("txtOneTime").value;
//if (oneTime == "Done")
// return;
//
//oneTime = "Done";
//alert(checkedValues.value);
for (var i=0; i<document.form1.elements.length; i++) {
var e = document.form1.elements[i];
//(e.name != 'allbox') &&
//&& (!e.disabled)
if ( (e.type=='checkbox') ) {
if (inSelectedList(e.id) == true)
e.checked = true;
else
e.checked = false;
}
}
}
function inSelectedList(Code)
{
var checkedValues;
checkedValues = document.getElementById("txtSelectedValues").value;
//alert(Code + ' ---- ' + checkedValues + ' >>>>>>>' + checkedValues.indexOf(Code));
if (checkedValues.indexOf(Code) == -1)
return false;
else
return true;
}
</script>
</head>
<body onload="javascript:document.forms[0]['txtContains'].focus(); resizeWinTo('mydiv'); "
onactivate="ShowChecked();" onblur="ShowChecked();">
<form id="form1" runat="server">
<div id="mydiv">
<table style="width: 100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="border-bottom: solid 1px Silver" height="25px">
<asp:Label ID="lblCaption" runat="server" CssClass="labelBold2" ForeColor="DarkBlue"></asp:Label>
<asp:Image ID="imgHederImage" runat="server" ImageUrl="" Visible="false" />
<asp:Label ID="LblError" runat="server" BorderStyle="None" CssClass="labelError1"></asp:Label></td>
</tr>
<tr>
<td style="background-repeat: repeat" valign="middle">
<table>
<tr>
<td>
<asp:Button ID="OKButton" runat="server" Text="OK" Width="60px" CssClass="buttonRegular" UseSubmitBehavior="False" /></td>
<td>
<asp:Button ID="CancelButton" runat="server" Text="Cancel" CssClass="buttonRegular" UseSubmitBehavior="False" /></td>
<td>
<asp:Button ID="SaveLayoutButton" runat="server" Text="Save Layout" CssClass="buttonRegular" UseSubmitBehavior="False" /></td>
<td>
<asp:Label ID="lblLookin" runat="server" CssClass="labelRegular" Text="Look in" ForeColor="DarkBlue"></asp:Label></td>
<td>
<asp:DropDownList ID="cmbSearchIn" runat="server" CssClass="comboBoxRegular">
</asp:DropDownList></td>
<td>
<asp:TextBox ID="txtContains" runat="server" CssClass="labelRegular" Width="130px"
Style="height: 13px"></asp:TextBox></td>
<td>
<asp:ImageButton ID="FilterButton" runat="server" ImageUrl="~/App_Images/Icons/ico_search16.gif" /></td>
<td>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSearchresults" runat="server" CssClass="labelRegular" ForeColor="DarkBlue"
Visible="true"></asp:Label>
<div ondblclick="GridItemOnDoubleClick()">
<eo:Grid ID="Grid1" runat="server" BorderColor="#7F9DB9" BorderWidth="1px" ColumnHeaderAscImage="00050104"
ColumnHeaderDescImage="00050105" ColumnHeaderDividerImage="00050103" FixedColumnCount="1"
Font-Bold="False" Font-Italic="False" Font-Names="Tahoma" Font-Overline="False"
Font-Size="8.75pt" Font-Strikeout="False" Font-Underline="False" GoToBoxVisible="True"
GridLineColor="220, 223, 228" GridLines="Both" Height="610px" Width="100%" AllowColumnReorder="True"
LoadingHTML="Loading..." ClientSideOnCellSelected="alert('clientsideoncellselected')"
ClientSideOnItemSelected="alert('clientsideoncellselected2')">
<FooterStyle CssText="padding-bottom:4px;padding-left:4px;padding-right:4px;padding-top:4px;">
</FooterStyle>
<ItemStyles>
<eo:GridItemStyleSet>
<ItemStyle CssText="background-color: white" />
<ItemHoverStyle CssText="background-color: whitesmoke" />
<SelectedStyle CssText="background-color: #316ac5; color: white" />
<FixedColumnCellStyle CssText="border-right: #d6d2c2 1px solid; padding-right: 10px; border-top: #faf9f4 1px solid; border-left: #faf9f4 1px solid; border-bottom: #d6d2c2 1px solid; background-color: #ebeadb; text-align: right; color: black;" />
<CellStyle CssText="padding-left:8px;padding-top:2px;" />
</eo:GridItemStyleSet>
</ItemStyles>
<ContentPaneStyle CssText="border-bottom-color:#7f9db9;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#7f9db9;border-left-style:solid;border-left-width:1px;border-right-color:#7f9db9;border-right-style:solid;border-right-width:1px;border-top-color:#7f9db9;border-top-style:solid;border-top-width:1px;">
</ContentPaneStyle>
<GoToBoxStyle CssText="BORDER-RIGHT: #7f9db9 1px solid; BORDER-TOP: #7f9db9 1px solid; BORDER-LEFT: #7f9db9 1px solid; WIDTH: 40px; BORDER-BOTTOM: #7f9db9 1px solid">
</GoToBoxStyle>
<Columns>
<eo:RowNumberColumn>
</eo:RowNumberColumn>
</Columns>
<ColumnHeaderStyle CssText="background-image:url('00050101');padding-left:8px;padding-top:3px;">
</ColumnHeaderStyle>
</eo:Grid>
</div>
<asp:HiddenField ID="txtSQL" runat="server" />
<asp:HiddenField ID="txtWindowURL" runat="server" />
<asp:HiddenField ID="txtWindowParameters" runat="server" />
<asp:HiddenField ID="txtRefColValue" runat="server" />
<asp:HiddenField ID="txtSortExpression" runat="server" />
<asp:HiddenField ID="txtSortOrder" runat="server" />
<asp:HiddenField ID="txtSelectedValues" runat="server" />
<asp:HiddenField ID="txtOneTime" runat="server" />
<eo:PageContextMenu ID="PageContextMenu1" runat="server" CheckIconUrl="OfficeCheckIcon2"
ControlSkinID="None" Width="200px">
<LookItems>
<eo:MenuItem DisabledStyle-CssText="background-color:transparent;border-bottom-style:none;border-left-style:none;border-right-style:none;border-top-style:none;padding-bottom:1px;padding-left:5px;padding-right:5px;padding-top:1px;color:gray"
Height="24" HoverStyle-CssText="background-color:#C0D6F4;border-bottom-color:#000080;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#000080;border-left-style:solid;border-left-width:1px;border-right-color:#000080;border-right-style:solid;border-right-width:1px;border-top-color:#000080;border-top-style:solid;border-top-width:1px;padding-left:4px;padding-right:4px;padding-top:0px;padding-bottom:0px;"
ItemID="_TopLevelItem" NormalStyle-CssText="background-color:transparent;border-bottom-style:none;border-left-style:none;border-right-style:none;border-top-style:none;padding-bottom:1px;padding-left:5px;padding-right:5px;padding-top:1px;"
SelectedStyle-CssText="background-color:white;border-bottom-color:#000080;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#000080;border-left-style:solid;border-left-width:1px;border-right-color:#000080;border-right-style:solid;border-right-width:1px;border-top-color:#000080;border-top-style:solid;border-top-width:1px;padding-left:4px;padding-right:4px;padding-top:0px;padding-bottom:0px;">
<SubMenu CollapseEffect-Type="GlideTopToBottom" ExpandEffect-Type="GlideTopToBottom"
ItemSpacing="3" LeftIconCellWidth="25" SideImage="Office2003SideBar2" Style-CssText="background-color:#F6F6F6;border-bottom-color:#002D96;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#002D96;border-left-style:solid;border-left-width:1px;border-right-color:#002D96;border-right-style:solid;border-right-width:1px;border-top-color:#002D96;border-top-style:solid;border-top-width:1px;color:black;cursor:hand;font-family:Tahoma;font-size:8pt;padding-bottom:1px;padding-left:1px;padding-right:1px;padding-top:1px;">
</SubMenu>
</eo:MenuItem>
<eo:MenuItem IsSeparator="True" ItemID="_Separator" NormalStyle-CssText="background-color:#6a8ccb;height:1px;margin-left:30px;width:1px;">
</eo:MenuItem>
<eo:MenuItem DisabledStyle-CssText="background-color:transparent;border-bottom-style:none;border-left-style:none;border-right-style:none;border-top-style:none;padding-bottom:1px;padding-left:2px;padding-right:5px;padding-top:1px;color:gray"
Height="24" HoverStyle-CssText="background-color:#FFEEC2;border-bottom-color:#000080;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#000080;border-left-style:solid;border-left-width:1px;border-right-color:#000080;border-right-style:solid;border-right-width:1px;border-top-color:#000080;border-top-style:solid;border-top-width:1px;padding-left:1px;padding-right:4px;padding-top:0px;"
ItemID="_Default" NormalStyle-CssText="background-color:transparent;border-bottom-style:none;border-left-style:none;border-right-style:none;border-top-style:none;padding-bottom:1px;padding-left:2px;padding-right:5px;padding-top:1px;"
SelectedStyle-CssText="background-color:white;border-bottom-color:#000080;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#000080;border-left-style:solid;border-left-width:1px;border-right-color:#000080;border-right-style:solid;border-right-width:1px;border-top-color:#000080;border-top-style:solid;border-top-width:1px;padding-left:1px;padding-right:4px;padding-top:0px;"
Text-Padding-Right="30">
<SubMenu CollapseEffect-Type="GlideTopToBottom" ExpandEffect-Type="GlideTopToBottom"
ItemSpacing="3" LeftIconCellWidth="25" SideImage="Office2003SideBar2" Style-CssText="background-color:#F6F6F6;border-bottom-color:#002D96;border-bottom-style:solid;border-bottom-width:1px;border-left-color:#002D96;border-left-style:solid;border-left-width:1px;border-right-color:#002D96;border-right-style:solid;border-right-width:1px;border-top-color:#002D96;border-top-style:solid;border-top-width:1px;color:black;cursor:hand;font-family:Tahoma;font-size:8pt;padding-bottom:1px;padding-left:1px;padding-right:1px;padding-top:1px;">
</SubMenu>
</eo:MenuItem>
</LookItems>
<TopGroup Style-CssText="background-color:#abc7f6;">
</TopGroup>
</eo:PageContextMenu>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
--------------------- CODE BEHIND ----------------
Imports System.Data
Partial Class App_Common_frmHelpWin
Inherits System.Web.UI.Page
Dim m_objDB As New DBManager
Dim m_HelpID As String
Dim strSQL As String, MultiSelect As Boolean = False
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' reload page always
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Try
Session("CallbackPage") = Request.Url.AbsoluteUri
Catch ex As Exception
Session("CallbackPage") = ""
End Try
'Enforce security here ...
Dim SecurityErrorCode As String = AccessAllowed(Session("user_id"), Session("session_id"), "MENU000001")
If SecurityErrorCode = "SESSION_EXPIRED" Then
Response.Redirect("frmReLogin.aspx?reason=SESSION_EXPIRED")
Response.End()
ElseIf SecurityErrorCode = "SESSION_LOGGEDOFF" Then
Response.Redirect("frmReLogin.aspx?reason=SESSION_LOGGEDOFF")
Response.End()
ElseIf SecurityErrorCode = "ACCESS_DENIED" Then
Response.Write("<h1>Access denied</h1><br><h5>You are not authorized to access this page. Please contact system admin.</h5>")
Response.End()
End If
OKButton.Attributes.Add("onclick", "returnValues()")
CancelButton.Attributes.Add("onclick", "window.close()")
m_HelpID = UCase((Trim(Request.QueryString("helpID") & "")))
'm_HelpID = "HELP000011"
' if any reference values are passed
txtRefColValue.Value = Trim(Request.QueryString("RefColValue") & "")
MultiSelect = IIf(Trim(Request.QueryString("MultiSelect")) = "true", True, False)
'MultiSelect = True
If Page.IsPostBack = False Then
'Read the query for Help Window
Call LoadViewHeader()
' Load customized grid settings
Call LoadViewSettings()
' Load the data rows
'Call LoadData(0)
Call LoadDataEx()
txtSelectedValues.Value = (Trim(Request.QueryString("values") & ""))
' Load the Context menu on grid
m_objDB.LoadGridViewContextMenus(m_HelpID, Session("user_id"), PageContextMenu1)
Else
LblError.Text = ""
End If
End Sub
Private Sub LoadViewHeader()
Try
' Read the query for Help Window
strSQL = " SELECT * FROM sy_listview_header WHERE view_id = '" & m_HelpID & "' AND view_status = 'A' "
Dim Ds As DataSet
Ds = m_objDB.GetQueryResult(strSQL)
If Not Ds Is Nothing Then
If Ds.Tables("tblResult").Rows.Count > 0 Then
imgHederImage.ImageUrl = Ds.Tables("tblResult").Rows(0).Item("view_form_icon") & ""
lblCaption.Text = Ds.Tables("tblResult").Rows(0).Item("view_caption") & ""
' if any additional parameters are passed
If Trim(Request.QueryString("pageHeading") & "") <> "" Then
lblCaption.Text = lblCaption.Text & " (" & Trim(Request.QueryString("pageHeading") & "") & ")"
End If
pageCaption.Text = lblCaption.Text & ""
txtSQL.Value = Ds.Tables("tblResult").Rows(0).Item("view_sql_query") & ""
txtWindowURL.Value = Ds.Tables("tblResult").Rows(0).Item("view_form_url") & ""
txtWindowParameters.Value = "height=" & Ds.Tables("tblResult").Rows(0).Item("view_form_height") & ";width=" & Ds.Tables("tblResult").Rows(0).Item("view_form_width") & ";"
Call LoadViewColumns()
Else
Response.Write("No such View ID found in table: " & m_HelpID)
Response.End()
End If
Ds = Nothing
End If
Catch ex As Exception
LblError.Text = ex.Message
End Try
End Sub
Private Sub LoadViewColumns()
' clear dropdown box
cmbSearchIn.Items.Clear()
cmbSearchIn.Items.Add(New ListItem("All columns", ""))
' remove columns from the grid
Grid1.Columns.Clear()
' add a number column
Dim dgRowNumColumn As New EO.Web.RowNumberColumn
dgRowNumColumn.Width = 30
dgRowNumColumn.AllowSort = True
Grid1.Columns.Add(dgRowNumColumn)
' Query in SQL 2005 format, testing in progress
strSQL = " SELECT a.column_id, a.table_column, a.column_desc, b.column_id AS user_column_id, a.default_yn, a.column_pkey_yn, b.column_width "
strSQL = strSQL & " FROM SY_LISTVIEW_COLUMNS a LEFT OUTER JOIN "
strSQL = strSQL & " SY_LISTVIEW_USER_COLUMNS b ON a.view_id = b.view_id AND a.column_id = b.column_id AND (b.user_id = '" & Session("user_id") & "') "
strSQL = strSQL & " WHERE (a.view_id = '" & m_HelpID & "') AND (a.column_status = 'A') "
strSQL = strSQL & " ORDER BY b.display_order, a.display_order "
Dim Ds As DataSet, TotalCols As Integer, i As Integer, ColumnList As String = "", ColumnName As String
Ds = m_objDB.GetQueryResult(strSQL)
If Not Ds Is Nothing Then
TotalCols = Ds.Tables("tblResult").Rows.Count
If TotalCols > 0 Then
' -------------------------------------------------------------------------
' USER SELECTED COLUMNS
' -------------------------------------------------------------------------
For i = 0 To TotalCols - 1
' check for user selected columns
If Trim(Ds.Tables("tblResult").Rows(i).Item("user_column_id") & "") <> "" Then
ColumnName = Ds.Tables("tblResult").Rows(i).Item("column_desc") & ""
ColumnList = ColumnList & Ds.Tables("tblResult").Rows(i).Item("table_column") & " "
ColumnList = ColumnList & "'" & ColumnName & "',"
' add this column to the grid also
If Trim(Ds.Tables("tblResult").Rows(i).Item("column_pkey_yn") & "") = "Y" Then
' assumption primary key
Grid1.KeyField = ColumnName
If MultiSelect = True Then
Dim dgStaticColumn As New EO.Web.StaticColumn
dgStaticColumn.AllowSort = True
dgStaticColumn.Width = 30
dgStaticColumn.DataField = ColumnName
dgStaticColumn.DataFormat = "<input type=""checkbox"" id='{0}' onclick=""on_checkbox_clicked(this, '{0}')""/>"
Grid1.Columns.Add(dgStaticColumn)
End If
Else
' other columns, add to dropdown
cmbSearchIn.Items.Add(ColumnName)
' adding to grid
Dim dgColumn As New EO.Web.StaticColumn()
dgColumn.HeaderText = ColumnName
dgColumn.DataField = ColumnName
dgColumn.Name = Trim(Ds.Tables("tblResult").Rows(i).Item("column_id") & "")
dgColumn.Width = Val(Trim(Ds.Tables("tblResult").Rows(i).Item("column_width") & ""))
dgColumn.AllowSort = True
Grid1.Columns.Add(dgColumn)
End If
End If
Next
' -------------------------------------------------------------------------
' IF USER NOT SELECTED ANY OF THE COLUMNS
' LOAD THE DEFAULT COLUMNS (ALL COLUMNS)
' -------------------------------------------------------------------------
If ColumnList = "" Then
' this means user is not selected any columns
' so load the default view
Dim SQLArray(1) As String, SQLCount As Integer = 0
For i = 0 To TotalCols - 1
' check for default columns
If Trim(Ds.Tables("tblResult").Rows(i).Item("user_column_id") & "") = "" Then
If Trim(Ds.Tables("tblResult").Rows(i).Item("default_yn") & "") = "Y" Then
ColumnName = Ds.Tables("tblResult").Rows(i).Item("column_desc") & ""
ColumnList = ColumnList & Ds.Tables("tblResult").Rows(i).Item("table_column") & " "
ColumnList = ColumnList & "'" & ColumnName & "',"
' add the default columns to the user
' customization table, for the first time
strSQL = "INSERT INTO SY_LISTVIEW_USER_COLUMNS "
strSQL = strSQL & " ( user_id, column_id, view_id, display_order, column_width ) "
strSQL = strSQL & " VALUES ("
strSQL = strSQL & " '" & Session("user_id") & "', "
strSQL = strSQL & " '" & Trim(Ds.Tables("tblResult").Rows(i).Item("column_id") & "") & "', "
strSQL = strSQL & " '" & m_HelpID & "', "
strSQL = strSQL & " '" & (i + 1).ToString & "', "
strSQL = strSQL & " '100') "
ReDim Preserve SQLArray(SQLCount)
SQLArray(SQLCount) = strSQL
SQLCount = SQLCount + 1
' add this column to the grid also
If Trim(Ds.Tables("tblResult").Rows(i).Item("column_pkey_yn") & "") = "Y" Then
' assumption primary key
Grid1.KeyField = ColumnName
Else
' other columns
cmbSearchIn.Items.Add(ColumnName)
Dim dgColumn As New EO.Web.StaticColumn()
dgColumn.HeaderText = ColumnName
dgColumn.DataField = ColumnName
dgColumn.Name = Trim(Ds.Tables("tblResult").Rows(i).Item("column_id") & "")
dgColumn.AllowSort = True
Grid1.Columns.Add(dgColumn)
End If
End If
End If
Next
Call m_objDB.executeSQLArray(SQLArray)
End If
' remove last comma
ColumnList = Mid(ColumnList, 1, Len(ColumnList) - 1)
txtSQL.Value = Replace(txtSQL.Value, "{column}", ColumnList)
' Inject the WHERE condition, if any reference key is passed
Dim tmpVar1 As String = Trim(UCase(Request.QueryString("RefColName") & ""))
Dim tmpVar2 As String = Trim(Request.QueryString("RefColValue") & "")
Dim tmpVar3 As String = Trim(Request.QueryString("Join") & "")
If tmpVar1 <> "" Then
Dim strWhereCondition As String = ""
If tmpVar2 = "_SELF" Then ' use session variable user_id
strWhereCondition = " ( " & tmpVar1 & " = '" & Session("user_id") & "' " & tmpVar3 & " "
ElseIf tmpVar2 = "_EMPLOYEE_CODE" Then ' use session variable employee_code
strWhereCondition = " ( " & tmpVar1 & " = '" & Session("employee_code") & "' " & tmpVar3 & " "
Else
strWhereCondition = " ( " & tmpVar1 & " = '" & m_objDB.fixStr(tmpVar2) & "' " & tmpVar3 & " "
End If
' CHECK FOR 2ND CONDITION
' --------------------------------------------------------------------------------------------------
tmpVar1 = Trim(UCase(Request.QueryString("RefColName2") & ""))
tmpVar2 = Trim(Request.QueryString("RefColValue2") & "")
If tmpVar1 <> "" Then
If tmpVar2 = "" Then ' NULL OR BLANK
strWhereCondition = strWhereCondition & tmpVar1 & " = '' "
End If
Else
strWhereCondition = strWhereCondition
End If
' ---------------------------------------------------------------------
' ATTACHING SPECIAL CONDITION
' ---------------------------------------------------------------------
If Request.QueryString("entity") = "_CLIENT" Then
' show all the client, if selected
If Session("view_all_clients") = "Y" Then
strWhereCondition = " ( 1 = 1 "
lblCaption.Text = lblCaption.Text & " (View: All)"
Else
' Restrict Clients
strWhereCondition = strWhereCondition & " OR " & tmpVar1 & " IN ( "
strWhereCondition = strWhereCondition & " SELECT child_employee_code FROM WP_EMPLOYEE_HIERARCHY WHERE root_employee_code = '" & Session("employee_code") & "' )"
lblCaption.Text = lblCaption.Text & " (View: Restricted)"
End If
End If
' Attaching "WHERE" key word
strWhereCondition = " WHERE " & strWhereCondition & " ) AND "
' Locate the WHERE condition
Dim iPos As String = InStr(txtSQL.Value, "WHERE", CompareMethod.Text)
If iPos > 0 Then
Dim tmpSQL As String
tmpSQL = Mid(txtSQL.Value, 1, iPos - 1) ' string before WHERE condition
tmpSQL = tmpSQL & strWhereCondition ' Add where condition
tmpSQL = tmpSQL & Mid(txtSQL.Value, iPos + 5) ' string after WHERE condition
txtSQL.Value = tmpSQL ' final sql
End If
End If
Else
Response.Write("No such View ID found in table: " & m_HelpID)
Response.End()
End If
Ds = Nothing
End If
End Sub
Protected Sub FilterButton_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles FilterButton.Click
'Call LoadData(0)
Call LoadDataEx()
End Sub
Private Sub SaveViewSettings(ByVal KeyName As String, ByVal KeyValue As String)
Dim strSQL As String
Dim SQLArray(1) As String, SQLCount As Integer = 0
If Not Grid1.SortColumn Is Nothing Then
strSQL = "SELECT Count(*) AS result FROM sy_listview_user_header "
strSQL = strSQL & " WHERE user_id = '" & Session("user_id") & "' AND view_id = '" & m_HelpID & "'"
If m_objDB.IsExists(strSQL) = True Then
' Update the values
strSQL = "UPDATE sy_listview_user_header SET "
strSQL = strSQL & " sort_column = '" & Grid1.SortColumn.DataField & "', "
strSQL = strSQL & " sort_order = '" & IIf(Grid1.SortColumn.SortOrder = EO.Web.SortOrder.Ascending, "ASC", "DESC") & "', "
strSQL = strSQL & " user_id = '" & Session("user_id") & "'" ' dummy column
strSQL = strSQL & " WHERE user_id = '" & Session("user_id") & "' AND view_id = '" & m_HelpID & "'"
Else
' create customization for user
strSQL = "INSERT INTO sy_listview_user_header (user_id, view_id, sort_column, sort_order) "
strSQL = strSQL & " VALUES ('" & Session("user_id") & "','" & m_HelpID & "','"
strSQL = strSQL & Grid1.SortColumn.DataField & "', '" & IIf(Grid1.SortColumn.SortOrder = EO.Web.SortOrder.Ascending, "ASC", "DESC") & "')"
End If
ReDim Preserve SQLArray(SQLCount)
SQLArray(SQLCount) = strSQL
SQLCount = SQLCount + 1
End If
' ----------------------------------------
' Save grid column width to table
' ----------------------------------------
Dim i As Integer
For i = 0 To Grid1.Columns.Count - 1
If Grid1.Columns(i).Name <> "" Then
strSQL = "UPDATE SY_LISTVIEW_USER_COLUMNS SET "
strSQL = strSQL & " display_order = '" & i.ToString & "', "
strSQL = strSQL & " column_width = '" & Grid1.Columns(i).Width & "' "
strSQL = strSQL & " WHERE "
strSQL = strSQL & " user_id = '" & Session("user_id") & "' AND "
strSQL = strSQL & " column_id = '" & Grid1.Columns(i).Name & "' AND "
strSQL = strSQL & " view_id = '" & m_HelpID & "' "
ReDim Preserve SQLArray(SQLCount)
SQLArray(SQLCount) = strSQL
SQLCount = SQLCount + 1
End If
Next
If SQLCount > 0 Then
Call m_objDB.executeSQLArray(SQLArray)
End If
End Sub
Private Sub LoadViewSettings()
Try
Dim strSQL As String
strSQL = "SELECT * FROM sy_listview_user_header "
strSQL = strSQL & " WHERE user_id = '" & Session("user_id") & "' AND view_id = '" & m_HelpID & "'"
Dim Ds As DataSet
Ds = m_objDB.GetQueryResult(strSQL)
If Not Ds Is Nothing Then
If Ds.Tables("tblResult").Rows.Count > 0 Then
' set the sort options
txtSortExpression.Value = Ds.Tables("tblResult").Rows(0).Item("sort_column") & ""
txtSortOrder.Value = Ds.Tables("tblResult").Rows(0).Item("sort_order") & ""
End If
Ds = Nothing
End If
Catch ex As Exception
LblError.Text = ex.Message
End Try
End Sub
' Populate the grid
Private Sub LoadDataEx()
' grid settings
Dim intGridPageSize As Integer = m_objDB.GetSysParameter("GRID", Session("user_id") & "_PAGESIZE", 30)
Dim intGridHeight As Integer = m_objDB.GetSysParameter("GRID", Session("user_id") & "_HEIGHT", 667)
Dim intPageNumber As Integer = Grid1.CurrentPage
Dim lngTopRecordStart As Long = (intPageNumber * intGridPageSize)
Dim lngMaxRecords As Long
' build sql query
Try
strSQL = " SELECT * FROM ( "
strSQL = strSQL & txtSQL.Value
strSQL = strSQL & " ) tab1 "
' add the data filter where condition
If Trim(txtContains.Text) <> "" Then
' If all column is selected
Dim LookInColumn As String, i As Integer
LookInColumn = UCase(Trim(cmbSearchIn.SelectedValue))
If LookInColumn = "" Then
' search in all the columns
For i = 1 To cmbSearchIn.Items.Count - 1
If UCase(Trim(cmbSearchIn.Items(i).Value)) <> "" Then
' avoids picture column with blank heading
LookInColumn = LookInColumn & """" & UCase(Trim(cmbSearchIn.Items(i).Value)) & """ LIKE '%" & m_objDB.fixStr(UCase(Trim(txtContains.Text))) & "%' OR "
End If
Next
' remove last OR
LookInColumn = Mid(LookInColumn, 1, Len(LookInColumn) - 4)
Else
LookInColumn = """" & UCase(Trim(cmbSearchIn.SelectedValue)) & """ LIKE '%" & m_objDB.fixStr(UCase(Trim(txtContains.Text))) & "%' "
End If
strSQL = strSQL & " WHERE " & LookInColumn
End If
If Not Grid1.SortColumn Is Nothing Then
strSQL = strSQL & "ORDER BY """ & Grid1.SortColumn.DataField & """" & " " & IIf(Grid1.SortColumn.SortOrder = EO.Web.SortOrder.Ascending, "ASC", "DESC")
Else
If Trim(txtSortExpression.Value) <> "" Then
strSQL = strSQL & "ORDER BY """ & Trim(txtSortExpression.Value) & """" & " " & txtSortOrder.Value
'Grid1.SortColumn.DataField = Trim(txtSortExpression.Value)
'Grid1.SortColumn.SortOrder = IIf(txtSortOrder.Value = "ASC", EO.Web.SortOrder.Ascending, EO.Web.SortOrder.Descending)
End If
End If
' read the full dataset
Dim j As Integer
Dim Ds As DataSet = m_objDB.GetQueryResult(strSQL)
lngMaxRecords = Ds.Tables("tblResult").Rows.Count
' if the total records are less than the current page number,
' reset to first page
If (lngMaxRecords / intGridPageSize) < intPageNumber Then
Grid1.CurrentPage = 0
lngTopRecordStart = 0
End If
' transfer the required records to the new dataset (to attach to grid)
Dim Ds1 As DataSet = Ds.Clone
If lngMaxRecords > 0 Then
For j = 0 To intGridPageSize
Ds1.Tables("tblResult").ImportRow(Ds.Tables("tblResult").Rows(lngTopRecordStart))
lngTopRecordStart += 1
If lngTopRecordStart >= lngMaxRecords Then
Exit For
End If
Next
End If
' ---------------------------------------------------------------------------------------------------------
' Setting the navigations
' ---------------------------------------------------------------------------------------------------------
Grid1.Visible = False
'Grid1.Items.Clear not working
If Ds Is Nothing Then
lblSearchresults.Text = "<b>Communication failure: </b> " & ErrMessage
Else
If Ds.Tables("tblResult").Rows.Count <= 0 Then
lblSearchresults.Text = "<b>No data found.</b>"
Else
lblSearchresults.Text = ""
With Grid1
.Visible = True
.PageSize = intGridPageSize
.Height = intGridHeight
.RecordCount = lngMaxRecords
.AllowColumnReorder = True
.AllowPaging = True
.AutoGenerateColumns = True
.EnableKeyboardNavigation = True
.FullRowMode = True
.RunningMode = EO.Web.RunningMode.Callback
.SelectedItemIndex = 0
.DataSource = Ds1
.DataBind()
End With
End If
End If
'lblLookin.Visible = Grid1.Visible
'cmbSearchIn.Visible = Grid1.Visible
'txtContains.Visible = Grid1.Visible
'FilterButton.Visible = Grid1.Visible
'Ds1.Dispose()
Ds.Dispose()
Catch ex As Exception
LblError.Text = ex.Message
End Try
Exit Sub
' -------- end of sql query building ----------
End Sub
Protected Sub Grid1_ColumnSort(ByVal sender As Object, ByVal e As EO.Web.GridColumnEventArgs) Handles Grid1.ColumnSort
Call LoadDataEx()
End Sub 'LoadGridData
Protected Sub Grid1_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grid1.PageIndexChanged
Call LoadDataEx()
End Sub
Protected Sub SaveLayoutButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SaveLayoutButton.Click
Call SaveViewSettings("", "")
End Sub
End Class
please help me
Saji