Design of aspx page
Looking at the page design, it is normal as like other data entry screens. The first half of the screen has data entry and next half has grid view to show the records.
The aspx script goes like this
<table style="border:1px;">
<tr>
<td class="EntryHeading" colspan="2">Product Entry</td>
</tr>
<tr>
<td style="width:120px;">
Product Name:<strong class="red">*</strong><
/td>
<td style="width:280px;">
<asp:TextBox ID="txtProductName" runat="server"
CssClass="text" Width="245px" />
<asp:HiddenField ID="hidProductID" runat="server" Value="0" />
</td>
<td rowspan="7" style="width:320px;">
<img id="imgProduct" alt="" runat="server"
src="~/Images/EmptyProduct.jpg"
onclick="ShowImageWindow()"
class="ImageContainer" />
</td>
</tr>
<tr>
<td>Supplier:<strong class="red">*</strong></td>
<td>
<asp:DropDownList ID="DDLSupplier" runat="server"
DataTextField="CompanyName"
DataValueField="SupplierId"
AppendDataBoundItems="true" Width="250px">
<asp:ListItem Text="Select" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td>Category:<strong class="red">*</strong></td>
<td>
<asp:DropDownList ID="DDLCategory" runat="server"
DataTextField="CategoryName"
DataValueField="CategoryId"
AppendDataBoundItems="true" Width="250px">
<asp:ListItem Text="Select" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td>Quantity Per Unit:</td>
<td>
<asp:TextBox ID="txtQtyPerUnit" CssClass="textR"
runat="server" Width="120px" />
</td>
</tr>
<tr>
<td>Unit Price:</td>
<td>
<asp:TextBox ID="txtUnitPrice" CssClass="textR"
runat="server" Width="120px" />
</td>
</tr>
<tr>
<td>Reorder Level:</td>
<td>
<asp:TextBox ID="txtReorderLevel" CssClass="textR"
runat="server" Width="120px" />
</td>
</tr>
<tr>
<td>Discontinued:</td>
<td>
<asp:CheckBox ID="chkDiscontinued" runat="server"
Checked="false" />
</td>
</tr>
<tr>
<td>Product Image:</td>
<td colspan="2">
<asp:FileUpload ID="fileName" runat="server" style="width:80%;"
onchange="ChangeImage(this.value)" />
</td>
</tr>
<tr style="text-align:center">
<td colspan="3">
<asp:Button ID="SaveButton" runat="server" Text="Save"
Width="100px" onclick="SaveButton_Click" />
<asp:Button ID="CancelButton" runat="server" Text="Cancel"
Width="100px" onclick="CancelButton_Click" />
</td>
</tr>
</table>
<asp:GridView ID="grdViewProducts" runat="server"
AutoGenerateColumns="False" GridLines="None"
AllowPaging="True" PageSize="8" DataKeyNames="ProductID"
Width="100%" CellPadding="4" ForeColor="#333333">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:TemplateField HeaderText="Product Name" >
<ItemTemplate>
<asp:LinkButton Text='<%# Bind("ProductName") %>'
ID="lnkProductName" runat="server"
OnClick="lnkProductName_Click"
CommandArgument='<%# Bind("ProductID") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
<asp:BoundField DataField="CategoryName" HeaderText="Category" />
<asp:BoundField DataField="QuantityPerUnit"
HeaderText="Quantity Per Unit"/>
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"
ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
The javascript for the aspx page is follows:
function ChangeImage(url) {
if (url.length == 0) {
}
else {
if (document.getElementById("<%= imgProduct.ClientID %>")) {
document.getElementById("<%= imgProduct.ClientID %>").src = url;
}
}
}
function ShowImageWindow() {
popupWindow = window.open("", "popUpImage",
"location=0;status=1,scrollbars=1");
popupWindow.document.write("<img src='" +
document.getElementById("imgProduct").src + "' />");
popupWindow.document.close();
popupWindow.focus();
}
As shown in the script, I have a file upload control to select the image from the local system. I also placed an image control next to the data entry controls, which is used for showing image of the product on selection of file upload control or selection of a product from the grid view.
I have also implemented in this example, when I click the image on the image control in the web form, the system should open a popup to show the image in actual size.
One more point to say here is – I have an empty image, which is nothing but to show an image inside “Select the Product Image”. All are to beautify my screen :) (skip if you don’t want to look all those codes)
The style sheet of the page given below:
.EntryHeading
{
font-weight:bold;
font-size:14px;
text-align:center;
}
.text
{
height: 16px;
color: #2A2A2A;
border: 1px solid #5A5A5A;
font-size: 11px;
}
.textR
{
height: 16px;
color: #2A2A2A;
border: 1px solid #5A5A5A;
font-size: 11px;
text-align: right;
}
.red
{
color: #FF0000;
}
.ImageContainer
{
width:320px;
height:175px;
text-align:center;
vertical-align:middle;
background-color:gray;
border:2px;
cursor:hand;
}
Now let us see the code behind
The page_load even to refresh the dropdown box and grid records.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
FillDropDownList();
BindGrid();
hidProductID.Value = "0";
}
}
private void FillDropDownList()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"select CompanyName, SupplierID from Suppliers", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader
(CommandBehavior.CloseConnection);
DDLSupplier.DataSource = dr;
DDLSupplier.DataBind();
connection.Open();
command = new SqlCommand("select CategoryName, CategoryID
from Categories", connection);
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
DDLCategory.DataSource = dr;
DDLCategory.DataBind();
}
}
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT ProductID, ProductName, CompanyName, CategoryName, " +
"QuantityPerUnit, UnitPrice FROM Products " +
"JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
"JOIN Categories ON Products.CategoryID = Categories.CategoryID " +
"Order by ProductID desc",
connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader
(CommandBehavior.CloseConnection);
IList productViewList = new List();
while (dr.Read())
{
ProductView productView = new ProductView();
productView.ProductID = dr["ProductID"].ToString();
productView.ProductName = dr["ProductName"].ToString();
productView.CompanyName = dr["CompanyName"].ToString();
productView.CategoryName = dr["CategoryName"].ToString();
productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
productView.UnitPrice =
Convert.ToDouble(dr["UnitPrice"].ToString());
productViewList.Add(productView);
}
grdViewProducts.DataSource = productViewList;
grdViewProducts.DataBind();
}
}
public class ProductView
{
public string ProductID { get; set; }
public string ProductName { get; set; }
public string CompanyName { get; set; }
public string CategoryName { get; set; }
public string QuantityPerUnit { get; set; }
public double UnitPrice { get; set; }
}Here I wish to say one point is – I have used an additional class ProductView for binding the data to the GridView. When you bind the SqlDataReader into Gridview directly, you will be getting an error message "The data source does not support server-side data paging". To resolve the issue only I have used an entity class to make IList collection (Even I can use DataTable in this case). For more information on this error, please look at this post (linking soon).
Now let us look at the Save method, which is what the actual code comes to store the data into the database.
protected void SaveButton_Click(object sender, EventArgs e)
{
if ((fileName.PostedFile.FileName.Trim().Length > 0) &&
(fileName.PostedFile != null))
{
byte[] image = new byte[fileName.PostedFile.ContentLength];
fileName.PostedFile.InputStream.
Read(image, 0, (int)fileName.PostedFile.ContentLength);
SqlConnection connection = new SqlConnection(ConfigurationManager.
ConnectionStrings["SQLConnection"].ConnectionString);
SqlCommand command = new SqlCommand();
if (Convert.ToInt64(hidProductID.Value) == 0)
{
command.CommandText =
"INSERT INTO Products (ProductName, SupplierID, CategoryID, " +
"QuantityPerUnit, UnitPrice, ReorderLevel, Discontinued, " +
"ProductImage) VALUES (@ProductName, @SupplierID, @CategoryID," +
"@QuantityPerUnit, @UnitPrice, @ReorderLevel, @Discontinued, " +
"@ProductImage)";
}
else
{
command.CommandText =
"UPDATE Products SET ProductName = @ProductName, " +
" SupplierID = @SupplierID, " +
" CategoryID = @CategoryID, " +
" QuantityPerUnit = @QuantityPerUnit, " +
" UnitPrice = @UnitPrice, " +
" ReorderLevel = @ReorderLevel, " +
" Discontinued = @Discontinued, " +
" ProductImage = @ProductImage " +
"WHERE ProductID = " + hidProductID.Value;
}
command.CommandType = CommandType.Text;
command.Connection = connection;
PrepareSQLParameter(command, "@ProductName",
SqlDbType.VarChar, 40, txtProductName.Text);
PrepareSQLParameter(command, "@SupplierID",
SqlDbType.Int, 4, DDLSupplier.SelectedValue);
PrepareSQLParameter(command, "@CategoryID",
SqlDbType.Int, 4, DDLCategory.SelectedValue);
PrepareSQLParameter(command, "@QuantityPerUnit",
SqlDbType.NVarChar, 40, txtQtyPerUnit.Text);
PrepareSQLParameter(command, "@UnitPrice",
SqlDbType.Money, 8, txtUnitPrice.Text);
PrepareSQLParameter(command, "@ReorderLevel",
SqlDbType.Int, 2, txtReorderLevel.Text);
PrepareSQLParameter(command, "@Discontinued",
SqlDbType.Bit, 1, chkDiscontinued.Checked);
PrepareSQLParameter(command, "@ProductImage",
SqlDbType.Image, image.Length, image);
connection.Open();
int result = command.ExecuteNonQuery();
connection.Close();
BindGrid();
ClearScreen();
}
}
private SqlParameter PrepareSQLParameter
(
SqlCommand command,
string parameterName,
SqlDbType parameterType,
int parameterLength,
object parameterValue
)
{
SqlParameter parameter =
new SqlParameter(parameterName, parameterType, parameterLength);
parameter.Value = parameterValue;
command.Parameters.Add(parameter);
return parameter;
}
private void ClearScreen()
{
txtProductName.Text = "";
DDLSupplier.SelectedIndex = 0;
DDLCategory.SelectedIndex = 0;
txtQtyPerUnit.Text = "";
txtUnitPrice.Text = "";
txtReorderLevel.Text = "";
chkDiscontinued.Text = "";
imgProduct.Src = "~/Images/EmptyProduct.jpg";
hidProductID.Value = "0";
}
Here I am declaring an array of byte type to get the image stream and assigning to the parameter. The code you are required to look at the line [06 to 09] and [55, 56]
The PopulateValues method is used to retrieve the values from the database and show into entry fields with the image for a particular product. This method will be fired when clicking the hyperlink on the GridView.
protected void lnkProductName_Click(object sender, EventArgs e)
{
LinkButton lnkProductName = (LinkButton)sender;
if (Convert.ToInt32(lnkProductName.CommandArgument) > 0)
{
PopulateValues(Convert.ToInt32(lnkProductName.CommandArgument));
}
}
private void PopulateValues(int ProductID)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand("select * from Products where ProductID = '" + ProductID + "'", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
txtProductName.Text = dr["ProductName"].ToString();
DDLSupplier.SelectedValue = dr["SupplierID"].ToString();
DDLCategory.SelectedValue = dr["CategoryID"].ToString();
txtQtyPerUnit.Text = dr["QuantityPerUnit"].ToString();
txtUnitPrice.Text = dr["UnitPrice"].ToString();
txtReorderLevel.Text = dr["ReorderLevel"].ToString();
chkDiscontinued.Checked = Convert.ToBoolean(dr["Discontinued"]);
hidProductID.Value = dr["ProductID"].ToString();
if (dr["ProductImage"].ToString().Length > 0)
imgProduct.Src = string.Format(
"GetImageHandler.ashx?ProductID={0}", ProductID.ToString());
else
imgProduct.Src = "~/Images/EmptyProduct.jpg";
}
}
}
Here, to get the image from the database I have two open. 1. can have an Generic Hanlder, 2. Can have an aspx page. But In both the ways, we are writing the image stream as response to the calling place. We also required to pass necessary parameter to the aspx or ashx handlers to get the proper image from the database.
If you are planning to use Generic Handler, add a Generic Handler in the project (.ashx extension) and place the following code in ProcessRequest() event
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand("select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"] + "'", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
if (dr["ProductImage"].ToString().Length > 0)
{
context.Response.BinaryWrite((byte[])dr["ProductImage"]);
}
}
}
If you are planning to use Web Page, add a Web Form in the project (.aspx extension) and place the following code in Page_Load() event
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand("select ProductImage from Products where ProductID = '" + Request.QueryString["ProductID"] + "'", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
if (dr["ProductImage"].ToString().Length > 0)
{
Response.BinaryWrite((byte[])dr["ProductImage"]);
}
}
}
As you seen here, the image as retrieve to the calling place as a response from this aspx or ashx. So we don’t have to write any other code other then for image as response.
Also I am passing ProductID as query string to get particular product image.
Very Nice Tutorial.
changeimage function not working
This is because of browser issue. In IE 6.0, it works. But in other browsers it is not working.
Verify the below thread -
http://forums.asp.net/t/1651442.aspx/1
i want to complete code because error given