locked
Populating DropDownList control from MS Access query RRS feed

  • Question

  • User-881997857 posted

    Hi everyone Smile

    I am a .NET novice so please be gentle with me...

    I have a web form which allows users to search for documents. Every document is assigned to a category in a Microsoft Access database. I didn't create the form but want to tweak it!

    Currently the list of categories is hardcoded as such:

    <asp:DropDownList ID="ddlCategory" runat="server">
       <asp:ListItem>Cars</asp:ListItem>
       <asp:ListItem>Boats</asp:ListItem>
       <asp:ListItem>Trains</asp:ListItem>
    </asp:DropDownList>

    In my database I have created a table containing all of the categories (currently 66 of them and growing!) and would like to output this list dynamically in the control ddlCategory so I don't have to keep manually adding another ListItem every time I add a new category.

    I have a query within the MS Access database called qu_categories which outputs the category list but I'm not sure of the syntax to get this working on my form.

    So my questions are:

    1. What code would I need to execute in my C# code file?
    2. How would the markup look in my main .aspx file, e.g. how would I modify the above to allow for a query-driven list instead of hardcoded ListItems?

    Hopefully someone can help. Thank you. Smile

    Thursday, June 18, 2015 8:35 AM

Answers

  • User1577371250 posted

    Hi,

    1. Add the following namespace on top of the page

    using System.Data;
    using System.Data.OleDb;

    2. Write this code

     protected void Page_Load(object sender, EventArgs args)
            {
                if (!IsPostBack)
                {
                    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;";
                   
                    using (OleDbConnection con = new OleDbConnection(connectionString))
                    {
                        //Open Database Connection
                        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Categories", con);
                       
                        DataSet ds = new DataSet();
                      
                        //Fill the DataSet
                        da.Fill(ds);
    
                        ddlCategories.DataSource = ds.Tables[0];
                        ddlCategories.DataValueField = "Id"; // Value Field
                        ddlCategories.DataTextField = "Name"; // Text field which will be show in UI
                        ddlCategories.DataBind();
                    }
              }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 8:57 AM
  • User1577371250 posted

    Hi,

    here is the code to add default item at first.

     protected void Page_Load(object sender, EventArgs args)
            {
                if (!IsPostBack)
                {
                    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;";
    
                    using (OleDbConnection con = new OleDbConnection(connectionString))
                    {
                        //Open Database Connection
                        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Categories", con);
    
                        DataSet ds = new DataSet();
    
                        //Fill the DataSet
                        da.Fill(ds);
    
                        ddlCategories.DataSource = ds.Tables[0];
                        ddlCategories.DataValueField = "Id"; // Value Field
                        ddlCategories.DataTextField = "Name"; // Text field which will be show in UI
                        ddlCategories.DataBind();
    
                        ddlCategories.Items.Insert(0, new ListItem() { Text = "Please Select", Value = "0" });
                    }
    
            }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 10:36 AM

All replies

  • User1577371250 posted

    Hi,

    1. Add the following namespace on top of the page

    using System.Data;
    using System.Data.OleDb;

    2. Write this code

     protected void Page_Load(object sender, EventArgs args)
            {
                if (!IsPostBack)
                {
                    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;";
                   
                    using (OleDbConnection con = new OleDbConnection(connectionString))
                    {
                        //Open Database Connection
                        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Categories", con);
                       
                        DataSet ds = new DataSet();
                      
                        //Fill the DataSet
                        da.Fill(ds);
    
                        ddlCategories.DataSource = ds.Tables[0];
                        ddlCategories.DataValueField = "Id"; // Value Field
                        ddlCategories.DataTextField = "Name"; // Text field which will be show in UI
                        ddlCategories.DataBind();
                    }
              }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 8:57 AM
  • User-881997857 posted

    Thanks so much. That worked perfectly - much appreciated! Smile

    Just one quick question: the query is pulling through all of the categories that exist and populating the dropdown with them (which is perfect!) however I'd quite like to have a "blank" option at the top of the form saying "Please select a category" - or alternatively, just a blank field. Is this a possibility with a dynamically-generated list?

    Thursday, June 18, 2015 10:08 AM
  • User1577371250 posted

    Hi,

    here is the code to add default item at first.

     protected void Page_Load(object sender, EventArgs args)
            {
                if (!IsPostBack)
                {
                    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;";
    
                    using (OleDbConnection con = new OleDbConnection(connectionString))
                    {
                        //Open Database Connection
                        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Categories", con);
    
                        DataSet ds = new DataSet();
    
                        //Fill the DataSet
                        da.Fill(ds);
    
                        ddlCategories.DataSource = ds.Tables[0];
                        ddlCategories.DataValueField = "Id"; // Value Field
                        ddlCategories.DataTextField = "Name"; // Text field which will be show in UI
                        ddlCategories.DataBind();
    
                        ddlCategories.Items.Insert(0, new ListItem() { Text = "Please Select", Value = "0" });
                    }
    
            }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 18, 2015 10:36 AM
  • User-881997857 posted

    Perfect, thank you so so so much!

    Thursday, June 18, 2015 10:46 AM