[RESOLVED] ASP:Hyerlinkfield with Whitespace

[stephena]

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FixedLineProvisioningConnectionString %>"
        SelectCommand="SELECT trim(tblFixedLine_order_summary.bid_model) FROM tblFixedLine_order_summary WHERE (tblFixedLine_order_summary.fllog_ref = @fllog_ref)">

I'm getting 'trim' is not a recognized built-in function name.

I need to trim it as when i display the data from tblFixedLine_order_summary.bid_model in my detailsview its a hyperlink to the file and the link doesnt work becuase of trailing %20's.

Thanks in advance for your help and advice.

Steve

[gep13]

Hey,

Have you verified that the query works correctly against the database, before you try to run it through the SqlDataSource? i.e. can you put it into Enterprise Manager, or Query Analyzer to verify that it is valid?

Gary

[stephena]

The link comes out correctly in the hyperlink field except for the fact it has several %20%20%20 where the whitespace is in the Database.

I believe the whitespace is caused by the field being an nchar(20) but i do not have schema access to the database merely the ability to pull data from it.

[vbbit]

Have you tried:

1. Retrieve the data then use substring to cut the text that starts with that % sign.
2. Retrieve the data as toString().trim(), <= not sure if this could work

[brin351]

try RTRIM - right trim spaces.

SELECT Rtrim(tblFixedLine.............

[gep13]

Hey,

The if above doesn't work, then you may need to handle the population of the hyperlinkfield in the code behind, rather than directly in the aspx. That way, you will be able to have full control over the string, and can format it however you want.

Gary

[stephena]

When i use rtrim i get the error "A field or property with the name 'bid_model' was not found on the selected data source."

So it looks like i need to setup the hyperlink field in the VB code behind as gep13 suggested... any ideas where to start?

[stephena]

I chanced across the answer if anyone ever has the same difficulties i used:

LTrim(RTrim(tblFixedLine_order_summary.bid_model))AS bid_model

Thanks all for your suggestions!

[mendhak]

You could just URLDecode it instead, but did you mean that there are &#37;20s at the end of the URL?