Using a VB Function as a SqlDataSource SelectParameter value

Question

in my code behind i have a function getEmail() that returns an email address, in the front end i have a sql data source selectcommand that grabs info according to the email address. as of now the email address is hardcoded i was wondering how to put getEmail() in to the selectcommand.

VB

    Public Function getEmail() As String
     Dim x As PublicProfile
     x= UserProfileContext.Current.UserInfo
    Return x.LoginName.ToString()
   End Function

front end

   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="select Count(ID)nums from revs where Email='[email protected]' and Source='PUB' and Status='a'"></asp:SqlDataSource>

I tried putting <% =getEmail() %> instead of the email but had no luck

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="select Count(ID)nums from revs where Email='<% =getEmail() %>' and Source='PUB' and Status='a'"></asp:SqlDataSource>

Answer 1

To parameterize your query, modify your SqlDataSource markup as follows to add an "@Email" SelectParameter:

 <asp:SqlDataSource> 
      ID="SqlDataSource1" 
   runat="server" 
   ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
   SelectCommand="select Count(ID)nums from revs where Email=@EMail and  
                  Source='PUB' and Status='a'">
</asp:SqlDataSource>

In your codebehind/Page_Load(), try adding the following (before your binding) to place a value into the SelectParameter defined in the markup, which is (in turn) provided by your function:

SqlDataSource1.SelectParameters("Email").DefaultValue = getEmail()