0

Hello,

I have created a site in VB.Net and ASP.Net where a user types in a centre code and clicks on 2 buttons to retreive 2 GridView's of records from an SQL Server backend database.

The records appear sucessfully, but when I go to sort the records in ascending/descending order, nothing happens.

I have been trying this for a week now, but with no success.

The code I have is the following:

<script runat="server" >  
        
        Public Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=ServerName;Initial Catalog=DBName;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlCmd.CommandText = "exec ProcName'" & TextBox2.Text & "' "
                
                'This will read the rows in the database.
                sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                    GridView1.DataSource = sqlReader
                    GridView1.DataBind()
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                End If
                
                'This is closing the connection to the database once we have finished with it.
                sqlConn.Close()
                
              
            End If
        End Sub
        
        Public Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=ServerName;Initial Catalog=DBName;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlCmd.CommandText = "exec ProcName'" & TextBox2.Text & "' "
                
                'This will read the rows in the database.
                sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                    GridView2.DataSource = sqlReader
                    GridView2.DataBind()
                    
                    'GridView2.DataSource = sqlDataTable
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                End If
                
                'This is closing the connection to the database once we have finished with it.
                sqlConn.Close()
                
              
            End If
        End Sub
                           
        
        


        Public Sub gvSorting_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
               
            Dim dtSortView As New DataTable
            dtSortView = GridView2.DataSource
                          
            If Not dtSortView Is Nothing Then
                Dim dvSortedView As New DataView(dtSortView)
                
                dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
                               
                GridView2.DataSource = dvSortedView
                GridView2.DataBind()
            Else
                MsgBox("No data to select")
            End If
            
        End Sub
                   
        Public Function getSortDirectionString(ByVal SortDirection As SortDirection) As String
            
            Dim newSortDirection As String = String.Empty
                  
            If (SortDirection = SortDirection.Ascending) Then
                newSortDirection = "ASC"
            Else
                newSortDirection = "DESC"
            End If
            
            Return newSortDirection
            
        End Function
        
        
        </script>

My GridView declaration is the following:

<asp:GridView ID="GridView2" runat="server" Height="143px" AllowSorting="true" OnSorting="gvSorting_Sorting"

If anyone can advise on what I am doing wrong and where...it would be much appreciated.

Many thanks,

Dan

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by rciprogrammer
0

Can't you simply sort the data before you add it? Or do you want to be able to sort and resort different fields?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.