[SOLVED] Modify text values in datagrid returned by SELECT command

Modify text values in datagrid returned by SELECT command

I would like to modify the text values after the data has been returned from the database, I need to modify the MBS1270 and the MBS1263 to look like the others when displayed in the datagrid.

SelectCommand="SELECT PartNumber as 'Part Number', OnhandStockLevel as 'In Stock', DueIn as 'In Production' FROM vw_Rb_FreeStock WHERE PartNumber = 'UFP-0390B' OR PartNumber = 'UFP-0690B' OR PartNumber = 'MBS1270' OR PartNumber = 'MBS1263'">

Now, I have used the javascript to replace the values in body load, however, this messes up the sorting of the datagrid and makes the new replaced values appear on top still, because 'M' comes before 'U', and it still sorts based on the old value.

Javascript:

 <script type="text/javascript">
    function modText(original, replace) {
        alert("Hello");
        var find = original;
        var repl = replace;
        var page = document.body.innerHTML;
        while (page.indexOf(find) >= 0) {
            var i = page.indexOf(find);
            var j = find.length;
            page = page.substr(0, i) + repl + page.substr(i + j);
            document.body.innerHTML = page;
        }
</script>

I want to replace the MBS values with values 'UFP-1690B' and 'UFP-1290B'.

Is there any way to sort based on the replaced values? Or is there another approach I could take? I tried to do something similar as to how you replace the column header using the 'AS' sql function but that failed, something like that would be perfect.

And no, I can't change the raw database value.

Gridview code:

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" 
                CellPadding="3" CssClass="style1" DataKeyNames="Part Number" 
                DataSourceID="SqlDataSource1" GridLines="Vertical">
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <Columns>
                    <asp:BoundField DataField="Part Number" HeaderText="Part Number" 
                        ReadOnly="True" SortExpression="Part Number" />
                    <asp:BoundField DataField="In Stock" DataFormatString="{0:0}" 
                        HeaderText="In Stock" SortExpression="In Stock" />
                    <asp:BoundField DataField="In Production" DataFormatString="{0:0}" 
                        HeaderText="In Production" ReadOnly="True" SortExpression="In Production" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="#DCDCDC" />
            </asp:GridView>

Solution

A month has been since this, but thought I'd add a final answer for anyone else with a similar problem.

I used the SQL REPLACE function, see: http://msdn.microsoft.com/en-us/library/ms186862.aspx

After that I combined the original data with the replaced data using UNION.

Final code:

SELECT REPLACE(REPLACE(PartNumber, 'MBS1269', 'UFP-1290S'), 'MBS1262', 'UFP-1690S') AS PartNumber... (and so on)

Output was absolutely perfect.