Gridview sorting - No datasource, parameters passed from main page, dynamic sql

Hi All,

 This is not a question but I had to share something I was finally able to figure out and thought it may be helpful to others as well. With the help from Ryan Olshan's website I was able to take what he had and make it work for my needs. The difficulty I had was that I need to dynamically build my SQL statement based on the users selections from the main page. Also because it's Oracle and I don't have access to create stored procedures. Hope this helpful and I signed up because I felt the need to give back to those who helped me via countless Google searches. Note, I commented out the paging code because it is not necessary for my project.




<asp:GridView ID="gvResults" runat="server"

                      EmptyDataText="No data exists for this query."







                      OnSorting = "gvResults_Sorting">


<asp:BoundField HeaderText="Sender" DataField="SENDING_BIC8" SortExpression="SENDING_BIC8">

                    <HeaderStyle Wrap="False" />

                    <ItemStyle Wrap="False" />


<asp:BoundField HeaderText="Receiver" DataField="RECEIVING_BIC8" SortExpression="RECEIVING_BIC8">

                    <HeaderStyle Wrap="False" />

                    <ItemStyle Wrap="False" />






            <RowStyle BorderStyle="Solid" />

            <EmptyDataRowStyle HorizontalAlign="Center" />






Imports System.Data

Imports System.Data.Odbc


Partial Class results

    Inherits System.Web.UI.Page


    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load

        If Not Page.IsPostBack Then

            Dim vd1 = Page.Request.Form("txtVDFrom").ToString()

            If vd1 = "" Then

                Dim dtDate As Date = Today.Date()

                Dim strMonth As String = dtDate.Month.ToString()

                Dim strDay As String = dtDate.Day.ToString()

                Dim strYear As String = dtDate.Year.ToString()


                If Len(strMonth) < 2 Then

                    strMonth = "0" & strMonth

                End If

                If Len(strDay) < 2 Then

                    strDay = "0" & strDay

                End If


                Dim strInDate As String = strMonth & "/" & strDay & "/" & strYear

                vd1 = strInDate

            End If

            Dim vd2 = Page.Request.Form("txtVDTo").ToString()

            Dim amt1 = Page.Request.Form("txtAmt1").ToString()

            Dim amt2 = Page.Request.Form("txtAmt2").ToString()

            Dim rbic = Page.Request.Form("txtRcv").ToString()

            Dim ccy = Page.Request.Form("cmbCCY").ToString()

            Dim mtype = Page.Request.Form("cmbMT").ToString()

            Dim str72 = Page.Request.Form("txt72").ToString()

           'Store fields in Session variables so we don't lose them on sorting

            Session.Add("vd1", vd1)

            Session.Add("vd2", vd2)

            Session.Add("amt1", amt1)

            Session.Add("amt2", amt2)

            Session.Add("rbic", rbic)

            Session.Add("ccy", ccy)

            Session.Add("mtype", mtype)

            Session.Add("str72", str72)

        End If

BindData(Session("vd1"), Session("vd2"), Session("rbic"), Session("ccy"), Session("mtype"), Session("str72"), Session("amt1"), Session("amt2"))


    End Sub


    Sub BindData(ByVal vd1 As String, ByVal vd2 As String, ByVal rbic As String, _

                    ByVal ccy As String, ByVal mtype As String, ByVal str72 As String, _

                        ByVal amt1 As String, ByVal amt2 As String)


        Dim strWhere As String = ""

        Dim strSelect As String = "Select SENDING_BIC8, RECEIVING_BIC8, RECEIVING_BRANCH_CODE,

        NVL(AMT, 0) AMT, UPD_DT, UNID, REPLACE(Fld72, 'NULL', '') Fld72 "

        Dim strFrom As String = "From TABLE_NAME "

        If vd2 <> "" Then

            strWhere = " Where VALUE_DT Between TO_DATE('" & vd1 & " 00:00:00', 'mm/dd/yyyy hh24:mi:ss') And TO_DATE('" & vd2 & " 23:59:59', 'mm/dd/yyyy hh24:mi:ss')"


            strWhere = " Where VALUE_DT Between TO_DATE('" & vd1 & " 00:00:00', 'mm/dd/yyyy hh24:mi:ss') And TO_DATE('" & vd1 & " 23:59:59', 'mm/dd/yyyy hh24:mi:ss')"

        End If

        If rbic <> "" Then

            strWhere = strWhere & " And Fld20 = '" & rbic & "'"

        End If

        If ccy <> "" Then

            strWhere = strWhere & " And CCY = '" & ccy & "'"

        End If

        If mtype <> "" Then

            strWhere = strWhere & " And MESSAGE_TYPE = '" & mtype & "'"

        End If

        If str72 <> "" Then

            strWhere = strWhere & " And Fld72 LIKE ('%" & str72 & "%')"

        End If

        If amt1 <> "" And amt2 = "" Then

            strWhere = strWhere & " And AMT = " & CDbl(amt1)

        End If

        If amt1 <> "" And amt2 <> "" Then

            strWhere = strWhere & " And AMT Between " & CDbl(amt1) & " And " & CDbl(amt2)

        End If

        Dim strOrder As String = "Order By UPD_DT DESC"


        Dim sql As String = strSelect & strFrom & strWhere & strOrder


Dim oCN As New  OdbcConnection(ConfigurationManager.ConnectionStrings("NAME").ConnectionString)



        Dim oDA As New OdbcDataAdapter(sql, oCN)

        Dim oDS As New DataTable




        gvResults.DataSource = oDS






    End Sub












Thank you Ryan.


    Private Property GridViewSortDirection() As String




            Return IIf(ViewState("SortDirection") = Nothing, "ASC", ViewState("SortDirection"))


        End Get


        Set(ByVal value As String)


            ViewState("SortDirection") = value


        End Set


    End Property




    Private Property GridViewSortBLOCKED EXPRESSION As String




            Return IIf(ViewState("SortBLOCKED EXPRESSION = Nothing, String.Empty, ViewState("SortBLOCKED EXPRESSION)


        End Get


        Set(ByVal value As String)


            ViewState("SortBLOCKED EXPRESSION = value


        End Set


    End Property




    Private Function GetSortDirection() As String


        Select Case GridViewSortDirection


            Case "ASC"


                GridViewSortDirection = "DESC"




            Case "DESC"


                GridViewSortDirection = "ASC"


        End Select




        Return GridViewSortDirection


    End Function




    Protected Sub gvresults_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)


        gvResults.DataSource = SortDataTable(gvResults.DataSource, True)


        gvResults.PageIndex = e.NewPageIndex




    End Sub




    Protected Function SortDataTable(ByVal dataTable As DataTable, ByVal isPageIndexChanging As Boolean) As DataView


        If Not dataTable Is Nothing Then


            Dim dataView As New DataView(dataTable)


            If GridViewSortExpression <> String.Empty Then


                If isPageIndexChanging Then


                    dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection)




                    dataView.Sort = String.Format("{0} {1}", GridViewSortExpression, GetSortDirection())


                End If


            End If


            Return dataView




            Return New DataView()


        End If


    End Function




    Protected Sub gvresults_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)


        GridViewSortExpression = e.SortExpression


        'Dim pageIndex As Integer = gvResults.PageIndex


        gvResults.DataSource = SortDataTable(gvResults.DataSource, False)




        'gvResults.PageIndex = pageIndex


    End Sub


End Class

Thank you and, by all means, if you see something that I could be doing better, please let me know. I am very new to .NET so any advice is always appreciated.

Kind regards,

6/13/2008 7:12:42 PM 72751 articles. 3 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 46


   Exactly what I was looking for! Any possible way to clean up the post to make it more copy friendly?



6/13/2008 7:23:28 PM

I realized as soon as I posted it was garbled. It should look better now.

Thank you,

6/13/2008 7:28:12 PM

Much better thanks ;)


6/13/2008 7:30:12 PM

I forgot to add this code in case you want to hide the post back information from the status bar.

Protected Sub SetWebUICtrlStsBarMsg(ByVal Message As String, ByVal mSrcControl As Control)

Dim SrcControl As WebControls.WebControl = mSrcControl

Dim mMouseDownOver As String = "self.status='" + Message + "';return true;"

Dim mMouseOutUp As String = "self.status='';return true;"

SrcControl.Attributes.Add("onmouseout", mMouseOutUp)

SrcControl.Attributes.Add("onmouseup", mMouseOutUp)

SrcControl.Attributes.Add("onMouseover", mMouseDownOver)

SrcControl.Attributes.Add("onMousedown", mMouseDownOver)

SrcControl.Attributes.Add("onClick", mMouseDownOver)

End Sub

Protected Sub gvResults_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvResults.RowDataBound

If e.Row.RowType = DataControlRowType.Header Then

SetWebUICtrlStsBarMsg("Order By Sender", e.Row.Controls(0).Controls(0))

SetWebUICtrlStsBarMsg("Order By Branch", e.Row.Controls(1).Controls(0))

SetWebUICtrlStsBarMsg("Order By Receiver", e.Row.Controls(2).Controls(0))

SetWebUICtrlStsBarMsg("Order By Branch", e.Row.Controls(3).Controls(0))

SetWebUICtrlStsBarMsg("Order By Type", e.Row.Controls(4).Controls(0))

SetWebUICtrlStsBarMsg("Order By Value Date", e.Row.Controls(5).Controls(0))

SetWebUICtrlStsBarMsg("Order By ISO CCY", e.Row.Controls(6).Controls(0))

SetWebUICtrlStsBarMsg("Order By Amount", e.Row.Controls(7).Controls(0))

SetWebUICtrlStsBarMsg("Order By Field 20", e.Row.Controls(8).Controls(0))

SetWebUICtrlStsBarMsg("Order By Field 72", e.Row.Controls(9).Controls(0))

End If

End Sub

6/13/2008 8:13:40 PM

Similar Artilces:

Trying to use the FAQ " Sorting and paging in the GridView control when not using data source controls "Sorting and paging in the GridView control when not using..."
and I am not getting the results I had hoped for.  I keep getting a "Compiler Error Message: CS0123: No overload for 'AdhocJobGridView_PageIndexChanged' matches delegate 'System.EventHandler'".   Here is my event handling... protected void AdhocJobGridView_PageIndexChanged(object sender, GridViewPageEventArgs e){ try { AdhocJobGridView.DataSource = wsBaseInfoArray; AdhocJobGridView.PageIndex = e.NewPageIndex; AdhocJobGridView.DataBind(); }catch (Exception ex) {throw ex; }   Here is my aspx... <asp:GridView ID="AdhocJobGridView...

How to pass a value for QueryStringParameter from GridView on one page to SQL datasource on another page
Hi,I am trying to pass a value for the QueryStringParameter EmployeeID from a GridView on one page to a SQL datasource on another. I have set up the GridView's hyperlink field as follows:<asp:HyperLinkFieldText="Contact me"DataTextFormatString="{0}"DataNavigateUrlFields="EmployeeID"DataNavigateUrlFormatString="~/people/contactme.aspx?EmployeeID={0}"Target="_blank"/>and when I run the page and click the hyperlink, it takes me to the correct URL (e.g. when the EmployeeID is 106, http://myserver/mysite/people/contactme.aspx...

code for Custom paging without using sqldatasource control and sql datasource control for gridview...........
 code for Custom paging without using sqldatasource control and sql datasource control for gridview...........i need code for how to show all pagenumbers in gridview with custom paging in gridview..............It is very urgent................................. T.Ashok Kumar  suppose that we have a table like thisCountryId  identity intCountryTitle    nvarchar(50)you can use this sql command for paging the dataUSE [CyberOffice]GO/****** Object:  StoredProcedure [dbo].[CountryGetListPaged]    Script Date: 09/17/2008 14:01:06 ******/SET A...

Sorting and paging in GridView without a DataSource Control
Hi, With very close reference to, I tried implementing the code in my own VB.NET app, but I can't seem to get it to work. I downloaded the VB.NET version from the same blog:  This is the error I'm getting: Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataTable'. The error hits on this l...

Sorting and paging nested gridview without datasource control
Hi all! I have created a nested gridview like so... with a nested gridview <asp:GridView ID="gvParent" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="2px" CellPadding="3" CellSpacing="2" CssClass="style4" ForeColor="Black" AllowPaging="True" AllowSorting="True" PageSize="12" Width="750px">                 ...

How to change the SQL used for the GridView, when using a control parameter in the datasource
I'm using a GridVeiw with and Access database. I want to allow users to select a date and then update the GridView to show only data entered after that date. So my question is, how do I modify the SQL used by the datagrid? I'm using a control parameter as part of my AccessDataSource and I think that is what I'm having trouble figuring out how to include. Here is my datasource, with the control parameter in the the GridView. The GridView works fine with these initial settings settings. <asp:AccessDataSource ID="AccessDataSourceForwView" runat="server" ConflictDetection="Compa...

Dynamic menu control using sql database and passing parameters
I want to do the following using menu control in Asp .net 2 1. Want a  menu control in my master page which has to be bound using sql database dynamically. 2. After binding the data once an user clicks on an item in the menu, the click event handler should capture the values of the menu item and allow me to manipulate further. Any help!      In order to do so First Create a User Control and put the menu designing and Binding logic in there, and then put that User Control inside your Master page.A fundamental rule in technology says what...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Best way to pass control parameter between page and control
I have a web user control that serves as a nav bar. I call it on all my pages. Like: <UserControl5:navBar ID="navBar1" runat="server" /> In the nav bar there is an image button: <asp:ImageButton ID="btnNavCorAct" runat="server" ImageUrl = "img/NTSNavBar_r1_c13.jpg" ToolTip = "Corrective Actions"/>     On my the parent page I would like to set btnNavCorAct.Enabled= False btnNavCorAct.ImageUrl = "img/oNTSNavBar_r1_c13.jpg" How can I do this? Thanks!!! Cheers!- Aron Are you using 2.0 with the single file method because you should be abl...

Passing SQL Parameter Array to Object datasource as parameter
I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to this.  Raj Kiran S------------Chennai, India CALLINGBELLS: I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to t...

Sorting gridview in ajax tab causes page reload?
Clicking the gridview to sort a particular column causes the page to reload, the page then loads at the default tab. Can the gridview be set so that the sort happens without the page reloading? Software Coding Is No Monkey Business! Try setting this to true: EnableSortingAndPagingCallbacksThanks,MaxLet Me Google That For You!  I had to deal with something like this in the last 2 days.  If you need to keep the tab from going back to the default, look at the 2nd to last post in this thread:  That solved my problem when I had to re-...

Loading a dynamic user control on main page from current user control
Hi,  Please help,  I've posted this a week ago and never got a response.  I'm still stuck on the same problem.I have a panel that I load user Control in no problem.  The problemarrises when I do a post back on one of these user controls.  I havebutton it does a click event.  In this click event I will do somedatabase updates, then I will use reflection to call a method in theparent ( to load the new user control.  Its at this pointafter it loads the new user control that it throws the error "Thecontrol must be placed inside a form tag...

Passing Parameters from a GridView Control
I’ve been playing around with the GridView and I trying to wire up a dropdown list when you’re editing. This works great until I want to pass a parameter into the SqlDataSource (that does the select for the dropdown list): I can’t seem to be able to pass the parameter value which results in the query returning no rows and an error message stating that “'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value”. The following is a repro using the pubs database: <%@ page language="C#" %> <script runat=...

how to pass dynamic parameter to control?
I have a user control to get button label for user specific language, <%@ Register TagPrefix="test" TagName="Label" Src="label.ascx" %> and i want to pass the label to a button like following, how can i do? I know the following won't work?it is just to show what i want to do. <asp:Button id="Submit" Text="<%=Label.GetLabel("Submit")%>" runat="server"/> appreciate if any advice or suggestion. do the assignment from your codebehind or <script> block Submit.Text = Label.GetLabel(&q...

Web resources about - Gridview sorting - No datasource, parameters passed from main page, dynamic sql -

Datasource - Wikipedia, the free encyclopedia
A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property ...

Inverness Graham Acquires DataSource
Inverness Graham , a lower middle market private equity firm headquartered in suburban Philadelphia, has acquired DataSource , a print supply ...

SmartGlance for iPad for iPad on the iTunes App Store
Read reviews, get customer ratings, see screenshots, and learn more about SmartGlance for iPad on the App Store. Download SmartGlance for iPad ...

Chaitanya Pandit (@chaitanyapandit) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Data source - Wikipedia, the free encyclopedia
... Data source A data source is any of the following types of sources for (mostly) digitized data: a database in the Java software platform, datasource ...

2ndQuadrant - PostgreSQL expertise from specialists with a source code level understanding of RDBMS ...
PostgreSQL expertise from specialists with a source code level understanding of RDBMS PostgreSQL Planets Gabriele’s PlanetPostgreSQL Gianni’s ...

Tagged entries for CLOUD COMPUTING
Alan Williamson's output as a Java Champion, Blog-City Architect, BlueDragon Creator, Author, Speaker and Internet Guru

Private equity deals
... of biometric identity management systems, applications and services. Inverness Graham Investment has acquired DataSource ...

C# C Sharp and Tutorials on C#
Learn the c# langauge to build web applications using our online tutorials with live demos. Participate in our forums and learn from others. ...

JavaScript UI Library, Ajax Components & HTML5 Framework - DHTMLX
DHTMLX offers a rich JavaScript library, UI components & HTML5 mobile framework. Build impressive web apps for both desktop and mobile devices. ...

Resources last updated: 1/15/2016 2:19:46 PM