• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

DefaulView Sort returns no records for a parameterized query

Hi -
I have an ASPX page with a SQLDataAdapter, Connection, and Dataset. This ASPX page sets the CommandString property with a parameter based on what is selected in a radiobuttonlist. This piece works correctly, but I have just added in this piece of code for reversible sorting and when I click on a header, the datagrid then shows 0 rows. I'm sure I've got something wrong on how I am handling the dataset.


Here is the code that populates the datagrid:
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim X As Integer, SearchString As String
        SqlDataAdapter1.SelectCommand.CommandText = "SELECT ID, SSN, LastName, FirstName, MI, Rank, AwardReq, AwardStartDate, Reason FROM Main order by AwardStartDate desc"
        If searchtext.Value <> "" Then
            If RadioButtonList1.SelectedValue = "SSN" Then
                SqlDataAdapter1.SelectCommand.CommandText = "SELECT ID, SSN, LastName, FirstName, MI, Rank, AwardReq, AwardStartDate, Reason FROM Main WHERE (SSN = @SSNSearch)"
                SqlDataAdapter1.SelectCommand.Parameters.Add("@SSNSearch", SqlDbType.BigInt)
                SqlDataAdapter1.SelectCommand.Parameters("@SSNSearch").Value = CLng(searchtext.Value)
            End If

            If RadioButtonList1.SelectedValue = "name" Then
                SearchString = searchtext.Value & "%"
                SqlDataAdapter1.SelectCommand.CommandText = "SELECT ID, SSN, LastName, FirstName, MI, Rank, AwardReq, AwardStartDate, Reason FROM Main WHERE (LastName like @name)"
                SqlDataAdapter1.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar)
                SqlDataAdapter1.SelectCommand.Parameters("@name").Value = SearchString

            End If

        End If

        SqlDataAdapter1.Fill(DsResults1)
        dgSearch.DataSource = DsResults1
        X = DsResults1.Main.Rows.Count()
        lblMatchingRecords.Text = "Matching Records: " & X

        'dgSearch.DataSource = "Select SSN, LastName, FirstName, MI, Rank, AwardReq, AwardStartDate, Reason from Main where SSN=262956992"
        dgSearch.DataBind()


Here is the sort code:______________________________________________
  Private Sub dgSearch_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgSearch.SortCommand
        Dim SortView As DataView = DsResults1.Main.DefaultView
        Dim CurrentSort As String = ""
        If Not ViewState("Sort") Is Nothing Then
            CurrentSort = ViewState("Sort")
        End If

        If CurrentSort.StartsWith(e.SortExpression) Then
            If CurrentSort.EndsWith("DESC") Then
                SortView.Sort = e.SortExpression
            Else
                SortView.Sort = e.SortExpression & " DESC"
            End If
        Else
            SortView.Sort = e.SortExpression
        End If

        ViewState("Sort") = SortView.Sort
        dgSearch.DataSource = SortView
        dgSearch.DataBind()


    End Sub
0
jfitz7777
Asked:
jfitz7777
  • 5
  • 3
1 Solution
 
raterusCommented:
I don't see anywhere where you are persisting DsResults1 in the session, or at least reloading it when the page posts back for the sortcommand.  If you don't, it'll be empty...
0
 
jfitz7777Author Commented:
I didn't include that part; sorry. Here's what I have but it still causes it to disappear:
        If IsPostBack Then
            DataBind()
        End If
I tried the VB.NET solution link listed above to store it in session but it gives me the following error (error occurs on Line 189):

Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: dataSet

Source Error:


Line 187:        End If
Line 188:
Line 189:        SqlDataAdapter1.Fill(DsResults1)
Line 190:        dgSearch.DataSource = DsResults1

 I changed the Page_Load event to reflect the following:
        If IsPostBack Then
            DsResults1 = CType(Session("dataset"), dsResults)
        End If
(Since the Button is doing the initial loading)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AerosSagaCommented:
does dsResults1 contain any data?
0
 
jfitz7777Author Commented:
The Datagrid populates with the expected data; clicking on the header (sort) makes it disappear.
0
 
AerosSagaCommented:
please post your sort command, and are you setting the sort expression for the columns?
0
 
jfitz7777Author Commented:
"Main" is the name of the SQL Server Table.

Private Sub dgSearch_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgSearch.SortCommand
        Dim SortView As DataView = DsResults1.Main.DefaultView

        Dim CurrentSort As String = ""
        If Not ViewState("Sort") Is Nothing Then
            CurrentSort = ViewState("Sort")
        End If

        If CurrentSort.StartsWith(e.SortExpression) Then
            If CurrentSort.EndsWith("DESC") Then
                SortView.Sort = e.SortExpression
            Else
                SortView.Sort = e.SortExpression & " DESC"
            End If
        Else
            SortView.Sort = e.SortExpression
        End If

        ViewState("Sort") = SortView.Sort
        dgSearch.DataSource = SortView
        dgSearch.DataBind()


    End Sub
0
 
AerosSagaCommented:
Try something like this:

 Private Sub dtg_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dtg.SortCommand
    Dim dataGrid As DataGrid = source
    Dim strSort = dataGrid.Attributes("SortExpression")
    Dim strASC = dataGrid.Attributes("SortASC")

    dataGrid.Attributes("SortExpression") = e.SortExpression
    dataGrid.Attributes("SortASC") = "Yes"

    If e.SortExpression = strSort Then
      If strASC = "Yes" Then
        dataGrid.Attributes("SortASC") = "No"
      Else
        dataGrid.Attributes("SortASC") = "Yes"
      End If
    End If

    Dim dt As DataTable = GetTableData()
    Dim dv As DataView = New DataView(dt)
    dv.Sort = dataGrid.Attributes("SortExpression")

    If dataGrid.Attributes("SortASC") = "No" Then
      dv.Sort &= " DESC"
    End If

    dataGrid.CurrentPageIndex = 0
    dataGrid.DataSource = dv
    dataGrid.DataBind()
  End Sub

Function GetTableData() As DataTable
    Dim ds As New DataSet
    Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
    Dim strSQL As String

    strSQL = "SELECT TOP 30 CompanyName, ContactName, ContactTitle, City FROM Customers ORDER BY CompanyName"
    Dim da As New SqlDataAdapter(strSQL, conn)
    da.Fill(ds, "tblCustomers")
    Return ds.Tables("tblCustomers")

  End Function

I use this all the time for paging, sorting datagrids:

http://www.dotnetjohn.com/articles.aspx?articleid=108
0
 
AerosSagaCommented:
just be sure your sort expression for each column is the actual column name of the field in the database
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now