Asp dotnet rajkumar: Welcome to aspdotnetrajkumar

Friday, May 31, 2013

SQL SERVER 2005 FUNCTIONS

http://www.sql-server-helper.com/tips/date-formats.aspx

SELECT * FROM SALARY_FITMENT_CODE ORDER BY AUTO_CODE

SELECT MODE FROM SALARY_FITMENT_CODE GROUP BY MODE

SELECT COUNT(QUANTITY) AS QTY, FLIGHTCOMPANYNAME FROMAIR_TICKET_BOOKING_MASTER WHERE STATUS = 'DONE' GROUP BYFLIGHTCOMPANYNAME

SELECT COUNT(QUANTITY) AS QTY, FLIGHTCOMPANYNAME FROMAIR_TICKET_BOOKING_MASTER WHERE STATUS = 'DONE' GROUP BYFLIGHTCOMPANYNAME HAVING COUNT(QUANTITY)>1

SELECT ROW_NUMBER() OVER(ORDER BY code), * FROM SALARY_FITMENT_CODE

SELECT ROW_NUMBER() OVER(PARTITION BY mode order by Auto_code),*FROM TABLE

SELECT UPPER('string is now in upper case')

SELECT LOWER('STRING IS NOW IN LOWER CASE')

SELECT LEN('STRING LENGTH')

SELECT ROUND(10.25654654,3)

SELECT (10+10)

SELECT GETDATE()

SELECT CONVERT (VARCHAR, GETDATE(), 103)

SELECT CAST (GETDATE() AS VARCHAR)

SELECT SUBSTRING ('ABCDEFGHIJLMNOP',2,6)

SELECT LTRIM(' LEFT')

SELECT RTRIM('RIGHT ')

SELECT MAX(SALARY) FROM TABLE

SELECT MIN(SALARY) FROM TABLE

SELECT SUM(SALARY) FROM TABLE

SELECT AVG(SALARY) FROM TABLE

SELECT COUNT(*) FROM TABLE

SELECT SQRT(16)

SELECT RAND()

SELECT ('FIRSTNAME' + 'LASTNAME') AS FULLNAME

SELECT ISNULL('FIRSTNAME','') + ' ' + ISNULL('LASTNAME','') AS FULLNAME

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

SELECT ASCII('2')

SELECT REVERSE('ARKA')

SELECT LEFT('ARKAGUPTA', 4)

SELECT RIGHT('ARKAGUPTA', 5)

SELECT REPLACE('http://www.tutorialspoint.com/', 'w', 'W')

SELECT SOUNDEX('Hello')

SELECT SPACE(6)

SELECT REPLACE('ABCDEFGH','CDE','XXX')
SELECT DISTINCT * FROM ITEM_MASTER
SELECT COUNT(ITEM_CODE), ITEM_CODE FROM ITEM_MASTER GROUP BYITEM_CODE

SELECT COUNT(ITEM_CODE),ITEM_CODE FROM ITEM_MASTER GROUP BYITEM_CODE HAVING COUNT(ITEM_CODE)>1

AGEING

SELECT ITEMID,

SUM(CASE WHEN UPDATE_DATE >= GETDATE() - 30 THEN QTY ELSE 0 END) AS"0-30",

SUM(CASE WHEN UPDATE_DATE BETWEEN GETDATE() - 60 AND GETDATE() - 31THEN QTY ELSE 0 END) AS "31-60",

SUM(CASE WHEN UPDATE_DATE BETWEEN GETDATE() - 90 AND GETDATE() - 61THEN QTY ELSE 0 END) AS "61-90",

SUM(CASE WHEN UPDATE_DATE < GETDATE() - 90 THEN QTY ELSE 0 END) AS"90+",

SUM(QTY) AS TOTAL_OUTSTANDING FROM STOCK_TRANS_DETAILS GROUP BYITEMID

ORDER BY TOTAL_OUTSTANDING DESC

FETCH TOP 10 ROWS ONLY

SELECT ABS(2) -- ABSOLUTE VALUE OF NUMERIC EXPRESSION

SELECT ACOS(1)-- ARCCOSINE OF NUMERIC EXPRESSION

SELECT ASIN(1)-- ARCSINE OF NUMERIC EXPRESSION

SELECT ATAN(1)-- ARCTANGENT OF NUMERIC EXPRESSION

SELECT CEILING(4.46)SMALLEST INTEGER THAT IS NOT LESS THAN PASSED NUMBER

SELECT FLOOR(7.55)LARGEST INTEGER THAT IS NOT GREATER THAN PASSED NUMBER

SELECT SIN(90)-- SINE OF NUMERIC EXPRESSION

SELECT COS(90)-- COSINE OF NUMERIC EXPRESSION

SELECT TAN(90)-- TANGENT OF NUMERIC EXPRESSION

SELECT COT(90)-- COTANGENT OF NUMERIC EXPRESSION

SELECT DEGREES(PI())-- NUMBERCONVERTED FROM RADIANS TO DEGREES

SELECT PI() -- VALUE OF PI

SELECT EXP(3)BASE OF NATURAL LOGARITHM (E) RAISED TO THE POWER OF NUMBER

SELECT LOG(45) -- RETURNS THE NATURAL LOGARITHM OF THE PASSED NUMBER

SELECT LOG10(100) -- RETURNS THE BASE-10 LOGARITHM OF THE PASSED NUMBER

SELECT POWER(3,3) -- VALUE OF NUMBER TO THE POWER OF ANOTHER NUMBER

SELECT RADIANS(90)-- PASSED EXPRESSION CONVERTED FROM DEGREES TO RADIANS

SELECT ROUND(5.693893,2)-- ROUND NUMBER OF DECIMAL POINTS

SELECT SIGN(0)-- SIGN OF X (NEGATIVE, ZERO, OR POSITIVE) AS -1, 0, OR 1

SELECT SQRT(49)-- NON-NEGATIVE SQUARE ROOT OF NUMERIC EXPRESSION

SELECT GETDATE() -- 2013-05-02 18:35:57.647

SELECT DATENAME (DD, GETDATE()) -- 2

SELECT DATENAME (MM, GETDATE()) -- May

SELECT DATENAME (YY, GETDATE()) -- 2013

SELECT DATENAME (WEEKDAY, GETDATE()) -- Thursday

SELECT LEFT(DATENAME (WEEKDAY, GETDATE()),3) -- Thu

SELECT LEFT(DATENAME (MM, GETDATE()),3) -- May

SELECT DATEPART (DAY, GETDATE()) -- 2

SELECT DATEPART (YY, GETDATE()) -- 2013

SELECT DATEPART (YEAR, GETDATE()) -- 2013

SELECT RIGHT (DATEPART (YY, GETDATE()),2) -- 13

SELECT DATEPART (QUATER, GETDATE()) --

SELECT DATEPART (MONTH, GETDATE()) -- 5

SELECT DATEPART (DAYOFYEAR, GETDATE()) -- 122

SELECT DATEPART (WEEK, GETDATE()) -- 18

SELECT DATEPART (WEEKDAY, GETDATE()) -- 5

SELECT DATEPART (HOUR, GETDATE()) -- 18

SELECT DATEPART (MINUTE, GETDATE()) -- 35

SELECT DATEPART (SECOND, GETDATE()) -- 57

SELECT DATEPART (MILISECOND, GETDATE()) --

SELECT DATEPART (MICROSECOND, GETDATE()) --

SELECT DATEPART (NANOSECOND, GETDATE()) --

SELECT DATEPART (TZoffset, GETDATE()) --

SELECT DATEPART (ISO_WEEK, GETDATE()) --

SELECT REPLICATE ('ARKA',2) -- ARKAARKA

SELECT ('ARKA') + SPACE(0) + ('GUPTA') -- ARKAGUPTA

SELECT ('ARKA') + SPACE(1) + ('GUPTA') -- ARKA GUPTA

SELECT REPLACE ('ARKA GUPTA', 'GUPTA', 'SQL') -- ARKA SQL

SELECT STUFF ('SQLTUTORIAL', 4, 6,'FUNCTION') -- SQLFUNCTIONAL

SELECT REPLICATE ('0',2) -- 00

SELECT UNICODE('A') -- 65

SELECT ASCII ('A') -- 65

SELECT REVERSE ('ARKA GUPTA') -- ATPUG AKRA

SELECT SUBSTRING ('ARKA GUPTA',4,3) -- A G

SELECT LEFT ('ARKA GUPTA',4) -- ARKA

SELECT RIGHT ('ARKA GUPTA',4) -- UPTA

SELECT CHARINDEX ('A','ARKAGUPTA',0) -- 1

SELECT CHARINDEX ('A','ARKAGUPTA',2) -- 4

SELECT * FROM LOGIN WHERE USER_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS='sa' AND PASSWORD COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS ='SA'

How to Make Symbol with Keyboard

Friday, May 17, 2013

How to handle 404 Server side error in .net

This server side error usually will be come, when we give the wrong URL. now how we can handle the in your .net application. That time we will show the custom error message. like this create one 404.aspx page in your own way to display that error message using HTML. Next this method is URLRewriter. we can get the error response then response is Empty you can redirect to 404.aspx page.

public void context_BeginRequest(object sender, EventArgs e)
        {
            HttpApplication application = (HttpApplication)sender;
            HttpContext context = application.Context;

            if (HttpContext.Current.Request.Url.ToString().Contains("Redirect.aspx"))
            {
                if (context.Request.QueryString["key"] != null)
                {
                    string inputStr = context.Request.QueryString["key"].ToString();

                    if (inputStr != "none/")
                    {
                        if (inputStr.EndsWith("/"))
                        {
                            inputStr = inputStr.Substring(0, inputStr.Length - 1);
                        }

                        HttpContext.Current.RewritePath(Helper.GetRedirectPage(inputStr), false);
                    }
                }
                else
                {
                    HttpContext.Current.RewritePath("~/Server_Error/404.aspx", false);
                }
            }
        }

and another one way to handle this error message. simply we can wrote in your web.config file like this...

<customErrors mode="On">
   <error statusCode="403" redirect="NoAccess.htm"/>
   <error statusCode="404" redirect="~/Server_Error/404.aspx"/>
  </customErrors>

How to hid div while click the check box using jquery

Its very useful while click the checkbox the onclick funcation called jquery that hidden value used div will be open while checkbox checked. Uncheck the checkbox the div will be hid.

<input type="checkbox" id="ChangePassword" onclick = "DisplayChangePasswordField();"/>
 <input type="hidden" id="hidChangePassword" name="hidChangePassword" value="0" />
<div id="divchangepassword">
you can hid this div while clicking the checkbox.
</div>

$(document).ready(function () {
            $("#divchangepassword").hide();
        });
        function DisplayChangePasswordField() {
            var hideValue = $("#hidChangePassword").val();
            var newHideValue = 0;

            if (hideValue == 0) {
                newHideValue = 1;
            }
            else {
                newHideValue = 0;
            }
            if (newHideValue == 0) {
                $("#divchangepassword").hide();
            }
            else {
                $("#divchangepassword").show();
            }
            $("#hidChangePassword").val(newHideValue);
            return false;

        }

GridView With Scrollbar in Asp.net

Gridview have not Scrollbar property so you have to use two things to achieve this

1 Div Tag

<div id="scrollP" style="overflow:scroll;height:100px;
width:200px;">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>

2 Panel control

<asp:Panel ID="Panel1" ScrollBars="Both" runat="server" Width="220px">
<asp:GridView ID="GridView1" runat="server"
</asp:GridView>
</asp:Panel>

Check image height & width in pixel during uploading image using File Upload Control.

You can check the size, height and width of image and do some restriction during file upload. Include System.Drawing namespace for this.Here ImageUploader is the File Upload Control.

//********* Chcek Image Size ************

Bitmap img = new Bitmap(ImageUploader.PostedFile.InputStream, false);

int height = img.Height;

// get the height of image in pixel.

int width = img.Width;

// get the width of image in pixel.

int fileSize = (ImageUploader.PostedFile.ContentLength) / 1024;

//get the size of image file.

if (height >= 500 && width >= 500 && fileSize > 500)

{

lblErrorMessage.BackColor = Color.Red;

lblErrorMessage.ForeColor = Color.Black;

lblErrorMessage.Text = "File size not be exceed than 500 KB,500x500 px";

return;

}

//*************** End of Block *****************

Form closing event with Yes/No message in winform(C#)

If you want to display a message like 'Do you want to close this form(Yes/No) when clicking of the form close button

then use this code

write this event in form contructor

public Form1()

{

this.Closing += new CancelEventHandler(Form1_Closing);

}

then write closing event of Form1

protected void Form1_Closing(object sender, CancelEventArgs e)

{

DialogResult dr =MessageBox.Show("Do u want to close(Y/N)","Warning",MessageBoxButtons.YesNo,MessageBoxIcon.Warning);

if (dr == DialogResult.Yes)

{

//do some thing

}

else

{

e.Cancel = true;

}

Planet Source

VB.Net, C Sharp

JQUERY, JAVASCRIPT

SQL SERVER 2005,2008

VbClassic

Pages