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!