Table-record-editor-by-DropDownList-and-ListBox


            File: index.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="RecordEditor" %>


    Record Editor


    
    
                                  runat="server" 
                          AutoPostBack="True" 
                          Width="280px" 
                          OnSelectedIndexChanged="lstProduct_SelectedIndexChanged">
        
        
            
                
                
            
        
                     
                
                                                     runat="server" 
                                 Height="120px" 
                                 Width="152px">
                    
                                                    runat="server" 
                                Text="Update" 
                                OnClick="cmdUpdate_Click" />
                
                

        

    


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;
public partial class RecordEditor : System.Web.UI.Page
{
    private string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            string selectSQL = "SELECT ProductName, ProductID FROM Products";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(selectSQL, con);
            con.Open();
            lstProduct.DataSource = cmd.ExecuteReader();
            lstProduct.DataTextField = "ProductName";
            lstProduct.DataValueField = "ProductID";
            lstProduct.DataBind();
            con.Close();
            lstProduct.SelectedIndex = -1;
        }
    }
    protected void lstProduct_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectProduct = "SELECT ProductName, QuantityPerUnit, " +
         "CategoryName FROM Products INNER JOIN Categories ON " +
         "Categories.CategoryID=Products.CategoryID " +
         "WHERE ProductID=@ProductID";
        
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmdProducts = new SqlCommand(selectProduct, con);
        cmdProducts.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmdProducts.ExecuteReader();
            reader.Read();
            lblRecordInfo.Text = "Product: " + reader["ProductName"] + "";
            lblRecordInfo.Text += "Quantity: " + reader["QuantityPerUnit"] + "";
            lblRecordInfo.Text += "Category: " + reader["CategoryName"];
            string matchCategory = reader["CategoryName"].ToString();
            reader.Close();
            string selectCategory = "SELECT CategoryName, CategoryID FROM Categories";
            SqlCommand cmdCategories = new SqlCommand(selectCategory, con);
            lstCategory.DataSource = cmdCategories.ExecuteReader();
            lstCategory.DataTextField = "CategoryName";
            lstCategory.DataValueField = "CategoryID";
            lstCategory.DataBind();
            lstCategory.Items.FindByText(matchCategory).Selected = true;
        }
        pnlCategory.Visible = true;
    }
    protected void cmdUpdate_Click(object sender, EventArgs e)
    {
        string updateCommand = "UPDATE Products SET CategoryID=@CategoryID WHERE ProductID=@ProductID";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(updateCommand, con);
        cmd.Parameters.AddWithValue("@CategoryID", lstCategory.SelectedItem.Value);
        cmd.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}
File: Web.config
Data Binding ASP.Net Tutorial
