hi,

i'm having trouble with the sort order from the TableAdapter class in that the DataSet method invokes a stored procedure on an SQL EXPRESS table which returns sorted data.  i'm using the following code in my business object to return a collection:

 

TeamsDataSetTableAdapters.TeamsTableAdapter teamsTableAdapter = new TeamsDataSetTableAdapters.TeamsTableAdapter();

TeamsDataSet.TeamsDataTable teamsDataTable = new TeamsDataSet.TeamsDataTable();

teamsTableAdapter.FillAllByTeamName(teamsDataTable);

return teamsDataTable;

 

in the page load routine i'm using the following code to populate the dropdownlists:

System.Data.

DataTable dt = Team.AllTeams();

DataRow[] dr = dt.Select();

for (int i = 0; i < dr.Length; i++)

{

   String text = (String)dr[i]["TeamName"];

   int value = (int)dr[i]["TeamID"];

   for (int j = 0; j < listBoxList.Count; j++)

   {

      listBoxList[j].Items.Add(

new ListItem(text, value.ToString()));

   }

}

 

the problem is that the listbox is populated however the sort order has been lost.  to get the sort order back i need to use the command:

dt.Select(null, "TeamName");

why can't i get the sort order from the stored procedure?

 

thanks in advance

Hello Del my friend,

I remember having this problem.  The trick is to never rely on the order of the records within a DataTable.  Use a DataView against the DataTable and then bind to the DataView.  I have enclosed an example web page.  The other benefit of doing it this way is if 2 or more web pages need the same data but ordered differenlty, they can use the same stored procedure and the DataView in the page will handle the custom ordering. 

Kind regards

Scotty

 

Imports System.Data.SqlClient
Imports System.Data

Partial Class PageInitTest
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        If Not IsPostBack Then

            Dim conn As New SqlConnection("myconnectionstring")
            Dim command As New SqlCommand("mySQL", conn)
            Dim da As New SqlDataAdapter(command)
            Dim tblData As New DataTable
            da.Fill(tblData)

            ' now the sorting
            Dim dv As New DataView(tblData)

            ' you can also do filtering to not show all of them
            'dv.RowFilter = "MyField = 1"

            dv.Sort = "MyField ASC"    ' ASC or DESC

            ' then bind the control to the DataView, not the DataTable
            dgdMyData.DataSource = dv
            dgdMyData.DataBind()

            conn.Close()

        End If

    End Sub

End Class

 

 

thanks for the workaround,

i can't help but thinking there's still something missing... the DataSet allows you to set up the ordered data but doesn't let you use it?

Hi,
I think this should work.
Have you tested the dataset is filled datas that are already sorted? Is the stored procedure working properly?

Hi,

I created the stored procedure via the DataSet wizard however originally there was no sort order, an oversight on my part.  I amended the stored procedured to correct this and it tested okay via Execute now button.  I also can test this via the DataSet Execute method; it it too returns a sorted list.  It's when I use the table adapter that the order is lost, actually the list comes in ID order.

I'm thinking of just removing the sort order on the stored procedure and sorting the data via the DataTable.Select() however I doubt this will be as efficient as the SQL feature.

I may also try creating a new DataSet with the sort order in from the start in-case there some glitch with the wizard.

If you know of any other reason why the sort order may be lost then let me know.

Thanks for looking at the post.

Del

I think there might be something wrong with your code, the same thing works fine with me. Here are my codes related:

Products.GetProductsDataTable pdt = new Products.GetProductsDataTable();
ProductsTableAdapters.GetProductsTableAdapter ad = new ProductsTableAdapters.GetProductsTableAdapter();
ad.Fill(pdt);
GridView1.AutoGenerateColumns = true;
GridView1.DataSource = pdt;
GridView1.DataBind();


[System.Diagnostics.DebuggerNonUserCodeAttribute()]
private void InitCommandCollection() {
this._commandCollection = new System.Data.SqlClient.SqlCommand[1];
this._commandCollection[0] = new System.Data.SqlClient.SqlCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "dbo.GetProducts";
this._commandCollection[0].CommandType = System.Data.CommandType.StoredProcedure;
this._commandCollection[0].Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));
}




ALTER PROCEDURE GetProducts
AS
/* SET NOCOUNT ON */
select
*
from
products
order by productname
RETURN


Please make sure the select command has been configured properly.

Hi Raymond,

I've narrowed the issue down to this line:

DataRow[] dr = dt.Select();

Whilst debugging this I noticed that, using the DataSet Visualizer, the the data is indeed sorted.  However once the dr object has been created dr is unsorted.  To get the data sorted I must specify the sort order in one of the Select method overrides.

I've managed to avoid using the DataRow collection with the following code (where dt is the DataTable returned from the TableAdapter):

for (int i = 0; i < dt.Rows.Count; i++)

{

String text = (String)dt.Rows[i]["TeamName"];

int value = (int)dt.Rows[i]["TeamID"];

for (int j = 0; j < listBoxList.Count; j++)

{

listBoxList[j].Items.Add(

new ListItem(text, value.ToString()));

}

}

Do you know if this is an efficient way to work?  I'm relatively new to .NET and don't want to pick up bad habits early on!

Regards,

Del

The Select Method returns the current rows in order of primary key (or lacking one, order of addition.).
If you want it be orderd in a field other than primary key, use this one:
Select Method(string, string), the second argument indicates ordey by which field explicitly.
For more info, pls refer to MSDN:
DataTable.Select Method