Solved

DefaulView Sort returns no records for a parameterized query

Posted on 2004-08-30
10
402 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
 
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Json and ajax 1 42
ASP.NET data base connection 35 91
Need help for captcha 2 40
e commerce steps shown instead of self testing 2 55
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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