/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Thursday, December 22, 2011

Using Datediff() Function in SQL SERVER

The DATEDIFF() function returns the time between two dates.
DATEDIFF(datepart,startdate,enddate)

Simple example using Datediff()

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS [Difference Date]

Calculating Intervals using Datediff() Function

USE test

CREATE TABLE [dbo].[MyTable](
    [CustomerId] [int] NULL,
    [DateTime] [datetime] NULL,
    [interval] [int] NULL
) ON [PRIMARY]

CREATE PROC CalculateInterval
AS
    DECLARE @InsertionTime DATETIME
    --SELECT @InsertionTime =  [datetime] FROM MyTable WHERE customerid=6
    SET @InsertionTime =  (SELECT TOP(1) datetime FROM MyTable ORDER BY datetime DESC)
BEGIN
    INSERT INTO MyTable(customerid,datetime,Interval)
    VALUES(7, GETDATE(), DATEDIFF(SECOND, @InsertionTime, GETDATE()))
END

EXEC CalculateInterval

Convert Seconds to hours:minutes:seconds

DECLARE @sec INT
SET @sec=7612
SELECT CONVERT(VARCHAR(5),@sec/3600)+':'+CONVERT(VARCHAR(5),@sec%3600/60)+':'+CONVERT(VARCHAR(5),(@sec%60))

Find total seconds of time in sql server. You can get the Total seconds of the current time in

SELECT [Total Seconds] =
(DATEPART(hh, GETDATE()) * 3600) +
(DATEPART(mi, GETDATE()) * 60) + DATEPART(ss, GETDATE())

References:
time (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb677243.aspx
Date and Time Type
http://msdn.microsoft.com/en-us/library/ff848733.aspx 
DateDiff()
http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx
Date and Time Functions
 
datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns  
Read More »

Wednesday, December 21, 2011

Classic ASP and ASP.NET Security Image (CAPTCHA) Generator

Create and download your own CAPTCHA

http://www.tipstricks.org/
Read More »

Tuesday, December 20, 2011

Send Mail in ASP.Net 2.0

Send Email in ASP.Net 2.0 - Feed back Form

using System.Web.Mail;
using System.Net.Mail;

The Send mail functionality is similar to Dotnet 1.1 except for few changes
System.Net.Mail.SmtpClient is used instead of System.Web.Mail.SmtpMail (obsolete in Dotnet 2.0).
System.Net.MailMessage Class is used instead of System.Web.Mail.MailMessage (obsolete in Dotnet 2.0)
The System.Net.MailMessage class collects From address as MailAddress object.
The System.Net.MailMessage class collects To, CC, Bcc addresses as MailAddressCollection.
MailMessage Body Format is replaced by IsBodyHtml

The Code is Self explanatory by itself.

public void SendMailUsingSmtp()
{
SmtpClient smtpclient = new SmtpClient();
MailMessage message = new MailMessage();
try
{
//SMTP Server Details
string smtp = "mail.emailsrvr.com";
string smtpuserid = "smtptest@magnonsolutions.com";
string smtppassword = "smtptest";
string port = "25";

//smtpclient.Host = "localhost";
//smtpclient.Port = 25;
//smtpclient.Host = "smtp.gmail.com";
//smtpclient.Port = 465;

//FROM E-Mail Address
MailAddress fromaddress = new MailAddress(txtsendmail.Text, string.Concat(txtfname.Text, txtlname.Text));
message.From = fromaddress;

//TO E-Mail Address
message.To.Add("yogesh.upreti@magnonsolutions.com");

//CC E-Mail Address
//message.CC.Add("test2@test.com");

//BCC E-Mail Address
message.Bcc.Add("test3@test.com");

//Subject Here
message.Subject = "Feedback";
message.SubjectEncoding = System.Text.Encoding.UTF8;

//Body can be Html or text format
//Specify true if it is html message
message.IsBodyHtml = false;

// Message body content
message.Body = txtfname.Text;
message.BodyEncoding = System.Text.Encoding.UTF8;

//Checkout the Credentitial for smtp mail server
smtpclient.Credentials = new System.Net.NetworkCredential(smtpuserid, smtppassword);
smtpclient.Port = Convert.ToInt32(port);
smtpclient.Host = smtp;
smtpclient.EnableSsl = false;
smtpclient.Send(message);
smtpclient.Send(message);
lblStatus.Visible = true;
lblStatus.Text = "Messgae has been sent successfully.";
}
catch (Exception ex)
{
lblStatus.Visible = true;
lblStatus.Text = "Send Email Failed." + ex.Message;
}
}

Below is a C# and VB.NET class that demonstrates using System.Net.Mail to send an email.
Calling the function from code
MailHelper.SendMailMessage("fromAddress@yourdomain.com", "toAddress@yourdomain.com", "bccAddress@yourdomain.com", "ccAddress@yourdomain.com", "Sample Subject", "Sample body of text for mail message")

MailHelper.cs
using System.Net.Mail;
public class MailHelper
{
///
/// Sends an mail message
///
///
Sender address
///
Recepient address
///
Bcc recepient
///
Cc recepient
///
Subject of mail message
///
Body of mail message

public static void SendMailMessage(string from, string to, string bcc, string cc, string subject, string body)
{
// Instantiate a new instance of MailMessage
MailMessage mMailMessage = new MailMessage();

// Set the sender address of the mail message
mMailMessage.From = new MailAddress(from);

// Set the recepient address of the mail message
mMailMessage.To.Add(new MailAddress(to));

// Check if the bcc value is null or an empty string
if ((bcc != null) && (bcc != string.Empty))
{
// Set the Bcc address of the mail message
mMailMessage.Bcc.Add(new MailAddress(bcc));
} // Check if the cc value is null or an empty value
if ((cc != null) && (cc != string.Empty))
{
// Set the CC address of the mail message
mMailMessage.CC.Add(new MailAddress(cc));
} // Set the subject of the mail message
mMailMessage.Subject = subject;
// Set the body of the mail message
mMailMessage.Body = body;

// Set the format of the mail message body as HTML
mMailMessage.IsBodyHtml = true;
// Set the priority of the mail message to normal
mMailMessage.Priority = MailPriority.Normal;

// Instantiate a new instance of SmtpClient
SmtpClient mSmtpClient = new SmtpClient();
// Send the mail message
mSmtpClient.Send(mMailMessage);
}
}

MailHelper.vb
Imports System.Net.Mail
Public Class MailHelper
''' ''' Sends an mail message
''' ''' Sender address
''' Recepient address
''' Bcc recepient
''' Cc recepient
''' Subject of mail message
''' Body of mail message

Public Shared Sub SendMailMessage(ByVal from As String, ByVal recepient As String, ByVal bcc As String, ByVal cc As String, ByVal subject As String, ByVal body As String)

' Instantiate a new instance of MailMessage
Dim mMailMessage As New MailMessage()

' Set the sender address of the mail message
mMailMessage.From = New MailAddress(from)
' Set the recepient address of the mail message
mMailMessage.To.Add(New MailAddress(recepient))

' Check if the bcc value is nothing or an empty string
If Not bcc Is Nothing And bcc <> String.Empty Then
' Set the Bcc address of the mail message
mMailMessage.Bcc.Add(New MailAddress(bcc))
End If

' Check if the cc value is nothing or an empty value
If Not cc Is Nothing And cc <> String.Empty Then
' Set the CC address of the mail message
mMailMessage.CC.Add(New MailAddress(cc))
End If

' Set the subject of the mail message
mMailMessage.Subject = subject
' Set the body of the mail message
mMailMessage.Body = body

' Set the format of the mail message body as HTML
mMailMessage.IsBodyHtml = True
' Set the priority of the mail message to normal
mMailMessage.Priority = MailPriority.Normal

' Instantiate a new instance of SmtpClient
Dim mSmtpClient As New SmtpClient()
' Send the mail message
mSmtpClient.Send(mMailMessage)
End Sub
End Class

Web.config xml version="1.0"?>
<configuration>
<system.net>
<mailSettings>
<smtp from="defaultEmail@yourdomain.com">
<network host="smtp.yourdomain.com" port="25" userName="yourUserName" password="yourPassword"/>
smtp>
mailSettings>
system.net>
configuration>
Read More »

Friday, December 16, 2011

SQL SERVER 2005 Ranking Functions (Transact-SQL) – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL Server 2005 ranking functions

SQL Server 2005 has total of 4 Ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

ROW_NUMBER () OVER ([] )
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.It Returns Bigint type Value

RANK () OVER ([] )
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([] )
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([] )
Distributes the rows in an ordered partition into a specified number of groups.

****************************************************************

CREATE TABLE OrderRanking (OrderID INT IDENTITY(1,1) NOT NULL, CustomerID INT, OrderTotal decimal(15,2))

************************Inserting Rows Into the table *****************************

INSERT OrderRanking (CustomerID, OrderTotal)
SELECT 1, 1000
UNION
SELECT 1, 500
UNION
SELECT 1, 650
UNION
SELECT 1, 3000
UNION
SELECT 2, 1000
UNION
SELECT 2, 2000
UNION
SELECT 2, 500
UNION
SELECT 2, 500
UNION
SELECT 3, 500
UNION
SELECT 4, 100

*******************Query for the Ranking Function One by One **********************

SELECT  *,

ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS [Row No],    
   
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [RowNo Partitionwise],

RANK() OVER (ORDER BY OrderTotal DESC) AS [Rank],

RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Rank Partitionwise],

DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS [Dense Rank],

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS [Dense Rank Partitionwise],

NTILE(2) OVER (ORDER BY OrderTotal) AS [NTILE]

FROM OrderRanking ORDER BY OrderTotal DESC

Query Results:

==================== For more information ===================
- NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }
- RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }
- DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }
- ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }
- Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }
- OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }
- ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }
- What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }
Read More »

Wednesday, December 7, 2011

Sql Server Connection Module in VB 6.0

**************************************************  
Global conn As New adodb.connection
Global con As New adodb.connection
Global pconn As New adodb.connection
Global rs As New adodb.Recordset
Global app_path As String
Global server_name As String
Global server_username As String
Global server_password As String
Global userName As String

Public Sub connect()  
    If conn.State = 1 Then conn.Close
    app_path = "C:\Program Files\Microsoft Visual Studio\VB98\"
  
    server_name = "your server name data source"
    server_username = "dbusername"
    server_password = "dbpassword"

    Call OpenConnection
   
    ''******* connection ***********
    If pconn.State = adStateOpen Then pconn.Close
    pconn.CursorLocation = adUseClient
    pconn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & server_username & ";pwd=" & server_password & ";Initial Catalog=Your DataBase Name;Data Source= " & server_name
    pconn.Open 
End Sub
**************************************************
Read More »

Tuesday, December 6, 2011

My Blog List