How to change asp.net gridview datasource/connection string based on login/session parameter?

I need to change a gridview datasource/connectionstring based on session parameter/login.

Just a little background, we have an in-house software that generates an .mdb database file and then uploads it to our hosted database-driven website via FTP. Each of our customers get their own .mdb file and a login to the website. The website is designed to show only their assigned .mdb. But the website has limited capabilities.

So I was tasked to update the website and found out that it uses .asp, but I am not well versed in VB script and found that creating an .aspx web application is easier.

Now I'm developing a web application in Visual Studio that utilizes the same database but with added features such as sorting, paging, and filtering based on keyword searches.

I've finished creating the login part (with sessions), and I've been able to create a page that displays data on a gridview. My problem now is that I don't really know how to change the gridview datasource based on login/session.

By the way, the database for login information is also an .mdb and is also uploaded by our in-house software. If we need to add new users, we use the in-house software to update the .mdb and then upload it to the website. There are 3 columns in the database table, username, password, and client code. I would like to use the client code to pull the correct database file because each of the database files contain a specific client code already.

Please assist. Thanks!
ramAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Chinmay PatelChief Technical NinjaCommented:
Hi Ram,

Please post the code of how you are currently showing the data in GridView and I will suggest you the changes you need to make - to be honest it is just the connection string that needs to be different(along with credentials) but I can be more specific if you can share your code(You can remove sensitive data or anything that you don't want to put on a public website).

Regards,
Chinmay.
0
ramAuthor Commented:
Here is the HTML code for the gridview:

   <asp:GridView ID="GridView" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" PageSize="50">
    <Columns>
        <asp:BoundField DataField="Client" HeaderText="Client" SortExpression="Client" />
        <asp:BoundField DataField="SampleNumber" HeaderText="SampleNumber" SortExpression="SampleNumber" />
        <asp:BoundField DataField="SampleDesc" HeaderText="SampleDesc" SortExpression="SampleDesc" />
        <asp:BoundField DataField="SampleDate" HeaderText="SampleDate" SortExpression="SampleDate" />
        <asp:BoundField DataField="SampleTime" HeaderText="SampleTime" SortExpression="SampleTime" />
        <asp:BoundField DataField="Matrix" HeaderText="Matrix" SortExpression="Matrix" />
        <asp:BoundField DataField="Analyte" HeaderText="Analyte" SortExpression="Analyte" />
        <asp:BoundField DataField="Result" HeaderText="Result" SortExpression="Result" />
        <asp:BoundField DataField="SQLValue" HeaderText="SQLValue" SortExpression="SQLValue" />
        <asp:BoundField DataField="Dilution" HeaderText="Dilution" SortExpression="Dilution" />
        <asp:BoundField DataField="Units" HeaderText="Units" SortExpression="Units" />
    </Columns>
    </asp:GridView>

    <asp:SqlDataSource 
        ID="SqlDataSource1" 
        runat="server" 
        ConnectionString="<%$ ConnectionStrings:IU_Daily_V1_1_ALLConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:IU_Daily_V1_1_ALLConnectionString.ProviderName %>" 
        SelectCommand="SELECT * FROM [DATA] ORDER BY [SampleDate] DESC" FilterExpression="[SampleDesc] LIKE '%{0}%'">

    <FilterParameters>
        <asp:ControlParameter Name="SampleDesc" 
            ControlID="txtSearch" PropertyName="Text" />
    </FilterParameters>

    </asp:SqlDataSource>

Open in new window


Here is the code for the connectionstring in the web.config file:

  <connectionStrings>
    <add name="IU_Daily_V1_1_ALLConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot;path\IU_Daily_V1_1_ALL.mdb&quot;"
      providerName="System.Data.OleDb" />
  </connectionStrings>

Open in new window

0
Chinmay PatelChief Technical NinjaCommented:
It seems you are not using CodeBehind in this page.

In your CodeBehind you can easily manipulate this value

SqlDataSource1.ConnectionString = "Customer1ConnectionString"

Open in new window


Basically on Page_Load you will determine which customer database needs to be loaded and change the value of ConnectionString.
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

ramAuthor Commented:
Could you please elaborate a bit more?

Do I leave 'ConnectionString' and 'ProviderName' blank in the HTML code?

I'm new to asp.net so please bear with me.
0
Chinmay PatelChief Technical NinjaCommented:
No worries ram. You are free to ask as many questions as long as you are putting efforts behind it.
I will answer them ALL.

Ok.. so in ASP.Net you have the Visual Form Designer - this is how you have generated your Markup code <asp:GridView>, <asp:SqlDataSource > etc.

And when that page is open if you press F7 it will take you to associated code file i.e. CodeBehind.

In CodeBehind you will have various page events like Page_Load, and control events like Button_Click... so on and so forth.

I am suggesting that you can easily change them from the CodeBehind dynamically. You can leave the connection as it is so that when the page loads it shows some data but say you don't want to show any data unless your user selects a client(and thus a database), you can remove the connection details from Markup code.
0
ramAuthor Commented:
Ok, I wasn't sure what to do with the sqldatasource parameters.

So I did this to the gridview HTML:

    <asp:SqlDataSource 
        ID="SqlDataSource1" 
        runat="server" 
        ConnectionString="" 
        ProviderName="" 
        SelectCommand="SELECT * FROM [DATA] ORDER BY [SampleDate] DESC" FilterExpression="[SampleDesc] LIKE '%{0}%'">

    <FilterParameters>
        <asp:ControlParameter Name="SampleDesc" 
            ControlID="txtSearch" PropertyName="Text" />
    </FilterParameters>

    </asp:SqlDataSource>

Open in new window


And then added this to the Page_Load in CodeBehind:

        protected void Page_Load(object sender, EventArgs e)
        {
            string username = Session["username"].ToString();

            DataLayer.DataConnector dat = new DataLayer.DataConnector("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Server.MapPath("database/IU_LEData_User_V1_1.mdb") + "'; Persist Security Info=False;");
            DataTable dt = dat.DataSelect("select CLIENT from users where USERNAME = " + username.Replace("'", "''") + "'");
            foreach (DataRow dr in dt.Rows)
                if (dt.Rows.Count > 0)
                {
                    SqlDataSource1.ConnectionString = "<%$ ConnectionStrings:IU_Daily_V1_1_" + dr["CLIENT"].ToString() + "ConnectionString %>";
                }
        }

Open in new window


By the way, I'm using a class library as my data access layer to connect to the user login database file. With this, I'm getting an error saying "[InvalidOperationException: The ConnectionString property has not been initialized.]".

I got the code to use class library from a tutorial. I'm open for a better solution.
0
ramAuthor Commented:
I just tried re-coding my user validation code in my login page to grab the client code from the database table and assign it to a session:

        private bool ValidateUser(String username, String password)
        {
            bool status = false;
            DataLayer.DataConnector dat = new DataLayer.DataConnector("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Server.MapPath("database/IU_LEData_User_V1_1.mdb") + "'; Persist Security Info=False;");
            DataTable dt = dat.DataSelect("select USERNAME, CLIENT from users where USERNAME = '" + username.Replace("'", "''") + "' and PASSWORD = '" + password.Replace("'", "''") + "'");
            foreach (DataRow dr in dt.Rows)
                if (dt.Rows.Count > 0)
                {
                    Session["username"] = dr["username"].ToString();
                    Session["client"] = dr["client"].ToString();
                    status = true;
                }
                else
                {
                    status = false;
                }
            return status;
        }

Open in new window

 

This way I don't have to use the datalayer code twice, and then I re-coded the Page_Load section like so:

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["client"] != null)
            {
                SqlDataSource1.ConnectionString = "<%$ ConnectionStrings:IU_Daily_V1_1_" + Session["client"].ToString() + "ConnectionString %>";
            }
        }

Open in new window


But now it's giving me an error message that says 'System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.'

This is what it looks like in my web.config file (I only have 3 customers coded at the moment, and the connectionstring 'ALL' is for admin):

  <connectionStrings>
    <add name="IU_Daily_V1_1_ALLConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot;path\database\IU_Daily_V1_1_ALL.mdb&quot;"
      providerName="System.Data.OleDb" />
    <add name="IU_Daily_V1_1_AConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot;path\database\IU_Daily_V1_1_A.mdb&quot;"
      providerName="System.Data.OleDb" />
    <add name="IU_Daily_V1_1_BConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot;path\database\IU_Daily_V1_1_B.mdb&quot;"
      providerName="System.Data.OleDb" />
    <add name="IU_Daily_V1_1_CConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot;path\database\IU_Daily_V1_1_C.mdb&quot;"
      providerName="System.Data.OleDb" />
  </connectionStrings>

Open in new window

0
ramAuthor Commented:
Ok so I removed the inline declaration of the connectionstring from the HTML code and changed the value of 'ProviderName' to just say 'System.Data.OleDb'.

And then I changed the code for the connectionstring in CodeBehind to:

SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["IU_Daily_V1_1_" + Session["client"].ToString() + "ConnectionString"].ConnectionString;

Open in new window


And now it works! Thanks for the help!
0
Chinmay PatelChief Technical NinjaCommented:
Glad to help you. And I am very happy that you figured out rest of it yourself.

just in case, the <% %> markup is called expressions(sometimes Eval expressions as they are used heavily with Eval() ) and they are valid for ASPX only not the CodeBehind(ASPX.CS).
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
ASP.NET

From novice to tech pro — start learning today.