How to Modify SQLDataSource Form Parameters on the fly

Posted on 2014-07-14
Last Modified: 2014-07-21
I have a datagridview attached to a SQL stored procedure that accepts 2 arguments. One is the employee number and the second is the level of access a user has. I have 2 hidden text box controls on the page that will drive the stored procedure. One is call txtEmployeeNumber and the other is txtAccessLevel. I use 2 functions to pull the currently logged on user's employee number and searches that employee number to figure out if they are an Admin, SuperUser, or just a normal User(Default Option).

Current ASP Markup:
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:TextBox ID="txtEmployeeNumber" runat="server"></asp:TextBox>
    <asp:TextBox ID="txtAccessLevel" runat="server"></asp:TextBox>
    <br />
        txtAccessLevel.Text = CamIMS.pt_Functions.AccessLevel(CamIMS.pt_Functions.GetEmployeeNumber())
        txtEmployeeNumber.Text = CamIMS.pt_Functions.GetEmployeeNumber()

Open in new window

I then reference those controls in the SQLDataSOurce:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMSConnectionString %>" SelectCommand="EmployeeBalances" SelectCommandType="StoredProcedure">
            <asp:ControlParameter ControlID="txtAccessLevel" Name="type" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="txtEmployeeNumber" Name="EmployeeNumber" PropertyName="Text" Type="Int32" />

Open in new window

The problem I have is that the text boxes remain blank. I have tried setting this in the Page_load area with no luck either. I know this is probably not the best way to do this, so I am open to any suggestions.
Question by:tekkieguru
    1 Comment
    LVL 83

    Accepted Solution

    Not a very secure approach in my opinion.

    Problem is that by the time you have set the textboxes, the SQLDataSource has already retrieved data.

    I would suggest that when user logs in, you retrieve the employee ID and access level and store it in the session. Then on this page, use SessionParameter instead of ControlParameter. Example on below link

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now