HTML: Design table with thin single line border
In this quick tips, we will see a way to render an HTML table with single line(thin) border.
Generally when we write mark-up for HTML table with border specified, it renders as Fig. A which looks ugly with a very thick border. In Fig. A I have used markup starting with following line
<table border="1" cellpadding="0" cellspacing="0" width="200px">
Now, to have the HTML table to be rendered as Fig. B with thin border, we need to add style attribute with css property border-collapse set to collapse.
<table border="1" cellpadding="0" cellspacing="0" width="200px" style="border-collapse:collapse;">
I hope, it’s useful.
SQL Server: Custom Sorting
This is one of the many custom sorting we do with SQL queries. In few cases we may need to sort all records of a result set in a particular order except one specific row containing some specific value, which we may want to keep on the top of all other rows or we may like to move it to the bottom. Now, let’s relate this to few real time scenarios.
Scenario-1:
Sometimes we need an element to be placed at bottom in the list. For example, a Dropdownlist containing educational qualifications may contain an item with text as “Other”. While showing all these educational qualifications from database we may need this particular element (i.e, “Other”) to move to the bottom in the list. If we try to sort this list in the SELECT query by normal ORDER BY keyword (like ORDER BY Qualification) then we may not get the desired sequence.
To get the desired result we need to use CASE WHEN keyword along with ORDER BY. Let’s illustrate this with following example-
Simple ORDER BY :
SELECT * FROM ( SELECT 'I.Sc.' AS Qualification UNION SELECT 'B.Sc.' AS Qualification UNION SELECT 'Other' AS Qualification UNION SELECT 'P.G.' AS Qualification UNION SELECT 'MCA' AS Qualification UNION SELECT 'DCA' AS Qualification UNION SELECT 'P.H.D' AS Qualification ) AS TBL ORDER BY Qualification
OUTPUT:
ORDER BY with CASE WHEN :
SELECT * FROM ( SELECT 'I.Sc.' AS Qualification UNION SELECT 'B.Sc.' AS Qualification UNION SELECT 'Other' AS Qualification UNION SELECT 'P.G.' AS Qualification UNION SELECT 'MCA' AS Qualification UNION SELECT 'DCA' AS Qualification UNION SELECT 'P.H.D' AS Qualification ) AS TBL ORDER BY CASE WHEN Qualification='Other' THEN 'ZZZZZ' ELSE Qualification END
OUTPUT:
EXPLAINATION:
Here we have set the value of item “Other” as “ZZZZZ” while the sorting is being done. Generally “ZZZZZ” will be the last item in any real world list and hence will move to the last.
Scenario 2:
In some other cases we may need the desired item to be moved to top of the result set. An example for this situation can be, a Dropdownlist containing items for Book Category. The list may have an item with text “General” , which is meant for those books whose category is not clearly known. Let’s assume that most of the books are supposed to belong this category. Then we have to place this item in the first index of the Dropdownlist so that it will be easier for user to pick the item. Now, we need a little modification in the ORDER BY clause as follows.
ORDER BY CASE WHEN Qualification='General' THEN NULL ELSE Qualification END
I’ll love to hear from you.
Thanks
You may also like :
Dynamic ORDER BY in SQL Server
ASP.Net: Disable TabIndex Focusing
If you need to disable/restrict focusing on one or more controls in a page then just mention TabIndex=”-1″.
This is useful particularly when you have some read only controls in your WebForm and you don’t want the control to move(focus) to those controls.
This is known to most of the web-developers but hopefully it will be useful to others.
Javascript: Thousand separator
We may use following javascript function to show numbers formatted with thousands separator.
Definition
function formatThousandPlace(orgNum) {
arr = orgNum.split('.'); //split integer part & fractional part
intPart = arr[0]; //store integer part in a variable
fractionPart = arr.length > 1 ? '.' + arr[1] : ''; //store fractional part in a variable
var rgx = /(\d+)(\d{3})/; //regexp to find thousand place
while (rgx.test(intPart)) {
intPart = intPart.replace(rgx, '$1' + ',' + '$2'); //replace integer part after inserting a comma in the thousandth place
}
document.write(intPart + fractionPart); //write final value to the document
}
Function Call
Now, we can use it anywhere in the document where we want to show those numbers. For example: it may inside normal html elements or some dynamic ASP.Net controls like GridView, DataList, FormView etc.
<script language="javascript">formatThousandPlace('10000000.00')</script>
While calling this from ItemTemplate of an ASP GridView the syntax will be
<asp:TemplateField HeaderText="Price">
<ItemTemplate>
<script language="javascript">formatThousandPlace('<%#Eval("SalePrice") %>')</script>
</ItemTemplate>
</asp:TemplateField>
I hope this is helpful.
ASP.Net : DropDownList containing category – sub category hierarchy
Introduction:
This article will provide us a way to embed both categories and sub-categories hierarchy in a single DropDownList exactly same as the image on the left.
I searched the web for this but did not find exactly what I needed. So, I planned to write my own logic for this. If somebody has a better idea then I really appreciate and request to share.
Description:
Lets take an example where we need to show states and districts of India in a single DropDownList with their hierarchy. Here state will behave as category and districts can be considered as sub-categories.
In DB part we need two tables i.e, States and Districts may be like following structure.
- State –> (StateId int pk, StateName varchar(50))
- Districts –> (DistrictId int pk, StateId int fk, DistrictName varchar(50))
Or, this script may help you creating the tables
CREATE TABLE [dbo].[States]( [StateId] [int] IDENTITY(1,1) NOT NULL, [StateName] [varchar](50) NULL, CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED ( [StateId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Districts]( [DistrictId] [int] IDENTITY(1,1) NOT NULL, [StateId] [int] NOT NULL, [DistrictName] [varchar](50) NULL, CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED ( [DistrictId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Districts] WITH CHECK ADD CONSTRAINT [FK_Districts_States1] FOREIGN KEY([StateId]) REFERENCES [dbo].[States] ([StateId]) GO ALTER TABLE [dbo].[Districts] CHECK CONSTRAINT [FK_Districts_States1] GO
Now insert some sample data or you can execute this script.
/****** Object: Table [dbo].[States] ******/ SET IDENTITY_INSERT [dbo].[States] ON INSERT [dbo].[States] ([StateId], [StateName]) VALUES (1, N'Odisha') INSERT [dbo].[States] ([StateId], [StateName]) VALUES (2, N'West Bengal') INSERT [dbo].[States] ([StateId], [StateName]) VALUES (3, N'Bihar') INSERT [dbo].[States] ([StateId], [StateName]) VALUES (4, N'Jharkhand') SET IDENTITY_INSERT [dbo].[States] OFF /****** Object: Table [dbo].[Districts] ******/ SET IDENTITY_INSERT [dbo].[Districts] ON INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (1, 1, N'Mayurbhanj') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (2, 1, N'Keonjhar') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (3, 1, N'Khorda') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (4, 1, N'Balasore') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (5, 2, N'Hoogly') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (6, 2, N'Howrah') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (7, 2, N'Jalpaiguri') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (8, 3, N'Aurangabad') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (9, 3, N'Patna') INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (10, 4, N'Bokaro') SET IDENTITY_INSERT [dbo].[Districts] OFF
Place a DropDownList in your page.
<asp:DropDownList ID="drpDistricts" runat="server"></asp:DropDownList>
Lastly do the coding required to bind the DropDownList. Things to remember are we have a single DropDownList and it should be meant for choosing a sub-category(District), which means categories should be disabled for clicking. Although there may be several ways like looping the sub-categories and inserting categories in-between etc. but, I personally prefer to query both of them in a single query.
SELECT '- '+DistrictName AS TextField,DistrictId AS ValueField,StateId FROM DBO.Districts UNION SELECT UPPER(StateName),0,StateId FROM dbo.States ORDER BY StateId,DistrictId
Put DataTextField =”TextField” and DataValueField=”ValueField”
Now, lets do the coding to disable the categories (States).
foreach (ListItem item in drpDistricts.Items)
{
if (item.Value.ToString() == "0")
{
item.Attributes.Add("Style", "color:maroon");
item.Attributes.Add("Disabled", "true");
}
}
That’s all.
Thanks for reading.
Feedback or suggestion are most welcome.
ASP.Net: Download files from server with their original name
Introduction :
Purpose of writing this post is to share a way to resolve a very common but important issue with uploading-downloading file in ASP.Net. I don’t claim it to be the most efficient way but it works for me.
Problem:
While uploading a file to the server we may save it with its original file name and may code like-
FileUploader1.SaveAs(Server.MapPath("UploadedFiles/" + FileUploader1.FileName));
But, problem is if we upload another file with same name as previous file then the previous one will be replaced with the latest one. Now all the links for downloading both theses files will reference to the same file on the server.
Again we may resolve this issue by saving files with unique name on the server and may code like-
FileUploader1.SaveAs(Server.MapPath("UploadedFiles/" + DateTime.Now.ToString("yyyyMMddhhmmssfffffff") + System.IO.Path.GetExtension(FileUploader1.FileName);));
By doing this we will lose the original file name forever. But, in some or, more scenarios user may expect the file to be downloaded to his/her hard-disk with the original file name with which he/she uploaded the file to the server. (Example: Uploading-Downloading multiple resumes of a candidate).
We can solve this issue by storing both the names of a file i,e original & unique name on server.
The actual headache now comes in to picture. File is saved on the server with some unique name but we want the to change it’s name to the original name while user downloading it to his/her hard-disk.
Solution:
The above said problem can be solved by following few steps. For the illustration I am taking a Gridview to show files for downloading and LinkButton for the download links.
- Store both actual and unique names of the file in Database
- Use a Hidden Field to store actual(unique) file name in the gridview
<asp:TemplateField HeaderText="Uploaded File"> <ItemTemplate> <asp:LinkButton ID="lnkDownload" Text='<%#Eval("FileNameOriginal")%>' runat="server" OnClick="lnkDownload_Click" /> <asp:HiddenField ID="hfDownload" runat="server" Value='<%#Eval("FileNameActual")%>' /> </ItemTemplate> </asp:TemplateField> - Now, put following code in the link button click event
LinkButton lnkDwnload = (LinkButton)sender; GridViewRow grow = (GridViewRow)lnkDwnload.Parent.Parent; HiddenField hfDownload = (HiddenField)grow.FindControl("hfDownload"); Response.AddHeader("Content-Disposition", "attachment;filename=" + lnkDwnload.Text); Response.ContentType = GetContentType(Server.MapPath("~/UploadedFiles/" + hfDownload.Value.ToString())); Response.TransmitFile(Server.MapPath("~/UploadedFiles/" + hfDownload.Value.ToString())); Response.End();
Note that I have used a method GetContentType() to get the content type/MIME type of the file to be downloaded.
private string GetContentType(string fileName)
{
string contentType = "application/octetstream";
string ext = System.IO.Path.GetExtension(fileName).ToLower();
Microsoft.Win32.RegistryKey registryKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
if (registryKey != null && registryKey.GetValue("Content Type") != null)
contentType = registryKey.GetValue("Content Type").ToString();
return contentType;
}
Note : Microsoft.Win32 is OS specific MSDN
For a universal solution we may use another way of getting the MIME type.
private string GetContentType(string fileName)
{
string ext = System.IO.Path.GetExtension(fileName).ToLower();
switch (fileName)
{
//add as many extensions of file types you expect to be uploaded
//for a complete list you may visit http://stackoverflow.com/questions/1029740/get-a-mime-from-an-extension
case ".doc": return "application/msword";
case ".docx": return "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
case ".jpeg": return "image/jpeg";
case ".jpg": return "image/jpeg";
case ".bmp": return "image/bmp";
default: return "application/octet-stream";
}
}
Conclusion:
This works very fine for me. If you are facing problem then drop a comment, it will be my pleasure to help you. Any better idea is welcome.
Thanks for reading !
Javascript: Function to get current fiscal year
This can be useful when you need to print current session year or, current fiscal/financial year somewhere in your page and specifically when you do not want to invoke a server method for it.
Defining Function
<script language="javascript">
function getCurrentFiscalYear() {
//get current date
var today = new Date();
//get current month
var curMonth = today.getMonth(); //get current month
var fiscalYr = "";
if (curMonth > 3) { //
var nextYr1 = (today.getFullYear() + 1).toString();
fiscalYr = today.getFullYear().toString() + "-" + nextYr1.charAt(2) + nextYr1.charAt(3);
}
else {
var nextYr2 = today.getFullYear().toString();
fiscalYr = (today.getFullYear() - 1).toString() + "-" + nextYr2.charAt(2) + nextYr2.charAt(3);
}
document.write(fiscalYr);
}
</script>
Calling Function
<script language="javascript">getCurSession()</script>
Note:
- I have made the function as per Indian fiscal year. You can change it according to your country. You only need to modify the conditional statement i.e, if (curMonth > 3) where April(3) is the starting month of fiscal year.
- getMonth() returns number of the month in a date value starting from 0 to 11
- You can change this function to return the session year which can be input for another logic/function. Accordingly you have to change the function.
Visual Studio Tips: Move or select code between matching braces
This short and quick post is inspired by the Blog Post (Read) written by Pinal Dave (SQL Authority), describing shortcut to select code between two parenthesis in SQL Server. This can be very useful when we are working with complex queries. Fortunately, it is the same shortcut we use in Visual Studio (C++, C#).
To select code between two braces, we just need to press the key combinations CTRL+SHIFT+] after placing the cursor either on the opening or closing brace (in C++ and C#: Curly brace/ Square bracket/ Parentheses).
Another thing we can do is – travel/move between two parenthesis using key combinations CTRL+] .(applies to both SQL Server and Visual Studio)
Thanks for reading.
C Sharp: Find greater of two numbers
Assumption:
int num1 =double.Parse(Console.ReadLine()); int num2 = double.Parse(Console.ReadLine());
Supplied numbers are not equal.
Way 1:(Using Relational operator)
if (num1 > num2)
Console.WriteLine("Max is:{0}", num1);
else
Console.WriteLine("Max is:{0}", num2);
Way 2:(Using Conditional operator)
Console.WriteLine("Max is:{0}", num1>num2?num1:num2);
Way 3:(Using Math.Max())
Console.WriteLine("Max is:{0}", Math.Max(num1,num2));
Way 4:(Using Math.Abs())
Console.WriteLine("Max is:{0}", ((num1+num2)+Math.Abs(num1-num2))/2);
Analysis:
Among these ways I don’t think the first three solutions need any explaination. But what we will anayse here is the Way 4. The half of the summation of sum of two numbers and their absolute difference results the greater of the two. Similarly we can get the lower among them as the half of the substraction between the sum of the two numbers and their absolute difference.
Ex:
Console.WriteLine("Min is:{0}", ((num1+num2)-Math.Abs(num1-num2))/2);
SQL Server: Delete a record with condition on multiple columns of another table
If you want to delete a record from a table where the relation with another table is on multiple columns or you want to check it for some conditions on multiple columns of another table then with a simple join your job can be done.
Example:
DELETE Table1 FROM Table1 INNER JOIN Table2 ON Table1.Column1=Table2.Column1 AND Table1.Column2=Table2.Column2
Drop a comment in case you have any question or suggestion.
Thanks !
