Murray Brown
asked on
ASP.net Programatically populate ListBox from SQL backend table
Hi
Using the System.Data.SqlClient namespace, how would I programatically populate
a ListBox on an ASP.net WebPage?
Using the System.Data.SqlClient namespace, how would I programatically populate
a ListBox on an ASP.net WebPage?
refer
http://geekswithblogs.net/dotNETvinz/archive/2009/02/24/binding-dropdownlist-listbox-and-checkboxlist-control-the-ado.net-way.aspx
http://geekswithblogs.net/dotNETvinz/archive/2009/02/24/binding-dropdownlist-listbox-and-checkboxlist-control-the-ado.net-way.aspx
private void BindListBox()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(20)* FROM Customers";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
ListBox1.DataSource =dt;
ListBox1.DataTextField = "ContactName"; // the items to be displayed in the list items
ListBox1.DataValueField = "CustomerID"; // the id of the items displayed
ListBox1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
}
on aspx page add listbox control like
In Code behind
<asp:DropDownList ID="dpDistributor" runat="server" Height="22px" Width="153px"
DataSourceID="LinqDataSource1" DataTextField="CompanyName" DataValueField="CompanyName"
>
In Code behind
private void FillListBox()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE");
try
{
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT * FROM TableName ", connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
ListBox1.DataTextField = "ColumnName";
ListBox1.DataValueField = "ColumnName";
ListBox1.DataSource = dt;
ListBox1.DataBind()
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack){
FillListBox();
}
}
ASKER
Hi. Thank you very much. I forgot to add that I need this in VB.net.
ASPX code same as above and below is vb code
Private Sub FillListBox()
Dim dt As New DataTable()
Dim connection As New SqlConnection("YOUR CONNECTION STRING HERE")
Try
connection.Open()
Dim sqlCmd As New SqlCommand("SELECT * FROM TableName ", connection)
Dim sqlDa As New SqlDataAdapter(sqlCmd)
sqlDa.Fill(dt)
If dt.Rows.Count > 0 Then
ListBox1.DataTextField = "ColumnName"
ListBox1.DataValueField = "ColumnName"
ListBox1.DataSource = dt
ListBox1.DataBind()
End If
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Fetch Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
connection.Close()
End Try
End Sub
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
FillListBox()
End If
End Sub
Dim cmd As New SqlCommand("SELECT * FROM [tblOne]", New SqlConnection(Configuratio nManager.A ppSettings ("ConnStri ng")))
cmd.Connection.Open()
Dim ddlValues As SqlDataReader
ddlValues = cmd.ExecuteReader()
ListBox1.DataSource = ddlValues
ListBox1.DataValueField = "theName"
ListBox1.DataTextField = "theName"
ListBox1.DataBind()
cmd.Connection.Close()
cmd.Connection.Dispose()
cmd.Connection.Open()
Dim ddlValues As SqlDataReader
ddlValues = cmd.ExecuteReader()
ListBox1.DataSource = ddlValues
ListBox1.DataValueField = "theName"
ListBox1.DataTextField = "theName"
ListBox1.DataBind()
cmd.Connection.Close()
cmd.Connection.Dispose()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much
http://geekswithblogs.net/dotNETvinz/archive/2009/02/24/binding-dropdownlist-listbox-and-checkboxlist-control-the-ado.net-way.aspx
Open in new window