Sunday, 22 April 2012

Capitalise the first letter of each word


create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';

   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end


Result

select dbo.ProperCase('test’)


Test




Get column names and datatypes in particular table using SQL Server


SELECT column_name as ColumnName,
data_type as DataType,
character_maximum_length as MaximumLength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Employee_Details'

Result:

ColumnName  DataType    MaximumLength
id          bigint      NULL
username    varchar     50
firstname   varchar     50
lastname    varchar     50
city        varchar     50
designation varchar     50
Salary      decimal     NULL

Auto loading content on page scroll in asp.net using jquery

call asp.net page methods from javascript


 Mark your method as static and give it the WebMethod attribute
The method has to be declared static. It must also be marked with the WebMethod attribute. You'll probably find that you need to include System.Web.Services
using System.Web.Services;
[WebMethod]
public static string MyMethod(string name)
{
    return "Hello " + name;
}

Client Side


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication3._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
  
     <script language="javascript" type="text/javascript">
         function OnRequestComplete(result, userContext, methodName) {
             alert(result);

         }
         function OnRequestError(error, userContext, methodName) {
             if (error != null) {
                 alert(error.get_message());
             }
         }
         function SubmitData() {

             PageMethods. MyMethod ("Test",OnRequestComplete, OnRequestError);
         }
  
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
  
      <asp:ScriptManager ID="ScriptManager1" EnablePageMethods="true" EnablePartialRendering="true" runat="server" />
       <input type="button" value="Submit" onclick="SubmitData()" />
    </div>
    </form>
</body>
</html>


Mark your method as static  and EnablePageMethods=true are very important.

If you miss static keyword, it will show error like "Microsoft JScript runtime error : Page method is undefined"


Call Server Side function using jquery

  [WebMethod]
    public static string DisplayData(string name,int id)
    {
        return "name:" + name  + " Id: " + id;
    }

Client side


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>JQuery Call asp.net page methods</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "Default.aspx/DisplayData",
            data: '{"name":"test","id":"1"}',
            dataType: "json",
            success: function (data) {
                $("#lbltxt").text(data.d);
            },
            error: function (result) {
                alert("Error");
            }
        });
    });
</script>
</head>
<body>
<form id="form1" runat="server">
<label id="lbltxt" runat="server"></label>
</form>
</body>
</html>





Thursday, 19 April 2012

show alert message from server side using javascript


VB

Page.ClientScript.RegisterStartupScript(Me.GetType(), "showalert", alert('Only alert Message');__doPostBack('__Page', '');", True)


C#
this.Page.ClientScript.
RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString() , "alert('" + ex.Message + "');__doPostBack('__Page', 'EmptyPostback');", true);

__doPostBack('__Page', 'EmptyPostback'); is used to avoid alert message while click on browser back button.

When we register the client script with the alert, immediately follow the alert statement with one which will refresh the page. This ensures that the client script is removed from the page as soon as the OK is clicked, and if the user returns to the page the alert pop up does not reappear.


Register Javascript when using Update Panel

VB
ScriptManager.RegisterStartupScript(Me, [GetType](), "showalert", "alert('Only alert Message');__doPostBack('__Page', '');", True)
C#

ScriptManager.RegisterStartupScript(Page, Page.GetType(), Guid.NewGuid().ToString(),"alert('I am from the Server');__doPostBack('__Page', '');", true);

Error Handling in SQL Server


BEGIN TRY
      select 1/0
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Wednesday, 18 April 2012

Set max length for multiline textbox



Jquery Common Function:

setMaxLengh: function (id, len, msg) {

      if ($('#' + id).length == 0) return;

      if ($('#' + msg).length > 0) $('#' + msg).html(len + " Characters left");


      $('#' + id).keypress(function (e) {
          if ($(this).val().length >= len) {
              e.preventDefault();
          }
      });

      $('#' + id).keyup(function (e) {
          var total = parseInt($(this).val().length);

          if ($('#' + msg).length > 0) $("#" + msg).html('Characters entered <b>' + total + '</b> out of ' + len + '.');

          if (total >= len) $(this).val($(this).val().substring(0, len));
      });
  }

(or)

Javascript:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>set Max Length</title>
    <script type="text/javascript">
        //To display the number of characters left
        function CharactersCount() {
            var CharLength = '<%=txtMsg.MaxLength %>';
            var txtMsg = document.getElementById('txtMsg');
            var lblCount = document.getElementById('lblCount');          
            if (txtMsg.value.length > CharLength) {
                txtMsg.value = txtMsg.value.substring(0, CharLength);
            }
            lblCount.innerHTML = CharLength - txtMsg.value.length;          
        }
    </script>
    <style type="text/css">
.lblstyle
{
color:Red;
font-weight:bold
font-size11px;
font-familyverdana;
}
</style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
             
                <td>
                    <span style="font-family: Verdana; font-size: 12px">Left:</span>
                    <asp:Label ID="lblCount" runat="server" Text="100" CssClass="lblstyle"></asp:Label>
                </td>
            </tr>
          
            <tr>
                <td colspan="2">
                    <asp:TextBox ID="txtMsg" onkeyup="CharactersCount();" runat="server"
                        Style="overflow: hidden;" Height="80px" TextMode="MultiLine" Width="300px" MaxLength="100" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>