Asp .Net ,Jquery ,WebService,SQL Server

SQL SERVER Query

2013-12-27T00:26:00.002-08:00

select max(a) from
(
Select
(
SELECT Max(v)
FROM (VALUES (Theatre), (ClassRoom), (UShape),(Cabaret),(

DinnerDance),(Reception))
AS value(v))as a
FROM PropertyMeetingRoom
)t

How to check string is number or not in C#

2013-06-10T02:11:00.002-07:00

Some time we have required to check the value of string is Number or not if it no then offcourse our task on these like sum, avg, max etc etc but somehow it contain string value is not Numeric then its panic . Then what is the solution

Simply check the no if it is numeric then perform the action other wise no action performed.

Below is the simple example .

there are a method in C# which check the datavalue. Method name is TryParse

it returns always boolean value.

Solution :

string strValue = "ABC";
double Num;
bool isNum = double.TryParse(Str, out Num); // out parameter are used
if (isNum)
Response.Write(Num.ToString()); // you use here your label id or other thing
else
Response.Write("Invalid number");
// you use here your label id or other thing

How to get all table name dependent on any column in SQl Server

2013-05-31T04:49:00.000-07:00

Query for this is

SELECT o.Name as Table_Name , c.Name as Field_Name, t.Name as Data_Type
, t.length as Length_Size
FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id
LEFT JOIN systypes t on t.xtype = c.xtype
WHERE o.type = 'U' and c.name like '%country%'
ORDER BY o.Name, c.Name

here red color name is your required column name .

Select Query in SQL Server

2013-03-07T05:53:00.001-08:00

Hi all
I have a Question for you .
I have one table with two columns a and b
like this
Create table test(
a varchar(10),
b varchar(10)
)

After that i execute a query

select a b from test

Then my question is what will be the output of this simple query .

Difference between === and == in javascript

2013-01-18T04:56:00.000-08:00

if(1=="1")
{
alert("hello");
}else
{
alert("hi");
}

Some time question arises that what is the output.
what you think about this ?
of course your answer is hello .
But now can you guess what is the result of following code

if(1==="1")
{

alert("hello");
}else
{
alert("hi");
}
bit confusing .
answer is hi .
It is becuase the expreesion === to compare datatype as well as value .

How to start Visual Studio from run prompt

2012-12-17T22:29:00.003-08:00

Hi friends some time question arise that how to open visual studio from run command so there are the solution.

there are some simple step to complete this task

Step -1

open the run prompt like this

Start->Programs->Run

press

window logo + R

then below pop up open

Step -2

after that type on this run prompt

devenv

or
devenv /nosplash

after that press enter. then your visual studio open .

there are difference in both keyword

devenv for Opens Visual Studio With Splash Screen.
devenv /nosplash for Opens Visual Studio Without Splash Screen.

now the question arise that what is Splash screen

when you type only devenv on run prompt then below screen are shown

if you are type devenv /nosplash then this screen are not shown.

devenv /nosplash this command is faster than devenv

Hope this will help you ..

if any issue then please let me know

Thanks

ROHIT KUMAR SRIVASTAVA

Advantage and disadvantage of Juqery

2012-12-14T00:43:00.004-08:00

Advantage-

Good User Experience, close to a Windows GUI
Much easier to use than plain JavaScript
Impressive speed
Coders don’t have to worry about Browser differences
Reduces Server Load as fewer round-trips
Widely used, good community support
Many components already developed
Open Source

Disadvantage-

You probably still need a Server framework for initialisation and Session management
You need another app to supply your data (like a web service) written in another language (like .NET, Java or PHP)
In larger apps, slower to code than PHP or ASP.NET
You need to understand CSS
Can still be difficult to debug, especially if scripts interfere with each other.
Source code is difficult to protect

Delete duplicate record in SQL SERVER

2012-10-30T21:59:00.000-07:00

Question :- Some time the question arise that how to delete duplicate record from table ?

Answer : firstly we create the table

CREATE TABLE tblemployee1(
    [empid] int identity(1,1) NOT NULL,
    [empname] [varchar](10) NULL,
    [mgrid] [varchar](10) NULL,
    [test] [varchar](100) NULL)

insert some data on this like below

insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Rohit',2,'Useless')
insert into tblemployee1 values('Rohit',3,'Useless')
insert into tblemployee1 values('Usertest',3,'test')
insert into tblemployee1 values('Usertest',3,'test')

Select * from tblemployee1

output is

empid   empname   mgrid    test
1      Cherry          1    Useful
2      Cherry    1             Useful
3 Cherry          1       Useful
4            Cherry          1    Useful
5 Cherry      1           Useful
6      Rohit     2        Useless
7        Rohit            3    Useless
8 Usertest       3              test
9    Usertest    3              test

now we waana to delete the duplicate record which is with name

the query is

;with cte as(
Select ROw_number() over(partition by empname   order by empname) as col ,* from tblemployee1)
delete from cte where col>1

then the ouput is

empid    empname    mgrid    test
1      Cherry 1      Useful
6 Rohit      2    Useless
8    Usertest 3    test

here we use the rownumber for deletion with COMMON TABULAR EXPRESSION.

Hope this will help you

if any issue then please let me know

Thanks
ROHIT KUMAR SRIVASTAVA

Change Color of Status Bar in SQL SERVER

2012-10-29T03:49:00.001-07:00

Some time when we work on multiple SQL SERVER then we are confused in which database we have to upload the data or execute the query so we are see the server name or scroll up down to check is it right server or not.
the best solution is if in this there are color combinatin then we are easily identfy this .
here is the solution in SQL SERVER 2008
Click on options>> then new window open

after that click on select button then new popup open

after that

after that select any color than OK .After that open the new window your task pane color is change

You set the different color for different SQLSERVER.

ROHIT KUMAR SRIVASTAVA

How can I quickly identify most recently modified stored procedures or table in SQL Server

2012-10-29T03:21:00.001-07:00

1. Select * From sys.objects where type='u' and modify_date between GETDATE()-1 and GETDATE()

Note : use p for store procedure,tr for trigger and fn for function

OR
2. Select * From sys.objects where type='u' order by modify_date desc

Note : use p for store procedure,tr for trigger and fn for function

you change the date accodingly in first Query
use type='p' for storeprocedure in this Query

ROHIT SRIVASTAVA

Get the Data In List and this list to string with LINQ in ASP.Net

2012-10-03T22:12:00.001-07:00

How to get the Data In List and retrive data from this list to string with LINQ

Firslty create a table from which we have to retrive data .
Create table category_name(
id tinyint IDENTITY(1,1),
category_name varchar(200),
cat_id tinyint
)

and insert some value in this table .In this table we enter some category Id like 1,2,3,4,5

insert into category_name
values('Football',1),
('Cricket',1),
('Rugby',1),
('Hockey',1),
('Tennis',1),
('Polo',1),
('Horse Racing',1),
('Musical',2),
('Ballet',2),
('Classical music',2),
('Opera',2),
('Traditional art',4),
('Modern art',4),
('Historical',4),
('Military',4),
('Religious',4),
('wine',5),
('whiskey',5),
('liquor',5),
('Others',5),
('Kosher',3),
('Halal',3),
('No red meat',3),
('No fish',3),
('No shell fish',3),
('Dairy free',3)

After createion and insertion in table Now we come to ASp.net code .
Make a class (name DTCategory ) in App_code folder for Set the property like below
*You are free to add any name according you. I USe the DTCategory and call this everywhere where its requirement*

public class DTCategory
{
    public string CategoryName
    { get; set; }
    public Int16 ID
    { get; set; }
}

we make above because we have to pass this in our list for getting more than one value .
After this add another file in your App_code folder and put method for get all category name as well as ID from database .Lets this file name is
DALGetName.
Do'nt forget to add common namespace like System.Collection

put this code into DALGetName
public List<DTCategory> GetAllCategoryName()
    {
        List<DTCategory> lst = new List<DTCategory>();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "proc_get_all_category_name";
        try
        {
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTCategory dto = new DTCategory();
                dto.CategoryName= Convert.ToString(dr["catergory_name"]);
                dto.ID = Convert.ToInt16(dr["id"]);
                lst.Add(dto);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
        return lst;
    }

=============================
Now your Appcode work is Finish . now lets come in own aspx page where we want to retrive this with LINQ.
In above i get all the categoryname as well as Id in List . But our requirment is get the data in ID wise and bind it into DropDown List are any string . So we use the LINQ to get data
with our requirement .

Firstly we have to declare this is in above the page load. Dont forgot to add namespace System.Text

public string _allsports = "";
    public string _allarts = "";
    public string _allcultural = "";
    public string _allgastronomy = "";
    public string _alldietry = "";

protected void GetAllCategoryName()
    {
        #region /*This region is used for get the data dynamically */

        DALGetName objdalcat = new DALGetName(); // Call this method into your .cs page
        List<DTCategory> lstCategory = new List<DTCategory>();
        lstCategory = objdalcat.GetAllCategoryName();
        //Bind Sport
        var QuerySports = from x in lstCategory
                          where x.Id== 1
                          select x;
        StringBuilder sbSports = new StringBuilder();
        foreach (DTOContactCategoryName i in QuerySports)
        {
            sbSports.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _allsports = sbSports.ToString();

        //Bind Arts
        var QueryArts = from x in lstCategory
                        where x.Id== 2
                        select x;
        StringBuilder sbArt = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryArts)
        {
            sbArt.Append("<option value=" + i.ID + " >" + i.CategoryName + "</option>");
        }
        _allarts = sbArt.ToString();

        //Bind Cultural
        var QueryCultural = from x in lstCategory
                            where x.Id== 4
                            select x;
        StringBuilder sbCultural = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryCultural)
        {
            sbCultural.Append("<option value=" + i.ID + " >" + i.CategoryName + "</option>");
        }
        _allcultural = sbCultural.ToString();

        //Bind Gastronomy
        var QueryGastronomy = from x in lstCategory
                              where x.Id== 5
                              select x;
        StringBuilder sbGastronomy = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryGastronomy)
        {
            sbGastronomy.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _allgastronomy = sbGastronomy.ToString();

        //Bind Other one Category which ID is 3
        var QueryDietary = from x in lstCategory
                           where x.Id== 3
                           select x;
        StringBuilder sbDietary = new StringBuilder();
        foreach (DTOContactCategoryName i in QueryDietary)
        {
            sbDietary.Append("<option value=" + i.ID + ">" + i.CategoryName + "</option>");
        }
        _alldietry = sbDietary.ToString();
        #endregion
    }

call this method on pageload method

====================in .aspx page we have some dropdown list and we bind the data which is Bind into this .cs Page

<select   class="ddlOptions">
                                        <%=_allsports %>
</select>

<select   class="ddlOptions">
                                        <%=_allcultural %>
</select>

<select   class="ddlOptions">
                                        <%=_allarts %>
</select>
<select   class="ddlOptions">
                                        <%=_allgastronomy %>
</select>
<select   class="ddlOptions">
                                        <%=_alldietry %>
</select>

Hope this will help you . If any issue then let me know

Thanks

ROHIT SRIVASTAVA

Silly mistake by me in SQL SERVER

2012-09-27T03:50:00.001-07:00

Today I had tried to solved this bug . I have to confess that this take some extra time by me .

The problem is i create a table name color and insert the field into them and execute this query

select * from color
Black
Blue
Green
Red

After inserting some data i run this query for checking any value is available in it or not

SELECT COUNT(1) color

It seems good becuase i write this query very rapidally and do not think about this create a bug?

when i run this it always return 1 . i am afraid WTF in this .
i spend some time on this but not able to get the error. really i think that is SQL Server corrupted or anything else .
after frustrating i go to took a cup of tea .

and when i came i suddenly saw the error there is mistake of from . i am not using from in my query .
SELECT COUNT(1) from color

this is silly but happened with me .
Hope this will help you .

ROHIT KUMAR SRIVASTAVA

Query for getting All Primary key and other constraint in SQL SERVER

2012-09-20T02:00:00.001-07:00

Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk

WHERE pk.CONSTRAINT_TYPE ='PRIMARY KEY'

Hope this will help you .
Thanks
ROHIT SRIVASTAVA

How to handle checkbox check uncheck event with Jquery

2012-08-30T23:28:00.000-07:00

Firstly you have to load all jquery file which is required

$('#myform :checkbox').click(function() {


    if ($(this).is(':checked')) {

           
        //Do your work //here check box checked code 
    } else {
        // Do your work // here check box unchecked code 
    }
});

How to Call JavaScript function After a time interval with Jquery

2012-08-29T01:54:00.002-07:00

use the function setInterval() like below.
setInterval(function () {
YourJavaScriptFunction();
}, 300000);

Hope this will help you
ROHIT KUMAR SRIVASTAVA

What is parsename function in SQL SERVER

2012-08-28T23:56:00.002-07:00

Q: What is the parsename function in sqlserver and what is the use of this ?
A:The PARSENAME string function returns the specified part of an object name.
The PARSENAME() function can be useful for parsing small strings.

Detail comming soon------------

Difference Between DataSet.Tables.Count() And DataTable.Rows.Count()

2012-08-27T22:09:00.000-07:00

We are aware about the DataSet and DataTable . Some time the question arise that what is difference between DataSet.Tables.Count() And DataTable.Rows.Count().

The solution is given below .

DataSet.Tables.Count()
-It Gives the number of tables available within a dataset

DataTable.Rows.Count() -
Gives the number of rows available within a datatable.it is basically used for counting total number of rows in a table.

Hope this will help you .
Thanks
ROHIT KUMAR SRIVASTAVA

How to Create Identity Incremented By 2 or any no in SQL SERVER

2012-08-27T21:44:00.005-07:00

Some time we Required when we create a table then auto increment id is increment by even no any specific no .

so the solution is here .
We create a table and i want its auto increment id is like 0 , 2 ,4 etc etc

Create table
create table test ( id int identity(0,2), name varchar(10) )

here in Red color if we increase with no 3 then its like 1,3,6 etc etc

Select * from test
output is
id name

Insert into test values('ROHIT')
Insert into test values('Riya')
Insert into test values('Immy')

Select * from test
output is
id name
0    ROHIT
2    Riya
4    Immy

Hope this will help you .
Thanks
ROHIT KUMAR SRIVASTAVA

Difference between 2 tier and 3 tier architecture

2012-08-22T04:05:00.001-07:00

Some time Question arised that what is basic difference between 2 tier and 3 tier

Two Tier Architecture: It is nothing but client server Architecture, where client will hit request directly to server and client will get response directly from server.

Three tier Architecture: It is nothing but Web Based application,here in between client and server middle ware will be there, if client hits a request it will go to the middle ware and middle ware will send to server and vise-versa.

Hope this will help you .
Thanks
ROHIT KUMAR SRIVASTAVA

How to get only month name with SQL Server

2012-08-16T21:57:00.001-07:00

declare @start DATE = '2012-04-01'
declare @end DATE = '2012-08-03'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select Datename(month,date) from months

Hope this will help you .
ROHIT KUMAR SRIVASTAVA

Difference Between Get And Post method in ASP.Net

2012-08-16T03:25:00.001-07:00

So many new programmer are confused about post and get method for send the data. In this article i discuss about this .

The first question arise that what is the get and post method

so many developer confused he/she said that get is used for get the data from server and post is used for post the data in server this is totally wrong(in starting i am also said this ).

Both Get and Post Method are form submission method.it is used for send the data from client side to server side.

The Second Question where it is .

The method is inside the form tag like below

syntax is like below

you use single method name get or post

Third Question is ByDefault which method is used

Ans is By default Get method are used

Now we discuss these method individually
What is the difference between these two method

GET	POST
Data will be arranged in HTTP header by appending to the URL as query string	Data will be arranged in HTTP message body.
Data is in query string so user can view the data	Data is not shown to user
Less secured compared to POST method because data is in query string so it will be saved in browser history and web server logs	safer than GET method because data is not saved in history or web server logs.
As data is saved in URL so its saves only 2048 bytes data	We can used any amount of data and any format
Can be bookmarked	Can’t bookmarked
Hacking will be easy	Hacking is difficult
Only ASCII character data type allowed	No restrictions. Allows binary data also
Caching is possible	No caching

When Use Post Method

If the form data is large then best is use POST method because GET method cannot handle long Url.

Form data contains Non-ASCII characters use POST because GET doesn’t support it.

Example: Usage of Get method In ASP.NET:

In the below example we will see how GET method passed data from abc.html (client) to login.aspx page (server).

abc.html

<html>

<body>

User Name: <input id="txtuserName" type="text" name="username" />

</form>

</body>

</html>

<html>

<body>

<div>

Welcome <b><% Response.Write(Request.QueryString["username"].ToString()); %></b>

</div>

</form>

</body>

</html>

Post Method:

We can access form data in ASP.NET using Request.Form["key1"]

Example: Usage of Post method In ASP.NET:

abc.html

<html>

<body>

User Name: <input id="txtuserName" type="text" name="username" />

</form>

</body>

</html>

<html>

<body>

<div>

Welcome <b><% Response.Write(Request.Form["username"].ToString()); %></b>

</div>

</form>

</body>

</html>

Output:

how to get all table or store procedure name with query in SQL Server

2012-08-14T00:12:00.001-07:00

Tested In SQLServer 2008

Below query is used for getting all table name from Your Database in SQL server

use YourDataBaseName

Select * from sys.objects where type='u'

Below query is used for getting all Store procedure name from Your Database in SQL server

use YourDataBaseName

Select * from sys.objects where type='p'
Below query is used for getting all trigger name from Your Database in SQL server

use YourDataBaseName
Select * from sys.objects where type='tr'

this query is used for getting table field name as well as datatype and many more
SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'urtablename'

in bold section put the relevent name

Hope this will help you .
Thanks
ROHIT KUMAR SRIVASTAVA

How to call JavaScript function on Code Behind or .Cs Page

2012-08-13T23:41:00.003-07:00

ROHIT KUMAR SRIVASTAVA

Copy one data from another table in SQL Server

2012-08-13T22:46:00.003-07:00

select * into newTable from OldTable
where newtable is ur new table name
and old table signify ur old table name from
ex.
Select * into tbluser_login from tblAdmin_login

Hope this will help you
Thanks
ROHIT SRIVASTAVA

How to get Image Size from fileupload on client side with Jquery

2012-08-13T22:39:00.003-07:00

Some time we require the condition that if image size greator than 2 MB then it show message that file size not allowed more than 2MB . You do it in server side but this is time taken . Now below are method where we check the size of upload file on Client side .
--------------------------------------------------------------------------------------
here i use the JQuery . Please do not forgot to add jquery file
lets the fileupload control name is
<input type="file" name ="fileuploadcatg" id="fileuploadcatg"/>
and a label for display label size
<label id="lblSize"></label>

if ($("#fileuploadcatg").val() != "") {
//below line is getting the size of file in MB.
                        var fSize = ($("input[name='fileuploadcatg']")[0].files[0].size / 1024);
                        var actualSize = fSize;
                        if (fSize / 1024 > 1) {
                            if (((fSize / 1024) / 1024) > 1) {
                                fSize = (Math.round(((iSize / 1024) / 1024) * 100) / 100);
                                $("#lblSize").html(fSize + "Gb");
                            }
                            else {
                                fSize = (Math.round((fSize / 1024) * 100) / 100)
                                $("#lblSize").html(fSize + "Mb");
                            }
                        }
                        else {
                            fSize = (Math.round(fSize * 100) / 100)
                            $("#lblSize").html(fSize + "kb");
                        }

                        if (parseInt(actualSize) > 4000) {

                            alert("Image size should not be more than 4 MB");
                            return false;
                        }
                         else
                      {
                                  // do what you want
                       }

hope this will help you .