How to Handle Exception and Error in SQL Server 2000 and SQL Server 2005 (Transactions – Rollback Transaction, Commit Transaction)

SQL Server 2000 doesn’t have a standard and structured way to handle exception in SQL Query. However, we can user the standard error variable @@ERROR to check whether any error has occurred in order to take the necessary action.

Example for SQL Server 2000 to handle exception:-

BEGIN TRANSACTION
DECLARE @Err AS INT
-- Put your statements here.
SET @Err = @@ERROR
IF @Err <> 0
  BEGIN
    ROLLBACK TRANSACTION
    SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
  END
ELSE
  BEGIN
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
  END

However, SQL Server 2005 has come with a structured way to handle the exception (Try-Catch blocks). This structure is very similar to those used in C#, VB.Net and Java.

Example for SQL Server 2005 to handle exception :-

BEGIN TRY
  BEGIN TRANSACTION
  -- Put your statements here.
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
END CATCH

Query a field size in Database (SQL Server)

If you want to query the size in bytes for a record or field in database (SQL Server), you can use the DATALENGTH function.
The DATALENGTH function can return the length in bytes for any data types including text, ntext, image and varbinary.

For example you want to know the size of Field1 in TableA just run the following query:

SELECT Field1, DATALENGTH(Field1) AS [Size Field1 in bytes] FROM TableA

The output will be the value of Field1 and it's size in bytes for each rows record in TableA

Syntax Differences of C# and VB.NET

Sometimes, I need to convert the codes from C# to VB.NET or from VB.NET to C#. So, I need to know some of the syntax differences of C# and VB.NET. The Table below shows some syntax differences of them:

C#	VB.NET	Description
using	Imports	Allows methods to be called without fully qualifying with Namespace name
this	Me	Refer to the current object
base	MyBase	Refer to the base class
static	Shared	Specifies that a member is shared by all instances of a class. To call the member, an instance of the class is not required.
no equivalent	Static	Specifies that a local variable's value is preserved between calls.
not required	ByVal	Passing parameters by value
ref	ByRef	Passing parameters by reference
enum	Enum	Declare an enumerator
struct	Structure	Declare a structure
obj == null	obj = Nothing	Check whether an object is not ponit to anything
on by default and not changable	Option Explicit	Specifies that all variables must be declared.
delegate	AddressOf	Use the address of a method
no equivalent	With  ... End With	Evaluate an object one and use many times.
int[] x = new int[4] {1,2,3,4};	Dim a() as	Initialise an array.
lock	SynLock	Threading primitives.
sealed	NotInheritable	Specifies that a class cannot be used as a base class (cannot be inherited).
sealed	NotOverridable	Specifies that a method cannot be overriden.
abstract	MustInherit	Specifies that a class can only be inherited (an instance of the class cannot be created).
abstract	MustOverride	Specifies that a method must be implemented in a derived class.
virtual	Overridable	Specifies that a member can be overriden
override	Overrides	Specifies that a member is overriding another member.
not required	Overloads	Specifies that a member is overloading another member.
class Class1:I1	Implements I1	Specifies that the class (Class1) implements the interface I1.
class Class1:BaseClass1	Inherits BaseClass1	Specifies that the class (Class1) inherits class BaseClass1.
Class1	New	Constructor method, called when object is created. Class1 is classname.
~Class1	Finalize	Method called by system just before garbage collection reclaims object, known as destrutor method. Class1 is class name.

Err Msg: “The system cannot log you on to this domain because the system’s machine account in its primary domain is missing or the password..."

Error Message: “The system cannot log you on to this domain because the system’s machine account in its primary domain is missing or the password on that account is incorrect”

Today, I need to login my ex-colleague’s PC to setup something at that PC. The PC had not been used anyone for a few months. When I trying to log on to the domain, the error message mentioned in above is displayed. However, I can log into locally.

I tried to do some configuration setting of the PC but the issue still not been solved. After that, I tried to search with the error message in Google and find the solution to resolve the issue.

Solution:
If you face this problem, determine whether the PC can be logged into locally. If can, try to perform the following steps to resolve the issue:

Remove the Server from the Domain:

1. Click [Start], point to [Settings], click [Control Panel], and double-click [System]
   
2. On the [Network Identification] tab, click [Properties]
   
3. Under [Member Of], click [Workgroup], type the name of a workgroup to join, then click [OK]
   
4. Click [OK] again
   
5. Restart the PC

Add the Server back to the Domain:

1. Click [Start], point to [Settings], click [Control Panel] then double click [System]
   
2. On the [Network Identification] tab, click [Properties]
   
3. Under [Member Of], click [Domain], type the name of a workgroup to join, then click [OK]
   When prompted, provide a user name and password, and join the PC to the domain.
   
4. Restart the PC

Then try to log on the domain, the problem solved.

Different between Authorization and Authentication (.NET)

Authentication is the mechanism where a system uses to identify their user. In ASP .NET, the authentication may be set in web.config file to verify the credentials of the user and decides whether the user is authenticated or not.

Authorization is the mechanism where a system uses to determine what level of access for a particular user.

So, when a user logs on to a system, the user will be authenticated first before be authorized.

Ways to authenticate user in ASP .NET:

1. Forms Authentication
   This authentication depends on code written in the system. Credentials are entered on web forms then used to match with the user information in the database table.
   
2. Windows Authentication
   This authentication is the default authentication provider for ASP .NET application. The user logs in to an application using this authentication, the credentials are matched with the Windows domain through IIS.
   There are 4 types of Window Authentication methods:
   
   • Anonymous Authentication – IIS allows any user
     
   • Basic Authentication – windows username and password (credentials) has to be sent across the network in plain text format (not very secure).
     
   • Digest Authentication – Same as Basic Authentication but the credentials are encrypted.
     
   • Integrated Windows Authentication – Depend on Kerberos technology, with strong credential encryption.
3. Passport Authentication
   This authentication already obsolete in .NET 2.0
   
4. “None” mean no authentication

Types Authorization in .NET

1. File Authorization
   Relying on the NTFS system for granting permission
   
2. Url Authorization
   Specify authorization rules in web.config for different web URLs.

Calling Javascript Function from Code Behind (.vb)

This is the function at html page:

<script language="javascript">
   function test()
   {
      //Add the function code here
   }
</script>

To call this javascript function from code behind (.vb), Use Page.RegisterStartupScript function like the code below:

Dim strScript As String = "<script language='javascript' id='myClientScript'>test();</script>"
Page.RegisterStartupScript(“callTest”,strScript)

SQL Query Script Used to List out All User Table Name of a Database

To List out all user table name of a database, just using the query statement as below:

SELECT Table_Name
FROM INFORMATION_SCHEMA.Tables
WHERE Table_Type = 'BASE TABLE'

Ways to Convert Date to Different Formats in SQL Server 2000

You may want to convert the date into different format that you want. At here, I got example how to convert date to 15 different formats using SQL Query.

--'YYYYMMDD'
SELECT CONVERT(CHAR(8), GETDATE(), 112)
--'YYYY-MM-DD'
SELECT CONVERT(CHAR(10), GETDATE(), 23)
--'YYYY-MMM-DD'
SELECT STUFF(CONVERT(CHAR(10), GETDATE(), 23), 6, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'YYMMDD'
SELECT CONVERT(VARCHAR(8), GETDATE(), 12)
--'YY-MM-DD'
SELECT STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 5, 0, '-'), 3, 0, '-')
--'YY-MMM-DD'
SELECT STUFF(STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 3, 2, LEFT(DATENAME(m, GETDATE()), 3)), 6, 0, '-'), 3, 0, '-')
--'MM-DD-YY'
SELECT CONVERT(CHAR(8), GETDATE(), 10)
--'MMDDYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 10), '-', SPACE(0))
--'MM/DD/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 1)
--'MM/DD/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 101)
--'DD-MM-YY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-')
--'DD-MMM-YY'
SELECT STUFF(REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-'), 4, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'DDMMYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0))
--'DD/MM/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 3)
--'DD/MM/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 103)

Hope that is one the format you want. If not, please write a comment with the format date you want. I will try my best to help you. Thanks.

Gets IP Address of Server PC using C# (ASP .NET 1.1)

The function below is used to get the IP Address of Server. The System.Net namespace is needed for this function. This is because IPHostEntry and IPAddress are class in System.Net namespace. Beside this, GetHostName() function also belong to System.Net.Dns.

public string GetIPAddress()
{
   string strHostName = System.Net.Dns.GetHostName();
   IPHostEntry ipHostInfo = Dns.Resolve(Dns.GetHostName());
   IPAddress ipAddress = ipHostInfo.AddressList[0];

   return ipAddress.ToString();
}

Import Data from Excel File using VB .NET

The following is the sample code how to query an Excel spreadsheet from an ASP.NET page using VB .NET:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""
da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()
da.Dispose()

Note:

• The code above is just select data from Sheet1 (Worksheet) only.

If you want to select data from first sheet in the Excel workbook, you need to know the name of the first sheet first.
To get the name of the first sheet in the Excel workbook, refer code below:

Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
da0_DB.Close()

Note:

• Microsoft DAO 3.5 Library needs to add to the project when source code above is used. From the Project menu, click References, click Add Reference… and then select the Microsoft DAO 3.5 Library to add.

So, complete source code should be like this:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strFirstSheetName & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()

da.Dispose()
da0_DB.Close()

Highlight Lottery Numbers (TOTO) using Microsoft Excel

Let me show you an example how to highlight the ticket numbers that have been drawn in a lottery. Let said you have 5 ticket numbers that are in cells B4:G8 and the drawn numbers are entered in cells B2:G2.

1. Select cells B4:G8.
   
2. Choose [Format] > [Conditional Formatting].
   
3. From the first dropdown list, choose Formula Is.
   
4. For the formula, use the CountIf function: =COUNTIF($B$2:$G$6,B4)
   
5. Click the [Format] button.
   
6. Select formatting options (green pattern, in this example), then click [OK] button.
   
7. Finally, click [OK] button.

Wish you have a lucky day!

How to link to location on the same page using HTML

How to create a links that can jump into specific section on a page? We can do it by using the Anchor Tag and the Name Attribute. That is simple and direct.
Below is the syntax:
<a name=”name_of_section”>Text to be displayed</a>
<a href=#name_of_section”>Text to be displayed</a>

For Example:
I put the line below at the top of this post
<a name=”top”></a>
To go back to the top of this post, I use the following line:
<a href=#top>Go to top of this post</a>
Go to top of this post

You can also link to a particular section on another page directly too; just add a # sign and the name of the anchor to the end of a URL. For Example, to link directly to section 2 (“w2”) of my previous post (with title “ASP .NET Web Service: “The request failed with HTTP status 401: Access Denied.” Error”), I use like this:
<a href="http://wec-library.blogspot.com/2008/02/asp-net-web-service-request-failed-with.html#w2”>Section 2 of Previous Post</a>
Section 2 of Previous Post

ASP .NET Web Service: “The request failed with HTTP status 401: Access Denied.” Error

You may face this error when trying to access a Web Service from your ASP.NET Application. This is because the Anonymous access authentication for the Web Service is turned off. I also was facing this error before. However, I found out 2 ways to resolve this error, there are:

1. Enable Anonymous Access in the IIS Directory Security
2. Assign the Credential Cache Programmatically

1. Enable Anonymous Access in the IIS Directory Security
To do this, try the following steps:

1. Click [Start] -> [Run] -> Type “inetmgr” and press [OK] or [Enter] key to open IIS Control panel.
   
2. Expand the appropriate nodes and navigate to the virtual directory of your Web Service Application.
   
3. Select the Virtual Directory, right click and select [Properties].
   
4. Click to [Directory Security] Tab and then click [Edit] button.
   
5. Check the [Anonymous access] check box.
   
6. Click [OK] twice to exit.

2. Assign the Credential Cache Programmatically
You also can programmatically to allow the permissions by specifying the credential cache.

Let say WS is the object of your Web Service (WebService1 which has the default Method HelloWorld.)

C#
WebService1 WS = new WebService1();
WS.Credentials = System.Net.CredentialCache.DefaultCredentials;
Response.Write(WS.HelloWorld());

Visual Basic
Dim WS As New WebService1
WS.Credentials = System.Net.CredentialCache.DefaultCredentials
Response.Write(WS.HelloWorld())

Export Data to Excel File not in HTML Table Format using C#.NET

If you would like to see how to export data to Excel file not in HTML table using VB.NET, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ + strFilePath + “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.ApplicationClass.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}

Export Data to Excel File not in HTML Table Format using VB.NET

If you would like to see how to export data to Excel file not in HTML table using C#, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.Application.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

Private Funtion ExportToExcelFile(ByVal dt As System.Data.DataTable)
   Dim xlsApp As New Excel.Application
   Dim xlsWorkbook As Excel.Workbook
   Dim xlsWorksheet As Excel.Worksheet
   Dim strhdr As String
   Dim row As Integer
   Dim dr As DataRow
   Dim strFile As String = "file1.xls"
   Dim filename As String = Server.MapPath(strFile)

   If dt.Rows.Count > 0 Then
      'Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add

      'Get the first worksheet
      xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

      'Activate current worksheet
      xlsWorksheet.Activate()

      'Set header row to row 1
      row = 1

      'Add table headers to worksheet
      xlsWorksheet.Cells(row, 1).Value = "Name"
      xlsWorksheet.Cells(row, 2).Value = "Gender"
      xlsWorksheet.Cells(row, 3).Value = "Age"

      'Format header row (bold, extra row height, autofit width)
      With xlsWorksheet.Range("A" & row, "C" & row)
         .Font.Bold = True
         .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
         .EntireRow.AutoFit()
      End With

      'Freeze the column headers
      With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
         xlsApp.ActiveWindow.FreezePanes = True
      End With

      'Write data to Excel worksheet
      For Each dr In dt.Rows
         row += 1
         If Not IsDBNull(dr.Item("Name")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("Name")
         If Not IsDBNull(dr.Item("Gender ")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("Gender")
         If Not IsDBNull(dr.Item("Age")) Then xlsWorksheet.Cells(row, 3).Value = dr.Item("Age")
      Next

      'Format data rows (align to center and left, autofit width and height)
      With xlsWorksheet.Range("A2", "C" & row)
         .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
         .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
         .EntireColumn.AutoFit()
         .EntireRow.AutoFit()
      End With

      'Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = False
      xlsWorkbook.Close(True, filename)

      'Export data to client machine
      strhdr = "attachment;filename=" & strFile
      With Response
         .Clear()
         .ContentType = "application/vnd.ms-excel"
         .ContentEncoding = System.Text.Encoding.Default
         .AppendHeader("Content-Disposition", strhdr)
         .WriteFile(filename)
         .Flush()
         .Clear()
         .Close()
      End With
   End If
End Function

.NET Web Service error - "The test form is only available for requests from the local machine"

I faced this error before and I already solved it.

I had created a window service application that will call web service functions that located at my local PC. The application is working as what I did and required. However, after I has migrated the web service application to Server (or different PC) and I changed my window service application to call the web service functions at Server, the application cannot work.

Then, I tried to browse to the web service at Server using Internet Browser, when I click the function, it showed the message – “The test form is only available for requests from the local machine”.

After that, I found the solution to solve it. I just added in some code into the Web.config for the Web Service application. The code is like below:

<configuration>
   <system.web>
      <webServices>
         <protocols>
            <add name="HttpGet"/>
            <add name="HttpPost"/>
         </protocols>
      </webServices>
   </system.web>
</configuration>

Cheer! Happy Programming!

Window Service timer problem - VB.NET

I had written a Windows Service application in VB.NET before and I faced a problem. I used timer to do the schedule task in this application. I dropped a Timer Control on the designer then add the Tick event to do the schedule task. I can built, install, stop and restart the service. However, the problem is it the Tick event never fires at all.

After that, I try to use code to create the timer. I changed to use System.Timers.Timer instead of System.Windows.Forms.Timer dropped on the designer from Toolbox. Then it is work already. The code is like below:

Dim timerSchedule As System.Timers.Timer

Protected Overrides Sub OnStart(ByVal args() As String)
   timerSchedule = New System.Timers.Timer(1000)
   AddHandler timerSchedule.Elapsed, AddressOf timerSchedule_Elapsed
   timerSchedule.Start()
End Sub

Private Sub timerSchedule_Elapsed(ByVal pSender As Object, ByVal pArgs As System.Timers.ElapsedEventArgs)
   Try
      timerSchedule.Stop()
      'call my a function to do the scheduled task
      DoScheduledTask()
   Catch ex As Exception
   Finally
      timerSchedule.Start()
   End Try
End Sub

Try to know more about System.Timers Namespace, it will help you more.

Server.MapPath Method

Server.MapPath is a function that takes one argument, a virtual path on the Web server, and returns the corresponding physical path.

This function can be used in a number of ways.
First, you can use it to obtain the physical path of a particular web page. For example:
C#
string strFilePath;
strFilePath = Server.MapPath("/WebApp/myWebPage.aspx");

Visual Basic
Dim strFilePath As String
strFilePath = Server.MapPath("/WebApp/myWebPage.aspx")

The output would depend on the web site's physical root directory, but it might be something like: "C:\Inetpub\wwwroot\WebApp\myWebpage.aspx".

You also can use this method to obtain the physical path of a particular directory. For example:
C#
//current directory
string strCurrDir = Server.MapPath("");
//parent directory
string strParentDir = Server.MapPath("..");
//root directory
string strRootDir = Server.MapPath("/");

Visual Basic
'current directory
Dim strCurrDir As String = Server.MapPath("")
'parent directory
Dim strParentDir As String = Server.MapPath("..")
'root directory
Dim strRootDir As String = Server.MapPath("/")

Whether you use backslahed (\) or forward slashes (/), it is same in this function. If you do not put a forward or backward slash at the beginning of the string passed into Server.MapPath, the current directory that the ASP page is being executed is used as the base for the physical path. Else, the root physical path is used as the base for the physical path. For example:
C#
string strFilePath1 = Server.MapPath("someXmlFile.xml");
string strFilePath2 = Server.MapPath("/someXmlFile.xml");

Visual Basic
Dim strFilePath1 As String = Server.MapPath("someXmlFile.xml")
Dim strFilePath2 As String = Server.MapPath("/someXmlFile.xml")

The two lines code above is executed in a ASP page running in the /Dir directory, where the page had physical path C:\Inetpub\wwwroot\Dir. So, the value of strFilePath1 and strFilePath2 will not be the same, where the value of strFilePath1 is C:\Inetpub\wwwroot\Dir\someXmlFile.xml and the value of strFilePath2 is C:\Inetpub\wwwroot\someXmlFile.xml.

Note:
To use MapPath function in a code-behind module, use HttpContext.Current.Server.MapPath.

SQL Server function for datetime - DATEADD

DATEADD is a date function will return a datetime value based on the number interval add to the particular date part of the specified date. The syntax DATEADD is like:
DATEADD(date_part, number_interval, specified_date)

date_part is the parameter that specifies on which part of the date to be manipulated with the number interval. You can add month, year, day and etc. You can use
MONTH, MM or M for month
YEAR, YYYY, YY for year
DAY, DD, D for day
HH for hour
SS, S for Second

For example :
SELECT DATEADD(D, -1, GETDATE())AS [Yesterday]
SELECT DATEADD(MM, 3, GETDATE())AS [ThreeMonthsFromNow]
SELECT DATEADD(YEAR, -2, GETDATE())AS [TwoYearsAgo]