SQL SERVER – Find First Day / Last Day of any month - Current - Previous - Next

Use the below queries as a sample to find the First day and Last Day of any month.
The sample below shows the First and Last days for previous and next months.


DECLARE @today DATETIME
SELECT @today = GETDATE();
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@today)),@today),101) ,
'Last Day of Previous Month'
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@today)-1),@today),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION ALL
SELECT CONVERT(VARCHAR(25),@today,101) AS Date_Value, 'Today' AS Date_Type
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@today))),DATEADD(mm,1,@today)),101) ,
'Last Day of Current Month'
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@today))-1),DATEADD(mm,1,@today)),101) ,
'First Day of Next Month'
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(dd,1-day((DATEADD(mm,-1,@today))),DATEADD(mm,-1,@today)),101) AS Date_Value,
'First Day of Previous Month' AS Date_Type

Alternatively you can also use the below code, this gives time precision also:

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

SQL SERVER - How to use TRANSACTION in Stored Procedure | COMMIT, ROLLBACK

A SQL Server Transaction in a Stored procedure can be used to do a batch of queries especially INSERT / UPDATE / DELETE. The changes made to data in a transaction will persist only once COMMITTED. Hope the sample below helps you to get a practical idea about its usage.

BEGIN TRAN

INSERT INTO Person(Name, Age, Phone, Address)
VALUES ('Gates',50, '800-MSFT', 'Redmond, Washington, U.S')

IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
        COMMIT TRAN

ASP.NET - Adding AjaxToolkit Accordion Pane Dynamically in C#

Issue:
How to add AjaxToolkit Accordion Pane Dynamically in C#

Solution:
In the below code, it iterate thought a List/Dictionary of custom object BannerList and creates Accordion Panes dynamically. The code is part of a sample I was working on. But it will help you understand the idea.


foreach (KeyValuePair<int, List<Banner>> entry in BannerList)
{
    AccordionPane ap1 = new AccordionPane();
    ap1.HeaderContainer.Controls.Add(new LiteralControl(Countries[entry.Key].ToUpper()));
             
    CheckBoxList chkLst = new CheckBoxList();
    chkLst.ID = "chkBoxLst" + Countries[entry.Key];
    chkLst.RepeatColumns = 5;
    foreach(Banner b in entry.Value)
    {
        chkLst.Items.Add(new ListItem(b.BannerName, b.BannerID.ToString()));
    }
    Button btnSave = new Button();
    btnSave.ID = "btnSave" + Countries[entry.Key];
    btnSave.Text = "Save";
    Button btnCancel = new Button();
    btnCancel.ID = "btnCancel" + Countries[entry.Key];
    btnCancel.Text = "Cancel";
    ap1.ContentContainer.Controls.Add(chkLst);
    ap1.ContentContainer.Controls.Add(btnSave);
    ap1.ContentContainer.Controls.Add(btnCancel);
    acc1.Panes.Add(ap1);
}

C#/VB.NET - Iterate through a Dictionary - Generic Collection

The cleanest and most straight forward way to iterate through a Dictionary is as follows:

 
foreach(KeyValuePair<int,String> valuePair in dict)  
 {  
    int i = entry.Key;  
    string s = entry.Value;  
 }  

SQL SERVER - How to split a comma separated string / delimited text

Use this SQL Server Function to split any string delimited text and get back items as a recordset.


CREATE FUNCTION dbo.fnSplit(
    @string2split VARCHAR(8000) -- String to Split
  , @delimiter VARCHAR(8000) = ',' -- delimiter that separates string items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@delimiter,@string2split,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@string2split,1,CHARINDEX(@delimiter,@string2split,0)-1))),
  @string2split=RTRIM(LTRIM(SUBSTRING(@string2split,CHARINDEX(@delimiter,@string2split,0)+LEN(@delimiter),LEN(@string2split))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@string2split) > 0
 INSERT INTO @List SELECT @string2split -- Put the last item in
RETURN
END
GO


Query:
SELECT * FROM dbo.fnSplit('Sudan, Congo DRC, Kenya, Uganda, Bahrain',',')

Result:

Sudan
Congo DRC
Kenya
Uganda
Bahrain

ASP.NET - Method 'get_EnableCdn' in type 'System.Web.UI.ScriptManager' from assembly 'System.Web.Extensions' does not have an implementation

Issue:
Error: "Method 'get_EnableCdn' in type 'System.Web.UI.ScriptManager' from assembly 'System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' does not have an implementation."

Reason:
This error is frequent in ASP.NET projects which are migrated from an older version of .NET Framework to a new version of Framework. So mostly in projected build in Visual Studio 2005/2008 when build in Visual Studio 2010, this error might occur. This is because of the web application referring to an older version of "System.Web.Extensions" Assembly.

Solution:
You will have to explicitly reference to the latest version of the "System.Web.Extensions" Assembly. You can do it in web.config of your website/web application as below.

  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
      <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
      <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
    </dependentAssembly>
  </assemblyBinding>

SQL SERVER – Add Column With Default Column Constraint to Table

Setting a default value to a column in case a row in inserted with a no value provided for the column. Please find the below code to set a default value for a column. The example below adds a new column and applies the default constraint and default value of 'Hello' and the column doesn't allow null.


ALTER TABLE YourTable
ADD YourNewColumn VARCHAR(50)
CONSTRAINT DF_ YourTable_YourNewColumn DEFAULT 'Hello' NOT NULL
GO


Constraint name is not mandatory as SQL Server will take a default constraint name in case if you don't provide one.

SQL SERVER – Convert Text to Numbers (Integer) – CAST and CONVERT

Using  CAST

SELECT CAST(YourVarcharColumn AS INT) FROM YourTable

Using  CONVERT
SELECT CONVERT(INT, YourVarcharColumn) FROM YourTable

Here  YourVarcharColumn is of SQL type Varchar in table YourTable.

Exceptions:

Exception happens when using  CAST or CONVERT is applied on alpha-numeric and casted/converted to numeric.

C#/VB.NET - Object cannot be cast from DBNull to other types | .NET Exception | Error

Issue:
"Object cannot be cast from DBNull to other types" - Error / Exception in .NET / ASP.NET / ADO.NET

While executing the below code it throws and exception "Object cannot be cast from DBNull to other types".

while (reader.Read())  
 {  
   age = Convert.ToInt32(reader[0]);  
 }  

This is a common mistake from novice .net developers.

Reason:
The error occurs because the field fetched by the DataReader has a database value null. This null value is attempted to convert to type int and thus the error "Object cannot be cast from DBNull to other types". If "Allow Null" is enabled for this field in the database and it contains null, then there is a possibility for this error.

Solution:
Do a check for null before trying to access the value from the DataReader. Both solution 1) and solution 2) should work.

Solution 1)

 while (dr.Read())  
 {  
   if(!reader.IsDBNull(0))  
   {  
     age = reader.GetInt32(0);  
   }  
 }  

Solution 2)

 while (dr.Read())  
 {  
 if (reader[0] != DBNull.Value)  
 age = Convert.ToInt32(reader[0]);  
 }