Mega Code Archive
Table record editor by DropDownList and ListBox
<![CDATA[
File: index.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="RecordEditor" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Record Editor</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="lstProduct"
runat="server"
AutoPostBack="True"
Width="280px"
OnSelectedIndexChanged="lstProduct_SelectedIndexChanged">
</asp:DropDownList>
<table>
<tr>
<td>
<asp:Label ID="lblRecordInfo" runat="server"></asp:Label></td>
<td>
<asp:Panel ID="pnlCategory" runat="server" Visible="False">
<asp:ListBox ID="lstCategory"
runat="server"
Height="120px"
Width="152px">
</asp:ListBox>
<asp:Button ID="cmdUpdate"
runat="server"
Text="Update"
OnClick="cmdUpdate_Click" />
</asp:Panel>
</td>
</tr>
</table>
</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;
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 = "<b>Product:</b> " + reader["ProductName"] + "<br />";
lblRecordInfo.Text += "<b>Quantity:</b> " + reader["QuantityPerUnit"] + "<br />";
lblRecordInfo.Text += "<b>Category:</b> " + 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
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
]]>