This is a migrated thread and some comments may be shown as answers.

User-based Sort Order

5 Answers 65 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Rick asked on 13 Aug 2012, 01:59 PM

I am using MS Visual Studio 2010 in a 3.5 environment.
I am using Microsoft Vista (32 bit)
The database is currently located on an SQL 2008 server.
I am using RadGrid & tools, version 2012.2.724.35
I am using Internet Explorer (9.0.8112.16421)
I am programming in VB.net

I think this will be an easy one to solve. I want to set the sort order of a grid based on who is logged in. Basically if it's person A, then sort by X, Y. If it's anyone else, sort by Z.

I have a radGrid with 1 nested detail table.

I had tried to put this on the Page_load using an exit sub if it's a postback, but as you may have guessed, this has many side effects:

Go to another page produces a blank page (no grid at all because it's empty.)
Trying to edit a row in the top table makes the grid disappear.

If I remove the postback exit sub, editing a record just causes an error.

So I think I'm just not setting the data source in the right place or I'm just doing it wrong.

My Page Load code

Private Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

If Page.IsPostBack Then Exit Sub

Dim sSQLText As String = "SELECT Currency_CountryCode, Currency_CountryDesc, CurrencyTarget_CountryCode, Currency_LastUpdated, Currency_DisplayOrder FROM Currency_LatestConversions_TBL LEFT OUTER JOIN Currency_CurrentlyTracked ON Currency_LatestConversions_TBL.Currency_CountryCode = Currency_CurrentlyTracked.CurrencyTarget_CountryCode "

If Page.User.Identity.Name.ToString.ToUpper = "RSCOTT" Then

sSQLText &= "ORDER BY Existing, Currency_LatestConversions_TBL.Currency_CountryDesc"

Else

sSQLText &= "ORDER BY Currency_DisplayOrder"

End If

SqlDataSource1.SelectCommand = sSQLText

RadGrid1.DataBind()

End Sub

I think you'll know how to solve this without my HTML, but I'll give it to you anyway.

My HTML

<body>

<form id="form1" runat="server">

<div>

<asp:Label ID="Label1" runat="server" Text="Don Beaty's Foreign Currency Tracker" Font-Bold="False" Font-Names="Calibri" Font-Size="XX-Large"></asp:Label>

<telerik:RadScriptManager ID="RadScriptManager1" runat="server"></telerik:RadScriptManager>

<telerik:RadGrid ID="RadGrid1" runat="server" CellSpacing="0"

DataSourceID="SqlDataSource1"

GridLines="None"

AllowAutomaticInserts="True"

AllowAutomaticUpdates="True"

AllowAutomaticDeletes="True"

BorderColor="Gray"

BorderStyle="Inset" Skin="Office2010Silver" >

<MasterTableView AutoGenerateColumns="False" DataSourceID="SqlDataSource1"

commanditemdisplay="None"

allowpaging="True"

EditMode="InPlace"

PageSize="30"

Width="100%"

Name="Currency"

showgroupfooter="True" BorderColor="#E0E0E0" BorderStyle="Solid"

BorderWidth="1px" GridLines="Vertical" DataKeyNames="Currency_CountryCode, Currency_BriefcaseUsed, Currency_HomeOfficeUsed, Currency_OneHundUsed, Currency_LastUpdated">

<Columns>

<telerik:GridTemplateColumn

FilterControlAltText="Filter EditCommandColumn column"

Groupable="False"

UniqueName="EditCommandColumn">

<ItemTemplate>

<asp:LinkButton ID="lbuEditP" runat="server" CausesValidation="false" CommandName="Edit" Text="Edit"></asp:LinkButton>

</ItemTemplate>

<EditItemTemplate>

<asp:LinkButton ID="lbuUpdateP" runat="server" CommandName="Update" Text="Update"></asp:LinkButton>

<asp:LinkButton ID="lbuCancelUpdateP" runat="server" CausesValidation="false" CommandName="Cancel" Text="Cancel"></asp:LinkButton>

</EditItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridBoundColumn DataField="Currency_CountryDesc"

FilterControlAltText="Filter Currency_CountryDesc column"

HeaderText="Currency" SortExpression="Currency_CountryDesc"

UniqueName="Currency_CountryDesc"

ReadOnly="True">

<HeaderStyle VerticalAlign="Bottom"></HeaderStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ExchangeRate"

DataType="System.Decimal"

FilterControlAltText="Filter Currency_Conversion column"

HeaderText="Exchange Rate" SortExpression="ExchangeRate"

ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Center"

UniqueName="ExchangeRate"

ReadOnly="True">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Right"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridTemplateColumn HeaderText="Flag Briefcase (With DLB)" FilterControlAltText="Filter Currency_BriefcaseUsed column"

UniqueName="Currency_BriefcaseUsed">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Center"></ItemStyle>

<EditItemTemplate>

<asp:CheckBox ID="CheckBoxA" runat="server" Checked='<%# Bind("Currency_BriefcaseUsed") %>' />

</EditItemTemplate>

<ItemTemplate>

<asp:CheckBox ID="CheckBoxB" runat="server" Checked='<%# Bind("Currency_BriefcaseUsed") %>' OnCheckedChanged="UpdateBriefcaseFlag" autopostback="true"/>

</ItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn HeaderText="Flag Home (With DLB)" FilterControlAltText="Filter Currency_HomeOfficeUsed column"

UniqueName="Currency_HomeOfficeUsed">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Center"></ItemStyle>

<EditItemTemplate>

<asp:CheckBox ID="CheckBoxC" runat="server" Checked='<%# Bind("Currency_HomeOfficeUsed") %>' />

</EditItemTemplate>

<ItemTemplate>

<asp:CheckBox ID="CheckBoxD" runat="server" Checked='<%# Bind("Currency_HomeOfficeUsed") %>' OnCheckedChanged="UpdateHomeOfficeFlag" autopostback="true"/>

</ItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn HeaderText="Flag 100 (With DLB)" FilterControlAltText="Filter Currency_OneHundUsed column"

UniqueName="Currency_OneHundUsed">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Center"></ItemStyle>

<EditItemTemplate>

<asp:CheckBox ID="CheckBoxE" runat="server" Checked='<%# Bind("Currency_OneHundUsed") %>' />

</EditItemTemplate>

<ItemTemplate>

<asp:CheckBox ID="CheckBoxF" runat="server" Checked='<%# Bind("Currency_OneHundUsed") %>' OnCheckedChanged="UpdateOneHundFlag" autopostback="true"/>

</ItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridBoundColumn DataField="Currency_EnvelopeCount500"

DataType="System.Int16"

FilterControlAltText="Filter Currency_EnvelopeCount500 column"

HeaderText="500 Env. Count Needed (With Accounting)"

SortExpression="Currency_EnvelopeCount500"

UniqueName="Currency_EnvelopeCount500">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Center" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Currency_EnvelopeCount100"

DataType="System.Int16"

FilterControlAltText="Filter Currency_EnvelopeCount100 column"

HeaderText="100 Env. Count Needed (With Accounting)"

SortExpression="Currency_EnvelopeCount100"

UniqueName="Currency_EnvelopeCount100">

<HeaderStyle HorizontalAlign="Center" CssClass="maximize"></HeaderStyle>

<ItemStyle HorizontalAlign="Center" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Currency_EnvelopeCount500_Ready"

DataType="System.Int16"

FilterControlAltText="Filter Currency_EnvelopeCount500_Ready column"

HeaderText="500 Env. Count On Hand (With Accounting)"

SortExpression="Currency_EnvelopeCount500_Ready"

UniqueName="Currency_EnvelopeCount500_Ready">

<HeaderStyle HorizontalAlign="Center" VerticalAlign="Bottom"></HeaderStyle>

<ItemStyle HorizontalAlign="Center" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Currency_EnvelopeCount100_Ready"

DataType="System.Int16"

FilterControlAltText="Filter Currency_EnvelopeCount100_Ready column"

HeaderText="100 Env. Count On Hand (With Accounting)"

SortExpression="Currency_EnvelopeCount100_Ready"

UniqueName="Currency_EnvelopeCount100_Ready">

<HeaderStyle HorizontalAlign="Center" CssClass="maximize"></HeaderStyle>

<ItemStyle HorizontalAlign="Center" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Currency_DisplayOrder"

DataType="System.Int16"

FilterControlAltText="Filter Currency_DisplayOrder column"

HeaderText="Display Order"

SortExpression="Currency_DisplayOrder"

UniqueName="Currency_DisplayOrder">

<HeaderStyle HorizontalAlign="Center" CssClass="maximize"></HeaderStyle>

<ItemStyle HorizontalAlign="Center" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

</Columns>

<DetailTables>

<telerik:GridTableView runat="server" DataKeyNames="Currency_ID"

EditMode="InPlace" CommandItemDisplay="Bottom" Width="100%"

DataSourceID="SqlDataSource2" AutoGenerateColumns="False"

ShowGroupFooter="True" >

<ParentTableRelation>

<telerik:GridRelationFields DetailKeyField="Currency_CountryCode" MasterKeyField="Currency_CountryCode" />

</ParentTableRelation>

<CommandItemSettings ExportToPdfText="Export to PDF" />

<RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column"></RowIndicatorColumn>

<ExpandCollapseColumn Visible="True" FilterControlAltText="Filter ExpandColumn column"></ExpandCollapseColumn>

<Columns>

<telerik:GridEditCommandColumn FilterControlAltText="Filter EditCommandColumn column" InsertText="Save">

<ItemStyle HorizontalAlign="Right" BorderColor="Black"></ItemStyle>

</telerik:GridEditCommandColumn>

<telerik:GridTemplateColumn DataField="CurrencyTarget_ForeignDenomination"

DataType="System.Int32"

FilterControlAltText="Filter CurrencyTarget_ForeignDenomination column"

HeaderText="Foreign Currency Denomination"

SortExpression="CurrencyTarget_ForeignDenomination"

UniqueName="CurrencyTarget_ForeignDenomination">

<ItemStyle HorizontalAlign="Right" BorderColor="Black"></ItemStyle>

<InsertItemTemplate>

<asp:TextBox ID="CurrencyTarget_ForeignDenomination" runat="server"

Text='<%# Bind("CurrencyTarget_ForeignDenomination") %>'></asp:TextBox>

</InsertItemTemplate>

<ItemTemplate>

<asp:Label ID="CurrencyTarget_ForeignDenominationLabel" runat="server"

Text='<%# Eval("CurrencyTarget_ForeignDenomination") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:Label ID="CurrencyTarget_ForeignDenominationLabel2" runat="server"

Text='<%# Eval("CurrencyTarget_ForeignDenomination") %>'></asp:Label>

</EditItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridBoundColumn DataField="USDEquivalent"

HeaderText="US Dollar Value" SortExpression="USDEquivalent" UniqueName="USDEquivalent" FilterControlAltText="Filter USDEquivalent column"

DataType="System.Decimal" DataFormatString="${0:#}"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Right" BorderColor="Black"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn

DataField="CurrencyTarget_FCNeeded"

HeaderText="Enter Quantity to be Included for 1 '500'<br

/>Envelope" SortExpression="CurrencyTarget_FCNeeded" UniqueName="CurrencyTarget_FCNeeded" FilterControlAltText="Filter CurrencyTarget_FCNeeded column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum">

<ItemStyle HorizontalAlign="Center" BackColor="#CCFFCC" BorderColor="Green" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="USDValue"

HeaderText="US Dollar Value" SortExpression="USDValue" UniqueName="USDValue" FilterControlAltText="Filter USDValue column"

DataType="System.Decimal" DataFormatString="${0:###,##0.00}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Right" BackColor="#CCFFCC" BorderColor="Green"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalFor500Envelopes"

HeaderText="Total For '500' Envelopes<br

/>On Hand" SortExpression="TotalFor500Envelopes" UniqueName="TotalFor500Envelopes" FilterControlAltText="Filter TotalFor500Envelopes column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#CCFFCC" BorderColor="Green"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="CurrencyTarget_FCNeeded_100"

HeaderText="Enter Quantity to be Included for 1 '100'<br

/>Envelope" SortExpression="CurrencyTarget_FCNeeded_100" UniqueName="CurrencyTarget_FCNeeded_100" FilterControlAltText="Filter CurrencyTarget_FCNeeded_100 column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum">

<ItemStyle HorizontalAlign="Center" BackColor="#CCCCFF" BorderColor="#6666FF" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="USDValue_100"

HeaderText="US Dollar Value" SortExpression="USDValue_100" UniqueName="USDValue_100" FilterControlAltText="Filter USDValue_100 column"

DataType="System.Decimal" DataFormatString="${0:###,##0.00}" Aggregate="Sum"

ReadOnly="True">

<ItemStyle HorizontalAlign="Right" BackColor="#CCCCFF" BorderColor="#6666FF"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalFor100Envelopes"

HeaderText="Total For '100' Envelopes<br

/>On Hand" SortExpression="TotalFor100Envelopes" UniqueName="TotalFor100Envelopes" FilterControlAltText="Filter TotalFor100Envelopes column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#CCCCFF" BorderColor="#6666FF"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalForAllEnvelopes"

HeaderText="Total All Envelopes<br

/>On Hand" SortExpression="TotalForAllEnvelopes" UniqueName="TotalForAllEnvelopes" FilterControlAltText="Filter TotalForAllEnvelopes column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#FFCCCC" BorderColor="#FF6666"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="CurrencyTarget_OutsideEnvelope"

HeaderText="Enter Total Outside<br

/>the Envelope" SortExpression="CurrencyTarget_OutsideEnvelope" UniqueName="CurrencyTarget_OutsideEnvelope" FilterControlAltText="Filter CurrencyTarget_OutsideEnvelope column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum">

<ItemStyle HorizontalAlign="Center" BackColor="#FFCCCC" BorderColor="#FF6666" CssClass="maximize"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="AccountingTotalOnHand"

HeaderText="Total Quantity On Hand<br

/>In Accounting" SortExpression="AccountingTotalOnHand" UniqueName="AccountingTotalOnHand" FilterControlAltText="Filter AccountingTotalOnHand column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#FFCCCC" BorderColor="#FF6666"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalAllEnvelopesNeeded"

HeaderText="Total All Envelopes<br

/>Needed" SortExpression="TotalAllEnvelopesNeeded" UniqueName="TotalAllEnvelopesNeeded" FilterControlAltText="Filter TotalAllEnvelopesNeeded column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#FFCCCC" BorderColor="#FF6666"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="QuantityToOrder"

HeaderText="Quantity To<br

/>Order" SortExpression="QuantityToOrder" UniqueName="QuantityToOrder" FilterControlAltText="Filter QuantityToOrder column"

DataType="System.Int32" DataFormatString="{0:#}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Center" BackColor="#FFCCCC" BorderColor="#FF6666" ForeColor="Crimson" Font-Bold="true"></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalFCValue"

HeaderText="Total Foreign Currency Value" SortExpression="TotalFCValue" UniqueName="TotalFCValue" FilterControlAltText="Filter TotalFCValue column"

DataType="System.Int32" DataFormatString="{0:###,##0}" Aggregate="Sum"

ReadOnly="True" >

<ItemStyle HorizontalAlign="Right" BackColor="#FFCCCC" BorderColor="#FF6666" ></ItemStyle>

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="TotalUSDValue"

HeaderText="Total USD Value" SortExpression="TotalUSDValue" UniqueName="TotalUSDValue" FilterControlAltText="Filter TotalUSDValue column"

DataType="System.Decimal" DataFormatString="${0:###,##0.00}" Aggregate="Sum"

ReadOnly="True">

<ItemStyle HorizontalAlign="Right" BackColor="#FFCCCC" BorderColor="#FF6666"></ItemStyle>

</telerik:GridBoundColumn>

</Columns>

<GroupByExpressions>

<telerik:GridGroupByExpression>

<SelectFields>

<telerik:GridGroupByField FieldAlias="CountryCode" FieldName="Currency_CountryCode" HeaderText="Country Code" />

</SelectFields>

<GroupByFields>

<telerik:GridGroupByField FieldAlias="Currency_CountryCode" FieldName="Currency_CountryCode" />

</GroupByFields>

</telerik:GridGroupByExpression>

</GroupByExpressions>

<FooterStyle Font-Bold="true" />

<EditFormSettings>

<EditColumn FilterControlAltText="Filter EditCommandColumn column">

</EditColumn>

</EditFormSettings>

</telerik:GridTableView>

</DetailTables>

<CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

<RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column"></RowIndicatorColumn>

<ExpandCollapseColumn Visible="True" FilterControlAltText="Filter ExpandColumn column"></ExpandCollapseColumn>

<EditFormSettings>

<EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>

</EditFormSettings>

<PagerStyle AlwaysVisible="True" />

<HeaderStyle Font-Bold="True" />

</MasterTableView>

<FilterMenu EnableImageSprites="False"></FilterMenu>

</telerik:RadGrid>

</div>

<br />

<telerik:RadButton ID="btnReport" runat="server" style="top: 0px; left: 0px"

Text="Summary Report">

</telerik:RadButton>

<telerik:RadEditor ID="radPDFReport" Runat="server" Height="114px"

Width="685px" ExportSettings-FileName="ForeignCurrencyTotals"

Visible="False" Font-Names="Calibri">

<ExportSettings OpenInNewWindow="True">

<Pdf PageHeight="8.5in" PageWidth="11in" AllowAdd="True" AllowCopy="True"

AllowModify="True" Title="Foreign Curency Totals" Author="Accounting"

Creator="Accounting" PageBottomMargin="45px" PageFooterMargin="18px"

PageHeaderMargin="18px" PageLeftMargin="18px" PageRightMargin="18px"

PageTopMargin="37px" />

</ExportSettings>

<Content>CONTENT</Content>

<TrackChangesSettings CanAcceptTrackChanges="False"></TrackChangesSettings>

</telerik:RadEditor>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:DonPlannerCS %>"

UpdateCommand="UPDATE [Currency_LatestConversions_TBL]

SET [Currency_EnvelopeCount500] = @Currency_EnvelopeCount500, [Currency_EnvelopeCount100] = @Currency_EnvelopeCount100, [Currency_EnvelopeCount500_Ready] = @Currency_EnvelopeCount500_Ready, [Currency_EnvelopeCount100_Ready] = @Currency_EnvelopeCount100_Ready, [Currency_BriefcaseUsed] = @Currency_BriefcaseUsed, [Currency_HomeOfficeUsed] = @Currency_HomeOfficeUsed, [Currency_OneHundUsed] = @Currency_OneHundUsed, Currency_DisplayOrder = @Currency_DisplayOrder, Currency_LastEdited = GetDate()

WHERE [Currency_CountryCode]=@Currency_CountryCode">

<UpdateParameters>

<asp:Parameter Name="Currency_CountryCode" />

</UpdateParameters>

</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

ConnectionString="<%$ ConnectionStrings:DonPlannerCS %>"

SelectCommand="SELECT Currency_Grid.*

FROM Currency_Grid

WHERE([Currency_CountryCode]=@Currency_CountryCode)

ORDER BY CurrencyTarget_ForeignDenomination"

UpdateCommand="UPDATE [Currency_TargetAmounts_TBL]

SET [CurrencyTarget_FCNeeded] = @CurrencyTarget_FCNeeded, [CurrencyTarget_FCNeeded_100] = @CurrencyTarget_FCNeeded_100, [CurrencyTarget_OutsideEnvelope] = @CurrencyTarget_OutsideEnvelope, [CurrencyTarget_DateUpdated] = GetDate()

WHERE [Currency_ID] = @Currency_ID"

InsertCommand="INSERT INTO Currency_TargetAmounts_TBL(Currency_Location, CurrencyTarget_CountryCode, CurrencyTarget_ForeignDenomination, CurrencyTarget_FCNeeded, CurrencyTarget_FCNeeded_100, CurrencyTarget_OutsideEnvelope)

SELECT 'Accounting' AS Expr1, @Currency_CountryCode AS Expr2, @CurrencyTarget_ForeignDenomination AS Expr3, @CurrencyTarget_FCNeeded AS Expr4, @CurrencyTarget_FCNeeded_100 AS Expr5, @CurrencyTarget_OutsideEnvelope AS Expr6" >

<SelectParameters>

<asp:SessionParameter DefaultValue="XXX" Name="Currency_CountryCode" SessionField="@Currency_CountryCode" Type="String" />

</SelectParameters>

<UpdateParameters>

<asp:Parameter Name="CurrencyTarget_FCNeeded" />

<asp:Parameter Name="CurrencyTarget_FCNeeded_100" />

<asp:Parameter Name="CurrencyTarget_OutsideEnvelope" />

<asp:Parameter Name="CurrencyTarget_DateUpdated" />

<asp:Parameter Name="Currency_ID" />

</UpdateParameters>

<InsertParameters>

<asp:Parameter Type="Int32" Name="CurrencyTarget_ForeignDenomination" />

<asp:Parameter Type="Int32" Name="CurrencyTarget_FCNeeded" />

<asp:Parameter Type="Int32" Name="CurrencyTarget_FCNeeded_100" />

<asp:Parameter Type="Int32" Name="CurrencyTarget_OutsideEnvelope" />

<asp:Parameter Type="DateTime" Name="CurrencyTarget_DateCreated" />

</InsertParameters>

</asp:SqlDataSource>

</form>

</body>

5 Answers, 1 is accepted

answered on 14 Aug 2012, 01:46 PM

Thanks. I got it working, but there are side-effects to using the example on the page you gave me.

I originally had an SQLDataSource control that had an Update Command and Update Parameters. I don't imagine that I would have to set those in the NeedDataSource each time. I would think I could still use a control for the datasource, but I can't figure that out.

Help...

In the NeedDataSource, I have this:

Private Sub RadGrid1_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

Dim sSQLText As String = "SELECT Currency_CountryCode, Currency_CountryDesc, Currency_EnvelopeCount500, Currency_EnvelopeCount100, Currency_EnvelopeCount500_Ready, Currency_EnvelopeCount100_Ready, 1 / Currency_Conversion AS ExchangeRate, Currency_LastUpdated, Currency_BriefcaseUsed, Currency_HomeOfficeUsed, Currency_OneHundUsed, CASE LEN(CurrencyTarget_CountryCode) WHEN 3 THEN 0 ELSE 1 END AS Existing, CurrencyTarget_CountryCode, Currency_LastUpdated, Currency_DisplayOrder FROM Currency_LatestConversions_TBL LEFT OUTER JOIN Currency_CurrentlyTracked ON Currency_LatestConversions_TBL.Currency_CountryCode = Currency_CurrentlyTracked.CurrencyTarget_CountryCode "

If Page.User.Identity.Name.ToString.ToUpper = "DBEATY" Then

sSQLText &= "ORDER BY Existing, Currency_LatestConversions_TBL.Currency_CountryDesc"

Else

sSQLText &= "ORDER BY Currency_DisplayOrder"

End If

Dim ConnString As String = ConfigurationManager.ConnectionStrings("DonPlannerCS").ConnectionString

Dim conn As SqlConnection = New SqlConnection(ConnString)

Dim adapter As SqlDataAdapter = New SqlDataAdapter

adapter.SelectCommand = New SqlCommand(sSQLText, conn)

Dim myDataTable As New DataTable

conn.Open()

Try

adapter.Fill(myDataTable)

Finally

conn.Close()

End Try

RadGrid1.DataSource = myDataTable

End Sub

answered on 14 Aug 2012, 02:40 PM

I got a little further. I added the data source back with its parameters:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:DonPlannerCS %>"

UpdateCommand="UPDATE [Currency_LatestConversions_TBL]

SET [Currency_EnvelopeCount500] = @Currency_EnvelopeCount500, [Currency_EnvelopeCount100] = @Currency_EnvelopeCount100, [Currency_EnvelopeCount500_Ready] = @Currency_EnvelopeCount500_Ready, [Currency_EnvelopeCount100_Ready] = @Currency_EnvelopeCount100_Ready, [Currency_BriefcaseUsed] = @Currency_BriefcaseUsed, [Currency_HomeOfficeUsed] = @Currency_HomeOfficeUsed, [Currency_OneHundUsed] = @Currency_OneHundUsed, Currency_DisplayOrder = @Currency_DisplayOrder, Currency_LastEdited = GetDate()

WHERE [Currency_CountryCode]=@Currency_CountryCode">

<UpdateParameters>

<asp:Parameter Name="Currency_CountryCode" />

</UpdateParameters>

</asp:SqlDataSource>

And then I changed the NeedDataSource code:

Private Sub RadGrid1_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

Dim sSQLText As String = "SELECT Currency_CountryCode, Currency_CountryDesc, Currency_EnvelopeCount500, Currency_EnvelopeCount100, Currency_EnvelopeCount500_Ready, Currency_EnvelopeCount100_Ready, 1 / Currency_Conversion AS ExchangeRate, Currency_LastUpdated, Currency_BriefcaseUsed, Currency_HomeOfficeUsed, Currency_OneHundUsed, CASE LEN(CurrencyTarget_CountryCode) WHEN 3 THEN 0 ELSE 1 END AS Existing, CurrencyTarget_CountryCode, Currency_LastUpdated, Currency_DisplayOrder FROM Currency_LatestConversions_TBL LEFT OUTER JOIN Currency_CurrentlyTracked ON Currency_LatestConversions_TBL.Currency_CountryCode = Currency_CurrentlyTracked.CurrencyTarget_CountryCode "

If Page.User.Identity.Name.ToString.ToUpper = "DBEATY" Then

sSQLText &= "ORDER BY Existing, Currency_LatestConversions_TBL.Currency_CountryDesc"

Else

sSQLText &= "ORDER BY Currency_DisplayOrder"

End If

Dim ConnString As String = ConfigurationManager.ConnectionStrings("DonPlannerCS").ConnectionString

Dim conn As SqlConnection = New SqlConnection(ConnString)

SqlDataSource1.SelectCommand = sSQLText

RadGrid1.DataSource = SqlDataSource1

RadGrid1.MasterTableView.DataSource = SqlDataSource1

End Sub

It fails when I try to update a record where this worked before trying to set the datasource programmatically. The record stays in Edit mode and any values I changed revert back to what they were.

answered on 15 Aug 2012, 02:40 PM

Okay, so, it seems I can just set the sort in the page Load this way:

Dim expression As GridSortExpression = New GridSortExpression()

If Page.User.Identity.Name.ToString.ToUpper <> "DBEATY" Then

expression.FieldName = "Existing"

expression.SortOrder = GridSortOrder.Ascending

expression.FieldName = "Currency_CountryDesc"

expression.SortOrder = GridSortOrder.Ascending

Else

expression.FieldName = "Currency_DisplayOrder"

expression.SortOrder = GridSortOrder.Ascending

End If

RadGrid1.MasterTableView.SortExpressions.AddSortExpression(expression)

RadGrid1.MasterTableView.Rebind()

The only issue is I can't do a multi-column sort the way I've written it in the IF TRUE section. Any ideas on how to do that?

answered on 15 Aug 2012, 03:09 PM

Okay, it was as simple as enabling multi-column sorting, and this code:

Private Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

If Page.IsPostBack = True Then Exit Sub

If Page.User.Identity.Name.ToString.ToUpper <> "DBEATY" Then

Dim expression As New GridSortExpression

expression.FieldName = "Existing"

expression.SortOrder = GridSortOrder.Ascending

RadGrid1.MasterTableView.SortExpressions.AddSortExpression(expression)

RadGrid1.MasterTableView.Rebind()

Dim expression2 As New GridSortExpression

expression2.FieldName = "Currency_CountryDesc"

expression2.SortOrder = GridSortOrder.Ascending

RadGrid1.MasterTableView.SortExpressions.AddSortExpression(expression2)

RadGrid1.MasterTableView.Rebind()

Else

Dim expression As New GridSortExpression

expression.FieldName = "Currency_DisplayOrder"

expression.SortOrder = GridSortOrder.Ascending

RadGrid1.MasterTableView.SortExpressions.AddSortExpression(expression)

RadGrid1.MasterTableView.Rebind()

End If

End Sub