Solved

DefaulView Sort returns no records for a parameterized query

Posted on 2004-08-30
10
404 Views
Last Modified: 2012-05-05
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
Comment
Question by:jfitz7777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 33

Expert Comment

by:raterus
ID: 11935349
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
 

Author Comment

by:jfitz7777
ID: 11936492
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:AerosSaga
ID: 11937793
does dsResults1 contain any data?
0
 

Author Comment

by:jfitz7777
ID: 11937817
The Datagrid populates with the expected data; clicking on the header (sort) makes it disappear.
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11937822
please post your sort command, and are you setting the sort expression for the columns?
0
 

Author Comment

by:jfitz7777
ID: 11937860
"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
 
LVL 17

Accepted Solution

by:
AerosSaga earned 250 total points
ID: 11938286
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11938289
just be sure your sort expression for each column is the actual column name of the field in the database
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question