Query to find number of users connected in an MS SQL database

 

Sometimes you may get an error from your application saying "Connection pool size exceeded".

Here I am going to explain you few queries which can be used to find out the number of open connections in your MS SQL database and also the query to find the maximum connections allowed in SQL server.

First we will check, what is the maximum connections SQL server will allow you to connect.

SELECT @@MAX_CONNECTIONS AS 'Maximum Connections Allowed in the database'

Now you know that how many connections you can have in your database. So we will check how many are already used.

Below query can be used to find the number of connections in each database in your server if you execute this query against master database.

SELECT DB_NAME(dbid) as [Database Name], COUNT(dbid) as [Number of Connections] from master.dbo.sysprocesses with (nolock)

WHERE dbid > 0 GROUP BY dbid

Now we will see who all are the users connected to your database.

Below query will show you the login name and the host name of the user connected to your database. In below query you may change the database name when you execute in your system.

SELECT LOGINAME=RTRIM(LOGINAME),HOSTNAME FROM MASTER.DBO.SYSPROCESSES

WHERE DB_NAME(DBID) = 'DataBaseName' AND DBID != 0

Now at the end we will see a very simple query which can be used to show all the sessions that are currently established in the database.

sp_who2

How to Export Gridview data to Excel with special characters in ASP.NET

Many times you may get requirement to export data to excel with special characters. When you do normal export with special characters your excel file will have weird characters in place of special character.

So in such cases what you have to do is, Unicode encoding. I have given sample code below.

private void ExportToExcel()

      {

          string Excelfilename = "Test_Excel_Export" + DateTime.Now;

          Response.Clear();

          Response.Buffer = true;

          Response.ContentType = "application/vnd.ms-excel";

          Response.AppendHeader("Content-Disposition:", "attachment; filename=" + Excelfilename + ".xls");

          Response.Charset = "";

          Response.ContentEncoding = Encoding.Unicode;

          Response.BinaryWrite(Encoding.Unicode.GetPreamble());

          this.EnableViewState = false;

          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

          GridView1.RenderControl(oHtmlTextWriter);

          Response.Write(oStringWriter.ToString());

          Response.End();

      }

In the above code two import lines of code which does the encoding is below,

Response.ContentEncoding = Encoding.Unicode;

Response.BinaryWrite(Encoding.Unicode.GetPreamble());

To use above code you have to add below namespace otherwise you will get error.

using System.Text;

I hope now you are very clear about “How to Export Gridview data to Excel with special characters in ASP.NET”.

You are always welcome to post your queries below.

How to list all the constraints in SQL Server Database

 

There are many methods to lists the constraints in SQL database. We will go through few methods one by one.

Method 1:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in all the database.

Method 2:

SELECT * FROM [DataBaseName].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in the specific database.

Method 3:

SELECT OBJECT_NAME(OBJECT_ID) AS [Constraint_Name],

OBJECT_NAME(parent_object_id) AS [TableName],

type_desc AS [TypeOfConstraint]

FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'

This method will give bit more details about the constraints. This can be used to return all constraints in the database with Constraint Name, Table Name and Type of Constraint.

Method 4:

sp_help TableName

This query will return all the constraints in the specific table.

How to disable previous dates in Calendar control in ASP.NET

 

This is a simple code but many people got confused about this inbuilt feature in .NET Calendar control.

We will be using Calendar DayRender control to disable all the previous dates.

First we will add the Calendar control,

<asp:Calendar ID="Calendar1" runat="server" ondayrender="Calendar1_DayRender">

       </asp:Calendar>

No we will see what you have to write under DayRender event,

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

        {

            if (e.Day.Date < DateTime.Now.Date)

            {

                e.Day.IsSelectable = false;

                e.Cell.ForeColor = System.Drawing.Color.Gray;

            }

        }

 

See the Calendar control after disabling the previous dates,

Best of Microsoft Management Summit (UK) 2012‏

 

Learn about 2012's exciting wave of Microsoft Private Cloud product launches and announcements – starting with the release of System Center 2012.
Get the latest insights into the Microsoft Private Cloud and learn what's new in System Center 2012. The Best of MMS 2012 brings you the highlights from MMS 2012 conference in a choice of UK-wide one-day or virtual events.

Register for the event using the link Launching System Center 2012

How to read web.config file using JavaScript

 

I have seen many people asking this question in .NET forums. So I thought I will share this small piece of code.

First we will see web.config entry which we are going to read,

<configuration>

      <appSettings>

            <addkey="WordTemplate"value="template.doc"/>

      </appSettings>

      <connectionStrings>

                 <add name="MysqlCon"

         connectionString="server=1xx.1xx.x.x;User Id=username;password=password;database=dbName;Persist Security Info=True"

         providerName="MySql.Data.MySqlClient" />

   </connectionStrings>

      </connectionStrings>

...

<configuration>

Now we will see the JavaScript function which reads above connection string and the AppSettings key,

<script language="javascript" type ="text/javascript"> 

function ReadWebConfig()

{

    var strCon = '<%=ConfigurationManager.ConnectionStrings["MysqlCon"].ConnectionString %>'

    alert(strCon);

    var strTemp = '<%=ConfigurationManager.AppSettings["WordTemplate"].ToString() %>'

    alert(strTemp);

}

</script>

Visual Studio 11 Beta in LIVE now

 

Happy news for all .NET developers who was waiting to have glance on Visual Studio 11 Beta.

You can download Visual Studio 11 Beta from Microsoft download centre now.

Please check this link to download the Visual Studio 11 Beta

List all Stored Procedure Names and Scripts in the database.

 

Below SQL query can be used to display all Stored Procedures and SQL Scripts used to create those Stored Procedure in your database.

SELECT  sysObj.Name as [Stored Procedure Name], sysM.Definition as [Stored Procedure Script]

FROM sys.sql_modules as sysM INNER JOIN sys.objects as sysObj 

ON sysM.object_id = sysObj.object_id 

WHERE sysObj.type = 'P'

If you need more sample please refer my articles: Stored Procedures

Download for Internet Information Services (IIS) 8.0 Express Beta is now live

 

Happy news for the developers. Microsoft announce that download for IIS 8.0 Express Beta is now live.  You can download the same using the following URL:

http://www.microsoft.com/download/en/details.aspx?id=29055

The IIS 8.0 version of IIS Express shares more of its foundation with the full version of IIS, so you may not face much issues when you switch between your development environment and your production environment.

More details are available in the ReadMe file for IIS Express, which is can be seen at the following URL:

http://learn.iis.net/page.aspx/1266/iis-80-express-beta-readme/

How to display alert in outlook express if subject field is empty while sending mail

 

This is a common mistake every one does while sending mail using outlook. You may feel bad if you send an important mail to the client or to your manager without a subject.

Since Microsoft didn't provide any option to make the subject mandatory or any warning for the empty subject I thought I will do a little research and find out the workaround.

This is a VB code which will display an alert when you click on Send Mail button in Outlook if subject field is empty.

Just follow below steps,
1. Of course first we will open outlook
2. Then we will Open Visual basic editor by pressing ALT+F11
3. Then from left pane expand the Project1 until you see "ThisOutlookSession"
4. Double Click on "ThisOutlookSession"
5. Now it's the time to copy and paste the below code in the new window opened when you double click on "ThisOutlookSession".
6. Save and Close the window. You are done!

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean) 

Dim strOutlookSubject As String 

strOutlookSubject = Item.Subject 

If Len(strOutlookSubject) = 0 Then 

Prompt$ = "Are you sure you want to send the Mail without Subject?" 

If MsgBox(Prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Subject is empty") = vbNo Then 

Cancel = True 

End If 

End If 

End Sub

Note: Change the alert as per your interest from above code.

Below is the screen print of the alert which you may be getting,

Now try to send mail without subject from your outlook!!!

My Weblog post

How to add URL in favourites using ASP.NET and JavaScript

One another regular question in forum. This is a simple code which helps you to add URL in favourites. Here I have used both JavaScript and ASP.NET code.

I am giving two sample code here.

Method 1:

First we will add the current URL by opening Add URL pop up and allow the user to enter the favourites URL title.

//btnAddFavourites is the button name 

btnAddFavourites.Attributes.Add("OnClick", "window.external.AddFavorite(location.href, document.title); return true;");

Below is the pop up window sample,

Method 2:

In this method we will not take any input from user. We will add a simple JavaScript function and hard code both the URL and Title.

Add below JavaScrit just below your <head> opening tag,

<script language="javascript">

        function AddToFav()

        {

        bookmarkurl= "http://asheej.blogspot.com/";

        bookmarktitle="Good .NET Articles"

        if (document.all)

        window.external.AddFavorite(bookmarkurl,bookmarktitle)

        else if (window.sidebar) // firefox

        window.sidebar.addPanel(bookmarktitle, bookmarkurl, "");

        }

    </script>

Now we will call this function on button click,

//btnAddFavourites is the button name

btnAddFavourites.Attributes.Add("OnClick", "AddToFav(); return true;");

Just try this in your website.

Cheers Asheej!

How to get max value from a varchar field in MS SQL

Here I am going to explain you how to get max value from a varchar field in MS SQL.

Below is the table we are going to use here,

CREATE TABLE [Customer]

(

    [CustomerID] [nvarchar](50) NOT NULL,

    [CustomerName] [nchar](10) NULL,

    [Address] [nvarchar](50) NULL,

  CONSTRAINT [pk_Cust_ID] PRIMARY KEY CLUSTERED 

  (

    [CustomerID] 

  )

)

GO

If you look at the table I have declared CustomerID as nvarchar data type which means we will have both numeric and alphanumeric values.

Now we will insert some sample data,

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('1','Name1','Address1')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('2','Name2','Address2')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('11','Name11','Address11')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('2a','Name2a','Address2a')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('1a','Name1a','Address1a')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('21','Name21','Address21')

GO

In the above statement 21 is the highest value.

SELECT MAX(CAST(CustomerID AS Int)) as [Customer ID] FROM Customers

WHERE ISNUMERIC(CustomerID)=1  AND CustomerID LIKE '%[0-9]%'

When we execute above query all alphanumeric values in the field  will be ignored and the output will be 21.

Transaction in ASP.NET

I have seen many people asking questions in forum that how to use transaction in ASP.NET. Normally we do transaction from SQL. Here I am going to explain how to do the transaction from ASP.NET code behind.

Normally transaction will be used in the code where you do all your database operation.

Below is the sample code to show you how the code looks like when we use the transaction in ASP.NET code behind.

string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection SQLCon = new SqlConnection(strCon);

SQLCon.Open();

    SqlTransaction SQLTran = SQLCon.BeginTransaction("MyTrans");

    SqlCommand Cmd = new SqlCommand();

    Cmd.Transaction = SQLTran;

    Cmd.Connection = SQLCon;

    try

{

        Cmd.CommandText ="First SQL query to update/delete record";

        Cmd.ExecuteNonQuery();

        Cmd.CommandText ="Second SQL query to update/delete record";

        Cmd.ExecuteNonQuery();        

        SQLTran.Commit();

    }

    catch (Exception ex)

    {

    lblError.Text = "Error!! <br>+" + ex.Message.ToString();

        SQLTran.Rollback();

    }

In the code you would have noticed that roll back code is kept inside the catch block which means whole execution will be rolled back if there is an exception.

I hope now you are clear with the Transaction concept in ASP.NET

Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack

" Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack" - This is one of the common error we see in forum where people request solution for it.

Below is a small explanation and resolution to resolve this issue,

This error normally occurs when we use Response.Redirect or Server.Transfer or Response.End in your code before completing the actual process the page was doing.

In this case what you have to do is

In place of Response.End you have to use HttpContext.Current.ApplicationInstance.CompleteRequest

and in place of Response.Redirect, use the Response.Redirect ("Paganame.aspx", false);

and in place of Server.Transfer, use the Server.Execute method

There is also a KB article related to this issue,
http://support.microsoft.com/kb/312629/EN-US/

Received winners of Star of the Year - 2011 Award from dotnetspider.com

Happy to inform you all the I have been selected as "Star of the Year for the year 2011" and given iPad2 as gift by www.dotnetspider.com.

Thank you all for the support and guidance especially to Tony John who is the founder and webmaster of dotnetspider.com

You may read the announcement here Star of the Year