Only show records based on the user that has logged in (User.Identity.Name)

I have a webap using VS2005 and the DB is SQL2005.  I have a webform with a formview and gridview control that allows me to enter records and I use the following two sections of code to capture the username of the individual logged in when I am addeding or updating a existing record using a basic SQLdatasource that select all fields in atable.

Problem: WHen I login to the application to edit the records I worked on using the gridview or fromview control how do I configure the SQLdatasource to only populate the formview or gridviewcontrol with only the records that match the name of the loged in user?  I have tried  "SELECT * FROM TABLE WHERE Username = @Username" and no luck.


Thank you,

 




Protected Sub FormView1_ItemInserting(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.FormViewInsertEventArgs) Handles FormView1.ItemInserting 
 
sqldsCategory.InsertParameters( "UserName").DefaultValue = User.Identity.Name
 
End Sub 
 
Protected Sub ASPxGridView1_RowUpdating(ByVal sender As System.Object, ByVal e As DevExpress.Web.Data.ASPxDataUpdatingEventArgs) Handles ASPxGridView1.RowUpdating 
 
sqldsCategory.UpdateParameters("RecordModifier").DefaultValue = User.Identity.Name
 
End Sub

Open in new window

cesemjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
Add SelectParamter to your SqlDataSource and use onSelecting Event as below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:YourConnectionString %>"
            SelectCommand="SELECT * FROM [yourTable] where UserName=@UserName"
            onselecting="SqlDataSource1_Selecting">
            <SelectParameters>
                <asp:Parameter Name="UserName" />
            </SelectParameters>            
        </asp:SqlDataSource>

Add value to this parameter in codebehind:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("@UserName").Value = User.Identity.Name
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cesemjAuthor Commented:
I will try it and let you know what happens.
0
cesemjAuthor Commented:
Excellent insight.  Thank you again...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.