Month: May 2013

Sibasis Jena

Check Query string in JAVASCRIPT

i will expalin by taking an example:
“www.abc.com?UserId=4”
Here querystring[0]=UserId and querystring[1]=4

function QueryStringCheck()
{
  var field = 'userId';
  var url = window.location.href;
  if(url.indexOf('?' + field + '=') != -1)
  {    var x = url.split('?');
       if (x.length > 1) 
         {
         var querystring = x[1].split('=');
         alert(querystring[0] + ' is ' + querystring[1]);
         return true;
         }
  }
  else if(url.indexOf('&' + field + '=') != -1)
  {
      return true;
  }
  else
  {
     return false;
  }
}
Sibasis Jena

UNION,INTERSECT and EXCEPT

I will explain these by taking an example:

CREATE TABLE OrderDetail
(
ID INT Primary Key NOT NULL IDENTITY(1,1),
NAME NVARCHAR(100),
Price INT,
Quantity INT
)
Insert some records:
INSERT INTO OrderDetail VALUES('Biscuit','30',2)
INSERT INTO OrderDetail VALUES('Pen','10',1)
INSERT INTO OrderDetail VALUES('physics Book','100',1)
INSERT INTO OrderDetail VALUES('Chemistry Book','120',1)
INSERT INTO OrderDetail VALUES('Math Book','90',1)
INSERT INTO OrderDetail VALUES('Note Book','20',5)
INSERT INTO OrderDetail VALUES('Mechanics Book','20',5)
INSERT INTO OrderDetail VALUES('Mechanics Book','20',6)

UNION:
We can union two result set with same structure.
(SELECT ID,Name FROM OrderDetail where Quantity=1) UNION (SELECT ID,Name FROM OrderDetail where Quantity=5)
Result :

ID name
2 Pen
3 physics Book
4 Chemistry Book
5 Math Book
6 Note Book
7 Mechanics Book

INTERSECT:
We can union two result set with same structure.
(SELECT Name FROM OrderDetail where Quantity=6) INTERSECT (SELECT Name FROM OrderDetail where Quantity=5)
Result :

name
Mechanics Book

Sibasis Jena

Convert Date to string

string date = DateTime.Today.Date.ToString("dd/MM/yyyy"); //Result "27/08/2012"
string date = DateTime.Today.Date.ToString("dd_MM_yyyy"); //Result "27_08_2012"
string date =DateTime.Today.Date.ToString("MMMM"); //Result "August"
string date =DateTime.Today.Date.ToString("MMM"); //Result "Aug"

Sibasis Jena

Send mail in Classic Asp

Send mail

Sub sendEmail(mailFrom, mailTo, mailSubject, mailMessage, mailServer, mailUsername, mailPassword)
Set TestMail = CreateObject("cdo.message")
TestMail.From = mailFromAddress
TestMail.To = mailToAddress
TestMail.Subject = mailSubject
TestMail.HTMLBody = mailMessage
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServerIP
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailUsername
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailPassword
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
TestMail.Configuration.Fields.Update
TestMail.Send
Set TestMail = nothing
End Sub

Save mail in your local pickup directory.

Sub sendEmail(mailFrom, mailTo, mailSubject, mailMessage, mailServer, mailUsername, mailPassword)
Set TestMail = CreateObject("cdo.message")
TestMail.From = mailFromAddress
TestMail.To = mailToAddress
TestMail.Subject = mailSubject
TestMail.HTMLBody = mailMessage
TestMail.Configuration.Fields.Item('http://schemas.microsoft.com/cdo/configuration/sendusing') = 1
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServerIP
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = "C:\temp\maildrop"
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailUsername
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailPassword
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
TestMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
TestMail.Configuration.Fields.Update
TestMail.Send
Set TestMail = nothing
End Sub
Sibasis Jena

Query String Set and Get

Send Query String:
Response.Redirect("queryto.aspx?id=4");

Get Query String:
It is better to check for null before using the queystring.
if (string.IsNullOrWhiteSpace(Request.QueryString["id"]))
{
string queryString = Request.QueryString["id"];
}

Sibasis Jena

Data Table to excel c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;

namespace CallCodeBehindMethodFromJavascript
{
public class Student
{
public string Name { get; set; }
public int StudentId { get; set; }
public int Age { get; set; }
}
public class ListtoDataTableConverter
{
public DataTable ToDataTable(List items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}

foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values[i] = Props[i].GetValue(item, null);
}

dataTable.Rows.Add(values);

}
//put a breakpoint here and check datatable
return dataTable;
}
}
public partial class Import : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

protected void btnGetXls_Click(object sender, EventArgs e)
{

List Students = new List(){
new Student() { Name = "Jack", Age = 15, StudentId = 100 },
new Student() { Name = "Smith", Age = 15, StudentId = 101 },
new Student() { Name = "Smit", Age = 15, StudentId = 102 }
};
ListtoDataTableConverter converter = new ListtoDataTableConverter();
DataTable dt = new DataTable();
dt = converter.ToDataTable(Students);
DataTableToExcel(dt);
}

Sibasis Jena

Delete duplicate record from Table SQL

Create Table :

CREATE Table [Order]
(
     ID INT Primary key NOT NULL IDENTITY(1,1),
     NAME NVARCHAR(MAX),
     Quantity INT,
      Price INT
)

Insert duplicate record into the table:

INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,10)

INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,2)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,3)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,11)
 
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
 
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Kalia’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Kalia’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Tiki’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Tiki’,1,1)

Delete Duplicate record:

DELETE FROM [Order] WHERE ID NOT IN (SELECT MAX(ID) FROM [Order] GROUP BY NAME)