Solved

DefaulView Sort returns no records for a parameterized query

Posted on 2004-08-30
10
387 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
  • 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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11935576
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
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11937793
does dsResults1 contain any data?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now