Kundan "The world of IT"

ERROR: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

2010-07-05T02:26:00.000-07:00

ERROR: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Cause: This error occurs when a database query or stored procedure is not able to complete execution before a pre-set timeout period expires.

Solution:
Dim com As New SqlCommand(sql, myConnection, myTrans)
com.CommandType = CommandType.Text
com.CommandTimeout = 0

Note: CommandTimeout property is documented to support a value of 0, which provides for an infinite timeout. The default is 30 seconds. But sometime setting timeout value to zero, creates a problem, therefore it will be better to set timeout property to the maximum i.e. 7200.

Happy Coding

Convert DateTime Format (dd/mm/yyyy to mm/dd/yyyy) in VB.Net and Sql Server 2000, 2005.

2010-03-27T06:16:00.000-07:00

When we insert data in datetime type variable in sql server, sometimes we face error like below:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is all due to the date time FORMAT problem. SQL accepts mm/dd/yyyy format (by default setting, however we can change it, but it is out of topic), while we often provide dd/mm/yyyy format.

So we have two option, either we should provide the correct format from front end, or we need to apply any trick on back end (on sql server end).

Here we goes with both type of solution...

At first from Front End (here I am using VB.NET)

Dim [date] As String = "29/03/2010" 'format is MM/dd/yyyy

Dim dateTimeFormatterProvider As System.Globalization.DateTimeFormatInfo = TryCast(System.Globalization.DateTimeFormatInfo.CurrentInfo.Clone(), System.Globalization.DateTimeFormatInfo)

dateTimeFormatterProvider.ShortDatePattern = "dd/MM/yyyy"

'source date format

Dim dateTime As DateTime = dateTime.Parse([date], dateTimeFormatterProvider)

Dim formatted As String = dateTime.ToString("MM/dd/yyyy")

OUTPUT : 3/27/2010

Now at Back End i.e. SQL SERVER

SELECT CASE WHEN ISDATE('27/03/2008') = 0 THEN CONVERT(DATETIME,'27/03/2010',103) ELSE '27/03/2010' END

Expanation:

ISDATE('27/03/2008') function takes single arguments of text type and returns 1 if the supplied argument is valid date or return 0 if the supplied argument is invalid date.

CONVERT(DATETIME,'27/03/2010',103) function takes 3 arguments, first dattype in which we need to convert the datetime, second datetime in text format and last one is format (viz. 100,101,103,105 etc), in which conversion is possible.

Here is the complete details of these possible codes:

SELECT CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)

Mar 27 2010 7:33PM

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)

03/27/10

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)

10.03.27

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)

27/03/10

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)

27.03.10

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)

27-03-10

SELECT CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)

27 Mar 10

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)

Mar 27, 10

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)

19:34:55

SELECT CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)

Mar 27 2010 7:34:58:587PM

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)

03-27-10

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)

10/03/27

SELECT CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)

100327

SELECT CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)

27 Mar 2010 19:35:11:163

SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)

19:35:14:633

SELECT CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)

2010-03-27 19:35:17

SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)

2010-03-27 19:35:23.807

SELECT CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)

03/27/10 7:35:27 PM

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)

2010-03-27

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)

19:35:34

SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)

2010-03-27 19:35:37.320

SELECT CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)

Mar 27 2010 7:35PM

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)

03/27/2010

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)

2010.03.27

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)

27/03/2010

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)

27.03.2010

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)

27-03-2010

SELECT CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)

27 Mar 2010

SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)

Mar 27, 2010

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)

19:36:07

SELECT CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)

Mar 27 2010 7:36:10:210PM

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)

03-27-2010

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)

2010/03/27

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)

20100327

SELECT CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)

27 Mar 2010 19:36:26:930

SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)

19:36:29:693

SELECT CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)

2010-03-27 19:36:32

SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)

2010-03-27 19:36:35.210

SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)

2010-03-27T19:36:57.413

SELECT CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)

12 ???? ?????? 1431 7:37:20:837

SELECT CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)

12/04/1431 7:37:26:663PM

Format Int Into 2 or 3 Digit String, Varchar into SQL

2010-02-21T23:59:00.000-08:00

Sometimes we need to write or format sql (SQL Server 2000, 2005, 2008) int 1 as varchar or string '01' or '001'.

Or there may be the case when the number stored in column is a single digit and we need to convert it into a 2 or 3 (Two or Three) digit string that begins with a 0 (Zero).

Simply say Displaying 2 or 3 digit number left padding with 0 (Zero).
e.g 1 as '01', 2 as '02' and 17 as '17'
or 1 as '001' 2 as '00' and 175 as '175'
etc.

OK, No Problem Try Following Logic..

For 2 Digit Padding or Formatting

RIGHT ('00' + LTRIM(STR(<YourNumber>)),2)

In Case of Select Statement
SELCT RIGHT ('00' + LTRIM(STR(<YourNumber>)),2) FROM tableName

For 3 Digit Padding or Formatting

RIGHT ('00' + LTRIM(STR(<YourNumber>)),2)

In Case of Select Statement
SELCT RIGHT ('000' + LTRIM(STR(<YourNumber>)),3) FROM tableName

And So On....

Happy Coding..
Thanks..

The database principal owns a schema in the database, and cannot be dropped.

2010-02-08T05:40:00.001-08:00

Sometimes, We want to DROP a USER of DATABASE but an ERROR occurred:
'The database principal owns a schema in the database, and cannot be dropped.'

This happens as when we create user and assign its role to db_owner or something else, it makes an entry in the schema of db_owner or other roles.

So, if we want to delete the user, we need to make the same changes to all the schemas where the "Database Principal" is having/owning the role. For Example, if it is having "db_owner, db_datareader, db_datawriter" roles; then we have to make the above said changes for all these 3 schemas.

Let's assume, we want to delete an user 'user1' assigned role 'db_owner' and 'db_datareader'.
For it,
our_DB_Name > Schemas > db_owner > right click > select properties
Here we would find the name of 'user1', We would change it to any other like 'dbo'.
Do the same for 'db_datareader'
i.e.
our_DB_Name > Schemas > db_datareader> right click > select properties
Here we would find the name of 'user1', We would change it to any other like 'dbo'.

That's All.
Now, We can easily delete the 'user1'.

Thanks

How to disable CUT, COPY, PASTE in TextBox in ASP.NET

2010-02-05T05:03:00.000-08:00

Sometime you may come across a situation when you want to disable CUT, COPY or PASTE from/in any TextBox in ASP.NET.
e.g. Whenever you want to force user to type CONFIRM PASSWORD not by simply copy paste, but via TYPING IT IN TEXT BOX, then you may need this code.

Nothing to do..
just add few small script in .aspx file as bellow

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

Thanks
Happy Coding

To test the WebServices other than local machine

2009-12-04T04:26:00.000-08:00

Whenever you want to execute any webservices from any other machine than local machine, on which the webservices are created, you get an error message like bellow

"The test form is only available for requests from the local machine"

If you simply open the web config file and add the following, you will be able to access the test form outside of the localhost.

You need to write this code segment inside "SYSTEM.WEB" tag which is inside "CONFIGURATION" tag...

Check it out...

<webServices>
        <protocols>
<addname="HttpGet"/>
     <add name="HttpPost"/>
</protocols>
    </webServices>

Numeric Validation for a TextBox Using JavaScript on KeyPress

2009-12-01T02:58:00.000-08:00

No need to write any External JavaScript Code, Just go through the following code segment...

In HTML

<input type="Text" name="txtTerms" value="" size="20" class="txtbk" onkeypress=""> 57 || event.keyCode==45 || event.keyCode==47) event.returnValue = false;">

in ASP.NET

<asp:TextBox id="txtWardNo" runat="server" Width="50px" MaxLength="2" onKeypress="if (event.keyCode < 44 || event.keyCode > 57 || event.keyCode==45 || event.keyCode==47) event.returnValue = false;"></asp:TextBox>

Check and Uncheck all the CheckBoxes of DataGrid using Java Script

2009-01-23T02:43:00.001-08:00

Deleting duplicate records from table in SQL Server

2009-01-15T09:29:00.000-08:00

create table empdetails(empid int,empname varchar(50))
drop table empdetails
insert into empdetails values('1','Kundan Kumar')
insert into empdetails values('2','Kamal Kumar')
insert into empdetails values('3','Sunil Kumar')
insert into empdetails values('4','Ramesh Kumar')
insert into empdetails values('5','Jitendra Kumar')
insert into empdetails values('6','Atul Kumar')
insert into empdetails values('7','Kunal Kumar')
insert into empdetails values('8','Suresh Verma')
insert into empdetails values('9','Vijay Kumar')
insert into empdetails values('10','Abhishek Kumar')

select * from empdetails

select distinct * from empdetails
--Create a tempTABLE and fetch all the distinct record from table having duplicate record
select distinct * into tempempdetails from empdetails
--Now Drop old table that has duplicate record
drop table empdetails
--Now Rename the tempTABLE with the name that old table had
exec sp_rename 'tempempdetails', 'empdetails'
--Now we can check our new table
select * from empdetails
--METHOD-2
--Create a tempTABLE and fetch all the distinct record from table having duplicate record
select distinct * into tempempdetails from empdetails
--Now delete all records from old table
truncate table empdetails
--Now copy all unique records in old table from tempTABLE
insert empdetails select * from tempempdetails
--Finally drop the tempTABLE
drop table tempempdetails

Clustered Index Vs. Non-Clustrued Index in SQL Server

2009-01-10T10:11:00.000-08:00

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

To display or find the VIEW and TABLE from DATABASE in SQL SERVER 2000

2009-01-09T22:33:00.000-08:00

use DatabaseName
--To View the name of all the dataabases
sp_helpdb
--To display all the tables and views of current daataabes
--Outuput Format (Name,Owner,Object_type)
sp_help
--Output Format Table_Qualifier,Table_Owner,Table_Name,Table_Type,Remarks)
sp_tables
--Second Method where third parameter should be the name of current database
sp_tables null ,dbo, DatabaseName, "'TABLE' and 'VIEW'"
--To display all the TABLES and VIEWS of the current database
select * from information_schema.tables
--To display the name of all the USER TABLES i.e. BASE TABLES of the current database
select * from information_schema.tables where table_type = 'BASE TABLE'
--To find any BASE TABLES in the current database
select * from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%Income%Details%'
--To display the name of all the VIEWS(system defined as well as user defined) of the current database
select * from information_schema.tables where table_type = 'VIEW'
--To find any VIEW of the current database
select * from information_schema.tables where table_type = 'VIEW' and table_name like '%View%'

What is ASP.NET 2.० ?

2009-01-09T01:35:00.000-08:00

ASP.NET is a programming model for building Web-based applications. It is essentially a runtime and set of .NET Framework class libraries that can be used to build dynamic Web pages. It runs within the context of a Web server, such as Microsoft Internet Information Server (IIS), and processes programming instructions on the server to service browser requests. Unlike static HTML, which is served directly from the Web server, ASP.NET pages are actually executed on the server to produce dynamic results. The final rendering of a page might be constructed from a variety of different instructions and/or data sources.ASP।NET pages are stored under the .aspx extension. Pages are created by a programmer as a combination of text, markup (such as HTML), and ASP.NET server-specific tags and script, and then stored on the Web server. You can think of a stored ASP.NET page as a set of instructions for how to build an HTML page. When the page is requested, the server-side logic is processed to create a page in pure markup that the client browser can understand and render. Because the rendered output is pure markup, any browser can read it; all the dynamic processing happens on the Web server. ASP.NET server-specific tags are very powerful, including the capability to react to user actions, connect to data stores, and automatically build very complex HTML structures.As previously mentioned, ASP.NET is simply part of the .NET Framework and, consequently, ASP.NET pages can take advantage of all of the services offered by that framework, including networking, data access, security, and much more. The fact that all of these framework services are available to ASP.NET enables you to build much richer Web applications than ever before. You can spend less time reinventing the basic building blocks that all applications need, and spend more time focusing on the specific logic that is unique to your application.ASP.NET also introduces some unique innovations in Web programming that greatly improve the development model over classic Active Server Pages (ASP).