Pass UserName to the SQL Select ? (VB)

VB ASP.NET 2.0

How do I get the Authenticated UserName passed to a Select Command of a data control ?

I'm not clear on how to get the logged in username fromUser.Identity.Name as a string or how to pass it to the Select Command of a control.

I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.

I want to show the user data from a database, based on who is logged in .

Help, greatly appreciated.

Chris

User.Identity.Name will hold the value of the currently authenticated user. How you deal with parameters depends on how you approach it. with a straightforward Sql command, the following is a guide:

Dim query As String = "Select * From Users WHERE UserName = @.UserName"
Dim conn As New SqlConnection(myconnectionstring)
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@.UserName",User.Identity.Name)
conn.Open()
Dim rdr As New SqlDataReader = cmd.ExecuteReader()
While rdr.Read()...

If you want to use a SqlDataSource, this article should help:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

|||

valenumr:

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

But that'll get the SQL user, which probably won't be the same as the user logged on to the website..

Regards

Fredr!k

|||

Very true... it will work if your DB is using windows login's, and you don't have any impersonation going on in your web server... so I guess, I should have mentioned that.

|||

Thanks to all the rapid replies.

An error had lead me to believe that the User.Identity.Name did not return a string, but further research says it should.

I should be clearer about the approach I was attempting. Given that I'm struggling to learn .Net and ASP.Net I'm struggling to keep the issues clear.

I was trying to find a way to do the assignment in the Mark Up for the server controls.

I have found that you can also create a Parameter in the mark up for the SqlSourceControl section under the <SelectParameters> section that you can then use in the Select Command.

Still trying to tie that info together to do this.

BUT , your cmd.Parameters.AddWithValue method is eye opening. When I start working with code, I'm still trying to learn the models to understand whichevents to put such code.

(Anyone know a concise reference for this ? )

I want a dropdown box to be filled with a set of items from a database that are particular to the User that's logged on.

(ps. I find from my research that you can use My.User.Name , also to get the username)

Many Thanks for all the help !

Chris9876

|||

I'm not at my computer to verify, but try User.Identity.Name.tostring If that doesn't work, I will check when I get home as I do this exact thing, but with the userid and not the username.

|||

This is from memory, but you can create a parameter in your sqldatasource (Let's call it @.Username).

Then in the SqlDataSource1_Selecting event, just put:

e.Commad.Parameters("@.UserName").Value= My.User.Name

Then whenever the sqldatasource is about to do a select, you set the username parameter to whomever is currently logged it. Simple and easy.

If you are changing the output based on the persons roles, or if they are currently logged in, you might want to just use a loginview control.

|||

The following Markup and Event Code works great. (DropdownList is filled with data from MyField whereUserName=@.UserName)

Thank you all very much.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="ClassCode"DataValueField="ClassCode">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [MyField] FROM [MyView] WHERE ([UserName] = @.UserName)">

<SelectParameters>

<asp:ParameterName="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

e.Command.Parameters("@.UserName").Value =My.User.Name

EndSub