Database SQL Commands
select * from tabProducts
create table tabProducts
(
Category varchar(20),
Measure varchar(20)
)
insert into tabProducts values('Solid','100 Gms');
insert into tabProducts values('Solid','200 Gms');
insert into tabProducts values('Liquid','100 ml');
insert into tabProducts values('Liquid','200 ml');
insert into tabProducts values('Countable','10');
insert into tabProducts values('Countable','20');
insert into tabProducts values('Countable','50');
create procedure sp_GetProductCategories
as
begin
select distinct category from tabProducts
end
create procedure
sp_GetMeasurementsOfSelectedCategory
@cat varchar(20)
as
begin
select measure from tabProducts where category=@cat
end
DropDownListWithDBPopulation.aspx
<%@ Page Language="C#" AutoEventWireup="true"CodeBehind="DropDownListWithAutopostback.aspx.cs"Inherits="WebDemo01.DropDownListWithDBPopulation" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h3>Inventory Details</h3>
<table style="width:auto;">
<tr>
<td>Product Name</td>
<td>
<asp:TextBox ID="txtName" runat="server" Width="170px"></asp:TextBox>
</td>
<td> </td>
</tr>
<tr>
<td>Select Product Category</td>
<td>
<asp:DropDownList ID="ddlProductCategory" runat="server" Width="180px"
AutoPostBack="True" OnSelectedIndexChanged=
"ddlProductCategory_SelectedIndexChanged">
</asp:DropDownList>
</td>
<td> </td>
</tr>
<tr>
<td>Select Measure</td>
<td>
<asp:DropDownList ID="ddlMeasure"
runat="server" Width="180px">
</asp:DropDownList>
</td>
<td> </td>
</tr>
<tr>
<td>Enter Quantity</td>
<td>
<asp:TextBox ID="txtQuanityt"
runat="server" Width="170px">
</asp:TextBox>
</td>
<td> </td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" runat="server"
Text="Enter Price"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox1"
runat="server" Width="170px"></asp:TextBox>
</td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="btnSubmit"
runat="server" Text="Submit" Width="180px" />
</td>
<td> </td>
</tr>
<tr>
<td colspan="3">
<asp:Label ID="lblMsg" runat="server"
Text="Save Status...."></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Mandatory Actions
1. Select DropDown List set AutoPostBack Property to true.
2. Double click the DropDownList to get SelectedIndexChanged Event.
DropDownListWithDBPopulation.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace WebDemo01
{
public partial class DropDownListWithDBPopulation: System.Web.UI.Page
{
protected void
Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadProductCategory();
}
}
private void
LoadProductCategory()
{
//Step1
(Connection estab)
string conStr = ConfigurationManager
.ConnectionStrings["SampleDBConStr"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
//step2
(Initialize command object with a query)
SqlCommand cmd = new SqlCommand
("sp_GetProductCategories", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlProductCategory.Items.Add(dr["category"].ToString());
}
dr.Close();
cmd.Dispose();
con.Close();
}
protected void
ddlProductCategory_SelectedIndexChanged
(object sender, EventArgs e)
{
//Step1
(Connection estab)
string conStr = ConfigurationManager
.ConnectionStrings["SampleDBConStr"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
//step2
(Initialize command object with a query)
SqlCommand cmd = new SqlCommand
("sp_GetMeasurementsOfSelectedCategory", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cat", ddlProductCategory.SelectedItem.ToString());
SqlDataReader dr = cmd.ExecuteReader();
ddlMeasure.Items.Clear();
while (dr.Read())
{
ddlMeasure.Items.Add(dr["measure"].ToString());
}
dr.Close();
cmd.Dispose();
con.Close();
}
}
}
sir, It will be more helpful if you explain how to create connection string with our database
ReplyDelete