Dealing with apostrophe (‘) problem in asp.net, sql server, oracle database query

The use of apostrophe (') character as an input text in an sql query troubles the query operation. Say, you have a textbox in which user can input her search text and she inputs "john's" as her search text. Now your employee query will look like:

SELECT *FROM [Employee] WHERE Employee].Employee_Name LIKE 'john's' AND [Your other WHERE conditions]

See the problem? You are right. The database engine treats the query segment:

SELECT * FROM [Employee] WHERE [Employee].Employee_Name LIKE john

And the remaining portion will unfortunately contribute to syntax error.It is the problem and I tried my best to elaborate it. Then solution? Yeah, there exists simple but tricky one! Just replace the apostrophe (') character in the search text with double apostrophe ('') character and you are done. Let me put it simply.

string searchText = txtSearchInput.Text;
if xtSearchInput.Text.Contains("'"))
{
searchText = txtSearchInput.Text.Replace("'", "\''");
}
Happy Programming! Happy dot-netting!!

Unlock User in asp.net membership login system

The asp.net membership has the mechanism that it locks out a user's account if she tries to authenticate herself with false password five times, by default, or within 10 minute window. It is all for possible hacks. And I had no mechanism to unlock the user account. Because locked user can not login, I needed some way to unlock the user. Firstly, I was frustrated since I saw the MembershipUser class's IsLockedOut read only property. But later I came to know that there exists MembershipUser's UnlockUser() method that satisfies my requirement.

So here goes the tricky asp.net code.

MembershipUser user = Membership.GetUser(username);

user.UnlockUser();

The string username is the locked user's user name. You can use user's user name or providerKey [the user's unique id] to get the user detail in
MembershipUser object.

I just came across the solution and thought it would be a great help to those who have the same problem. Happy Programming! Happy dotnetting!!

Asp.net Application Level Error Handling from Global.asax global page

There may be tens, if not hundreds, of loop holes in an asp.net web application. It cannot be predicted which line in an .aspx.cs code behind file leads to unknown error. So to fetch the error occurred and inform the user in a friendly way, we can use the Application_Error event handler of global.asax file in asp.net web application.

Just add the global.asax file in the root of your asp.net web application. Now customize the following Application level error handler:

void Application_Error(object sender, EventArgs e)

{

Server.Transfer("~/Errors/CustomErrorPage.aspx?error=" + Server.GetLastError().Message);

}

This code will fetch the last error and redirects to the page CustomErrorPage.aspx create in Errors folder. Remember that the error message has been passed in the QueryString 'error'. The CustomErrorPage.aspx page will fetch the error from the Request Parameter and display it in the asp.net label control in its Page_Load event.

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

lblError.Text = Request.QueryString["error"].ToString();

}

}

This way you are done.

Of course, I have been blogging about custom error handling in asp.net web page from web.config file, in one of my earlier post. In that post, I devised the way to redirect to user friendly error pages when particular error occurred. I added that it was the presentation layer error handling practice. You can check the best practice of error handling in sql server 2005 stored procedure. And it was the error handling practice in the database level. We may handle erro, no doubt, but I always prefer the user friendly error handling in asp.net web page in presentation layer. The trick I am discussing in this post is all about handling error in the application level. Any errors unhandled otherwise will be caught and the user will be prompted with the error detail.

Your comment and suggestion are very valuable for this blog. You can comment this article. Further you can share this article by social-bookmarking it. Don't forget to subscribe the posts that are posted in this blog. You will get them all in your e-mail inbox! Happy programming!!

Asp.net Eval() and Bind() expressions in data bound controls like GridView and DetailsView

The asp.net Eval() and Bind() expressions are heavily used in asp.net GridView and DetailsView data bound controls. The difference between these two is that Eval is used for read only purpose and Bind is used for read/edit purpose. For example Eval can be used to bind the Text property of an asp.net Label control whereas Bind can be used to bind the asp.net TextBox control so that it can be edited to meet some requirement. Of course, asp.net GridView and DetailsView controls make large use of these Eval and Bind expressions. And I have put some cases here.

I have already talked about the DataFormatString in asp.net GridView and DetailsView controls when using Eval expression. You can take these two examples and customize these expressions as your requirement demands.

<asp:DetailsView Width="100%" ID="dvwCategory" runat="server" AutoGenerateRows="False" HeaderText="Product Category Details">    

        <FieldHeaderStyle Width="10%" />

        <Fields>

            <asp:BoundField DataField="Category_ID" HeaderText="ID" InsertVisible="false" ReadOnly="true"/>

            <asp:TemplateField HeaderText="Category Name">  

                <ItemTemplate>

                    <asp:Label ID="lblCategoryName" runat="server" Text='<%# Eval("Category_Name") %>'></asp:Label>

                </ItemTemplate>

                <EditItemTemplate>

                    <asp:TextBox ID="txtCategoryName" runat="server" Text='<%# Bind("Category_Name") %>'></asp:TextBox>

                </EditItemTemplate>

            </asp:TemplateField>

            <asp:CheckBoxField DataField="Active" HeaderText="Active"/>

        </Fields>

    </asp:DetailsView>

Then why use Eval and Bind since the BoundField can has DataField property (like DataField="Category_ID")? Yeah, it's mostly useful when we have no other choices but templatefileds in asp.net gridview and detailsview controls. We can format the date and currency in various formats, and so on. But most important is it provides us much flexibility in playing between the nature of asp.net GridView and DetailsView controls and an asp.net programmer's requirement. You can share yourself by commenting this post. You are heartily requested to share this post by bookmarking this post (You can just add by clicking in the social bookmak buttons just below this post! It's that easy! Thanks for your interest and support). Happy programming!

Asp.net Custom Error Handling from web.confing file’s customErrors section

In my previous posts, I have talked about the user friendly error handling in asp.net web application. It was focused on practicing more user friendly and interacting error handling through proper error message in well-designed user interfaces. This means the post seeds some ideas on presentation layer error handling. Further I also posted some earlier the way to asp.net web application error handling in the sql server stored procedures. It was basically related to database level error handling. There exists some great way to another effective error handling. Let me show the scenario.

There may occur a variety of cases like access denied, file not available, some unknown error etc. It would be far better if we could automatically redirect to the error page relevant to the type of error and ask the clients for excuse! Clear? Yeah, we can define the custom errors and the pages to redirect to in the web.config file. Here is the list of errors.

Error status code 400: Bad request. The request cannot be server.
Error status code 401: No authority to view the page.
Error status code 402: Page not available at this time. Payment is required.
Error status code 403: The page is forbidden.
Error status code 404: Requested page not available. (Or it does not exist, Check the URL's syntax)
Error status code 408: The request times out
Error status code 414: The request URL too long to server
Error status code 500: Internal server error has occurred.
Error status code 503: Requested service currently unavailable.

You can now make the relevant pages and prepare to point them in the asp.net web.config file. Here is the syntax:

<!--Redirect to the corresponding error page when errors occur-->

<customErrors
defaultRedirect="~/Errors/CustomErrorPage.aspx"
RemoteOnly="true">

<error statusCode="400"
redirect="~/Errors/ErrorPage400.aspx" />

<error
statusCode="401"
redirect="~/Errors/ErrorPage401.aspx" />

<error
statusCode="402"
redirect="~/Errors/ErrorPage402.aspx" />

<error
statusCode="403"
redirect="Errors/ErrorPage403.aspx" />

<error
statusCode="404"
redirect="Errors/ErrorPage404.aspx" />

<error
statusCode="408"
redirect="Errors/ErrorPage408.aspx" />

<error
statusCode="414"
redirect="Errors/ErrorPage414.aspx" />

<error
statusCode="500"
redirect="Errors/ErrorPage500.aspx" />

<error
statusCode="503"
redirect="Errors/ErrorPage503.aspx" />

</customErrors>

Put the above code within <system.web> and <system.web> sections. The error pages will be visible if the request is remote. To make the page redirections work in local domain, set the RemoteOnly false. Easy, isn't it? And more sophisticated too. At least saves from coding it everytime in each page. Great asp.net! Happy programming!!

CS0030: Cannot convert type 'ASP.login_aspx' to 'System.Web.UI.WebControls.Login'

I came to read this error: CS0030: Cannot convert type 'ASP.login_aspx' to 'System.Web.UI.WebControls.Login' in the deployed asp.net web application. Further it was working well when in the IDE Visual Studio 2005. Yeah, after having configured a virtual directory in the IIS of my machine, I had published my asp.net web application into thus configured virtual directory directly from my Visual Studio 2005 IDE. Then I tried to run it from the browser and exactly the mentioned error CS0030: Cannot convert type 'ASP.login_aspx' to 'System.Web.UI.WebControls.Login' occurred.

I searched on and found some solutions to this problem. Each of them are claimed to solve this error. I have listed them below:

You should rename your Login Page. Let this be LogIntoSite.aspx, the code behind being LogIntoSite.aspx.cs.

Add a namespace into the code behind of Login.aspx.cs. Then update the design file Login.aspx by pointing the Inherits property including this namespace. You can do it like below.
<p> <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="theLogin.Login" Title="Login to site"%>

Uncheck the 'Allow this precompiled site to be updatable' when you are publishing your asp.net web application from the Visual Studio 2005 IDE.

In my case, I followed the second trick, just because I loved the Login.aspx page's original name You can do any of the above to avoid the error. Happy programming!

Asp.net GridView DataFormatString not working

People have reported that the DataFormatString property of asp.net GridView used for formatting price {0:c} or date {0:d} etc is not working in asp.net GridView. However, they say, it works in DataGrid control. Yeah, I had the same problem. I wished to display the date in the format MM/DD/YYYY, e.g. 11/11/2008. And naturally I used the DataFormatString="{0:d}" in my GridView's property. But it did not work. So I had to go a little further. After some time, I came to know the reason.

Why asp.net GridView's DataFormatString is not working in asp.net 2.0?

It has been reported that the GridView control performs the html encoding before the data is formatted according to the DataFormatString. So the simple workaround is to disable the HtmlEncode property of the column in which DataFormatString is being applied.

<asp:gridview id="gvTest" runat="server"><columns> <asp:boundfield headertext="BirthDate" sortexpression="Birth_Date" dataformatstring="{0:d}" htmlencode="true"></columns></asp:GridView>

Now it works fine. Let me force that the same trick works for asp.net DetailsView control also. Happy Programming!

User friendly error handling in asp.net web pages

One of the beauties of an asp.net web application charms from the user friendly error handling. User friendly error handling does not mean that there be no errors but it quite means that users may not be afraid of the user error pops up to the user interface. But still, beauty of error handling does not limit to end users, but also means a lot for web masters. In one of the ways to handling errors in an asp.net web application, I once wrote an appreciable way to handling errors in SQL Server 2005 database level. Today I am describing a tiny step that can improve user experience to the transaction errors.

Fig. Displaying user friendly error messages in asp.net web page

After having displayed user level and application level errors in so many pages, I came across the idea that these should be presented in some attractive way that represent one or most of: clarity, better color combination, eligible at a glance, uniqueness and so on. So these days I use a simple error control that tries to fulfill these requirements. Please don't get puzzled. All I am doing is create a user control consisting table layout and a label, add an Image control and reference to some images that represent success (like right sign) and failure (like cross sign in red) and display appropriate one according to the success or failure in a page operation.

Here is the CustomErrorControl.aspx code

<%@
Control
Language="C#"
AutoEventWireup="true"
CodeFile="ErrorControl.ascx.cs"
Inherits="Controls_ErrorControl" %>
<table
id="tblError"
bordercolor="blue"
width="100%"
height="50px"
runat="server"
cellpadding="1"
cellspacing="1"
bgcolor="#ffffcc"
bordercolordark="#0099ff"> <tr>
<td
style="width: 20px; height: 48px"><asp:Image
ID="imgErrorSuccess"
runat="server"
/></td>
<td
style="width: 580px; height: 48px"><asp:Label
ID="lblError"
runat="server"
Font-Bold="False"
Font-Names="Arial"
Font-Size="11pt"
EnableViewState="false"></asp:Label></td>
</tr>

</table>

Here is the code behind.

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; System.Web.UI.WebControls.WebParts; System.Web.UI.HtmlControls; using System.Drawing;

public
partial
class
Controls_ErrorControl : System.Web.UI.UserControl
protected
void Page_Load(object sender, EventArgs e)
{ }
public
void ShowErrorMessage(bool success, string message)
{
if (success == true)
{

imgErrorSuccess.ImageUrl = "~/Images/success.gif"; lblError.Text = message;

lblError.ForeColor = Color.Green;

}
else
{

imgErrorSuccess.ImageUrl = "~/Images/error.gif";

lblError.Text = message;

lblError.ForeColor = Color.Red;

}

}

}

Now you can reference the user control from any page and use it's method ShowErrorMessage to display success or failure message. This method takes two parameters as inputs. First is success (bool) and another is message to be displayed (string). If [success] is true, the message passed as string will be displayed with corresponding success image, else with error image.

You can customize it to make more functional. For instance for failure I have used red message text and for success I have used green message text displays. Anyway, I just wanted to focus one of several ways to better represent error messages to users.

Fedbacks re always welcome. Happy Programming!

ObjectDataSource 'myObjDataDource' could not find a non-generic method 'myMethod' that has parameters: my_ID

Most programmers working with asp.net ObjectDataSource are bound to be irritated by this error: ObjectDataSource 'myObjDataDource' could not find a non-generic method 'myMethod' that has parameters: my_ID

 

I guess this is due to the nature the asp.net ObjectDataSource does so many things automatically. As I have described in this article that well describes how to do master detail data display using asp.net GridView and DetailsView controls by use of asp.net ObjectDataSource, we can use business objects to perform various database operations using asp.net ObjectDataSource control. All we have to do is define the correspomding methods in the business logic layer (BLL), which can be done easily using either three tier or two tier asp.net web application architecture, either involving only . business logic layer (BLL) or using both business logic layer (BLL) and Data Access Layer (DLL). So there are some points that will help detect why the "objectdatasource could not find the non-generic method" error occurs.

1. Make sure all methods [SelectMethod, UpdateMethod, DeleteMethod, SelectCountMethod, InsertMethod] used by the ObjectDataSource are typed correctly.
   
2. Check to verify the Type actually contains the above methods (mostly programmers may have misspelled them).
   
3. Third but important point: do these methods contain exact parameters as pointed in the error, both in number and order. Remember that the order of parameters mean a lot for ObjectDataSource methods. (And perhaps you too are stumbled here! At least I had this problem.)
   

Follow the above steps and you are nearest to the solution. At least my experience tells these careful steps will guide you understand the core of asp.net objectdatasource control's behavious. Please share if you have similar or advanced experiences regarding this "objectdatasource could not find the non-generic method" error.

Happy programming!

Windows Forms like Messagebox in asp.net web page

I have often come accross web pages where web programmers resent they miss the MessageBox that is available and highly used in windows programming. And for sure I am no exception. While searching for MessageBox functionality in asp.net web page, I have found one which is quite simple and useful.

Note the MessageBox function in the code snippet below. It takes a string as a parameter and pops it up in a messagebox window using asp.net Label control and javascript. For illustration of it's use, I have added a textbox with ID txtName and a button with ID btnOK. User enters his/her name in the textbox and clicks ok. If the user input contains special characters &, _ or @, the messagebox will be prompted with the message that name can not contain such charaters. If not, user's name will be prompted using same message box.

Fig: Displaying windows form like MessgeBox in asp.net web page

Here goes the MesageBox function in C#, which we can call anytime. It takes the message text (string) as input and displays as in the figure above. The only trick involved is:
1. Create a new label from code behind
2.Initialize the text of the lable to a little javascript code [which is a call to javascript's alert function].
3. Add this label to to the page's control bag.

That's all. Now lets look into the function itself.

//the message box function
    private void MessageBox(string msg)
    {
        Label lbl = new Label();

        lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";

        Page.Controls.Add(lbl);
    }

Now lets go with an example. We will have a form in which there is one textbox and one button. We will message user when undesired special character is detected in the textbox. Lets have the form markup like this [within the form].

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Label ID="Label1" runat="server" Text="Your Name:"></asp:Label>

        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>

        <asp:Button ID="Button1" runat="server" Text="OK" onclick="Button1_Click" />

    </div>

    </form>

</body>

Now is the time for the event handler on button click.

//invoked when button btnOK is clicked
    protected void Button1_Click(object sender, EventArgs e)
    {
        string name = txtName.Text;
        if (!ValidateString(name))
        {
            MessageBox("You can not put special characters _, & or @ in your name.");
        }
        else
        {
            MessageBox("Your name is" + " " + name);
        }
    }

This is just another function for test purpose only, that validates the characters in the textbox.

 //returns true if the string contains _,& or @
    //otherwise returns false
    public bool ValidateString(string str)
    {
        if((str.Contains("_") || (str.Contains("&"))||(str.Contains("@"))))
        {
            return false;
        }
        else
            return true;
    }

This MessageBox function, although a simple one can be extensively used throughout the pages, using MasterPage concept or base page concept or anything else that propagates inheritance. The only limitation is that for this method to work javascript must enabled in the browser (and you know this is the issue in all pages that use javascript). Further, for the function ValidateString, which currently checks only three special characters, you can extend it to include other special charactes also.

Your creative suggestions are always welcome! Happy programming!

Error Handling in SQL Server 2005 Stored Procedure using Try Catch Block

I am fascinated by the easy and safer way SQL Server 2005 provides for error handling. Traditionally we used @@ERROR for this purpose, along the BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION blocks. Below is one example.

Traditional Error Handling in SQL Server 2000/2005

ALTER PROCEDURE dbo.DeleteFromMyTable

AS

/* SET NOCOUNT ON */

BEGIN TRANSACTION

DELETE FROM myTable1 WHERE myColumn='myColumnValue'; --delete transaction1

IF @@ERROR>0 --some error occured

BEGIN

ROLLBACK TRANSACTION

RETURN

END

ELSE --no error has occured till now, move ahead (do some more transactions)

BEGIN

BEGIN TRANSACTION

DELETE FROM myTable2 WHERE myColumn='myColumnValue'; --delete transaction2

IF @@ERROR>0 --some error occured at this point

BEGIN

ROLLBACK TRANSACTION
--this rollbacks previous delete action also

RETURN

END

ELSE
--both the delete transactions successful !

BEGIN

--so commit the transactions

COMMIT TRANSACTION

RETURN

END

END

All these stuffs work (perfectly!). But oh! SQL Server 2005! There exists more easy and interesting way. If you are an asp.net programmer or the one who works with front end, there is exactly like what a developer faces with error handling. Guess what? You are right, it is Try..Catch block. Yes Try..Catch block has been introduced in SQL Server 2005, and it provides better way to performing transactions like above in SQL Server database.

Latest Error Handling in SQL Server 2005

ALTER PROCEDURE dbo.DeleteFromMyTable

AS

/* SET NOCOUNT ON */

BEGIN TRY

BEGIN TRANSACTION

DELETE FROM myTable1 WHERE myColumn='myColumnValue'; --delete transaction1

DELETE FROM myTable2 WHERE myColumn='myColumnValue'; --delete transaction2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 --some error has occurred

ROLLBACK TRAN --so rollback all transactions

--you raise error or fill output variable here

--for front end reference in your asp.net webpage

END CATCH

See how easy and better way? I found this Try..Catch has made sql programming like front end programming. Happy Programming!

mailto: Formatted Email Address in asp.net GridView or DetailsView Column using HyperLink control

I had an email address field in a GridView and I implemented a master detail data display using an asp.net GridView and asp.net DetailsView Controls. So I wished to display the email addresses so that one could directly email someone. I maintained to use a hyperlink and give a try to the old method, in which I added one HyperLinkField field in the columns field of an asp.net GridView, like below.

<asp:HyperLinkField
HeaderText="E-mail"
DataTextField="Email"
DataNavigateUrlFormatString="mailto:{0}"
DataNavigateUrlFields="Email"
/>

But this did not work. Using HyperLink only displays the email address but does not provide the link to mail. They say there are some security reasons associated with ImageField and HyperLink Field in the later version. So I had to go much search and finally I found it. The trick is simple. Add a template field and use a hyperlink control, like the one below.

<asp:TemplateField
HeaderText="E-mail"
SortExpression="Email">

<ItemTemplate>

<asp:HyperLink
ID="HyperLink1"
runat=server
Text='<%# Eval("Email") %>'
NavigateUrl='<%# Eval("Email", "mailto:{0}") %>'
/>

</ItemTemplate>

</asp:TemplateField>

Note: You can place your own Email Data column field in the Eval( "myFiledID") expression.

Happy Programming!!

Master Detail Data Display using an asp.net GridView and asp.net DetailsView Controls

ObjectDataSource is one of the multifeatured datacontrol in asp.net. In many ways it is similar to the SqlDataSource. For instance both provides data source that is bindable to data displaying controls like Repeater, GridView, FormsView and DetailsView. Similarly, GridView has properties like SelectCommand, InsertCommand, UpdateCommand and DeleteCommand whereas DetailsView provides SelectMethod, InsertMethod, UpdateMethod and DeleteMethod for select, insert, update and delete operations.

Fig: Showing asp.net GridView and DetailsView master detail data display using ObjectDataSource

But here lies some interesting differences. Those command propeties of GridView can be assigned with direct SQL statements. This means we can directly specify select, insert, update and delete commands. In other side, the method properties of ObjectDataSource specify the corresponding methods in the Data Access Layer (DLL) or Business Layer (BLL). All we have to do is specify the class name (along with the namespace, if the class has) in the Type property of the ObjectDataSource. Remember that this class will contain the select, insert, update and delete methods that are directly used by the ObjectDataSource.

By this time you may be thinking about parameters that have to be passed to those methods. Yes, you can specify select, insert, update and delete parameters in the ObjectDataSouce. But all these stuffs are so easy if your are working with GridView, DetailsView, FormsView or Repeater. I have worked in a project using GridView and DetailsView master detail data display using two different ObjectDataSource, one for asp.net GridView and another for asp.net DetailsView.

Since I have well architected three tier architecture in my asp.net web application, I have used the Business Logic Layer (BLL) methods in the ObjectDataSource. I want to share this experiece with you in this article.

To perform the master detail data display in asp.net, we will use GridView as the master data control and DetailsView as the detail data control. First of all lets define a class Category with the public properties Category_ID, Category_Name, Active (if this item is actively displayed in user interfaces). This very class will contain the necessary methods required by the ObjectDataSources.

Category Business Layer and Data Access Layer classes

Category BLL Class

//redefine the public properties and call to classes of DAL

//I have left this part to you to make it short

Category DAL Class

namespace MyTest.DAL {
public class Category {
public Category() { }
public Category(int id, string categoryName, bool isActive) {
this.Category_ID = id;
this.Category_Name = categoryName;
this.Active = isActive;

}

private int _category_id = 0;
public int Category_ID {
get { return _category_id;}
set { _category_id = value; }
}

private string _category_Name = ""; public string Category_Name { get { return _category_Name; }
set { _category_Name = value; } }

private bool _active;

public bool Active {
get { return _active; }
private set { _active = value; } }

//methods that work with Category
public abstract List<CategoryDetails> GetCategories(string sortExpression, int pageIndex, int pageSize);
public abstract CategoryDetails GetCategoryByID(int categoryID);
public abstract int GetCategoryCount();
public abstract bool DeleteCategory(int categoryID);
public abstract int InsertCategory(CategoryDetails category);
public abstract bool UpdateCategory(CategoryDetails category);
}
}

GridView, DetailsView and ObjectDataSources in asp.net page (ManageCategory.aspx)

<asp:GridView
ID="gvCategories"
runat="server"
AutoGenerateColumns="False"
DataSourceID="objAllCategories"
Width="80%"
DataKeyNames="Category_ID"
OnRowDeleted="gvCategories_RowDeleted"
OnRowCreated="gvCategories_RowCreated"
OnSelectedIndexChanged="gvCategories_SelectedIndexChanged"
AllowSorting="True"
AllowPaging="True"
PageSize="10">
<Columns>
<asp:BoundField
DataField="Category_Name"
HeaderText="Category"
SortExpression="Category_Name"
/> <asp:CheckBoxField
DataField="Active"
HeaderText="Is Active"
> <ControlStyle
Width="5px" />
<ItemStyle
Width="20px"
/>
</asp:CheckBoxField>
<asp:CommandField
ButtonType="Image"
SelectImageUrl="~/Images/Edit.gif"
SelectText="Edit category"
ShowSelectButton="True"> <ItemStyle
HorizontalAlign="Center"
Width="20px"
/>
</asp:CommandField>
<asp:CommandField
ButtonType="Image"
DeleteImageUrl="~/Images/Delete.gif"
DeleteText="Delete category"
ShowDeleteButton="True"> <ItemStyle
HorizontalAlign="Center"
Width="20px"
/>
</asp:CommandField>
</Columns>
<EmptyDataTemplate><b>No categories to show</b></EmptyDataTemplate> </asp:GridView>
<asp:ObjectDataSource
ID="objAllCategories"
runat="server"
SelectMethod="GetCategories"
TypeName="MyTest.BLL.Category"
DeleteMethod="DeleteCategory"
SortParameterName="sortExpression"
EnablePaging="true"
SelectCountMethod="GetCategoryCount">
</asp:ObjectDataSource> <p></p>
<asp:DetailsView
Width="80%"
ID="dvwCategory"
runat="server"
AutoGenerateRows="False"
DataSourceID="objCurrCategory"
Height="50px"
AutoGenerateEditButton="True"
AutoGenerateInsertButton="True"
HeaderText="Product Category Details"
OnItemInserted="dvwCategory_ItemInserted"
OnItemUpdated="dvwCategory_ItemUpdated"
OnItemCreated="dvwCategory_ItemCreated"
DefaultMode="Insert"
OnItemCommand="dvwCategory_ItemCommand"
DataKeyNames="Category_ID"> <FieldHeaderStyle
Width="40%"/> <Fields>
<asp:BoundField
DataField="Category_ID"
HeaderText="ID"
InsertVisible="false"
ReadOnly="true"/>
<asp:TemplateField
HeaderText="Category">
<ItemTemplate>
<asp:Label
ID="lblCategoryName"
runat="server"
Text='<%# Eval("Category_Name") %>'></asp:Label>

</ItemTemplate>
<EditItemTemplate>
<asp:TextBox
ID="txtCategoryName"
runat="server"
Text='<%# Bind("Category_Name") %>'
MaxLength="256"
Width="98%"></asp:TextBox>
<asp:RequiredFieldValidator
ID="valRequireTitle"
runat="server"
ControlToValidate="txtCategoryName"
SetFocusOnError="true"
Text="The Category field is required."
ToolTip="The Category field is required."
Display="Dynamic"></asp:RequiredFieldValidator>

lt;/EditItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField
DataField="Active"
HeaderText="Active"/>
</Fields>
</asp:DetailsView>
<asp:ObjectDataSource
ID="objCurrCategory"
runat="server"
InsertMethod="InsertCategory"
SelectMethod="GetCategoryByID"
UpdateMethod="UpdateCategory"
TypeName="MyTest.BLL.Category">
<SelectParameters>
<asp:ControlParameter
ControlID="gvCategories"
Name="Category_ID"
PropertyName="SelectedValue"
Type="Int32"
/> </SelectParameters>
</asp:ObjectDataSource>

Presentation Layer Backend (ManageCategory.aspx.cs)

(only required events have been listed)

protected void gvCategories_SelectedIndexChanged(object sender, EventArgs e) { dvwCategory.ChangeMode(DetailsViewMode.Edit); }

protected void gvCategories_RowDeleted(object sender, GridViewDeletedEventArgs e)
{ gvCategories.SelectedIndex = -1; gvCategories.DataBind(); dvwCategory.ChangeMode(DetailsViewMode.Insert);

}

protected void gvCategories_RowCreated(object sender, GridViewRowEventArgs e) {
if (e.Row.RowType == DataControlRowType.DataRow) {
ImageButton btn = e.Row.Cells[4].Controls[0] as ImageButton;
btn.OnClientClick = "if (confirm('Are you sure you want to delete this category?') == false) return false;";
}
}

protected void dvwCategory_ItemInserted(object sender, DetailsViewInsertedEventArgs e{
gvCategories.SelectedIndex = -1;
gvCategories.DataBind();
}

protected void dvwCategory_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e) {
gvCategories.SelectedIndex = -1;
gvCategories.DataBind();
}

protected void dvwCategory_ItemCreated(object sender, EventArgs e){ //enforce logic to the items created
//before it can be stored to database
//or used anywhere.
}

protected void dvwCategory_ItemCommand(object sender, DetailsViewCommandEventArgs e) {
if (e.CommandName == "Cancel")
{
gvCategories.SelectedIndex = -1;
gvCategories.DataBind();
}
}

By this time, you have displayed the master detail data using asp.net GridView control and asp.net DetailsView control with the use of ObjectDatasource data control. More interestingly, we have also observed architected a three tier asp.net application structure.

Happy programming!

Move Items from one ListBox to another ListBox in asp.net web application

Movement of items from one ListBox to another ListBox in asp.net is a typical issue sometimes programmers face. However in windows application it seems to be a common practice. It is due to the postback nature of asp.net web page. But we can do it. In my case I had to explore the situation in one of my projects where I had to convert a windows based project into web based one. Anyway I have presented below the way I use to code to move items from one asp.net ListBox control to another asp.net ListBox control.

Fig: Moving items from one asp.net ListBox control to another asp.net ListBox control

The left side ListBox in the above figure is listBoxFrom and another is listBoxTo. The top textbox is txtAddItem and top button is btnAddItem. The textbox and the button are used to add new items into the left ListBox. The remove button is used to remove the selected item from the ListBox .

Add Item in Source ListBox

public void AddItemInSourceListBox()
{
//do not let add empty into the source listbox
if (txtAddItem.Text == "")
{
return;
}

ListItem item = new ListItem();
item.Value = txtAddItem.Text;
item.Text = txtAddItem.Text;

listBoxFrom.Items.Add(item);
}

We have two buttons to move items left and right as indicated by the arrows in the button text. The left button is btnAdd and the right button is btnRemove. We should handle the button click event handler to move items from one ListBox to another.

//when btnAdd i.e. the button with double left arrow is clicked
//this moves item from source ListBox to destination ListBox
protected void btnAdd_Click(object sender, EventArgs e)
{
if (listBoxFrom.SelectedIndex == -1)
{
return;
}

ListItem item = new ListItem();
item.Value = listBoxFrom.SelectedItem.Value;
item.Text = listBoxFrom.SelectedItem.Text;

listBoxTo.Items.Add(item);
listBoxFrom.Items.Remove(item);

}

//when btnRemove i.e. the button with double right arrow is clicked
//this moves item from destination ListBox to source ListBox

protected void btnRemove_Click(object sender, EventArgs e)
{
if (listBoxTo.SelectedIndex == -1)
{
return;
}

ListItem item = new ListItem();
item.Value = listBoxTo.SelectedItem.Value;
item.Text = listBoxTo.SelectedItem.Text;

listBoxFrom.Items.Add(item);
listBoxTo.Items.Remove(item);
}

Happy Programming!

Fill asp.net DropdownList and ListBox controls with DataSource programmatically

DropdownList and Listbox both are asp.net data controls. They can be directly bound to a data source. But often programmers choose to fill them dynamically using code (in C# or VB.NET !).

I am listing the simple trick to programmatically bind asp.net DropdownList and ListBox controls.

Lets say myTable is the DataTable filled from the method FillMyDataTable. Now we can
start out the journey.

public void FillDropdownList()
{
DataTable table = new DataTable();
table = FillMyDataTable(); //a method that returns required DataTable in our case
dropdownlistDDL.DataSource = table;
dropdownlistDDL.DataValueField = "myID_Column";
dropdownlistDDL.DataBind();
}

Here dropdownlistDDL is the DropdownList and myID_Column is the field to be filled in
our DropdownList.

Now we are going to bind an asp.net ListBox control to the DataTable.

public void FillListBox()
{
DataTable table = new DataTable();
table = FillMyDataTable(); //a method that returns required DataTable in our case
listboxLB.DataSource = table;
listboxLB.DataValueField = "myID_Column";
listboxLB.DataBind();
}

Here listboxLB is the ListBox and myID_Column is the field to be filled in our ListBox.

We see that both the asp.net ListBox and DropdownList controls follow the same pattern
of binding to a data source programmatically.

Happy Programming!

Create unique id in oracle table using trigger and sequence

In one of my recent projects, I had to insert into a table where one column had to be unique and auto generated. Further I also had to maintain the data so that it was in serial with previously inserted data.

In oracle this can be accomplished by the combined use of Trigger and Sequence. I am going to describe the technique in this post.

Sequence: A sequence can generate unique number. User can limit the minimum and maximum value and also the starting value of the generated number. In other words a sequence is like a unique number generator whose nature can be controlled by a user. One sequence can be maintained per table so that we can provide unique and serial id for each table.

Trigger: A trigger can be fired before insert, update or delete operations in any table. This can be designed to help apply business logic while using data manipulations using insert, update and delete plsql programming.

Create a sequence

CREATE SEQUENCE SEQ_EMPLOYEE INCREMENT BY 1 START WITH 10000 MINVALUE 10000 MAXVALUE 99999 NOCYCLE NOCACHE NOORDER;

Create a Trigger on Employee Table

CREATE OR REPLACE TRIGGER TRIGGER_EMPLOYEE
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
begin
select SEQ_EMPLOYEE.nextval into :new.EMPLOYEE_ID from dual;
end;

Insert into Employee Table

INSERT INTO Employee (Name, Address, Post)
VALUES ('sangam','New Road-10', 'Software Developer')

Note: Since the trigger fetches unique number from sequence and inserts into Employee_id field, we need not to put this column in Insert statement as above.

Happy Oracling!!

Resources for Cascading Style Sheet (CSS)

I am not as good desinger as I am a programmer. But if you work with web applications there always comes the necessity of going hand in hand with the web page design. I have also come across the same requirement. Initially, like most of the programmers I also thought that design part should be out of a programmer's headache. But as more time passes I become more loyal to the designer's part. It is the nature of a programmer, especially web programmes.

While talking about design we can not forget the role of Cascading Style Sheet (CSS). In the beginning days I started out trying the existing web templates. They taught me a lot. Later I felt the necessity of a little bit knowledge of CSS language. I have searched through the Internet and found some useful resources. I have benifited from them. So I wanted to share the resources.

Resources for Cascading Style Sheet (CSS)

http://www.webreference.com/authoring/style/sheets/css_mastery/
This part gives detailed approach to CSS page layout.

http://blog.html.it/layoutgala/
Check this link out for 40 CSS based HTML pages in a zip file. One can then reuse them for their own requirement.

http://www.dustindiaz.com/css-shorthand/
A CSS shorthand guide better for quick reference.

http://lesliefranke.com/files/reference/csscheatsheet.html
A CSS cheat sheet covering major parts of CSS.

There are lots of resources for CSS in Internet. I think it is better to start out with the simple and basic ones.

Happy designing!

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

I have oracle database installed in my computer. My Operating system is XP. I wrote an application in asp.net. It connected to oracle database. I had the connection string in web.config where I used the OracleClient as the provider.

(Fig: Connection string with provider as OracleClient )

But I could not connect to the database. A connection error occured. The error speaks:
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater
But the odd is that I could easily connect to the database using OLEDB provider.

(Fig: Connection string with OLEDB provider)

I pasted the error statement in the google search textbox. I got almost same solutionfrom most of the sites. I will state the solution as suggested by the sites and put my experiences along with it.

Solution
Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:

1. Log on to Windows as a user with Administrator privileges.

2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME folder. This is typically the "Ora92" folder under the "Oracle" folder (i.e. D:\Oracle\Ora92).

3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.

4. Click on the "Security" tab of the "Properties" window.

5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").

6. Uncheck the "Read and Execute" box in the "Permissions" list under the "Allow" column (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users").

7. Re-check the "Read and Execute" box under the "Allow" column (this is the box you just unchecked).

8. Click the "Advanced" button and in the "Permission Entries" list make sure you see the "Authenticated Users" listed there with:
Permission = Read & Execute Apply To = This folder, subfolders and files

If this is NOT the case, edit that line and make sure the "Apply onto" drop-down box is set to "This folder, subfolders and files". This should already be set properly but it is important that you verify this.

9. Click the "Ok" button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.

10. Reboot your computer to assure that these changes have taken effect.
Re-execute the application and it should now work.

Thank you Paul for the solution.

My Experiences-I got the problem in Oracle 8i.-I use Windows xp. My ORACLE_HOME is ora81 folder. When I select the properties, Iget the following window.

(Fig. When I right click and select properties of Ora81 folder in Windows XP)

As we can see there is no security tab. I tried the Web Sharing tab but with vain. So I had to follow the following steps to view the security tab.

1. Open the C:/ folder.

2. From Tools menu and click Folder Options.

3. The Folder Options window opens. Click the View tab.

4. In the advanced setting go to the last check box- Use simple file sharing(Recommended) and uncheck it.

5. Click apply and then Ok.

(Fig: How to see security tab in properties of the folder Ora81 in Windows XP)

Now again view the properties of Ora81 folder you will see the security tab.

(Fig: Security tab displayed in folder properties in windows xp)

I could see the security tab but there was no Authenticated User group.
So I added it using the Add button. And I followed all above mentioned steps.But the problem persisted.

My Solution

I just completely uninstalled the Oracle product from my PC. Then I reinstalled it.To my surprise it worked now.

I am surprised now, is to reinstall the oracle database the only feasible solution?May someone listen and reply me.

Meantime, you may be interested in object relational features of Oracle implemented using a simple yet practical ecommerce database.

Happy dot-netting! Happy Oracling!!