Using database connection in web.config to populate dropdown

Question

I'm new to ASP.NET, and am converting a Classic ASP application. I have the following code which populates a dropdown from a database and then runs some conditional statements on the selection to affect the page.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

  // Global variable for SqlConnection
  SqlConnection con = new SqlConnection();

  protected void Page_Load(object sender, EventArgs e)
  {

    if (!this.IsPostBack)
    {

      // specifying sqlconnection string
      con.ConnectionString = "...MY CONNECTION STRING";
      {
        // Select rows from database where the ItemType field isn't empty. Sort them alphabetically by ItemType
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM NF_WhatWasteWhere WHERE ItemType <>'' Order By ItemType"))
        {
          //Open the connection and populate the dropdown list with ID and Itemtype
          cmd.CommandType = CommandType.Text;

          cmd.Connection = con;

          con.Open();

          ItemType1.DataSource = cmd.ExecuteReader();

          ItemType1.DataTextField = "ItemType";

          ItemType1.DataValueField = "BinType";

          ItemType1.DataBind();

          con.Close();

        }

      }
      // Add a non selectable "Select Item" row at the top of the dropdown list
      ItemType1.Items.Insert(0, new ListItem("--Select Item--", "0"));
      string BinColour = ItemType1.SelectedValue;
    }

  }
    protected void ItemType1_SelectedIndexChanged(object sender, EventArgs e)
    {
      //BinResultTest.Text = ItemType1.SelectedValue;

      if (ItemType1.SelectedValue == "Green")
      {
        BinResultTest.Text = "<div class='greenBin results'><div class='arrow'></div><p> should be disposed of in a <strong>green bin</strong>.</p></div>";
      }
      else if (ItemType1.SelectedValue == "Black")
      {
        BinResultTest.Text = "<div class='blackBin results'><div class='arrow'></div><p> should be disposed of in a <strong>black bin</strong>.</p></div>";
      }
      else
      {
        BinResultTest.Text = "<div class='noBin results'><div class='arrow'></div><p> should <strong>NOT</strong> be disposed of in a green or black bin.</p></div>";
      }
    }
}

This works fine, but it would be better if it used the connection string in my web.config file. However, due to my lack of experience in ASP.NET (C#), I'm not sure how to achieve this. I've tried looking at some code created when I added an SQLDataSource and Gridview control to another page in the same website, but I'm unsure how to add this to my codebehind above.

My web.config connection is as follows:

<connectionStrings>
    <add name="ConnectionString_MYDATABASE" connectionString="...MY CONNECTION STRING"
        providerName="System.Data.OleDb" />
</connectionStrings>

Any advice on how I can integrate the connection from web.config is much appreciated, thanks.

Community · Accepted Answer · 2017-05-23 12:14:37Z

1

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString_MYDATABASE"].ConnectionString;

If you want to see more detail example -> MSDN article.

Also as advice if you want to continue learning, you need to build different layers in your solution. So connection to the database should happen on Data Access layer, so you will not write same code for every dropdown. That means you need separate class in which you will access the database. Here is a question in which I wrote a simple Data Access Layer-> LINK

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Aug 11, 2015 at 11:49

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2335705 Over a year ago

Thanks. This worked, but only after I removed "Provider=SQLOLEDB;" from my connection string in web.config. Unfortunately, this has had the side-effect of causing an error when I attempt to update records using another page which has an SQLDataSource and Gridview controls connecting to the same connection.

Collectives™ on Stack Overflow

Using database connection in web.config to populate dropdown

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related