Last post May 10, 2012 10:23 PM by Decker Dong - MSFT
May 09, 2012 10:34 AM|MartinBCN|LINK
I have a scalar valued function in SQL that I need to include in the select clause.
While select dbo.function ('pass parameter to function') works fine in SQL, the same expression in the Select command is throwing an error. I tried some work arounds, thinking that perhaps a from clause is required, but nothing worked.
<asp:SqlDataSource runat="server" id="QueryMover" ProviderName="System.Data.SqlClient"
ConnectionString="Data Source=ESSBP1S9502\WIKASPAIN;Initial Catalog=IT;User ID=xxxx;Password=xxxx"
SelectCommand="select TOP 1 *, dbo.MyFunction (@GetParameter) AS Result from anyDatabase_in_this_catalog">
<asp:controlparameter ControlID="SetParameter" PropertyName="Text" Name="GetParameter" Type="String" />
May 09, 2012 10:40 AM|adamturner34|LINK
It's most likely a string parsing error. Set a breakpoint on the executereader or fill method and look at the t-sql. It's most likley obfuscated.
Why can't you use a stored procedure?
May 09, 2012 11:59 AM|MartinBCN|LINK
Thank you Adam for your answer,
I now turned it into a Stored procedure but still have difficulties and can't see how the correct syntax is.
exec QueryMover 'input_string'
declare @input varchar(max)
set @input='bla bla bla'
exec QueryMover @input
... does the job in SQL... so far so good.
so how do I transfer this into asp:SqlDataSource???
SelectCommand="QueryMover @GetInput AS Output" SelectCommandType="StoredProcedure">
<asp:controlparameter ControlID="SetInput" PropertyName="Text" Name="GetInput" Type="String" /
<asp:textbox runat="server" id="SetInput" AutoPostBack="true">
<asp:Label Text='<%# Bind("Output") %>' runat="server" id="Label" />
This is what a certain analogy to the default SelectCommand makes me think should work... but off course it doesn't!
Can't even think of how to set this up, if you don't use a parameter put parse just literal text. And how do I then assign the returned result to a parameter I can use in the repeater or form view?
May 10, 2012 10:23 PM|decker dong - msft|LINK
In my mind，I think it's hard to use SqlDataSource with your situation。I think you'd better use SqlDataAdapter+Fill method to fill all the datacontents into a DataTable and then do dataBind directly manually at code behind。
using(SqlDataAdapter adapter = new SqlDataAdapter("Your Stored procdure here","……"))
DataTable dt = new DataTable();
GridView1.DataSource = dt;