vb sqldatasource and passing identity to query

Soldato
Joined
8 Mar 2005
Posts
3,611
Location
London, UK
I'm trying to pass User.Identity.Name to a sqldatasource where query in vb asp and as always getting in to a pickle.

On page load I have the following to set up the parameter.
Code:
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ' When your page opens, set your datasource parameter '
        ' SqlDataSource1.SelectParameters.Add("@UserName", User.Identity.Name)
        SqlDataSource1.SelectParameters.Add("@UserName", HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf("\") + 1))
        Message.Text &= "User NameNoDomain: " & HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf("\") + 1)

    End Sub
in this instance; I'm stripping out the domain as I just want to pass the userid. I'm now having a brainburp in passing that to the sqldatasource.
Code:
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:blahblah %>" SelectCommand="SELECT blah FROM blahland WHERE (userblah LIKE '%' + @UserName + '%')">
           <SelectParameters>
                <asp:Parameter DefaultValue="@UserName" Name="Username" />
        </SelectParameters>
        </asp:SqlDataSource>
I suspect the code bahind isn't feeding to SelectParamters in the way I expect but I've tried various means and way and syntax's and not getting very far!

Any pointers would be greatly appreciated!

TIA!
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
Could just be the name of the parameter? You've got 'Username' here:
Code:
<asp: Parameter DefaultValue="@UserName" Name="Username" />
and 'UserName' here:
Code:
"SELECT blah FROM blahland WHERE (userblah LIKE '%' + @UserName + '%')">
SQL is case-sensitive so might not be finding the parameter you set?
 
Soldato
OP
Joined
8 Mar 2005
Posts
3,611
Location
London, UK
I think I've sussed it; a proper brainburp!
Code:
SqlDataSource1.SelectParameters.Add("@UserName", HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf("\") + 1))
>
SqlDataSource1.SelectParameters.Add("UserName", HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf("\") + 1))
Removed the ampersand and remove the defined sqlparamter.
Code:
 <asp:Parameter DefaultValue="@UserName" Name="Username" />
Now works as expected!

Cheers!
 
Back
Top Bottom