Unique Vs. Primary Key - Referential Integrity
When we create a Referential integrity, Parent column can be a PRIMARY KEY or UNIQUE column!
OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
So, The above statement uses only the Parent Table name. But, not Primary Key column.
By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.
But, Have you tried with UNIQUE key for the same scenario ?
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
You will get an Err message!
Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So, when referring an UNIQUE column, It should be TableName(ColumnName)
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO
So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
Monday 15 September 2014
Saturday 31 May 2014
Asp.Net Application Send Mail Sample Code Project
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Net.Mail;
using System.Runtime.InteropServices;
using System.Xml;
using System.Globalization;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection Oconnection = new SqlConnection(ConfigurationManager.AppSettings["Con"].ToString());
DataTable DTMailTrigger = new DataTable();
string MailTxt = "";
protected void Page_Load(object sender, EventArgs e)
{
//Session["SGID"] = "N8222335"; //N8222335
MailReports();
}
protected void MailReports()
{
try
{
SqlCommand cmd = new SqlCommand("USP_PENDING_INVOICE_STATIDCS", Oconnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqldat = new SqlDataAdapter(cmd);
sqldat.Fill(DTMailTrigger);
if (DTMailTrigger.Rows.Count > 0)
{
MailTxt = "
"; MailTxt += " "; MailTxt += " "; MailTxt += " "; MailTxt += "
";
MailTxt += "
"; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; } for (int i = 0; i < DTMailTrigger.Rows.Count; i++) { MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += "
";
Response.Write(MailTxt);
//Details To send a Mail using NET MAIL
MailMessage mail = new MailMessage();
mail.Subject = "PENDING INVOICE STATISTICS-SEKURT INDIA LTD.,"; //Subject
mail.From = new MailAddress("Nagarajan.R2@saint-gobain.com");
//mail.From = new MailAddress("Pending Invoice Statistics-Sekurt "); //From Address
//Send To Mail id
string[] strmailTO = { "Nagarajan.R2@saint-gobain.com", "Nagarajan.R2@saint-gobain.com", "nagarajan027@gmail.com" }; //TO Address
for (int j = 0; j < strmailTO.Length; j++)
{
mail.To.Add(strmailTO[j]);
}
//Send CC Mail id
//string[] strmailCC = {""};
//for (int s = 0; s < strmailCC.Length; s++)
//{
// mail.CC.Add(strmailCC[s]);
//}
//Send BCC Mail id
string[] strmailBCC = { "Nagarajan.R2@saint-gobain.com" }; //BCC Address
for (int k = 0; k < strmailBCC.Length; k++)
{
// mail.Bcc.Add(strmailBCC[k]);
}
mail.Body = (MailTxt);
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "10.87.10.11";
smtp.Send(mail);
}
}
catch (SqlException ex)
{
string errorInfo = "";
errorInfo += "";
errorInfo += "
";
MailMessage mail = new MailMessage();
mail.Subject = "PENDING INVOICE STATISTICS-SEKURT INDIA"; //Subject
mail.From = new MailAddress("Pending Invoice Statistics-Sekurt "); //From Address
string[] strmailTO = { "Nagarajan.R2@saint-gobain.com" }; //TO Address
for (int i = 0; i < strmailTO.Length; i++)
{
mail.To.Add(strmailTO[i]);
}
//Send CC Mail id
//string[] strmailCC = { "Lavanya.C@saint-gobain.com" };
//for (int s = 0; s < strmailCC.Length; s++)
//{
// mail.CC.Add(strmailCC[s]);
//}
//send BCC mail id Error Message
string[] strmailBCC = { "Nagarajan.R2@saint-gobain.com" }; //BCC Address
for (int j = 0; j < strmailBCC.Length; j++)
{
// mail.Bcc.Add(strmailBCC[j]);
}
mail.Body = (errorInfo);
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "10.87.10.11";
smtp.Send(mail);
}
}
}
"; MailTxt += " "; MailTxt += " "; MailTxt += " "; MailTxt += "
| "; MailTxt += " PENDING FOR INVOICE STATISTICS-SEKURT INDIA "; MailTxt += " (Report as on: " + DateTime.Now.ToString("dd-MMM-yyyy hh:mm tt") + ") "; MailTxt += " |
"; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; } for (int i = 0; i < DTMailTrigger.Rows.Count; i++) { MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += "
| Status | Total | |
| Invoice Pending for OCR | " + DTMailTrigger.Rows[0][0].ToString() + " | SSC-Team |
| Invoice Reject | " + DTMailTrigger.Rows[0][1].ToString() + " | FSG-Team |
| Invoice Pending For EDIFACT | " + DTMailTrigger.Rows[0][2].ToString() + " | IT-Team |
| Invoice Pending For Integration | " + DTMailTrigger.Rows[0][3].ToString() + " | IT-Team |
";
errorInfo += " ";
errorInfo += "";
errorInfo += " ";
errorInfo += "
|
Wednesday 28 May 2014
Classic ASP Page
Sample Page
Post Next page
Stored Procedure and Retrieval Date
Classic ASP page
<%
'Variable Declaration
Dim strcon,sqry,StrGender,SQL,names,rs,cmd,ORC,adocmd,conn
'Connection String
Set strcon=Server.CreateObject("ADODB.Connection")
'Database Connection String
strcon.open "PROVIDER=SQLOLEDB.1;DATA SOURCE=L04webche012;UID=demo;PWD=demo;database=Demo"
'Parameter
Set adocmd = Server.CreateObject("ADODB.Command")
With adocmd
Set .ActiveConnection = strcon
'Stored procedure Value
.CommandType = 4
.CommandTimeout = 180
'Procedure Name
.CommandText = "sample_getvalue"
'Parameter Value
.Parameters.Append .CreateParameter("@names", 200, 1, 30, Request.Form("Name"))
Set ORC = .Execute
response.Write "Successfull"
End With
%>
| id | Name |
| <%= RS.Fields("id")%> | <%= RS.Fields("names")%> |
Tuesday 27 May 2014
Asp.Net Design page
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
CodeFile="frm_Gtrk_Glass_Loading.aspx.cs" Inherits="frm_Gtrk_Glass_Loading" Title="G-Track Glass Loading" %>
<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %>
Asp.Net CS Page
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using BussineLayer;
public partial class frm_Gtrk_Glass_Loading : System.Web.UI.Page
{
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
string strApp = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txttruckType.Attributes.Add("ReadOnly", "ReadOnly");
txtTrnsName.Attributes.Add("ReadOnly", "ReadOnly");
txtTruckno.Attributes.Add("autocomplete", "off");
txtTrnsName.Attributes.Add("autocomplete", "off");
txtDrivername.Attributes.Add("autocomplete", "off");
txtLicence.Attributes.Add("autocomplete", "off");
txtmobile.Attributes.Add("autocomplete", "off");
txtRemarks.Attributes.Add("autocomplete", "off");
}
}
public string GenerateReqNumber()
{
try
{
string GenReqNo;
Int64 NextNum;
DataTable DtGeneReqNo = new DataTable();
DtGeneReqNo = ObBLL.Get_Gtrk_Generatereqno();
if (DtGeneReqNo.Rows.Count > 0 && DtGeneReqNo.Rows[0]["DOC_NO"].ToString() != "")
{
GenReqNo = DtGeneReqNo.Rows[0]["DOC_NO"].ToString();
NextNum = Convert.ToInt64(GenReqNo);
NextNum = NextNum + 1;
GenReqNo = Convert.ToString(NextNum);
GenReqNo = GenReqNo.PadLeft(10, '0');
return GenReqNo;
}
else
{
GenReqNo = "0000000001";
return GenReqNo;
}
}
catch (Exception ex)
{
return "Error in Generation Request Numbers";
}
}
[WebMethod]
public static string TrlNo(string TruckNo)
{
string result = "";
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
DataTable DtTruck = new DataTable();
ObBLL.TruckType = TruckNo.ToString().Trim();
DtTruck = ObBLL.Get_Gtrk_truck_Type(ObBLL);
if (DtTruck.Rows.Count > 0)
{
for (int i = 1; i <= DtTruck.Rows.Count; i++)
{
result = result + "
";
return result;
}
[WebMethod]
public static string TransName(string Transportname)
{
string result = "";
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
DataTable DtTrans = new DataTable();
ObBLL.TransName = Transportname.ToString().Trim();
DtTrans = ObBLL.Get_Transport_Name(ObBLL);
if (DtTrans.Rows.Count > 0)
{
for (int i = 1; i <= DtTrans.Rows.Count; i++)
{
result = result + "
";
return result;
}
[WebMethod]
public static string TrackNumber(string TrackNo)
{
string result = "";
BussineLayer.BGTrack objBLL = new BussineLayer.BGTrack();
DataTable DtTruck = new DataTable();
objBLL.TruckNo = TrackNo.ToString().Trim();
DtTruck = objBLL.Get_Gtrack_tracknumber(objBLL);
if (DtTruck.Rows.Count > 0)
{
for (int i = 1; i <= DtTruck.Rows.Count; i++)
{
result = result + "
";
return result;
}
public void btnSave_Click(object sender, EventArgs e)
{
string[] Transnames = new string[100];
string[] TrackTypes = new string[100];
TrackTypes = txttruckType.Text.Split('-');
Transnames = txtTrnsName.Text.Split('-');
string DocNo = "";
string TruckTime = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss");
DataTable DtInsInfor = new DataTable();
ObBLL.TruckNo = txtTruckno.Text.ToString().Trim();
ObBLL.Plantcode = Session["Client"].ToString();
ObBLL.TruckType = TrackTypes[0].ToString().Trim();
ObBLL.TransType = Transnames[0].ToString().Trim();
ObBLL.Remarks = txtRemarks.Text.ToString().Trim();
ObBLL.DriverName = txtDrivername.Text.ToString().Trim();
ObBLL.DrivLinceno = txtLicence.Text.ToString().Trim();
ObBLL.DriveMobileno = txtmobile.Text.ToString().Trim();
ObBLL.Createby = Session["UserName"].ToString();
if (Transnames[0].Length == 10 && TrackTypes[0].Length == 3)
{
ObBLL.Flag = "IN";
DocNo = ObBLL.Gtrk_UserInformation(ObBLL);
}
else
{
ObBLL.Flag = "OUT";
DocNo = ObBLL.Gtrk_UserInformation(ObBLL);
}
Resetvalue();
if (DocNo != "0000000000" && DocNo.Length != 0)
{
hdnDocNo.Value = DocNo;
hdnDate.Value = TruckTime;
ScriptManager.RegisterStartupScript(UPDGlass, this.GetType(), "Click", "SuccessMessage();", true);
}
else
{
ScriptManager.RegisterStartupScript(btnSave, btnSave.GetType(), "Failer", "javascript:alert('Wrong user information');", true);
}
}
private void Resetvalue()
{
txtTruckno.Text = "";
txttruckType.Text = "";
txtTrnsName.Text = "";
txtDrivername.Text = "";
txtLicence.Text = "";
txtmobile.Text = "";
txtRemarks.Text = "";
}
}
Jquery Validation
function pageLoad()
{
$("input[type='text']").live("keydown", function(){ $(this).css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" }); });
$(".imgtrucktype").click(function() {
$(".divAutoContent").html("");
$(".divProjectPopup").show();
});
$(".imgSearch").click(function(evt){
evt.preventDefault();
var Gtrkno=$(".txtTypeCode").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TrlNo",
data: '{"TruckNo":"'+Gtrkno+'"}',
success :function(res){
$("#divAutoContent").html(res.d);
},
error: function(){
alert("Error Ajax");
}
});
});
$(".trucktypes").live("click",function(){
$(".txttruckType").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
var reqNo = $(this).parents("tr").find(".trucktypes").html();
var truck=reqNo.split('-');
$(".txttruckType").val(truck.join('-'));
//$(".txttruckType").val(truck[1]);
// $(".lblTruckType").text(truck[1]);
$(".divProjectPopup").hide();
if($(".txttruckType").val().length!=0){
$(".txttruckType").attr("disabled",true);
}
else
{
$(".txttruckType").attr("enabled",false);
}
});
$(".modelPopupClose").click(function(){
$(".divProjectPopup").hide();
$(".divProjectPopup1").hide();
$(".divProjectPopup2").hide();
});
$(".imgTranname").click(function(evt) {
evt.preventDefault();
$(".divAutoContent1").html("");
$(".divProjectPopup1").show();
});
$(".imgTranSech").click(function(evt){
evt.preventDefault();
var Gtranname=$(".txtTransName").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TransName",
data: '{"Transportname":"'+Gtranname+'"}',
success :function(res){
$("#divAutoContent1").html(res.d);
},
error: function(){
alert("Error Ajax");}
});
});
$(".transname").live("click",function(){
$(".txtTrnsName").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
var Reqname =$(this).parents("tr").find(".transname").html();
var Trans=Reqname.split('-');
// $(".txtTrnsName").val(Trans.shift());
$(".txtTrnsName").val(Trans.join('-'));
// $(".lblTranname").text(Trans.join('-'));
$(".divProjectPopup1").hide();
if($(".txtTrnsName").val().length!=0){
$(".txtTrnsName").attr("disabled",true);
}
else
{
$(".txtTrnsName").attr("enabled",false);
}
});
$(".btnDediTruck").click(function(){
$(".divAutoContent2").html("");
$(".divProjectPopup2").show();
});
$(".imgTracknumber").click(function(evt){
evt.preventDefault();
var GetTruckNo=$(".txtTrackNumber").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TrackNumber",
data: '{"TrackNo":"'+GetTruckNo+'"}',
success :function(res){
$("#divAutoContent2").html(res.d);
},
error: function(){
alert("Error Ajax");}
});
});
$(".TrackNo").live("click",function(){
$(".txtTruckno").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
//var Track =$(this).parents("tr td.eq(1)").find(".TrackNo").html();
var Track=$(this).closest('tr').children('td:eq(1)').text();
var TrackNos=Track.split('^');
$(".txtTruckno").val(TrackNos[0]);
$(".txttruckType").val(TrackNos[2]);
$(".txtTrnsName").val(TrackNos[1]);
$(".divProjectPopup2").hide();
if($(".txttruckType").val().length!=0 || $(".txtTrnsName").val().length != 0 || $(".txtTruckno").val().lenght!=0){
$(".txttruckType").attr("disabled",true);
$(".txtTrnsName").attr("disabled",true);
$(".txtTruckno").attr("disabled",true);
}
else
{
$(".txttruckType").attr("enabled",false);
$(".txtTrnsName").attr("enabled",false);
$(".txtTruckno").attr("enabled",false);
}
});
$(".btnSave").click(function(){
//alert($(".divProjectPopup").attr("style"));
if($(".divMask").is(":visible")){
return false;
}
if($(".txtTruckno").val().length == 0)
{
alert("Please enter Truck No.");
$(".txtTruckno").focus();
$(".txtTruckno").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txttruckType").val().length == 0)
{
alert("Please enter Truck type");
$(".txttruckType").focus();
$(".txttruckType").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtTrnsName").val().length == 0)
{
alert("Please enter Transport name");
$(".txtTrnsName").focus();
$(".txtTrnsName").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtDrivername").val().length == 0)
{
alert("Please enter Driver name");
$(".txtDrivername").focus();
$(".txtDrivername").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtLicence").val().length == 0)
{
alert("Please enter Licence no");
$(".txtLicence").focus();
$(".txtLicence").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtmobile").val().length == 0)
{
alert("Please enter Mobile no");
$(".txtmobile").focus();
$(".txtmobile").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtmobile").val().length<10)
{
alert("Please enter ten digital mobile number");
$(".txtmobile").focus();
$(".txtmobile").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
});
$(".txtmobile,.txtDocno").live("keydown",function(evt){
var n=evt.keyCode;
if(!((n==8) || (n==9) || (n>=46 && n<=57) || (n>=96 && n<=105)))
evt.preventDefault();
});
$(".txtTruckno").bind("keypress", function (event) {
var regex = new RegExp("^[a-zA-Z0-9]+$");
var key = String.fromCharCode(!event.charCode ? event.which : event.charCode);
if (!regex.test(key)) {
event.preventDefault();
return false;
}
});
$(".btnReset").click(function(){
Resetvalue();
});
$(".sapnlnkDoc").click(function(){
var PrintDocumentNo =$(this).parents("tr").find(".sapnlnkDoc").html();
//var a=this($(".sapnlnkDoc").text());
var url="PrintDocumentNo.aspx?&DocNo=" + PrintDocumentNo + "";
window.open(url,'_blank','height=830,width=700,status=yes,toolbar=no,menubar=no,location=no,scrollbars=yes,resizable=no,titlebar=no' );
});
//----------------------------------------------Truck out Entry ---------------------//
$(".btnTrckOutentry").click(function(){
if($(".txtTruckOutDoc").val().length==0)
{
alert("Please enter Document No.");
$(".txtTruckOutDoc").focus();
$(".txtTruckOutDoc").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
});
$(".btnTruckExit").click(function(){
var url="frm_GTrack_Home.aspx";
window.open(url,"_self");
});
$(".btnResets").click(function(){
$(".txtDocno").val("");
});
}
function Resetvalue()
{
$(".txtTruckno").val("");
$(".txttruckType").val("");
$(".txtTrnsName").val("");
$(".txtDrivername").val("");
$(".txtLicence").val("");
$(".txtmobile").val("");
$(".txtRemarks").val("");
}
function SuccessMessage(){
if (!alert("Document Successfully Saved.(Document No:" + $(".hdnDocNo").val() +")\n\n"+ "Truck in Entry-Date & Time :" + $(".hdnDate").val() +"")){
var url="PrintDocumentNo.aspx?&DocNo=" + $(".hdnDocNo").val() + "";
window.open(url,'_blank','height=850,width=670,status=yes,toolbar=no,menubar=no,location=no,scrollbars=no,resizable=no,titlebar=no' );
}
}
| Incoming Trucks for Picking Materials | ||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||
|
| " + DtTruck.Rows[i - 1]["TRUCKTYPE"].ToString() + " |
| No Records Found... |
| " + DtTrans.Rows[i - 1]["TRANSNAME"].ToString() + " |
| No Records Found... |
| " + DtTruck.Rows[i - 1]["GTRK_TRUCK_NO"].ToString() + " |
| No Records Found... |
Number Ranger
CREATE TABLE [dbo].[M_NUMBER_RANGE](
[GTRK_NAME1] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GTRK_SUBNAME] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_PREFIX_LETTER] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_STARTING_NUMNER] [numeric](18, 0) NULL,
[GTRK_ENDING_NUMBER] [numeric](18, 0) NULL,
[GTRK_CURRENT_NUMBER] [numeric](18, 0) NULL,
[GTRK_INCREMENT] [numeric](18, 0) NULL,
[GTRK_PERCENTAGE] [numeric](18, 0) NULL,
[GTRK_NUMBER_LENGTH] [numeric](18, 0) NULL,
[GTRK_GROUP_TABLE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_REMARKS] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_ENTEREDDT] [datetime] NULL,
[GTRK_ENTEREDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_MODIFIEDDT] [datetime] NULL,
[GTRK_MODIFIEDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[GTRK_NAME1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GTRACK-V2 GTRACK-V2 10000000 99999999 543 1 0 8 GTRK_TRUCK_DETAILS NULL NULL 2014-01-29 11:15:46.857 ADMIN NULL NULL
Ranger Procedure :
/*************************************************************
CREATED BY : NAGARAJAN.R
CREATED DATE : 29/01/2014
PURPOSE : Get the Maximum Doc Number
EXECUTION : USP_GET_DOCNO 'GTRACK-V2','',''
*************************************************************/
CREATE PROCEDURE USP_GET_DOCNO
(
@ID VARCHAR(25) = NULL,
@DOCNO VARCHAR(250) OUTPUT,
@INDEXNO VARCHAR(9) OUTPUT
)
AS
BEGIN
SELECT @DOCNO = GTRK_PREFIX_LETTER + '' + convert(VARCHAR, GTRK_STARTING_NUMNER + (GTRK_CURRENT_NUMBER + GTRK_INCREMENT))
, @INDEXNO = (GTRK_CURRENT_NUMBER + GTRK_INCREMENT)
FROM
M_NUMBER_RANGE
WHERE
GTRK_NAME1 = @ID
SELECT @DOCNO AS DOCNO
, @INDEXNO AS INDEXNO
END
Automation Generated Number
--EXEC GTRK_CREATETRANSACTIONINFO'TNK4385784','839','HPL','RNJ','TEST','Nagarajan','TN4356','9842946063','Nagu','IN'
CREATE PROC [dbo].[GTRK_CREATETRANSACTIONINFO]
(
@GTRK_TRUCK_NO VARCHAR(15),
@GTRK_PLANT_CODE VARCHAR(15),
@GTRK_TRUCK_TYPE_CODE VARCHAR(100),
@GTRK_TRANSPORTER_CODE VARCHAR(100),
@GRRK_REMARKS VARCHAR(250),
@GTRK_DRIVER_NAME VARCHAR(50),
@GTRK_LICENCE_NO VARCHAR(15),
@GTRK_MOBILE_NO VARCHAR(10),
@GTRK_CREATE_BY VARCHAR(50),
@FLAG VARCHAR(10),
@referenceNumber VARCHAR(15)=NULL OUTPUT
)
AS
BEGIN
--DECLARE @AUTOMATION_DOCNO VARCHAR(10)
--SELECT @AUTOMATION_DOCNO=isnull(MAX(ISNULL(GTRK_DOC_NO,00000000001)+1),0000000001) FROM GTRK_TRUCK_DETAILS
--Find Maximum Order Number
--DECLARE @OrderNumber Varchar(50)
--SELECT @OrderNumber =CAST(ISNULL(Max(GTRK_DOC_NO),0) + 1 AS VARCHAR(40)) FROM GTRK_TRUCK_DETAILS
--SELECT @OrderNumber=STUFF('00000000', 8-LEN(@orderNumber)+1, LEN(@orderNumber),@orderNumber)
-- SELECT @OrderNumber
DECLARE @OrderNumber VARCHAR(10)
DECLARE @CURRENTNO VARCHAR(9)
EXEC USP_GET_DOCNO 'GTRACK-V2', @OrderNumber OUTPUT ,@CURRENTNO OUTPUT
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO GTRK_TRUCK_DETAILS(GTRK_DOC_NO,GTRK_TRUCK_NO,GTRK_PLANT_CODE,GTRK_TRUCK_TYPE_CODE,GTRK_TRANSPORTER_CODE,GTRK_MATL_MVMNT_CODE,GTRK_MATL_CODE,GTRK_REMARKS,GTRK_TRUCK_IN,GTRK_DRIVER_NAME,
GTRK_LICENCE_NO,GTRK_MOBILE_NO,GTRK_CREATE_BY,GTRK_CREATED_DATE,GTRK_MODIFIED_BY,GTRK_MODIFIED_DATE,GTRK_FLAG_TYPE)
VALUES(@OrderNumber,@GTRK_TRUCK_NO,@GTRK_PLANT_CODE,@GTRK_TRUCK_TYPE_CODE,@GTRK_TRANSPORTER_CODE,'10','10000001',@GRRK_REMARKS,getdate(),@GTRK_DRIVER_NAME,
@GTRK_LICENCE_NO,@GTRK_MOBILE_NO,@GTRK_CREATE_BY,getdate(),NULL,NULL,@FLAG)
set @referenceNumber=@OrderNumber
----------------UPDATE Current Number range table---------------
UPDATE M_NUMBER_RANGE SET GTRK_CURRENT_NUMBER = @CURRENTNO WHERE GTRK_NAME1 ='GTRACK-V2'
----------------------------------------------------------------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
--select *From GTRK_TRUCK_DETAILS
--0010953525
Trigger
1)When you create a trigger you have to identify event and action of your trigger
2)A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. .
3) A trigger is triggered automatically.
4)It does not pass parameters
Stored procedure
1)Explicitly executed by invoking call to SP
2)you can not join SP
3)We can call a stored procedure from front
4)Stored procedure can take the input parameters
Function
1)can be used with Select statement
2)Not returning output parameter but returns Table variables
3) Cannot have transaction within function
4)Cannot be used to change server configuration
Difference between Stored Procedure and Function
1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can't go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
Array List
1.Array List is a List.
2.In Array list we can only add items to the list.
3.Here we Can Add any datatype value,Every item in Arraylist is treated as object.
4.Index Numeric.
5.Data in Arraylist is stored in as only the value.
6.Slow when come to search
Hash Table
1.Hash Table is a map.
2.In Hashtable we can add data with the key.
3.Retrieving by key in Hashtable is faster than retrieving in Arraylist.
4.Index can be string also.
5.Data in Hashtable is stored in the key-value pair.
6.Fast when searching.
So lets first start with the difference between 1.0 and 2.0.
Support for 64 bit application.
Generics
SQL cache dependency
Master pages
Membership and roles
Now the next difference .NET 2.0 and 3.0
=========================================
WCF
WPF
WWF
WCS ( card space)
3.0 and 3.5
==========================================
LINQ
Ajax inbuilt
ADO Entity framework
ADO data services
Multi targeting
Finally 3.5 and 4.0
===========================================
MEF
Parallel computing
DLR dynamic
Code contract
language runtime
Lazy initialization
Custom controls: A control authored by a user or a third-party software vendor that does not belong to the .NET Framework class library. This is a generic term that includes user controls. A custom server control is used in Web Forms (ASP.NET pages). A custom client control is used in Windows Forms applications.
User Controls: In ASP.NET: A user-authored server control that enables an ASP.NET page to be re-used as a server control. An ASP.NET user control is authored declaratively and persisted as a text file with an .ascx extension. The ASP.NET page framework compiles a user control on the fly to a class that derives from the System.Web.UI.UserControl class.
Difference between Sql server 2005 and 2008
Sr No SQL Server 2005 SQL Server 2008
1 XML datatype is introduced. XML datatype is used.
2 Can not encrypt the entire database. Can encrypt the entire database introduced in 2008.
3 Datetime is used for both date and time. Date and time are seperately used for date and time
4 No table datatype is included. Table datatype introduced.
5 SSIS is started using. SSIS avails in this version.
6 CMS is not available. Central Management Server(CMS) is Introduced.
7 PBM is not available Policy based management(PBM) server is Introduced
What is the difference between unique key and primary key?
Unique Key Primary Key
Unique key can have nulls Primary key cannot have nulls.
In a single table we can create multiple unique keys. In a single table we can have only one primary key.
It creates a non-clustered index by default. It created a clustered index by default.
Both unique keys and primary keys can be referenced by foreign key.
View State:
View state is maintained in page level only.
View state of one page is not visible in another page.
View state information stored in client only.
View state persist the values of particular page in the client (browser) when post back operation done.
View state used to persist page-instance-specific data. (Read more)
Session State:
Session state is maintained in session level.
Session state value is available in all pages within a user session.
Session state information stored in server.
Session state persist the data of particular user in the server. This data available till user close the browser or session time completes.
Session state used to persist the user-specific data on the server side. (Read more)
Usage
If you want to access the information on different web pages, you can use SessionState- If you want to access from the same page, then you can use Viewstate
Security
Session state provides more security when compared with view state as the data value is stored in server side
Convert .tostring() and variable.tostirng()
The basic difference between them is “Convert.ToString(variable)” handles NULL values even if variable value become null but “variable.ToString()” will not handle NULL values it will throw a NULL reference exception error. So as a good coding practice using “convert” is always safe.
Example
//Returns a null reference exception for str.
string str;
object i = null;
str = i.ToString();
//Returns an empty string for str and does not throw an exception. If you dont
string str;
object i = null;
str = Convert.ToString(i);
Difference between GET and POST Methods?
GET Method ():
1) Data is appended to the URL.
2) Data is not secret.
3) It is a single call system
4) Maximum data that can be sent is 256.
5) Data transmission is faster
6) this is the default method for many browsers
POST Method ():
1) Data is not appended to the URL.
2) Data is Secret
3) it is a two call system.
4) There is no Limit on the amount of data. That is characters any amount of data can be sent.
5) Data transmission is comparatively slow.
6) No default and should be explicitly specified.
Difference between authorization and authentication?
Authorization is a process of allowing or denying resources to particular user or record
Declaration of authorization is
Sometimes authorization allows the unauthorized persons at that time we will use
Authentication means
Authentication is a process where we identify the credentials of user i.e. username, password and create an identity to mention user as an authenticated.
Difference between view state and hidden field?
viewstate is secured hidden field is insecure
Viewstate will store large amount of data but hidden filed will store small amount of data.
Difference between Abstract Class and Interface
In abstract class method can have definition as well as declaration also. But Interface should have only definition.
All the Methods are Public as default and don’t have any access Modifier Controls in interface, whereas for abstract class we can have access modifier for methods.
Abstract class can have constructor or destructor, whereas interface not.
Abstract class can’t be part of multiple inheritance and we can implement multiple interface.
Differences between Array list and Hash table?
Hash table store data as name, value pair. While in array only value is store.
To access value from hash table, you need to pass name. While in array, to access value, you need to pass index number.
you can store different type of data in hash table, say int, string etc. while in array you can store only similar type of data.
Database count to send mail daily reports
CREATE PROC SEND_SEKURIT_PENDING_AUTOMAIL
AS
BEGIN
SELECT A.ORD, A.NAMEE,A.AGEING,A.Pending_For_OCR_DW01,B.Pending_For_OCR_DW02,NULL as NC01, NULL as NC02,A.Pending_For_OCR_DW01+B.Pending_For_OCR_DW02 as Total,A.Responsibility
INTO #TEMPSEKURITMAILALTER FROM
(
SELECT 1 as ORD,'OCR Process' as NAMEE, '<2' AS AGEING, count(BARCODE_NO) AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 1 as ORD, 'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 1 as ORD,'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW01' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as C
LEFT OUTER JOIN
(
SELECT 2 as ORD, 'Clarification' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS Rejection_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as D
ON C.AGEING=D.AGEING
UNION
SELECT E.ORD,E.NAMEE,E.AGEING,E.EDI_Pending_DW01,F.EDI_Pending_DW02,NULL as NC01, NULL as NC02,E.EDI_Pending_DW01+F.EDI_Pending_DW02 as Total,E.Responsibility
FROM
(
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW02' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'
SET @Body =@Body +''
SET @Body =@Body +'
'
--SET @Body = @Body + '
'
PRINT @Body
DROP TABLE #TEMPSEKURITMAILALTER
--
--EXEC msdb.dbo.sp_send_dbmail
--
--@recipients = 'Vivek.Srivatsan@saint-gobain.com;Muralidaran.M@saint-gobain.com;Prithviraj.S@saint-gobain.com;Sujoy.Mukherjee@saint-gobain.com;Parthasarathi.S@saint-gobain.com;Gowdhami.M@saint-gobain.com;Chandra.G@saint-gobain.com',
--@copy_recipients = 'Sundaram.DR@saint-gobain.com;Jagathratchagan.J@saint-gobain.com;Nagarajan.R2@saint-gobain.com',
--@subject = @Subject,
--@body = @Body,
--@body_format = 'HTML'
END
| ' SET @Body =@Body +'Sekurit-Pending Invoice Statistics | |||||||
| Status | ' SET @Body =@Body +'Ageing | ' SET @Body =@Body +'DW01 | ' SET @Body =@Body +'DW02 | ' SET @Body =@Body +'NC01 | ' SET @Body =@Body +'NC02 | ' SET @Body =@Body +'Total | ' SET @Body =@Body +'Responsibility |
| '+ISNULL(@NAME,'')+' | ' END SET @Body=@Body+''+ISNULL(@AGEING,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | ' SET @Body=@Body+''+ISNULL(@NC01,0)+' | ' SET @Body=@Body+''+ISNULL(@NC02,0)+' | ' SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | ' SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' | ' SET @Body=@Body+'
| '+ISNULL(@NAME,'')+' | ' SET @Body=@Body+''+ISNULL(@AGEING,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | ' SET @Body=@Body+''+ISNULL(@NC01,'')+' | ' SET @Body=@Body+''+ISNULL(@NC02,'')+' | ' SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | ' SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' |
| *This is a system generated mail.Please donot reply to this. |
Subscribe to:
Posts (Atom)