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

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.

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

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.

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

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

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

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 &lt; 44 || event.keyCode &gt; 57 || event.keyCode==45 || event.keyCode==47) event.returnValue = false;"></asp:TextBox>