Introduction

Today, I have provided an article showing how to display SQL table data in a ListBox in ASP.NET. In this article, we create a table in a SQL Server database and drag a ListBox control on the aspx page in ASP.NET. After that we create a connection string object to connect to the database with the application and read data from the database using the select command. All you need to do is implement and hook it up to your requirement or need. First of all you start Visual Studio .NET and make a new ASP.NET web site using Visual Studio 2010.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns id, name, country, city. Set the identity property=true for id. The table looks as in the following:

img1.jpg

Now insert some values in this table. The table looks like this:

img2.jpg

Now you must create a web site.

  • Go to Visual Studio 2010
  • New-> Select a website application
  • Click OK

img6.jpg

Now add a new page to the website.

  • Go to the Solution Explorer
  • Right-click on the Project name
  • Select add new item
  • Add new web page and give it a name
  • Click OK

img7.jpg

Design the page and place the required control in it. Now drag and drop one ListBox control on the form. That displays SQL Server table data. Let's have a look at a practical example.

img3.jpg

.aspx Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:ListBox ID="ListBox1" runat="server" BackColor="#FF99FF" ForeColor="Black"

Height="205px" Width="260px"></asp:ListBox>

<br />

<br />

</div>

</form>

</body>

</html>


Now add the following namespaces:

using System.Data.SqlClient;

using System.Data;

Now write the connection string to connect to the database:

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=rohatash;";

In code-behind write the following code.

Code-behind

To store the data in a ListBox use a DataAdapter object to retrieve the data from the database and place that data into a DataSet.

using Microsoft.VisualBasic;

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Diagnostics;

using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page

{

public static ArrayList Files = new ArrayList();

protected void Page_Load(object sender, EventArgs e)

{

string str = "Data Source=.;uid=sa;pwd=wintellect;database=rohatash";

SqlConnection con = new SqlConnection(str);

string com = "Select id, name, Country,City from UserDetail";

SqlDataAdapter adpt = new SqlDataAdapter(com, con);

DataSet myDataSet = new DataSet();

adpt.Fill(myDataSet, "UserDetail");

DataTable myDataTable = myDataSet.Tables[0];

DataRow tempRow = null;

foreach (DataRow tempRow_Variable in myDataTable.Rows)

{

tempRow = tempRow_Variable;

ListBox1.Items.Add((tempRow["id"] + " (" + tempRow["name"] + ")" + " (" + tempRow["Country"] + ")" + " (" + tempRow["City"] + ")"));

}

}

}

Important Property

Rows - The Rows property gets the collection of rows that belongs to the table.

Now run the application and test it.

img4.jpg