Passing UserId to a stored procedure or query

FOD · May 28th, 2011, 11:44 PM

I have a table where one of my fields is the UserId (uniqueidentifier) assigned by the login system that is included with ASP.net. I am trying to write a query where when I log in I can go to a page with a listview control that SELECTS all of the table records WHERE the OwnerId within the table is equal to the logged in UserId.

I've tried setting a variable in the code behind and passing it as a parameter along with numerous other options such as trying to pass it to a stored procedure, none of which have worked.

To be honest I'm at a loss and would appreciate any help you can provide I'm sure the answer is probably simple and I'm just not seeing it.

Thanks!

Imar · May 29th, 2011, 06:23 AM

Hi there,

To which chapter and page number of the book are you referring?

Imar

FOD · May 29th, 2011, 09:43 AM

I just finished reading your book and am attempting to work on some concepts of my own. The question is not specific to your book examples, but the chapters that I have specifically looked through for answers to my question are 12, 13 & 16, 17.

I apologize if this is the wrong place to have placed this question I just remembered you mentioning this forum in your book.

Thanks again.

Imar · May 29th, 2011, 10:22 AM

Hi there,

Quote:

I just remembered you mentioning this forum in your book.

Yes, for book related questions, this is indeed the best place to go. However, for more general questions not directly related to the book you're better off picking a more generic ASP.NET category from this list: http://p2p.wrox.com/asp-net-4-539/

This way, you'll attract more viewers, and keep the forums easier to navigate.

Can you repost your question there and supply more details and the code you have so far?

Cheers,

Imar

mibrooks27 · May 31st, 2011, 02:44 PM

I have precisely the same question as FOD - how do you access the login name for a query or stored procedure using the login wizard you discuss in your book. After looking for weeks, all over the web, I found the question asked here. Now, I have purchased both the hard copy AND the Kindle version of "Beginning ASP.NET4...." and this *IS* the logical place to ask the question and receive an answer; at least I feel entitled to ask it. So, if you can, please answer the question here, or previde a link as to where you have answered it, so we don't have to run down this rabbit trail any longer.

mibrooks27 · May 31st, 2011, 02:57 PM

Oh, my use for thgis is pretty simple. I have created a form, for use in-house, where users simply decrement a field associated with a name (in this case, PCP's, and the maximum number of new patients they will accept). As the field is decremented in my SQL database, I need to fire a sijmple trigger that captures the PCP, clerk/operator-login-name, and date when the change was made:

INSERT INTO AUDIT_TABLE
SELECT @ProvName, @Operator, getdate()

The Provider-PCP Name is easy to obtain from a grid view, but the operator-login name doesn't seem to be stored anywhere, even though we know it must be somewhere......

Imar · May 31st, 2011, 03:13 PM

As I suggested in an earlier message in this thread, please post this in a more general ASP.NET category here at p2p.wrox.com.

It's not directly related to the book, so continuing the thread here clutters up this forum, makes things harder to navigate and will give you fewer viewers that may have an answer. At the same time, someone in the future with a similar question is more likely to find an answer there.

So, if you repost in a different category, provide more details and your current code and post a link here I'll be sure to take a look.

Cheers,

Imar

FOD · May 31st, 2011, 07:34 PM

Imar,

I have figured out a way to make it work, although have no practical experience I'm not sure it is the best way. I'll post the original question and the code I'm using in the other forum as you requested. Please take a look if you have a moment and let me know if there is a better method to accomplish the same thing.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Using TMSEntities As New TMSEntities()
Dim userId = Membership.GetUser().ProviderUserKey()
Dim myStuff = From stuff In TMSEntities.stuff
Where stuff.OwnerId = userId
Select stuff.stuffname, stuff.OwnerId

ListView1.DataSource = myStuff
ListView1.DataBind()
End Using
End Sub

Thanks,
FOD

Imar · June 1st, 2011, 12:13 PM

Hi there,

Yes, this is a great way to do it, and exactly how I would do it if I were using Entity Framework or LINQ to SQL.

If you're using straight SQL (with a SqlDataSource for example), it's a bit trickier and you need to handle the control's Selecting event and then dynamically inject the user name into one of the SelectParameters.

Here's an example of doing that:

How to use login user name in where clause?

Cheers,

Imar