Time Difference in MS SQL

A very common question on the ASP.NET Forums:

>> How to subtract two dates and get the time difference?

The answer is in this UDF. It takes as input two parameters:

1- StartDate

2- EndDate

It will return the time difference in this format: (-) HH:MM

CREATE FUNCTION [dbo].[TimeDiff](@date1 DATETIME, @date2 DATETIME)
-- Returns a string of (-) hh:mm
RETURNS VARCHAR(7)
AS
    BEGIN
        -- Check if the two dates are equal
        IF (@date1 = @date2)
            RETURN ('0:0')

        -- Fix both dates to have same date keeping same time
        DECLARE @DayDiff INT
        DECLARE @MonthDiff INT
        DECLARE @YearDiff INT

        SET @DayDiff        = DATEDIFF(dd,@date1,@date2)
        SET @date1         = DATEADD(dd,@DayDiff,@date1)        
        SET @MonthDiff    = DATEDIFF(mm,@date1,@date2)
        SET @date1         = DATEADD(mm,@MonthDiff,@date1)        
        SET @YearDiff        = DATEDIFF(yyyy,@date1,@date2)
        SET @date1         = DATEADD(yyyy,@YearDiff,@date1)        
            
        -- Get Hours and Minutes    
        DECLARE @Hours INT
        DECLARE @Minutes INT
        SELECT @Minutes = DATEDIFF(mi,@date1,@date2)
        SELECT @Hours   = (@Minutes - @Minutes/(24*60))/60
        SELECT @Minutes = @Minutes - @Hours*60

        DECLARE @returnTime VARCHAR(7)
        DECLARE @SignFlag BIT
        SELECT @SignFlag = 0

        IF ( (@Hours <0) OR (@Minutes<0) )
        BEGIN
            SELECT @SignFlag = 1
            If (@Hours <0)
                SELECT @Hours = @Hours * (-1)
            ELSE
                SELECT @Minutes = @Minutes * (-1)
        END

        IF ( (@Hours < 10) OR (@Hours = 0) )
            SELECT @returnTime = '0' + CAST(@Hours AS VARCHAR(2))
        ELSE
            SELECT @returnTime = CAST(@Hours AS VARCHAR(2))
    
        SELECT @returnTime = @returnTime + ':'

        IF ( (@Minutes < 10) OR (@Minutes = 0) )
            SELECT @returnTime = @returnTime + '0' + CAST(@Minutes AS VARCHAR(2))
        ELSE
            SELECT @returnTime = @returnTime + CAST(@Minutes AS VARCHAR(2))

        IF (@SignFlag = 1)
            SELECT @returnTime = '- ' + @returnTime
    
        RETURN @returnTime
    END

Hope it helps.

Regards

Identity Columns in MS SQL Server

When working with Identity Columns in MS SQL Server, and specially when testing your database and inserting some junk data for the sake of testing, you ask yourself, how would I reset my Identity Columns to their original values after I finish testing with the junk data?

dbcc checkident (Table1, reseed, 1)

Hope that helps.

Regards

Dynamic SQL Query Revisted

Based on Richard Dudley's comment on my previous post, here is an updated version of the Dynamic SQL Query:

CREATE PROCEDURE [dbo].[GetReport]
(
    @ID INT,
    @Type INT
)
AS

    DECLARE @strQuery NVARCHAR(4000)
    DECLARE @parameterList NVARCHAR(4000)

    SET @strQuery = N'SELECT * FROM Reports WHERE ( (Reports.ID = @RerpotID) AND '

    IF ( @Type=0 )
    BEGIN
        SET @strQuery = @strQuery  + N' (Reports.[In] = 3) )'
    END
    ELse
    IF ( @Type=1 )
    BEGIN
        SET @strQuery = @strQuery  + N' (Reports.[In] = 2) )'
    END
    ELSE
    IF ( @Type=2 )
    BEGIN
        SET @strQuery = @strQuery  + N' (Reports.[In] = 1) )'
    END
    ELSE
        BEGIN
            SET @strQuery = SUBSTRING(@strQuery, 1, (LEN(@strQuery) - 4))
                SET @strQuery = @strQuery + N' )'
            END
    
    SET @parameterList =   N '@ReportID    INT'            
    EXECUTE SP_EXECUTESQL @strQuery, @parameterList, @ReportID = @ID  
GO

Hope its better now.

Regards

Dynamic SQL Query

This is a neat way of creating a Dynamic Query:

CREATE PROCEDURE [dbo].[GetReport]
(
    @ID INT,
    @Type INT
)
AS

    DECLARE @strQuery NVARCHAR(2000)

    SET @strQuery = 'SELECT * FROM Reports WHERE ( (Reports.ID = ' + CAST(@ID AS NVARCHAR(5) ) + ') AND '

    IF ( @Type=0 )
    BEGIN
        SET @strQuery = @strQuery  + ' (Reports.[In] = 1) )'
    END
    ELSE
        IF (@Type=1)
        BEGIN
            SET @strQuery = @strQuery  + ' (Reports.[In] = 0) )'
        END
        ELSE
            IF (@Type=2)
            BEGIN
                SET @strQuery = @strQuery  + ' (Reports.[In] = 1) )'
            END
            ELSE
            BEGIN
                SET @strQuery     = SUBSTRING(@strQuery, 1, (LEN(@strQuery) - 4))
                SET @strQuery = @strQuery + ' )'
            END
        
                
    EXECUTE SP_EXECUTESQL @strQuery
print @strQuery
GO

Hope that helps.

Regards

Upload Multiple Files At Once

Check this nice trick to upload multiple files at once:

http://the-stickman.com/web-development/javascript/upload-multiple-files-with-a-single-file-element/

Regards

Another DateTime info

Check the following valuable MSDN page.

DateTime Formats

Regards

DropDownList Customized Data Bind

I was trying to bind the DropDownList without using ObjectDataSource in ASP.NET 2.0, in such a way, I want to have all ListItems inside the DropDownList as (FirstName, LastName).

How would I do such thing in ASP.NET x.x ? Here is the question

ListItem liFullName;
this.StudentDDL.Items.Clear();
foreach (Student objStud in StudentList)
{
 liFullName = new ListItem(objStud.FirstName + ", " + objStud.LastName);
        this.StudentDDL.Items.Add(liFullName);
}

Hope that helps,

Regards

DatePicker Free Control

David Truxall, in his article, An ASP.NET Composite Control for US and UK dates, shows how to develop a date picker control in asp.net together with a validator.

Based on that articles, I have done some improvements to that control.

In the projects I work on, there is a need for such a date control. You must be saying, why don't you use the Calendar control and you are done. Well, suppose I need to get the birthday of a user, how would I do that? Shall I go back like X years to get his birth day?

So, to make things easier, I read the above article and did some imporovments so that the control works fine with ASP.NET 2.0 plus some additional changes.

The control now is called: DatePicker and the validator.cs is called DatePickerValidate.cs and works only with ASP.NET 2.0.

Improvements:

1-
I added wo properties YearBack and YearForward which allows the page developer to specify number of years to display before and after the curent year. Default values are 10, 10 respectively.

2-
DatePicker.cs now inherits from CompositeControl, which is required control to inherit from in ASP.NET 2.0

3-
DatePickerValidate.cs is the control's validators. It has both client-side and server-side. I changed the DatePickerIsValid(val) client-side validation method in the way it access the controls' values on the page.

4-
Added a new method called GetMonthIndex in the javascript external file which takes a month name and returns a month index.

5-
DatePicker.cs uses an Embedded Resource which is the DatePicker.js using the .NET Framework 2.0 technique.

6-
AddAtributestoRender inside the DatePicker.cs control has been removed since no need for it.

This is in general what have been changed. Click here to get the new control.

Hope you will enjoy.

Best regards to you from:

David Truxall and Bilal Haidar

Windows 2003 Important Articles

I passed by a very important website which offers great articles on configuring Windows 2003 Server:

http://www.simongibson.com/

Hope you like it.

Regards

DateTime Formatting in ASP.NET 2.0

This is a nice summarized sample code on how to format DateTime instances:

// This code example demonstrates the String.Format() method.
// Formatting for this example uses the "en-US" culture.

using System;
class Sample 
{
    enum Color {Yellow = 1, Blue, Green};
    static DateTime thisDate = DateTime.Now;

    public static void Main() 
    {
// Store the output of the String.Format method in a string.
    string s = "";

    Console.Clear();

// Format a negative integer or floating-point number in various ways.
    Console.WriteLine("Standard Numeric Format Specifiers");
    s = String.Format(
        "(C) Currency: . . . . . . . . {0:C}\n" +
        "(D) Decimal:. . . . . . . . . {0:D}\n" +
        "(E) Scientific: . . . . . . . {1:E}\n" +
        "(F) Fixed point:. . . . . . . {1:F}\n" +
        "(G) General:. . . . . . . . . {0:G}\n" +
        "    (default):. . . . . . . . {0} (default = 'G')\n" +
        "(N) Number: . . . . . . . . . {0:N}\n" +
        "(P) Percent:. . . . . . . . . {1:P}\n" +
        "(R) Round-trip: . . . . . . . {1:R}\n" +
        "(X) Hexadecimal:. . . . . . . {0:X}\n",
        -123, -123.45f); 
    Console.WriteLine(s);

// Format the current date in various ways.
    Console.WriteLine("Standard DateTime Format Specifiers");
    s = String.Format(
        "(d) Short date: . . . . . . . {0:d}\n" +
        "(D) Long date:. . . . . . . . {0:D}\n" +
        "(t) Short time: . . . . . . . {0:t}\n" +
        "(T) Long time:. . . . . . . . {0:T}\n" +
        "(f) Full date/short time: . . {0:f}\n" +
        "(F) Full date/long time:. . . {0:F}\n" +
        "(g) General date/short time:. {0:g}\n" +
        "(G) General date/long time: . {0:G}\n" +
        "    (default):. . . . . . . . {0} (default = 'G')\n" +
        "(M) Month:. . . . . . . . . . {0:M}\n" +
        "(R) RFC1123:. . . . . . . . . {0:R}\n" +
        "(s) Sortable: . . . . . . . . {0:s}\n" +
        "(u) Universal sortable: . . . {0:u} (invariant)\n" +
        "(U) Universal sortable: . . . {0:U}\n" +
        "(Y) Year: . . . . . . . . . . {0:Y}\n", 
        thisDate);
    Console.WriteLine(s);

// Format a Color enumeration value in various ways.
    Console.WriteLine("Standard Enumeration Format Specifiers");
    s = String.Format(
        "(G) General:. . . . . . . . . {0:G}\n" +
        "    (default):. . . . . . . . {0} (default = 'G')\n" +
        "(F) Flags:. . . . . . . . . . {0:F} (flags or integer)\n" +
        "(D) Decimal number: . . . . . {0:D}\n" +
        "(X) Hexadecimal:. . . . . . . {0:X}\n", 
        Color.Green);       
    Console.WriteLine(s);
    }
}
/*
This code example produces the following results:

Standard Numeric Format Specifiers
(C) Currency: . . . . . . . . ($123.00)
(D) Decimal:. . . . . . . . . -123
(E) Scientific: . . . . . . . -1.234500E+002
(F) Fixed point:. . . . . . . -123.45
(G) General:. . . . . . . . . -123
    (default):. . . . . . . . -123 (default = 'G')
(N) Number: . . . . . . . . . -123.00
(P) Percent:. . . . . . . . . -12,345.00 %
(R) Round-trip: . . . . . . . -123.45
(X) Hexadecimal:. . . . . . . FFFFFF85

Standard DateTime Format Specifiers
(d) Short date: . . . . . . . 6/26/2004
(D) Long date:. . . . . . . . Saturday, June 26, 2004
(t) Short time: . . . . . . . 8:11 PM
(T) Long time:. . . . . . . . 8:11:04 PM
(f) Full date/short time: . . Saturday, June 26, 2004 8:11 PM
(F) Full date/long time:. . . Saturday, June 26, 2004 8:11:04 PM
(g) General date/short time:. 6/26/2004 8:11 PM
(G) General date/long time: . 6/26/2004 8:11:04 PM
    (default):. . . . . . . . 6/26/2004 8:11:04 PM (default = 'G')
(M) Month:. . . . . . . . . . June 26
(R) RFC1123:. . . . . . . . . Sat, 26 Jun 2004 20:11:04 GMT
(s) Sortable: . . . . . . . . 2004-06-26T20:11:04
(u) Universal sortable: . . . 2004-06-26 20:11:04Z (invariant)
(U) Universal sortable: . . . Sunday, June 27, 2004 3:11:04 AM
(Y) Year: . . . . . . . . . . June, 2004

Standard Enumeration Format Specifiers
(G) General:. . . . . . . . . Green
    (default):. . . . . . . . Green (default = 'G')
(F) Flags:. . . . . . . . . . Green (flags or integer)
(D) Decimal number: . . . . . 3
(X) Hexadecimal:. . . . . . . 00000003

*/

This samlpe code is taken from:

http://msdn2.microsoft.com/library/system.string.format.aspx

Regards

Interaction between Master Pages & Cotent Page

Have you ever needed to fire an event in the Content Page based on a button click in the Master Page and access some data sent from the Master Page to the Content Page?

Place this in the Content Page:

protected void Page_Init(object sender, EventArgs e)
    {
        (this.Master.FindControl("Button1") as Button).Command += new CommandEventHandler(CurrentPage_Click);
    }

 public void CurrentPage_Click(object sender, CommandEventArgs e)
    {
        string getValues = e.CommandArgument.ToString();
        string[] Chunks = getValues.Split('|');
        Response.Write("ID : " + Chunks[0] + "<br>Name : " + Chunks[1]);  
    }

In the Master Page:

<asp:Button ID="Button1" CommandArgument="05|Bilal" runat="server" Text="Button" />

Hope that helped you.

SuperDropDownList 1.0

Modal Dialog Window Caching Problem

Hello:

Suppose you open a Modal Dialog window inside your ASP.NET page. In that dialog window, you have a GridView, DataGrid, or whatever control. Do some changes on that control, you will see that the data has changed right?

Ok, then close the dialog window, then open it again from the ASP.NET Page, you will see the old data and not the data you changed last time, right?

Ok, this is because the data has been cached by the Modal Dialog. It seems that this is how Modal Dialog Windows are built to cache.

To overcome this, you would need to add the following in the top of the ASP.NET page that is being opened with the Modal Dialog:

<%@ OutputCache Location="None" VaryByParam="None" %>

Hope that helps,

Regards

CheckBoxField in GridView

In the new data control, GridView, you have a new column called CheckBoxField, this column, accepts a boolean value and display a CheckBox either selected or not and disabled.

What if you want to retreive the value from that column, to see if the CheckBox is selected or not?

CheckBox _checkBoxInGridRow = ((CheckBox)selectedRow.Cells[6].Controls[0]).Checked

Hope that helps,

Note: This code follows from this post: http://msmvps.com/simpleman/archive/2005/08/06/61782.aspx

Regards

DropDownList Tip

Hi:

I was binding the DropDownList on an ASPX page in the Page_Load as follows:

this.TypesDDL.DataSourceID = this.TypesDDL.DataSourceID;

Then, after all I was adding the following:

this.TypesDDL.Items.Insert(0,new ListItem(”text”, “value”));

That item was inserted inside ANY POSITION in the TypesDDL.

Only after 1 hour of trying to find out what is going wrong, I noticed this Property of the DropDownList:

ListControl.AppendDataBoundItems Property

By definition, Append Data Bind Items to Statically Declared List Items

So once you set it to True, then you can add any item to the DropDownList or any control that inherits from ListControl, since that property belong to the ListControl.

Note to Server Controls' Builders: Make sure to set this property to true if you are using any ListControl inside your custom/composite controls.

Hope that tip helps,

Regards

Access Selected GridViewRow in GridView

Hello:

I was working last night around 3:00 AM, and I found out that in my GridView Column's list, I have added a TemplateField of type LinkButton with CommandName=”EditRec”, in such a way, when the Edit link button is clicked, I want to access the selected GridViewRow, then populate a web form on the same page, and without having to show the “Update” and “Cancel” buttons on the GridView.

I did that, but the problem was now how to access the GridViewRow in the RowCommand method?

I used the “SelectedRow” property but I kept having “Object Reference not ....” I didn't know in fact.

I tried to use GridView1.Rows[e.CommandArgument], but the DataKey I set for the GridView was of type String, so cannot use that either.

I finally was able to use the following and it works fine for me:

if (e.CommandName.Equals("EditRec"))
  {
            string MediaIndex = e.CommandArgument.ToString();

            // Get the last name of the selected author from the appropriate
            // cell in the GridView control.
            GridViewRow selectedRow = (GridViewRow)(((Control)e.CommandSource).NamingContainer);

            string _mediaType   = selectedRow.Cells[0].Text;
            string _CheckOut    = selectedRow.Cells[1].Text;
}

The way the code works if as follows:

e.CommandSource -> LinkButton
((Control)e.CommandSource).NamingContainer -> GridViewRow

Then, simply use the Cells property inside the GridViewRow to access all cells.

Hope that Helps,

Regards

CompositeControl in ASP.NET 2.0 Beta2

A very important link that shows an example on using CompositeControl class in ASP.NET 2.0 Beta2.

http://msdn2.microsoft.com/library/3257x3ea(en-us,vs.80).aspx

Hope you will like it.

Regards

The ASPAlliance Times - 2nd August 2005 Issue

I just received by email the ASPAlliance Times – 2^nd August Issue and noticed that my article:
A Customizable Login Server Control is one of the Five Most Popular Articles on ASPAlliance.com.

I am so proud and honored to have this achievement.

Thanks to ALL the ASPAlliance.com team members for the support and time they offer.

Regards.

ASP.NET 2.0 Web Part Framework Presentation

Yesterday, Monday August 1, 2005. My colleague Wessam Zeidan and I, presented the second session, titled with ASP.NET 2.0 Web Part Framework, in the Community Nights event.

This event, is held by LebDev, the 1^st .NET user group in Lebanon, sponsored by Microsoft Office in Beirut.

The feedback was great on the presentation.

If you want to check the PowerPoint presentation and the demo code, click here to download.

You can even check some photos from the event.

Hope you will enjoy the presentation.

Regards

Insert a New Record Using GridView

Here is a nice link to insert a new record into the database:

http://fredrik.nsquared2.com/viewpost.aspx?PostID=155

Regards