Visual Basic

Re: GridView appears to have no data when trying to sort it

27-Jul-11 1:12

Hello,

I am currently trying to sort on a GridView after it has been populated with records from a SQL Server Express database.

I click on a column to sort it and I get the following error:

"DataTable must be set prior to using DataView."

The entirety of my code is as follows:

<script runat="server" >          
        
        Protected 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
                        
        '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 ProcedureName'" & 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()
                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()
                
        'This is to clear the list of items out of the GridView box.
              
            End If
        End Sub
                           
        
        


        Protected Sub gvSorting_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
            Dim dtSortView As New DataTable
            dtSortView = GridView2.DataSource
            
            If dtSortView Is Nothing Then
                Dim dvSortedView As New DataView(dtSortView)

                dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
                
                GridView2.DataSource = dvSortedView
                GridView2.DataBind()
            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"
                'Return newSortDirection
            End If

            Return newSortDirection

        End Function
        
        
        </script>

If anybody can give me any hints and tips on why this is happening, I would be extremely grateful.

From my understanding its telling me that there is no records in the GridView to sort, but why?

Thanks in advance,

Dan

Shameel27-Jul-11 2:24

Shameel

27-Jul-11 2:24

The problem is, you set the DataSource to a DataReader and trying to cast it back to a DataTable. Use a DataTable as the DataSource for the GridView and your code should work.

DataAdapter / Datatable

David Mujica27-Jul-11 2:49

27-Jul-11 2:49

Instead of using, sqlReader = sqlCmd.ExecuteReader()

Use this:
Dim DA As SQLDataAdapter

DA = New SqlDataAdapter(SQLcmd)
DA.Fill(DT)

Gridview2.Datasource = DT

When you use a dataadapter to "Fill" a datatable, it will fetch all the rows into the datatable, so you must be careful that you are not fetching thousands of rows of data, your memory and performance will suffer tremendously.

With the above changes, your grid should sort when you click on a column heading.

david

dannyboi8627-Jul-11 3:29

27-Jul-11 3:29

Hello David,

Thank you very much.

Quick question though - what is DT?

Is that DataTable?

Thanks,

Dan

David Mujica27-Jul-11 10:49

27-Jul-11 10:49

Oops. I forgot to declare the datatable.

<br />
Dim DA As SQLDataAdapter<br />
Dim DT As New DataTable   ' I forgot this line before<br />
 <br />
DA = New SqlDataAdapter(SQLcmd)<br />
DA.Fill(DT)<br />
 <br />
Gridview2.Datasource = DT<br />
 <br />

Give me a vote of 5 if this works for you. Smile | :)

dannyboi8627-Jul-11 22:29

27-Jul-11 22:29

Hello David,

I have made the changes you suggested, but am still getting the same error:

Here is the code now:

<script runat="server" >          
        
        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 ProcedureName'" & 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()
                    
                    sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
                GridView2.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 gvSorting_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
            
            Dim dt = TryCast(Session("TaskTable"), DataTable)
            Dim dtSortView As New DataTable
            dtSortView = GridView2.DataSource
            
            If dtSortView Is Nothing Then
                Dim dvSortedView As New DataView(dtSortView)

                dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
                
                GridView2.DataSource = dvSortedView
                GridView2.DataBind()
            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"
                'Return newSortDirection
            End If

            Return newSortDirection

        End Function
        
        
        </script>

Any ideas at all?

Is there code still in there that needs to be taken out?

Or have I missed a bit of code?

Thanks for your help,

Dan

Shameel28-Jul-11 3:16

Shameel

28-Jul-11 3:16

Declare the DataTable at the class level and use it wherever required.

David Mujica28-Jul-11 3:20

28-Jul-11 3:20

Here is some code I just tested this morning and when I click on the column headings the grid does the sorting.

<br />
Dim SQLconn As SqlConnection<br />
Dim DA As New SqlDataAdapter<br />
Dim DT As New DataTable<br />
<br />
  SQLconn = New SqlConnection(GetConnString())<br />
<br />
  DA.SelectCommand = New SqlCommand("EXEC z_select_test", SQLconn)<br />
<br />
  DA.Fill(DT)<br />
<br />
  Me.DataGridView1.DataSource = DT<br />

You may want to un-hook your gvSorting_Sorting and getSortDirectionString routines because they are not necessary.

give it a shot.

BTW: I'm based on the East Coast of the United States, that's why it takes so long for me to respond to you.

dannyboi8628-Jul-11 4:59

28-Jul-11 4:59

Hello David,

I have done what you have advised, and am now getting a different error:

"ExecuteReader: CommandText property has not been initialized"

The code is:

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=KenyaKCSE;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.
                sqlAdapter.SelectCommand = New SqlCommand("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()
                    
                    sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
 
                
                'GridView2.DataSource = sqlDataTable
                GridView2.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

Is there anything else that I am doing wrong?

I really appreciate all of your help.

Regards,

Dan

David Mujica28-Jul-11 9:59

28-Jul-11 9:59

Couple of things I would try.

1) Comment out: sqlConn.Open
2) Comment out: sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
3) Comment out: sqlCmd = sqlConn.CreateCommand
4) Add the sqlConn as shown below:

sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "'",sqlConn)

5) Comment out: sqlAdapter = New SqlDataAdapter(sqlCmd)

Then I believe your code will match my example more closely.

Remember to Un-Hook those other routines like I mentioned in an earlier post.

david

dannyboi8628-Jul-11 22:34

28-Jul-11 22:34

Hello David,

I did what you suggested, and now when I click on a column, I get the following error page:

"The GridView 'GridView2' fired event Sorting which wasn't handled."

My code (after being edited):

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.
                sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "' ", sqlConn)
                
                '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()
                    
                'sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
 
                
                'GridView2.DataSource = sqlDataTable
                GridView2.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

I create my GridView as thus:

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

I just cant understand what could be going wrong??

I really appreciate your help but do you know anything else that could poss be wrong?

Thanks,

Dan

David Mujica29-Jul-11 3:12