Populate DropDown List from Database in ASP.NET & C#

It is often useful to allow users to select items from a dropdown list for input into a web form. However, this can be somewhat too limiting in certain situations.
This tutorial will show how we can dynamically populate a dropdown menu with records from a database.
First, we start by declaring the assembly reference:

using System.Data.SqlClient;

In our Web.config, we declare the connection string:

<appSettings> <add key="ConnString" value="Data Source=CLIENT-TASK2\SQLEXPRESS;Initial Catalog=BasicDataAccess;Integrated Security=True"/> </appSettings>

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.


The ASPX page will look something like this:

<form id="form1" runat="server"> <div align="center"> <table><tr><th>Name:</th><td><asp:DropDownList ID="DropDownList1" runat="server"> </asp:DropDownList></td></tr> <tr><th>City:</th><td><asp:DropDownList ID="DropDownList2" runat="server"> </asp:DropDownList></td></tr></table> </div> </form>

In the code-behind, we can create two methods and then call them on page load:

using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Populate1(); Populate2(); } public void Populate1() { SqlCommand cmd = new SqlCommand("SELECT * FROM [tblOne]", new SqlConnection(ConfigurationManager.AppSettings["ConnString"])); cmd.Connection.Open(); SqlDataReader ddlValues; ddlValues = cmd.ExecuteReader(); DropDownList1.DataSource = ddlValues; DropDownList1.DataValueField = "theName"; DropDownList1.DataTextField = "theName"; DropDownList1.DataBind(); cmd.Connection.Close(); cmd.Connection.Dispose(); } public void Populate2() { SqlCommand cmd = new SqlCommand("SELECT * FROM [tblOne]", new SqlConnection(ConfigurationManager.AppSettings["ConnString"])); cmd.Connection.Open(); SqlDataReader ddlValues; ddlValues = cmd.ExecuteReader(); DropDownList2.DataSource = ddlValues; DropDownList2.DataValueField = "theCity"; DropDownList2.DataTextField = "theCity"; DropDownList2.DataBind(); cmd.Connection.Close(); cmd.Connection.Dispose(); } }

populate text box from database on page load

 Hi,I've found all kinds of info on displaying data with various gridview, listview, dataview, etc. controls. What I cannot find is info on how to manually display the data without using a control. I was able to find this type of info for inserting data, i.e. "insertcommandtype" and "insertparameters" and made a working manual input form, but to display the data I'm coming up blank. I'm a rookie at this...I've learned a lot on my own...the internet is an endless resource, but I think maybe what I'm looking for is "outdated" i.e. why do it the hard way, manually, when it can be automated. So, what I'm looking for is simply how to retrieve the data and then display it in an aspx page. This is what I've been trying in my code behind:
.....
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim SqlDataSource1 As New SqlDataSource()
SqlDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("BarbConnec tionString").ToString()

SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.Text

SqlDataSource1.SelectCommand = "Select * from BarbDatabase" 'Tried this on it's own
SqlDataSource1.SelectParameters.GetValues(HttpCont ext.Current, SqlDataSource1)
'SqlDataSource1.SelectParameters("Header") ToString
'SqlDataSource1.SelectParameters.GetValues(SqlData Source1) 'Header')

End Sub

and multiple variations of this (Header is the name of one of the db fields)

These I have tried in the aspx page:

<asp:TextBox ID="CentrePanelTextBox" runat="server" DataSourceID="SqlDataSource1" Text='<%# Bind("CentrePanel") %>'
TargetControlID="CentrePanelTextBox"/>

<asp:TextBox ID="HeaderTextBox" runat="server" Text="hello">
<asp:BoundField DataField="Header" HeaderText="Header" SortExpression="Header" />
</asp:TextBox>

And again every variation I could think of.
Or is it so much more complicated than this I should just stick with the controls?

Populate data from database in a ComboBox

Introduction

This article is the second way (version) of "how to populate data in a ComboBox" program. For doing that, I make use of 'TableMapping' and 'DataViewManager' that I'll explain later in "Code and How it works".
The application allows you to select a StudentID in a ComboBox and displays the columns StudentID, Student Subject and Student Name from the table in the three TextBoxes.
It shows also how to get connected to a MS Access database which you can also find in the project included (sudentDB.mdb). I chose MS Access database because not many people have SQL-Server running.
Form:

Code and How it works

Before starting, a short explanation of what TableMapping and DataViewManager means:
TableMapping is the process that controls how data adapters copy tables and columns of data from a physical data source to ADO.NET in-memory objects. When a data adapter reads data from a data source, it determines where to put the data in the corresponding DataSet table (or tables) using a table mapping. If you create a mapping in a data adapter, it allows you to establish a correspondence between columns in the data source and columns in a DataSet table.
A DataAdapter contains a collection of DataTableMapping objects in its TableMappings property. You can pass the DataTableMapping name in place of the DataTable name to the Fill method of the DataAdapter.
The following example creates a DataTableMapping named "MyStudentMappings" for the table "studentTable".

Collapse

dAdapter.TableMappings.Add("MyStudentMappings", "studentTable");

When you call the Fill method of the DataAdapter and do not specify a TableName or DataTableMapping name, the DataAdapter looks for a DataTableMapping called "Table". If you leave out that DataTableMapping, TableName of the DataTable will be "Table". This is the default DataTableMapping.
For example:

Collapse

dAdapter.TableMappings.Add("Table", "studentTable");

If the SELECT command creates a result set with a default name of Table, then its contents go into a new or existing DataTable object called "studentTable". The DataTableMapping object describes a mapped relationship between a SQL-result set and a DataTable object in a DataSet.
The DataViewManager represents a view onto an entire DataSet and represents a databindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation, whereas the DataView class acts as a view onto a single DataTable.
A DataViewManager is an object that contains a collection of data views. The DataViewManager returned by the DefaultViewManager property allows you to create custom settings for each DataTable in the DataSet. When you want to bind a control to more than one table of a DataSet, binding to a DataViewManager is the ideal choice. When only a single DataTable, then use DataView. When binding a DataSet, .NET automatically uses the corresponding DataViewManager provided through the DataSet.DefaultViewManager property:
For example:

Collapse

this.dviewmanager=dset.DefaultViewManager;

Here is the code for the method fnGetConnectedToDatabase():

Collapse

private void fnGetConnectedToDatabase() 
{
  //Connection string

  string conStr = 
    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=..\\..\\studentDB.mdb";
  try
   {
    //Instantiate OleDbConnection and open the connection to the database

    myConn = new OleDbConnection(conStr);
    myConn.Open();
  }
  catch(OleDbException ex)
  {
    //get the error message if connection failed

    MessageBox.Show("Error in connection ..."+ex.Message);
  }

  string sqlStr ="SELECT * FROM studentTable;";
  //Instantiate a DataAdapter by passing the sqlStr and myConn.

  //now data is in raw form

  dAdapter = new OleDbDataAdapter(sqlStr,myConn);
  //Instantiate a DataSet

  dset = new DataSet();
  //Gets a collection that provides the master mapping

  // between a source table and a DataTable

  dAdapter.TableMappings.Add("Table", "studentTable");

  //A data adapter object utilizes the Fill method to populate 

  //a DataSet or a DataTable object with

  //data retrieved by a SELECT command.

  dAdapter.Fill(dset);
  //When binding a DataSet, .NET automatically uses the corresponding

  //DataViewManager provided through the DataSet.DefaultViewManager property

  this.dviewmanager=dset.DefaultViewManager;
  this.comboBox1.DataSource=this.dviewmanager;
  //display "studentTable.StudentID" in the ComboBox

  this.comboBox1.DisplayMember="studentTable.StudentID";
  //DataBinding for the TextBox controls

  this.textBox1.DataBindings.Add("Text", this.dviewmanager, 
                                         "studentTable.StudentID");
  this.textBox2.DataBindings.Add("Text", this.dviewmanager, 
                                         "studentTable.StudentSubject");
  this.textBox3.DataBindings.Add("Text", this.dviewmanager, 
                                         "studentTable.StudentName");
  // Close the connection to the database.

  this.myConn.Close();
}

As soon as you select a StudentID from the ComboBox, the columns StudentID, StudentSubject and StudentName in the table "studentTable" are displayed in the three TextBoxes.

Conclusion

There are of course many ways to get data into a ComboBox control. I just tried to show a different way.