Find out the Binary, ASCII and Character of a Given String in SQL Server

When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.

Here’s the query:

DECLARE @MyAddress varchar(35)
SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343'

DECLARE    @BIN AS VARBINARY(100)
SET    @BIN = convert(varbinary(100),@MyAddress)

SELECT    SUBSTRING(@BIN, Number, 1) AS Binary,
ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII,
CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character
FROM    master..spt_values
WHERE    Type = 'p'
AND Number BETWEEN 1 AND DATALENGTH(@BIN)

Using JavaScript for Validation of Different Date Formats in Asp.NET Web Form

Most often asp.net web applications need to validate date in a form. It is not that easy to play with different format of dates at different places. Mostly the mm/dd/yyyy (alternatively, mm-dd-yyyy) format of date is used. But we may need to go dd/mm/yyyy (alternatively, dd-mm-yyyy) also, and yyyy/mm/dd (alternatively, yyyy-mm-dd) format is no exception too. This article will discuss how to validate date in all these formats using simple yet powerful javascript code snippet.

Date of Purchase: <asp:textbox id="txtPurchaseDate" runat="server"></asp:TextBox><br /><cc1:calendarextender id="CalendarExtenderTo" format="dd-MM-yyyy" targetcontrolid="txtPurchaseDate" popupbuttonid="imgCalendarTo" runat="server"></cc1:CalendarExtender><br /><img id="imgCalendar" runat="server" src="SmallCalendar.gif" border="0" /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclientclick="ValidateForm();"> Remember, we need ajax enabled page to use the calendar extender. Note that I have set the format in the calendarextender to dd-MM-yyyy. This will put the date in the targetcontrol (here, txtPurchaseDate) in dd-mm-yyyy format. On client click of the button btnSubmit, the javascript function ValidateForm() will validate the date in the textbox. If it is not in dd-mm-yyyy format, a message will be alerted as in the figures above. Here are the functions Validateform() and validateInputDate(). function ValidateForm() { if(document.getElementById("txtPurchaseDate")) { if(validateInputDate(document.getElementById("txtPurchaseDate").value)==false) { alert("Please input purchase date in the format dd-mm-yyyy."); return false; } } } //Validates date in the format dd-mm-yyyy (e.g. 19-10-2009) function validateInputDate(dateString){ if (dateString.match(/^(?:(0[1-9][12][0-9]3[01])[\-.](0[1-9]1[012])[\-.](1920)[0-9]{2})$/)) { return true; } else { return false; } } //Validates date in the format dd/mm/yyyy (e.g. 19/10/2009) function validateInputDate(dateString){ if (dateString.match(/^(?:(0[1-9][12][0-9]3[01])[\/.](0[1-9]1[012])[\/.](1920)[0-9]{2})$/)) { return true; } else { return false; } } //Validates date in the format dd-mm-yyyy or dd/mm/yyyy (e.g. 19-10-2009 or 19/10/2009) function validateInputDate(dateString){ if (dateString.match(/^(?:(0[1-9][12][0-9]3[01])[\- \/.](0[1-9]1[012])[\- \/.](1920)[0-9]{2})$/)) { return true; } else { return false; } } // Validates date in the format mm/dd/yyyy (e.g. 10/19/2009) function validateInputDate(dateString){ if (dateString.match(/^(?:(0[1-9]1[012])[\/.](0[1-9][12][0-9]3[01])[\/.](1920)[0-9]{2})$/)) { return true; } else { return false; } } // Validates date in the format yyyy-mm-dd or yyyy/mm/dd (e.g. 2009-10-19 or 2009/10/19) function validateInputDate(dateString){ if (dateString.match(/^(?:(1920)[0-9]{2})[\- \/.](0[1-9]1[012])[\- \/.](0[1-9][12][0-9]3[01])$/)) { return true; } else { return false; } } // Validates date in the format yyyy-mm-dd (e.g. 2009-10-19) function validateInputDate(dateString){ if (dateString.match(/^(?:(1920)[0-9]{2})[\-.](0[1-9]1[012])[\-.](0[1-9][12][0-9]3[01])$/)) { return true; } else { return false; } } // Validates date in the format yyyy/mm/dd (e.g. 2009/10/19) function validateInputDate(dateString){ if (dateString.match(/^(?:(1920)[0-9]{2})[\/.](0[1-9]1[012])[\/.](0[1-9][12][0-9]3[01])$/)) { return true; } else { return false; } }

22 Visual Studio Short Keys and 6 Short-cut Ways to Custom Jobs: A List of Tips and Tric

Efficient programmers use short keys in IDE like Visual Studio. This saves time and in many cases makes the work run faster also. I also love short keys. They are smart! And there also go some tricks that help make your visual studio days a party! I have listed here some short cuts and some tips with the hope that this will be helpful to you all. Visual Studio Short-cuts 1. Ctrl+F5 to run the website without debugging. F5 only will run the website in debugging mode. 2. Stop Debugging: Shift+F5 3. Restart Debugging: Ctrl+Shift+F5 4. Break All: Ctrl+Alt+Break 5. F11 to debug line by line downwards from the toggle point. This works with F10 also. In fact you can see F10 assigned for this task. See Debug menu in the visual studio. 6. F9 to toggle a break point. 7. To delete all the break points at once: Ctrl+Shift+F9. If you only wish to disable the break points you can go down the Debug menu. 8. Ctrl+S to save the current document. Ctrl+Shift+S to save all the documents at once. 9. Print a document directly from the visual studio? Press Ctrl+P. This will bring the print dialog. 10. Ctrl+F to find a text. A find dialog will be displayed. Then you are left with a number of options. 11. Ctrl+H or Ctrl+Shift+H for find and replace. 12. Ctrl+G to go to the specific line number in the current document. 13. Open the web browser right from within the visual studio? Press Ctrl+Alt+R. 14. Irritated switching back and forth the design view and code behind page of a file? Use F7 to navigate back and forth the files of a page. 15. Press F4 to view the properties window for a control from the design page. 16. To view the server explorer: Ctrl+Alt+S 17. To view the solution explorer: Ctrl+Alt+L 18. To view the output window: Ctrl+Alt+O 19. To view the toolbox: Ctrl+Alt+X 20. Press Shift+Alt+Enter to view/undo full screen. 21. Press Ctrl+O to open a document from the project folder. 22. Ctrl+F11 to view disassembly: the machine level code! Visual Studio Tips 1. To view the start page, navigate View->Other Windows->Start Page 2. To initiate the start page to display feed from a website, Tools->Options->Startup. Choose show start page at start from the combo fox. Input the start page news channel rss feed. Click Ok. 3. To display line numbers in the documents, Tools->Options->Text Editor. Now click the editor you need to display number in. Check Line Numbers at the right side of the window. 4. Initiate the home page for the web browser [opened by pressing Ctrl+Alt+R]? Follow Tools->Options->Environment->Web Brower. Set the home page and optionally the search page from the right side of the window. 5. Know the class name or method name but confused the right namespace? Right click the class name or method name and click Resolve; you will see the reference you need to add. Click the reference. It will be added for the page. 6. Using find window and clicking Find button each time to go to next match? Leave the trend. Just press F3 to go to next match!! Shortcuts are the small but useful ladders of programming. You can use them to go up and down, left and right or south and north. I would like to heartily welcome to share your tips and tricks that you have learned from your dedicated play around with the visual studio. All the tips and shortcuts I have listed above are tested and learned from visual studio 2005. They may or may not work in visual studio 2008 and later versions. You can specify the compatibility of your trick that you are posting as comment in this article. Thank you very much. Happy Programming!

How to detect browser using JavaScript

var isIE = false; var isFF = false; var isOP = false; var isSafari = false; function DetectBrowser() { var val = navigator.userAgent.toLowerCase(); if(val.indexOf("firefox") > -1) { isFF = true; } else if(val.indexOf("opera") > -1) { isOP = true; } else if(val.indexOf("msie") > -1) { isIE = true; } else if(val.indexOf("safari") > -1) { isIE = true; } }

Generating Unique Keys in .Net

Introduction :

I have few applications where I need to create some unique IDs. GUIDs are great as they give Globally Unique identifier but they are big. I mean if you want to issue unique number in your application which you want to give as Booking Number or any reference number then GUIDs is obviously not a solution. Therefore, I need some simple Id which is unique too. For example when I send a request to my Credit Card Processor there’s an ID that correlates my invoice with the transaction at the provider. A GUID isn’t what I’d want here.

But one relatively simple solution is to create sequential ID which will be of appropriate size as well as it will guarantee uniqueness. Very Good and Simple solution!!! Isn’t it !! My answer would be NO !! Because it is security threat for your website. In case of Web Application where you can Retrieve your Order or Booking Details by just giving Order Reference number, you can easily BruteForce Sequential Reference numbers to retrieve records.

In this article I will discuss some of techniques.
Using DateTime and HashCode:

Using DateTime for generating unique keys is very common practice. I have remixed this approach by inducing HashCode in it also. Like

DateTime.Now.ToString().GetHashCode().ToString(”x”);

It will give you some key like 496bffe0. At the first glance it seems to be satisfied Unique key as its using current time and hashing to generate key but GetHashCode() doesn’t procduce unique code everytime. Althought Microsoft is using Double Hashing algorithms with N Number of collision resolving double hash function but during my experimentation I found lot of collisions.

Using GUIDs and HashCode:
So then I tried
Guid.NewGuid().ToString().GetHashCode().ToString(”x”);

It gives key something like 649cf2e3.Somehow I don’t think that this string representation at least is unique… 38 characters represented as 8? Ok 32 bits, but still it’s 8 digits and characters limited to hex values and yes my doubt got right as I wrote program which generated 100,000 keys and checked it for collisions and found several keys duplicated.

Using RNGCryptoServiceProvider and Character Masking :

The .net Framwork provides RNGCryptoServiceProvider class which Implements a cryptographic Random Number Generator (RNG) using the implementation provided by the cryptographic service provider (CSP). This class is usually used to generate random numbers. Although I can use this class to generate unique number in some sense but it is also not collision less. Moreover while generating key we can make key more complicated by making it as alpha numeric rather than numeric only. So, I used this class along with some character masking to generate unique key of fixed length.

Below is code sample:

 private string GetUniqueKey()
        {
            int maxSize = 8;
            int minSize = 5;
            char[] chars = new char[62];
            string a;
            a = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
            chars = a.ToCharArray();
            int size = maxSize;
            byte[] data = new byte[1];
            RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
            crypto.GetNonZeroBytes(data);
            size = maxSize;
            data = new byte[size];
            crypto.GetNonZeroBytes(data);
            StringBuilder result = new StringBuilder(size);
            foreach (byte b in data)
            {
                result.Append(chars[b % (chars.Length - 1)]);
            }
            return result.ToString();
        }

Analysis shows that RNGCrypto with Character Masking is best method to generate Unique keys.

How to retrieve the identity value when inserting a record into a Microsoft SQL Server table

A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.

Why @@IDENTITY and IDENT_CURRENT should not be used

Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.

Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.

How and why to use SCOPE_IDENTITY

Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.

CREATE PROCEDURE [dbo].[InsertCases] @CaseID int output, @CaseName nvarchar(255), @CaseDescription ntext AS SET NOCOUNT ON INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription]) VALUES (@CaseName,@CaseDescription) SET @CaseID=SCOPE_IDENTITY() SET NOCOUNT OFF

The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to arameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.

C#

Command.Parameters.Add(new System.Data.SqlClient.SqlParameter(”@CaseID”,System.Data.SqlDbType.Int));

Command.ExecuteNonQuery();

CaseID = System.Convert.ToInt32( Command.Parameters["@CaseID"].Value);

Conclusion

By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.

Custom Paging in Datalist

Introduction

In this article i will explain a method for providing custom paging for datalist or repeater.

As you know the datalist is a very powerful control with one drawback that it does not have built-in paging capability, a feature the DataGrid offers. to provide paging to datalist or repeater we can either use “PagedDataSource” class, found in the System.Web.UI.WebControls namespace for auto paging like the datagrid or implement custom paging functionality.

So here is the scenario, i have an image gallery with 8 images per page. i need to provide paging so the user can navigate and view all images. The first step is to create the datalist and paging links.

Datalist <asp:DataList runat=”server” id=”dlGallery” RepeatColumns=”4″ RepeatDirection=”Horizontal”> <ItemTemplate> <table border=”0″ cellpadding=”0″ cellspacing=”0″> <tr> <td> <img src=”<%#DataBinder.Eval(Container.DataItem,”Image_URL”)%>” width=”90″ Height=”90″> </td> </tr> </table> </ItemTemplate> </asp:DataList> //Next/Prev Links. <table border=”0″ width=”410″> <tr> <td align=”left”><asp:LinkButton ID=”lbtnPrev” Runat=”server”>Prev</asp:LinkButton></td> <td align=”right”><asp:LinkButton ID=”lbtnNext” Runat=”server”>Next</asp:LinkButton></td> </tr> </table> //The Code Behind Create a public function that will return only the neccessary rows (After paging). For that we need five static variables. Collapse private int imgID; private string imgTitle; private string imgURL; private static int pageSize = 8; //(This one will hold the no of records return //i mean “no. of records per page”). private static int pageIndex = 0; //(This one is for checking the current page). public DataSet GetAllImagesCustom(int pageIndex, out int outPageIndex) { try { int count = 0; DataSet ds = new DataSet(); ds = //retrieve the data from the database. //for paging int page = 0; //checking the whether the pageIndex value is not Last. //And if it is then assigning the default //values for pageIndex and page variables. if(((pageIndex-1) <= (ds.Tables[0].Rows.Count/pageSize)) && (pageIndex-1) >= 0) { //If the pageIndex is >=first and = //eg. if pageIndex = 2 then value of ‘page’ = 8. //So in the loop it will add rows to the table //from the 8 th row. page = pageSize * (pageIndex-1); } else { //Assigning default values. page = 0; pageIndex = 1; } //creating a data table for adding the required rows or u //can clone the existing table. DataTable dtImg = new DataTable(“Images”); DataColumn newCol = new DataColumn(“Image_ID”,Type.GetType(“System.Int32″)); dtImg.Columns.Add(newCol);//For storing image id. newCol = new DataColumn(“Image_Title”,Type.GetType(“System.String”)); dtImg.Columns.Add(newCol);//For storing image Title. newCol = new DataColumn(“Image_URL”,Type.GetType(“System.String”)); dtImg.Columns.Add(newCol);//For storing image URL. //adding the required rows to the datatable dtImg. foreach(DataRow nRow in ds.Tables[0].Rows) { //if the page=8 and pageIndex =2 then //rows between 8 to 16(if exists) will be added to the new table. if(count >= page && count < (pageSize * pageIndex)) { //Adding rows to the datatable ‘dtImg’. dtImg.Rows.Add(nRow.ItemArray); } count++; } outPageIndex = pageIndex; return ds; } } catch(Exception ex) { throw ex; } } public void BindList() { ….. DataSet ds = new DataSet(); ds = GetAllImagesCustom(Convert.ToInt32(txtPageIndex.Text), out outPageIndex); dlGallery.DataSource = ds; dlGallery.DataBind(); //Assigning the new pageIndex value returned from the //function to the Hidden textbox. txtPageIndex.Text = Convert.ToString(outPageIndex); } Now we have a datalist with 8 images per page. But still we hav’nt done the navigation part. Thats simple as u can see from the above function there isn’t much logic needed. we only need to plus or minus the pageindex value and call the BindList function. private void lbtnPrev_Click(object sender, System.EventArgs e) { //Actual pageIndex -1 txtPageIndex.Text = Convert.ToString(Convert.ToInt32(txtPageIndex.Text) – 1); BindList(); } private void lbtnNext_Click(object sender, System.EventArgs e) { //Actual pageIndex +1 txtPageIndex.Text = Convert.ToString(Convert.ToInt32(txtPageIndex.Text) + 1); BindList(); }