Dataset problems

I am trying to set up sorting for my DataGrid. The grid is filled from a DW
Dataset. Here is the Dataset definition at the top of my file.

************************************************** *******************
<MMataSet
ID="DataSet1"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%#
System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING
_Contour_Server") %>'
DatabaseType='<%#
System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABA
SETYPE_Contour_Server") %>'
CommandText='<%# "SELECT tableName,startTime,executionTime = replicate(" &
"" + Chr(39) + "&nbsp;" + Chr(39) + "" & ",7) +
convert(char(10),Round((executionTime /
60),2)),numRecordsCopied,numRecordsInError FROM VDW..xferstats" %>'
Debug="true"

/MMataSet
MM:PageBind runat="server" PostBackBind="true" /

************************************************** ************************

In my DataGrid I have it setup to do sorting and is calling the routine
fine:

************************************************** ********************
<script runat="server">

Dim saveCommandText as String

Sub SortDataGrid (sender as Object, e as DataGridSortCommandEventArgs)
If saveCommandText = "" then
saveCommandText = DataSet1.CommandText
End if
DataSet1.CommandText = saveCommandtext & " order by " & e.SortExpression
end Sub
</script>
************************************************** *********************

As you can see, when SortDataGrid is called - saveCommandText is set, if not
already set so I have the original CommandText to use each time I do a sort.

I then append the "order by" to the original CommandText (which does seem to
work). But when I look at Sql Server, I find that the original CommandText
of the DataSet has been set back to the original.

The problem is that I can't use my own select because each time I press the
title, it goes to my SortDataGrid procedure and then the DataSet gets
re-executed (but with the original CommandText).

How do I handle this situation?

Thanks,

Tom.

I did a little more debugging and added a Page_Load and Page_PreRender
proceedure to see what it was doing and I got this result. This is
resulting page after the OnSortCommand.

************************************************** **************************
**************
In Page_Load DataSet1.CommandText =

In SortDataGrid DataSet1.CommandText = SELECT
tableName,startTime,executionTime = replicate(' ',7) +
convert(char(10),Round((executionTime /
60),2)),numRecordsCopied,numRecordsInError FROM VDW..xferstats

End of SortDataGrid DataSet1.CommandText = SELECT
tableName,startTime,executionTime = replicate(' ',7) +
convert(char(10),Round((executionTime /
60),2)),numRecordsCopied,numRecordsInError FROM VDW..xferstats order by
tableName

In Page_PreRender DataSet1.CommandText = SELECT
tableName,startTime,executionTime = replicate(' ',7) +
convert(char(10),Round((executionTime /
60),2)),numRecordsCopied,numRecordsInError FROM VDW..xferstats order by
tableName

************************************************** **************************
**************

Here is the pages grid and script sections:

************************************************** **************************
****************
<script runat="server">

Dim saveCommandText as String

Sub Page_Load(sender as Object, e as EventArgs)

response.Write("In Page_Load DataSet1.CommandText = " &
DataSet1.CommandText & "<p>")

if not IsPostBack then
end if
End Sub

Sub Page_PreRender(sender as Object, e as EventArgs)

response.Write("In Page_PreRender DataSet1.CommandText = " &
DataSet1.CommandText & "<p>")

if not IsPostBack then
end if
End Sub

Sub SortDataGrid (sender as Object, e as
DataGridSortCommandEventArgs)

response.Write("In SortDataGrid DataSet1.CommandText = " &
DataSet1.CommandText & "<p>")
DataSet1.CommandText = DataSet1.CommandText & " order by " &
e.SortExpression
response.Write("Emd pf SortDataGrid DataSet1.CommandText = " &
DataSet1.CommandText & "<p>")
end Sub
</script>
<br><div align="center">
<p class="HeaderText">&nbsp;Vantage Conversion Status</p>

<form runat="server">
<aspataGrid AllowPaging="false"
AllowSorting="True"
AutoGenerateColumns="false"
CellPadding="3"
CellSpacing="0"
DataSource="<%# DataSet1.DefaultView %>" ID="DataGrid1"
runat="server"
ShowFooter="false"
ShowHeader="true"
OnSortCommand="SortDataGrid"

HeaderStyle HorizontalAlign="center" BackColor="#E8EBFD"

ForeColor="#3D3DB6" Font-Name="Verdana, Arial, Helvetica, sans-serif"
Font-Bold="true" Font-Size="smaller" />
<ItemStyle BackColor="#F2F2F2" Font-Name="Verdana, Arial,
Helvetica, sans-serif" Font-Size="smaller" />
<AlternatingItemStyle BackColor="#E5E5E5"
Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Size="smaller" />
<FooterStyle HorizontalAlign="center" BackColor="#E8EBFD"
ForeColor="#3D3DB6" Font-Name="Verdana, Arial, Helvetica, sans-serif"
Font-Bold="true" Font-Size="smaller" />
<PagerStyle BackColor="white" Font-Name="Verdana, Arial,
Helvetica, sans-serif" Font-Size="smaller" />
<Columns>
<asp:BoundColumn DataField="tableName"
HeaderText="Table Name"
ReadOnly="true"
Visible="True"
SortExpression="tableName"/>
<asp:BoundColumn DataField="startTime"
HeaderText="Start Time"
ReadOnly="true"
Visible="True"
SortExpression="startTime"/>
<asp:BoundColumn DataField="executionTime"
HeaderText="Execution Time"
ReadOnly="true"
Visible="True"
SortExpression="executionTime"/>
<asp:BoundColumn DataField="numRecordsCopied"
HeaderText="Processed"
ReadOnly="true"
Visible="True"/>
<asp:BoundColumn DataField="numRecordsInError"
HeaderText="Errors"
ReadOnly="true"
Visible="True"/>
</Columns>
</aspataGrid>
</form>
************************************************** **************************
****************

So where is the page doing the Dataset call to the database?

This is the resulting page AFTER I push one of the columns titles to execute
the OnSortCommand.

So you can see the DataSet1.CommandText getting changed in the SortDataGrid
procedure. And it is still changed in the PreRender page.

All I can figure is either DW is doing something behind the scenes to use
the original (regardless to what you do or somehow the Sql Call is done
between the Page_Load and SortDataGrid procedure.

Is there anyway to fix this? I am stuck on a deadline and will have to
rewrite the page by taking the DW stuff out to make it work the way I want.
Normally, I would just do the Sql Call from the SortDataGrid proceedure, but
I can't do that if DW is also going to do its own Sql Call.

Thanks,

Tom.

"Thomas Scheiderich" <tfs (AT) deltanet (DOT) com> wrote

I am trying to set up sorting for my DataGrid. The grid is filled from a
DW
Dataset. Here is the Dataset definition at the top of my file.

************************************************** *******************
MMataSet
ID="DataSet1"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%#

System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_STRING
_Contour_Server") %>'
DatabaseType='<%#

System.Configuration.ConfigurationSettings.AppSett ings("MM_CONNECTION_DATABA
SETYPE_Contour_Server") %>'
CommandText='<%# "SELECT tableName,startTime,executionTime = replicate(" &
"" + Chr(39) + "&nbsp;" + Chr(39) + "" & ",7) +
convert(char(10),Round((executionTime /
60),2)),numRecordsCopied,numRecordsInError FROM VDW..xferstats" %>'
Debug="true"
/MMataSet
MM:PageBind runat="server" PostBackBind="true" /
************************************************** ************************

In my DataGrid I have it setup to do sorting and is calling the routine
fine:

************************************************** ********************
script runat="server"

Dim saveCommandText as String

Sub SortDataGrid (sender as Object, e as DataGridSortCommandEventArgs)
If saveCommandText = "" then
saveCommandText = DataSet1.CommandText
End if
DataSet1.CommandText = saveCommandtext & " order by " & e.SortExpression
end Sub
/script
************************************************** *********************

As you can see, when SortDataGrid is called - saveCommandText is set, if
not
already set so I have the original CommandText to use each time I do a
sort.

I then append the "order by" to the original CommandText (which does seem
to
work). But when I look at Sql Server, I find that the original
CommandText
of the DataSet has been set back to the original.

The problem is that I can't use my own select because each time I press
the
title, it goes to my SortDataGrid procedure and then the DataSet gets
re-executed (but with the original CommandText).

How do I handle this situation?

Thanks,

Tom.