Read User's Comments Hiding the Paragraph Text using Jquery
We can easily hide and show the Html elements and its contents using Jquery Hide() function. The bellow code is used to hide the paragraph.
<html>Jquery Code for hiding the elements
<head runat="server">
<title></title>
<link href="css/ui-lightness/jquery-ui-1.8.16.custom.css" type="text/css" />
<script src="Scripts/jquery-1.6.4.min.js" type="text/javascript"></script>
<script src="Scripts/jquery-ui-1.8.16.custom.min.js" type="text/javascript"></script>">
<script type="text/javascript>
$(document).ready(function () {
$("p").click(function () {
$(this).hide();
});
$("#<%=txtDOB.ClientID%>").datepicker();
});</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>If u Click me i will Hide!</p>
<p>Click Me to Hide!</p>
<div>Select Your DOB<asp:TextBox ID="txtDOB" runat="server"></asp:TextBox></div>
</div>
</form>
</body>
</html>
<script type="text/javascript>
$(document).ready(function () {
$("p").click(function () {
$(this).hide();
});
});
</script>
How to avoid Maximum request length exceeded in asp.net
Uploading files via the FileUpload control gets tricky with big files. The default maximum filesize is 4MB - this is done to prevent denial of service attacks in which an attacker submitted one or more huge files which overwhelmed server resources. If a user uploads a file larger than 4MB, they'll get an error message: "Maximum request length exceeded."
Increasing the Maximum Upload Size
The 4MB default is set in machine.config, but you can override it in you web.config. For instance, to expand the upload limit to 20MB, you'd do this:
<system.web>Since the maximum request size limit is there to protect your site, it's best to expand the file-size limit for specific directories rather than your entire application. That's possible since the web.config allows for cascading overrides. You can add a web.config file to your folder which just contains the above, or you can use the
<httpRuntime executionTimeout="240" maxRequestLength="20480" />
</system.web>
<location path="TestWeb\Upload">
<system.web>
<httpRuntime executionTimeout="110" maxRequestLength="20000" />
</system.web>
</location>
Limit Viewstate length in Asp.Net
In some occasions browsers may block or truncate generated viewstate due to it’s length. This short tweak may solve the issue by dividing the viewstate to independent hidden fields based on the number you provide as value. In this example maxPageStateFieldLength set to 50 this means when viewstate get beyond 50 characters it will be broken to different hidden fields holding viewstate’s value.
<system.web>
<pages maxPageStateFieldLength="50">
<controls />
</pages>
To trim the Textbox Using Jquery
- The Jquery.trim() function is used to remove the extra spaces from the left & right side of the string.
//To Removing the space in string using Jquery's Trim function
$(document).ready(function () {
var strName = $("#txtUserName").val();
strName = jQuery.trim(strName);
});
To prevent the cut, copy and paste in the Password Textbox using JQuery.
- To bind the event for the cut, copy and paste. Declare a function and make the event to prevent using ‘e.preventDefault();’.
- Then we can alert the user, this password textbox won’t allow cut, copy and paste.
//To Prevent the Cut, Copy, Paste in the Password.
$(document).ready(function () {
$('#<%=txtPassword.ClientID %>').bind('cut copy paste', function (e) {
e.preventDefault();
alert('Password is not allowed Cut-Copy Paste!');
});
});
Focusing the cursor from one textbox to another when entering the ‘Enter’ Key using JQuery.
// To Bind the function to the textbox for moving the cursor from one textbox to another
$(document).ready(function () {
$("input:text:first").focus();
$("input:text").bind("keydown", function (e) {
if (e.which == 13) {
e.preventDefault();
var nextIndex = $('input:text').index(this) + 1;
if ($('input:text')[nextIndex] != null)
$('input:text')[nextIndex].focus();
}
});
});
- Here $("input:text:first").focus(); this code helped to focus the first textbox and
- $("input:text").bind("keydown", function (e) {}); is used to bind the ‘Keydown’ event to the each textbox available textbox.
- $('input:text').index(this) is used to get the index of the current text box.
if ($('input:text')[nextIndex] != null)
- This if block is used to check if the next index is having textbox control or not. If the control is available then this will focus into the next textbox.
Working with Dropdown List Using Jquery
When the Items of dropdown box is selected then we need to get the item and display it into lable and also based on the one drop down we need to load the other. Normaly we will write this in server side code, but this can be done with client side iteslef using Jquery.
For that First we need to add the two dropdown list controls first in our aspx page
<div align="center">The bellow Jquery is used to bind the 'Key Change' Events to the dropdown box using the 'Bind' Method. The bind method of control is used bind the event before the DOM is loading.
<div id="message" class="Highlight" style="width: 300px;" align="center">
</div>
</div>
<table> <tr>
<td>
<asp:Label ID="lblEduLevel" runat="server" Text="Select Education Level:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlEduLevel" runat="server" Style="border-width:thin; border-color: #7F9DF9">
<asp:ListItem Text="SSLC" Value="0"></asp:ListItem>
<asp:ListItem Text="HSC" Value="1"></asp:ListItem>
<asp:ListItem Text="Graduate" Value="2"></asp:ListItem>
<asp:ListItem Text="Post Graduate" Value="3"></asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlMajor" runat="server" Style="border-width:thin; border-color: #7F9DF9">
<asp:ListItem Value="0" Text="--Select--"></asp:ListItem>
</asp:DropDownList>
</td>
</tr></table>
// Working with DropdownlistHere $('#<%=ddlEduLevel.ClientID %>') is dropdown list client Id and $('#message') is the 'Div' elements to dispaly the message. The .Css() is used to apply the Css Style to the control and the .text() method of jquery is used to assign the text to the div elements. Here $(this) is used jquery dorpdown list 'ddlEduLevel' and the .find() method with (":selected").text() is used to get the text of the selected Item, $(this).val() is used to display the value of the selected Item.
$(document).ready(function () {
$('#<%=ddlEduLevel.ClientID %>').bind('keyup change', function () {
if ($(this).val() != "") {
// this used to display the user message to Div element
$('#message').css("color", "blue");
$('#message').text("Qulification: " + $(this).find(":selected").text() + " And the Edu Level: " + $(this).val());
}
});
});
The following Jquery is used to loading the another dropdown control based on the current dropdown's selected Item.
$(document).ready(function () {
$('#<%=ddlEduLevel.ClientID %>').bind('keyup change', function () {
$("Select[id$=ddlMajor] > option").remove();
if ($(this).val() == "3") {
$('#<%=ddlMajor.ClientID %>').append("<option value='1'>Computer Application</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='2'>Computer Science</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='3'>Maths</option>");
}
else if ($(this).val() == "0") {
$('#<%=ddlMajor.ClientID %>').append("<option value='1'>General English</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='2'>General Tamil</option>");
}
else if ($(this).val() == "1") {
$('#<%=ddlMajor.ClientID %>').append("<option value='1'>English</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='2'>Tamil</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='3'>Computer</option>");
}
else if ($(this).val() == "2") {
$('#<%=ddlMajor.ClientID %>').append("<option value='1'>English</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='2'>Tamil</option>");
$('#<%=ddlMajor.ClientID %>').append("<option value='3'>Computer</option>");
}
});
}); The above we have used two dropdowns called ddlEduLevel,ddlMajor and we can load the ddlMajor based on the item selected from ddlEduLevel. $("Select[id$=ddlMajor] > option").remove(); is used to remove the all items from the list and the append() method of the dropdown list is used to add the new element into the dropdown list.
To Validate and Highlighting a TextBox(s) using JQuery
In our Asp.Net Client Side validation is most important and needed one. We can achive this using Jquery and when the user is focusing into the textbox control we need to highlight the controls. 
First we can add the aspx controls in the page using the following code in design view.
<div>
<table style="border-color: Gray; border-width: thin;">
<tr>
<td colspan="2">
<div style="background-color: Silver" width="50%">
<span>Welcome My JQuery Test Application! </span>
</div>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblUser" runat="server" Text="Enter User Name:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
</td>
<td>
<span id="errUser" style="visibility: hidden">!</span>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblPassword" runat="server" Text="Enter Password:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox>
</td>
<td>
<span id="errPassword" style="visibility: hidden">!</span>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblloc" runat="server" Text="Enter Location:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtLocation" runat="server"></asp:TextBox>
</td>
<td>
<span id="Span1" style="visibility: hidden">!</span>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblEduLevel" runat="server" Text="Select Education Level:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlEduLevel" runat="server" Style="border-width:thin; border-color: #7F9DF9">
<asp:ListItem Text="SSLC" Value="0"></asp:ListItem>
<asp:ListItem Text="HSC" Value="1"></asp:ListItem>
<asp:ListItem Text="Graduate" Value="2"></asp:ListItem>
<asp:ListItem Text="Post Graduate" Value="3"></asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlMajor" runat="server" Style="border-width:thin; border-color: #7F9DF9">
<asp:ListItem Value="0" Text="--Select--"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="BtnSubmit" runat="server" Text="Go!" />
</td>
</tr>
</table>
<table>
<tr>
<td>
<asp:Label ID="lblTestCopy" runat="server" Text="About your self:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtCopyToClipboard" runat="server" TextMode="MultiLine" Rows="5"
Width="300" Height="75"></asp:TextBox>
</td>
<td>
<asp:HyperLink ID="lnkHighlight" Text="Click here to copy!" runat="server"></asp:HyperLink>
</td>
</tr>
</table>
</div>
First we will validate the textbox controls using Jquery. Before that first load the JQuery Library using the bellow script.
<script src="JScript/jquery-1.6.1.js" type="text/javascript"></script>
Here the '$' is denoted the Jquery and the $(document).ready() is used to load the jquery before the DOM is loading.$("#txtUserName").val() is give the Textboxs value..
// To validating the User Name and Password text.
$(document).ready(function () {
$("#BtnSubmit").click(function () {
var StrMsg = "";
if ($("#txtUserName").val() == "") {
StrMsg = "User Name";
}
if ($("#txtPassword").val() == "")
StrMsg += StrMsg == "" ? "Password" : ", Password";
if (StrMsg != "") {
alert(StrMsg + " Are Required!");
return false;
}
//return true;
});
});
The Next thing is need to highlight the controls when we make focus to the control.
//When focusing the Asp.Net control and highlight the Particular controlFor this we need the CSS class name 'Highlight'.
// And applay & Remove the CSS Class.
$(document).ready(function () {
$(":text").focusin(function () {
$(this).addClass("Highlight");
});
$(":text").focusout(function () {
$(this).removeClass("Highlight");
});
$(":password").focusin(function () {
$(this).addClass("Highlight");
});
$(":password").focusout(function () {
$(this).removeClass("Highlight");
});
$("select").focusin(function () {
$(this).addClass("Highlight");
});
$("select").focusout(function () {
$(this).removeClass("Highlight");
});
});
.Highlight{ background: #fed; border: 1px solid #7F9DF9;}
::selection{background: #0000FF;}
Introduction to Jquery
About JQuery:
JQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript.
Download the Latest Jquery Library From Download JQuery or We can use CDN (Content Delivery Network). This will help you load the Jquery Library to your web site and also this will have more advantage when compare local downloaded.As it provides several advantages.
- You always use the latest JQuery framework.
- It reduces the load from your server..
- It saves bandwidth. JQuery framework will load faster from these CDN..
- The most important benefit is it will be cached, if the user has visited any site which is using JQuery framework from any of these CDN..
How to Use JQuery with Your ASP.Net :
- Just Download the Jquery library 1.6.2 and add to the Script of folder of asp.net, then just simply include Jquery lib using <script> </script>
<script src="JScript/jquery-1.6.1.js" type="text/javascript"></script>
- You can also use the CDN from the other web sites like google, MSN,etc.,
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>- Next write your own Javascript function with in the script tage.
//Code to load jQuery Framework from Microsoft CDN
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js" ></script>
//Code to load jQuery Framework from jQuery Site(EdgeCast CDN)
<script type="text/javascript" src="http://code.jquery.com/jquery-1.4.2.min.js"></script>
<script type="text/javascript"></script>
For example.,
<script src="JScript/jquery-1.6.1.js" type="text/javascript"></script>
<script type="text/javascript">
//My First JQuery Example
//To Test Button Click Event
$(document).ready(function () {
//Focus the First Textbox in the Form
$(":text:first").focus();
//This Allows to show message to the user while first Document Load
alert("Welcome To JQuery World!"); // This alert Raised @ the time of Page Load
//To Test Button Click Event
$("#btnMyTestJqery").click(function () {
alert("Welcome To JQuery World!");
return false;
});
});
</script>
<div><asp:Button ID="btnMyTestJqery" runat="server" Text="Go to JQueryWorld!" /></div>
How to use a simple AJAX Update Panel Control in Asp.Net
For using simple update panel in asp.net first need add the Script Manager, after that add the Update Panel control and add the <ContentTemplate> </ContentTemplate>, <Triggers>, </Triggers> tags.
- Inside the Content template please add the list of controls.
- Inside the Triggers add asp:AsyncPostBackTrigger for the event capture for the which controls event need to use the update panel.
The following codes are easy understand the AJAX Update panel control
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
The C# code behide is looks like :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
// Working with Ajax Update Panel
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
lblAge.Visible = false;
}
// When the txtDOB text change event fired we need to calculate the
//Age of the Give Person and display Age in Years with Days
protected void txtDOB_TextChanged(object sender, EventArgs e)
{
// TimeSpan class is used to find the time differnece and stored result temp
TimeSpan tsp=TimeSpan.MinValue;
try
{
DateTime dtDOB = Convert.ToDateTime(txtDOB.Text.Trim());
tsp = DateTime.Now - dtDOB;
}
catch (Exception ex)
{
lblAge.Visible = true;
lblAge.Text=ex.Message.Trim();
}
lblAge.Visible = true;
lblAge.Text = "Hi Mr/Ms." + TextBox1.Text.Trim() + ",
Your AGE:" + (tsp.Days / 365).ToString() + " Years " + (tsp.Days % 365).ToString() + " Days";
}
}
Some of Sql Server Circumstance
1.DECLARE @RowName Varchar(20)
SET @ROWName='SiteName'
SELECT * FROM M_Site ORDER BY @ROWName
What will be the Result?
ANSWER :
Msg 1008, Level 16, State 1, Line 3
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
2.SELECT 'test' Where exists(Select *)
Is the above Statement will throw error or Not, if Not what will be the Result?
ANSWER : test
3. What is Result for SELECT * , SELECT COUNT(*) and what is difference between them.
4. What is use of ‘WITH SCHEMABINDING’ in Sql Server?
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select statement must include the two-part names (schema. object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
SCHEMABINDING cannot be specified if the view contains alias data type columns.
What does this mean?
Well, to put it simply, once you create a view with schema binding, you cannot change the underlying tables in a way that would break the view. Examples of this would be removing columns or dropping tables that are specified in the view.
WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.
Triggers And it's Type
Triggers in SQL Server
- Normally the Triggers are special kind of stored procedure, it will fire when some event occured in the sQL object like Table, View.
- The triggers will fire before/After Insert, UPdate, Delete operations made on the Talbes or Views.
- Those Trigger is Classified into 2 categories.
1. After Triggers and
2. Instead Triggers.
1. After(For) Triggers:
The After triggers are excuted the After the above set operations(eigther Insert, Upadate or Delete)
Example:
--After Insert and After Update
CREATE TRIGGER ToAlert
ON M_Customer
FOR INSERT -- (OR)
FOR UPDATE
AS
SELECT 'You Have Inserted a New Record!'
GO
-- After Delete Tigger
CREATE TRIGGER ToDelAlert
ON M_Customer
AFTER DELETE
AS
SELECT 'You have Deleted a Records!'
GO
2. Instead Of Triggers
The instead of triggers are fired before the Operation what we given for the triggers (i.e., Insert, Update, Delete)
-- INSTED OF UPDATE/INSERT
CREATE TRIGGER ToAlert
ON M_Customer
INSTEAD OF INSERT -- (OR)
INSTEAD OF UPDATE
AS
SELECT 'You going to Make Insert a New Record or Update Operation! '
GO
-- INSTEAD DELETE
CREATE TRIGGER ToDelAlert
ON M_Customer
INSTEAD OF DELETE
AS
SELECT 'Are you going Delete a Records!'
GO
- Normally triggers are like a events in a programming language.
- We unable to call explicitly, this will fire while the event occured for the DB objects which its assigned for the particular operations.
- We unable to use triggers for Truncate Table, DROP table sql commands.
Inserting bulk Data Using Sql's Bulk Insert and XP_CMDSHELL Jobs
- To upload the list of available CSV or ‘^’ separated files in the shared location to the Sql server using SQL Bulk Insert and the ‘XP_cmdshell’.
- The SQL Bulk insert command is used to upload the special character separated files into DB at a time with Line feeds terminate.
- The ‘XP_CmdShall’ is used to list the files available the directory and accessing the files for bulk insert.
o For Running this we need a admin privilege and also need reconfigure to execute this Job.
o ‘sp_configure’ is used to reconfiguration.
- For Processing and Tracking Purpose we need 3 tables
o T_FILEDETAIL
This is used to Tracking the each files loaded and if made successfully then ‘FileStatus’ flage is to be ‘Y’, otherwise ‘N’.
o T_VikingData_Middleware
This table is act as a middleware for storing each file details temporaraly and moved to the T_Viking data orignal table.
o T_VIKINGDATA
This is the Main table to maintain all the values from the CSV or ‘^’ seprated files.
- The Table structures are:
CREATE PROCEDURE [dbo].[USP_BulkCSVUploader]
@DESTINATIONPATH VARCHAR(550)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FILENAMES VARCHAR(64);
DECLARE @CMDSCRIPT VARCHAR(8000);
-- DECLARE @DESTINATIONPATH VARCHAR(512);
DECLARE @MINROW INT;
DECLARE @MAXROW INT;
DECLARE @QRYCMD1 VARCHAR(1500);
DECLARE @QRYCMD2 VARCHAR(1500);
DECLARE @FILETABLE TABLE (ROWID INT IDENTITY, FILENAMES VARCHAR(128))
---** CREATING TEMP TABLE ** ---
CREATE TABLE #ORIGINALFILELIST
(
ROWID INT IDENTITY,
FILENAMES VARCHAR(1000) NULL
);
---** DESTINATION PATH WHERE RPT FILES ARE KEPT ** ---
-- SET @DESTINATIONPATH = '\\10.16.105.171\VIKINGDATA\UHG\DCNLOG\'
--SET @DESTINATIONPATH = '\\10.16.131.141\e\error\'
DELETE
FROM T_FILEDETAIL
WHERE FILESTATUS='N'
BEGIN TRY
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell',
1
-- To update the currently configured value for this feature.
RECONFIGURE
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
---** BUILD THE STRING TO CAPTURE THE FILE NAMES IN THE RESTORE LOCATION ** ---
SELECT @QRYCMD1 = 'MASTER.DBO.XP_CMDSHELL ' + CHAR(39) + 'DIR ' + @DESTINATIONPATH +'\*.* /L/B' + CHAR(39);
---** BUILD THE STRING TO POPULATE THE #ORIGINALFILELIST TEMPORARY TABLE ** ---
SELECT @QRYCMD2 = 'INSERT INTO #ORIGINALFILELIST(FILENAMES)' + CHAR(13) + 'EXEC ' + @QRYCMD1;
---** EXECUTE THE STRING TO POPULATE THE #ORIGINALFILELIST TABLE ** ---
EXEC (@QRYCMD2);
SELECT @DESTINATIONPATH,
FILENAMES ,
FILENAMES
FROM #ORIGINALFILELIST;
---** STORING THE RPT FOLDER FULL DETAILS
INSERT
INTO T_FILEDETAIL
(
FILENAMES ,
FILELOCATION,
FILESTATUS ,
FILEUPDATETIME
)
SELECT FILENAMES ,
@DESTINATIONPATH,
'N' ,
GETDATE()
FROM #ORIGINALFILELIST
---** STORING THE RPT FOLDER STATUS DETAILS ** ---
INSERT
INTO @FILETABLE
SELECT FILENAMES
FROM (SELECT FILENAMES,
FILESTATUS
FROM T_FILEDETAIL
WHERE (
FILENAMES LIKE '%.dcnlog'
OR FILENAMES LIKE '%.rpt'
)
AND FILESTATUS = 'N'
)
r
---** SELECTING ROWS ** ---
SELECT @MINROW = MIN(ROWID)
FROM @FILETABLE ;
SELECT @MAXROW = MAX(ROWID)
FROM @FILETABLE ;
IF EXISTS
(SELECT TOP 1
*
FROM @FILETABLE
)
BEGIN
WHILE @MINROW <= @MAXROW
BEGIN
TRUNCATE TABLE DBO.T_VikingData_Middleware;
SELECT @FILENAMES = FILENAMES
FROM @FILETABLE
WHERE ROWID = @MINROW;
UPDATE T_FILEDETAIL
SET FILESTATUS = 'W'
WHERE FILENAMES = @FILENAMES
---** INSERTING INTO INTERMEDIATE TABLE ** ---
BEGIN TRY
SET @CMDSCRIPT = 'BULK INSERT T_VikingData_Middleware FROM '''+@DESTINATIONPATH+ @FILENAMES+''' WITH (FIELDTERMINATOR = ''^'',ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@CMDSCRIPT)
---** INSERTING INTO MAIN TABLE ** ---
INSERT
INTO T_VIKINGDATA
(
CUST ,
PROJ ,
JULIANDATE ,
FOLDER ,
DCN ,
STAGEMODE ,
USERID ,
COFNAME ,
STARTTIME ,
ENDTIME ,
VIKDOCUMENT ,
FIELDVISITED ,
KEYSTROKES ,
VALIDCHARS ,
VERCHAR ,
VERCHARCHANGE,
VERFLDCHANGE ,
TDIFF ,
EventON ,
LOC_ORIGINAL ,
location
)
SELECT CUST ,
PROJ ,
JULIANDATE ,
FOLDER ,
DCN ,
STAGEMODE ,
USERID ,
COFNAME ,
STARTTIME ,
ENDTIME ,
VIKDOCUMENT ,
FIELDVISITED ,
KEYSTROKES ,
VALIDCHARS ,
VERCHAR ,
VERCHARCHANGE ,
CAST(SUBSTRING(VerFldChange, 1, 1) AS INT),
DATEDIFF(SECOND,STARTTIME,ENDTIME) ,
GETDATE() ,
LOCATION ,
LOCATION
FROM T_VikingData_Middleware
UPDATE T_FILEDETAIL
SET FILESTATUS = 'Y'
WHERE FILENAMES = @FILENAMES
END TRY
BEGIN CATCH
PRINT 'IN ERR 1' +CONVERT(VARCHAR,ERROR_NUMBER()) + ERROR_MESSAGE()
UPDATE T_FILEDETAIL
SET ERR_REASON = ERROR_MESSAGE()
WHERE FILENAMES = @FILENAMES
END CATCH
SET @MINROW = @MINROW + 1;
END
UPDATE V
SET LOCATION=DBO.FN_SPLIT_N(LOCATION,'^')
FROM T_VIKINGDATA V
WHERE LOC_ORIGINAL LIKE '%^%'
END
END
Removing Duplicate Records from the Table in SQL Server
If we need to delete or select the duplicated records from the table, we can do it in two ways.
1. If the tables is having identity column then use the Group By with MAx() to get the list of duplicated records.
-- The Bellow Sql Statement will Display the Duplicate Records Details
SELECT *
FROM M_Site
WHERE SEQID NOT IN
(SELECT MAX(SEQID)
FROM M_Site
GROUP BY SiteName
)
-- The Bellow SQL Statement will used to delete the Duplicated recoreds
DELETE
FROM M_Site
WHERE SEQID NOT IN
(SELECT MAX(SEQID)
FROM M_Site
GROUP BY SiteName
)
If the tables doesn't have identity column the we unable to use the group by With Max() clause alone.
So we need an alternative solutions. The bellow examples uses the ROW_NUMBER() Over Partition Clause to getting the
duplicated or tiriuplicated records from the DB.
Delete from a
from
(select Emp_Name, Company, Join_Date, Resigned_Date
,ROW_NUMBER() over (partition by Emp_Name, Company, Join_Date
,Resigned_Date
order by Emp_Name, Company, Join_Date
,Resigned_Date) RowNumber
from Emp_Details) a
where a.RowNumber > 1
-- For More Please visit:http://www.sqlservercentral.com/articles/T-SQL/70807/
To check the table is having particular columns and get the total no. of available columns in Sql Server
To using of 'INFORMATION_SCHEMA.Columns' to get the Sql Objects details as table. The following code snipts used to check the table is having particular columns and get the total no. of available columns in Sql Server.
To Call the User Defind Function
USE [gat]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UDF_IsColumnAvail]
(
@TableName VARCHAR(50),
@ColumnName VARCHAR(50),
@Option SMALLINT
)
RETURNS INT
BEGIN
DECLARE @RecCnt INT
IF(@Option=1)
BEGIN
SELECT @RecCnt=COUNT(*)
FROM [10.16.105.89].[Misrepdb].INFORMATION_SCHEMA.Columns
WHERE LTRIM(RTRIM(TABLE_NAME))=LTRIM(RTRIM(@TableName))
END
ELSE
BEGIN
SELECT @RecCnt=COUNT(*)
FROM [10.16.105.89].[Misrepdb].INFORMATION_SCHEMA.Columns
WHERE LTRIM(RTRIM(TABLE_NAME)) =LTRIM(RTRIM(@TableName))
AND LTRIM(RTRIM(Column_NAME))=LTRIM(RTRIM(@ColumnName))
END
RETURN @RecCnt
END
GO
SELECT [GAT].[DBO].[UDF_IsColumnAvail]('ViFLTESTWIPFloder_us','Cust',1)
BEGIN TRY
if([GAT].[DBO].[UDF_IsColumnAvail]('ViFLTESTWIPFloder_us','Cust')>0)
print 'ok'
else
print 'failed'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber ,
ERROR_SEVERITY() AS ErrorSeverity ,
ERROR_STATE() AS ErrorState ,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine ,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
Working with SQL Cursors
- The Cursors are used to loop through the Result of SQL Statement.
- This Bellow example shows how to create and loop through the Resulted temp table.
CREATE TABLE #temp
(
name VARCHAR(20),
Salary MONEY
)
INSERT
INTO #temp VALUES
(
'Siva',
20000
)
INSERT
INTO #temp VALUES
(
'Babu',
25000
)
INSERT
INTO #temp VALUES
(
'Raghu',
15000
)
SELECT *
FROM #temp
GO
-- Declaring the Variables to hold the Values
DECLARE @EMPNAME VARCHAR(20) ;
DECLARE @Salary MONEY;
-- Declaring the cursor for accessing the Resulted Records.
DECLARE @NameCursor
CURSOR
-- To refering the Selected values for the cursour
SET @NameCursor=
CURSOR FOR
SELECT NAME,
SALARY
FROM #temp
-- To Open the Cursor and Fetch the Record and Assign to variables
OPEN @NameCursor
FETCH NEXT
FROM @NameCursor
INTO @EMPNAME,
@Salary
-- Loop through the entire Result based on the Resultset
WHILE @@FETCH_STATUS=0
BEGIN
-- Print the Result as you like
PRINT @EMPNAME +'''s Salary:' + CAST(@Salary AS VARCHAR(10))
FETCH NEXT
FROM @NameCursor
INTO @EMPNAME,
@Salary
END
-- Close the cursor
CLOSE @NameCursor
-- Deallocate cursor
DEALLOCATE @NameCursor
--Result
--------
Siva's Salary:20000.00
Babu's Salary:25000.00
Raghu's Salary:15000.00
Listing the Available Tables in a DB
We can list the Tables of the Database using SYS.TABLES.
USE TEST
GO
SELECT *
FROM sys.Tables
GO
Bulk Insert in SQL Server 2005
In SQL Server 2005 the Bulk Insert is the special Sql statement to insert the bulk data of CSV or another type of files
with field separator character(i.e,; or ^ or , or . separator).
Syntax for Bulk insert:
USE Test
GO
CREATE TABLE USER
(
ID INT ,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
GO
BULK
INSERT USER
FROM 'F:\User.txt'WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Example Script Available for Bulk Insert and Shell Command for getting Files List as Table and upload the files to Database(i.e .dcnlog type files).Click Here to Download...