Read data from SQL server and fill asp:dropdownlist (C#) : SqlServer « ADO.net Database « ASP.NET Tutorial

Home
ASP.NET Tutorial
1.ASP.Net Instroduction
2.Language Basics
3.ASP.net Controls
4.HTML Controls
5.Page Lifecycle
6.Response
7.Collections
8.Validation
9.Development
10.File Directory
11.Sessions
12.Cookie
13.Cache
14.Custom Controls
15.Profile
16.Configuration
17.LINQ
18.ADO.net Database
19.Data Binding
20.Ajax
21.Authentication Authorization
22.I18N
23.Mobile
24.WebPart
25.XML
ASP.NET Tutorial » ADO.net Database » SqlServer 




File: Default.aspx

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:label id="Label1" 
                   runat="server" 
                   Width="120px" 
                   Height="20px">Select Author:</asp:label>&nbsp;
    <asp:dropdownlist id="lstAuthor" 
                      runat="server" 
                      Width="256px" 
                      Height="22px" 
                      AutoPostBack="True" 
                      onselectedindexchanged="lstAuthor_SelectedIndexChanged">
    </asp:dropdownlist>
        <br />
    <asp:label id="lblResults" runat="server" Width="384px" Height="168px">
    </asp:label>
  </div>
    </form>
</body>
</html>

File: Default.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Web.Configuration;
using System.Data.SqlClient;
using System.Text;

public partial class AuthorBrowser : System.Web.UI.Page
{
    private string connectionString = WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            FillAuthorList();
        }
    }

    private void FillAuthorList()
    {
        lstAuthor.Items.Clear();
        string selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataReader reader;

        try
        {
            con.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ListItem newItem = new ListItem();
                newItem.Text = reader["au_lname"", " + reader["au_fname"];
                newItem.Value = reader["au_id"].ToString();
                lstAuthor.Items.Add(newItem);
            }
            reader.Close();
        }
        catch (Exception err)
        {
            lblResults.Text = "Error reading list of names. ";
            lblResults.Text += err.Message;
        }
        finally
        {
            con.Close();
        }
    }

    protected void lstAuthor_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectSQL;
        selectSQL = "SELECT * FROM Authors ";
        selectSQL += "WHERE au_id='" + lstAuthor.SelectedItem.Value + "'";

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataReader reader;

        try
        {
            con.Open();
            reader = cmd.ExecuteReader();
            reader.Read();

            StringBuilder sb = new StringBuilder();
            sb.Append("<b>");
            sb.Append(reader["au_lname"]);
            sb.Append(", ");
            sb.Append(reader["au_fname"]);
            sb.Append("</b><br />");
            sb.Append("Phone: ");
            sb.Append(reader["phone"]);
            sb.Append("<br />");
            sb.Append("Address: ");
            sb.Append(reader["address"]);
            sb.Append("<br />");
            sb.Append("City: ");
            sb.Append(reader["city"]);
            sb.Append("<br />");
            sb.Append("State: ");
            sb.Append(reader["state"]);
            sb.Append("<br />");
            lblResults.Text = sb.ToString();

            reader.Close();
        }
        catch (Exception err)
        {
            lblResults.Text = "Error getting author. ";
            lblResults.Text += err.Message;
        }
        finally
        {
            con.Close();
        }

    }
}

File: Web.config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="Pubs" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=SSPI"/>
  </connectionStrings>
</configuration>














18.42.SqlServer
18.42.1.Connect to SQL server with integrated Authentication or SQL Authentication
18.42.2.Execute delete command with SqlCommand against SqlServer
18.42.3.You can connect to a Local database named MyLocalData.mdf by using the following connection string:
18.42.4.For example, the following connection string enables you to connect to a Server database named MyData:
18.42.5.You use SQLCMD by opening a command prompt and connecting to your database with the following command:
18.42.6.Read data from SQL server and fill asp:dropdownlist (C#)
18.42.7.Handle table relationship (C#)
18.42.8.Insert, update and delete (C#)
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.