asked on

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!

Chinmay Patel

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

ASKER

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

ASKER CERTIFIED SOLUTION

Chinmay Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ram

ASKER

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 Patel

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

ASKER

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

ASKER

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

ASKER

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 Patel

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