Autofill textbox with Ajax along with value

See below steps to Autofill textbox with Ajax with list of items along with value. Also value will be fetched once any of item is selected from list.

1. Add onkeyup event on textbox to autofill textbox as user start typing in textbox.

onkeyup=KeyUpEvent('txtSearch','1');

2. Add below javascript method which will be called on keyup event of textbox. This method will be used to make Ajax call and get item list with value.

function KeyUpEvent(txtBoxId, id) {
    try {
        if (txtBoxId != null) {
            $("#" + txtBoxId).autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: 'PageName.aspx/ListItemKeyValue',
                        data: "{'prefix':'" + $('#' + txtBoxId).val() + "','Parameter1':'" + id + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        dataFilter: function (data) { return data; },
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                   label: item.split('/')[0],
                                   val: item.split('/')[1]
                                }
                            }))
                        },
                        error: function (XMLHttpRequest, textStatus, errorThrown) {
                        }
                    });
                },

                select: function (event, ui) { alert(ui.item.val); return false; },
                minLength: 3
            });
        }
    }
    catch (e) {
       alert('Error: ' + e);
    }
}

3. Write below web method in .cs file of page to fetch item from database based on value passed from Javascript method.

[WebMethod]
public string[] ListItemKeyValue(string prefix, int Parameter1)
{
    List items;
    Data objData = new Data();
    //Fetch data into Datatable    System.Data.DataTable dt = objData.GetListItemKeyValue(Parameter1, prefix).Tables[0];
    items = new List(dt.Rows.Count);
    for (int intI = 0; intI < items.Capacity; intI++)
    {
       items.Add(string.Format("{0}/{1}", dt.Rows[intI]["Name"].ToString(), dt.Rows[intI]["Value"].ToString()));
    }
    return items.ToArray();
}

Importing a CSV file into your database with SQL Server Management Studio

While bulk copy and other bulk import options are not available on the SQL servers, you can import a CSV formatted file into your database using your SQL Server Management Studio.

You first need to create a table in your database in which you will be importing the CSV file. After the table is created, follow the steps below.

1. Log into your database using SQL Server Management Studio
2. Right click on your database and select Tasks -> Import Data
3. Click the Next > button
4. For the Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring how you want the data to be imported before clicking on the Next > button
5. For the Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0).  Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking on the Next > button
6. On the Select Source Tables and Views window, you can Edit Mappings before clicking on the Next > button
7. Check Run immediately and click on the Next > button
8. Click on the Finish button to run the package

NULLIF and ISNULL in SQL

ISNULL
Syntax: ISNULL(expression, value)
Explanation: This function will check expression value and if expression is NULL then it will replace it will given value.
Example: SELECT ISNULL(Income,0) AS Income FROM EmpDetail
Here if Income is NULL for any employee then it will return as 0.

NULLIF
Syntax: NULLIF(expression, value)
Explanation: This function will check expression value and if expression = value then it will replace it with NULL (This function is opposite of ISNULL).
Example: SELECT NULLIF(Income,0) AS Income FROM EmpDetail
Here if Income is 0 for any employee then it will return as NULL.

Disable right click on web page using JQuery

Use below JQuery code to disable right click on web page. This will work well on all browser (IE, Firefox, Google chrome).

// To prevent right click
$(document).bind('contextmenu', function (e) {
        e.preventDefault();
        return false;
});
 

Disable saving web page with CTRL-S using JQuery

Use below JQuery code to disable saving web page using "CTRL-S" keyword directly. This will work well on all browser (IE, Firefox, Google chrome).

// To prevent saving web site directly using "ctrl-s"
$(document).bind('keydown keypress', 'ctrl+s', function () {
        $('#save').click();
        return false;
    });

Take backup of Single Table in SQL Server and MySql

Using below query, you can take backup of table in SQL Server:
SELECT * INTO [NewTableName] FROM [BackupTable]

Using below query, you can take backup of table in MySql:

CREATE TABLE [NewTableName] SELECT * FROM [BackupTable]

Getting selected value from RadioButtonList in Javascript

Consider below ASP.Net RadioButtonList control:
<asp:RadioButtonList ID="rbList" runat="Server">
   <asp:ListItem Text="Kapil" Value="1" Selected="True">

   <asp:ListItem Text="Baheti" Value="2">
</asp:RadioButtonList>

You can now get selected radio button value using below javascript:
function GetRadioButtonValue()
{
  var id = <%= rbList.ClientID %>;
  var radio = document.getElementsByName(id);
  for (var i = 0; i < radio.length; i++)
     {
        if (radio[i].checked)
           alert(radio[i].value);
     }
}

Insert multiple rows together in table in SQL Server

You can insert multiple rows together in table in SQL Server using below 3 methods:

CREATE TABLE Test (ID INT, Value VARCHAR(100));

1. INSERT INTO Test (ID, Value) VALUES (1, 'One');
    INSERT INTO Test (ID, Value) VALUES (2, 'Two');
    INSERT INTO Test (ID, Value) VALUES (3, 'Three');

2. INSERT INTO Test (ID, Value)
    SELECT 1, 'One'
    UNION ALL
    SELECT 2, 'Two'
    UNION ALL
    SELECT 3, 'Three';

3. INSERT INTO Test (ID, Value)
    VALUES (1, 'One'), (2, 'Two'), (3, 'Three');

White text in simple text file using ASP.Net with C#

Use below code to write text in simple .txt file using ASP.Net with C#:

System.IO.StreamWriter sw = new System.IO.StreamWriter(Server.MapPath("C:\\test.txt"));
sw.WriteLine("Kapil Baheti");
sw.Close();

Above code will write "Kapil Baheti" in test.txt file. Also if file is not present at destination then it will be created automatically. 

Fix "Validation (): Element xxxxx is not supported" problem in visual studio

We face common issue in visual studio "Validation (): Element xxxxx is not supported" for ASP controls.
To solve this issue follow below steps:

1. Close down Visual Studio 2008 or Visual Studio 2010.

2. Now make sure to show hidden files, folders, and drives. 
    To do so go to Folder Options -> then select the View tab.  

3. Now you need to go toh below path:
   My Computer >> [C:] >> Users >> [username] >> AppData >> Roaming >> Microsoft >> VisualStudio >> 9.0 / 10.0 (For VS 2010)  
   Or
   Users >> Classic .NET AppPool >> AppData >> Roaming >> Microsoft >> VisualStudio >> 9.0 / 10.0 (For VS 2010)  

4. Now delete all items in ReflectedSchemes folder. 

5. Restart visual studio and your problem will be solved.

Jquery - For Each function

.each( function(index, Element) ) :
This is JQuery function used to iterate through each matched element. This function is generally used to iterate through similar elements to apply common functionality.

Check below simple example to understand this function:
This is simple HTML unordered list on page


<ul>
    <li>Kapil</li>
    <li>Baheti</li>
</ul>

Now you can iterate through each <li> using below jquery function:

<script>

$('li').each(function(index) {
    alert(index + ': ' + $(this).text());
});
</script>


Output will be
0: Kapil
1: Baheti

Get list of all files from directory in C#, ASP.Net

Using below code you can list out all files or files with respective extension for given directory in C#:

DirectoryInfo di = new DirectoryInfo("c:/demos");
FileInfo[] rgAllFiles = di.GetFiles();  //Get list of all files.
FileInfo[] rgFiles = di.GetFiles("*.aspx");  //Get list of only .aspx files.

// You can use each file using below iteration
foreach(FileInfo fi in rgAllFiles)
{
  Response.Write(fi.Name);       
} 

Get file name from path using Javascript

Using below javascript function you can get file name from path:

function GetFileName()
{
   var fullPath = 'http://kapilgbaheti.blogspot.in/2012/08/get-file-name-from-path.html';
   var filename = fullPath.replace(/^.*[\\\/]/, '');
   alert(filename);
}

You output will be : get-file-name-from-path.html

Get Screen Width and Screen Height using Javascript

Below is code to get Screen Width and Height using Javascript;

function getScreenInfo()
{
    var w = screen.width;
    var h = screen.height;
    alert("Screen Width :" + w + ", Screen Height:" + h);
}

Note: You cannot get screen width/height in ASP.net server side code, you need to manage it using Javascript.

SQL SERVER: Generate Comma Separated List with SELECT statement

Below example shows, how to get comma separated value from SQL server.

-- Create table
CREATE TABLE #test(field1 VARCHAR(5), field2 VARCHAR(5))


--Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'  UNION ALL
SELECT '001','BBB'   UNION ALL
SELECT '002','CCC'  UNION ALL
SELECT '003','DDD'  UNION ALL
SELECT '004','EEE'    UNION ALL
SELECT '004','FFF'    UNION ALL
SELECT '004','GGG'
 
SELECT field1,
SUBSTRING(
(
  SELECT ( ', ' + field2)
  FROM #test t2
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
), 3, 1000)
FROM #test t1
GROUP BY field1

Output will be:
field1   field2
001     AAA,BBB
002     CCC
003     DDD
004     EEE,FFF,GGG

How to split comma separated string into SQL server.

Below function can be used to split comma seperated string, output will be in fom of table.

-- SELECT * FROM dbo.fn_Split ('1,2,3',',')

CREATE FUNCTION [dbo].[fn_Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
RETURNS @ValueTable table (ID [int] IDENTITY (1, 1) NOT NULL,[Value] nvarchar(4000))


BEGIN


DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)


--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)


--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter


--Get position of first Comma
SET @Pos = charindex(@Delimiter,@String)
SET @NextPos = 1


--Loop while there is still a comma in the String of levels
WHILE (@pos <> 0)
BEGIN
  SET  @NextString = substring(@String,1,@Pos - 1)
  INSERT INTO @ValueTable ( [Value]) Values (@NextString)
  SET  @String = substring(@String,@pos +1,len(@String))
  SET  @NextPos = @Pos
  SET  @pos = charindex(@Delimiter,@String)
END


RETURN

END

Get number of days in month from given date in SQL server

Below is example of SQL query which will give number of days in month for given date:

DECLARE @dtDate AS DATETIME = GETDATE()   --This will select current date.
SELECT DAY(DATEADD(MONTH, 1, @dtDate) - DAY(DATEADD(MONTH, 1, @dtDate))) 

Search / find keyword in your entire database(Procedure, Function) in SQL Server

You can use below procedure to search any keyword in your entire database. In output you will get list of procedures and functions in which given keyword is found.

CREATE PROC SP_SEARCH_CODE
(
  @SearchStr VARCHAR(100),
  @RowsReturned INT = NULL OUT
)


AS
BEGIN


SET NOCOUNT ON


SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',


CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 THEN 'Inline function'
WHEN OBJECTPROPERTY(c.id, 'IsView') = 1 THEN 'VIEW'
END AS 'Object type',


'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN sysobjects o ON c.id = o.id
WHERE
c.text LIKE '%' + @SearchStr + '%' AND encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsView') = 1
)
ORDER BY 'Object type', 'Object name'


SET @RowsReturned = @@ROWCOUNT


END

Disable Copy Paste Cut options in textbox using javascript in ASP.NET

Below is example to disable COPY, PASTE and CUT option in textbox:

<asp:TextBox ID="TextBox1"runat="server"oncopy="return false" oncut="return false" onpaste="return false" ></asp:TextBox> 

How to convert lowercase to uppercase in SQL Server

Below query is example of converting lowercase into uppercase using SQL query:

SELECT UPPER(ColumnName) FROM TableName