Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DefaulView Sort returns no records for a parameterized query

Posted on 2004-08-30
10
Medium Priority
?
408 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
9 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 750 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

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…
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…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

571 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