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

ram
ram used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
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.
ram

Author

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

Chief Technology Ninja
Distinguished Expert 2018
Commented:
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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

ram

Author

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.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
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.
ram

Author

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

Author

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

ram

Author

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!
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial