[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to Modify SQLDataSource Form Parameters on the fly

Posted on 2014-07-14
Medium Priority
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

CodeCruiser earned 2000 total points
ID: 40196519
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

868 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