Removing Temporary Tables from Sql server

--unless you do something like this
USE tempdb
GO

--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

--let's go back to Norhtwind again
USE Norhtwind
GO


--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END


It doesn't exist and that is correct since it's a local temp table not a global temp table


Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

Formating Decimal Numbers to two or more decimal places in dotnet

Formating Decimal Numbers to two or more decimal places in dotnet

FormatNumber(convert.ToDouble("100.22"), 2, , , TriState.False)

using Case statmenet in sql server query


select [Disti Name], [Product Group],
CASE FQ
WHEN 'Q1' THEN 'Feb-Jul'
WHEN 'Q2'THEN 'March-Aug'
WHEN 'Q3' THEN 'April-Sept'
WHEN 'Q4' THEN ' May-Oct'
ELSE 'NULL'
END
,SUM([Extended Resale]) as [Total Post Extended Resale],SUM([Extended Cost]) AS [Total Pos Extended Cost],CONVERT(varchar,
(CONVERT(decimal(18, 2), (1.0 * ((SUM([Extended Resale])-SUM([Extended Cost]))/(nullif(SUM([Extended Resale]),0)))) * 100))) AS [Margin]
from dbo.POS_Details_FY09M10_FY10M05
group by [Disti Name],[product group],FQ
order by [Disti Name],[Product Group],FQ

Formating Excell Cells When Exporting Gridview To Excel

Add the style sheet with the below styles to the stream writer as below

Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim styleInfo As String = ""
Dim styleInfo1 As String = ""


There are many other style you can apply to get correct date and number decimal points amount.

mso-number-format:"0" No Decimals
mso-number-format:"0\.00" 2 Decimals
mso-number-format:"mm\/dd\/yy" Date format
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 03/07/2009
mso-number-format:"Medium Date" 05-jan-2008
mso-number-format:"Short Time" 8:67
mso-number-format:"Medium Time" 8:67 am
mso-number-format:"Long Time" 8:67:25:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
mso-number-format:"\0022£\0022\#\,\#\#0\.00" £12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " 2 decimals, negative numbers in red and signed
(1.86 -1.66)
mso-number-format:"\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)" Accounting Format –5,(5)

Dotnet 2005 Data List and Custom binding controls with Drop down list

Category Name:
Company Name:
Last Name:
Product Name:

Imports System
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

NameSpace DotNetJohn

Public Class ManyResults : Inherits System.Web.UI.Page

Protected ddlCategoryName As DropDownList
Protected ddlCompanyName As DropDownList
Protected ddlLastName As DropDownList
Protected ddlProductName As DropDownList

Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim dataReader As SqlDataReader
Dim strSql As String

objConn = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
strSql = _
"SELECT CategoryName FROM Categories ORDER BY CategoryName;" _
& "SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName;" _
& "SELECT LastName FROM Employees ORDER BY LastName;" _
& "SELECT Top 10 ProductName FROM Products ORDER BY ProductName;"

objCmd = New SqlCommand(strSql, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader()
'CategoryName
With ddlCategoryName
.DataSource = dataReader
.DataTextField = "CategoryName"
.DataValueField = "CategoryName"
.DataBind()
End With
'CompanyName
dataReader.NextResult()
With ddlCompanyName
.DataSource = dataReader
.DataTextField = "CompanyName"
.DataValueField = "CompanyName"
.DataBind()
End With
'LastName
dataReader.NextResult()
With ddlLastName
.DataSource = dataReader
.DataTextField = "LastName"
.DataValueField = "LastName"
.DataBind()
End With
'ProductName
dataReader.NextResult()
With ddlProductName
.DataSource = dataReader
.DataTextField = "ProductName"
.DataValueField = "ProductName"
.DataBind()
End With
Catch exc As Exception
Response.Write(exc)
Finally
If Not dataReader Is Nothing Then
dataReader.Close()
End If
objCmd = Nothing
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
objConn.Dispose()
End Try

End Sub

End Class

End NameSpace

"Failed to start monitoring directory changes" error message when you browse to an ASP.NET page

Access denied to 'C:\Inetpub\wwwroot\myapp\default.aspx'. Failed to start
monitoring file changes.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Access denied to
'C:\Inetpub\wwwroot\myapp\default.aspx'. Failed to start monitoring file
changes.

Source Error:


An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.


Stack Trace:


[HttpException (0x80070005): Access denied to
'C:\Inetpub\wwwroot\myapp\default.aspx'. Failed to start monitoring file
changes.]
System.Web.DirectoryMonitor.AddFileMonitor(String file) +381
System.Web.DirectoryMonitor.StartMonitoringFile(St ring file,
FileChangeEventHandler callback, String alias) +76
System.Web.FileChangesMonitor.StartMonitoringPath( String alias,
FileChangeEventHandler callback) +495
System.Web.Caching.CacheDependency.Init(Boolean isPublic, Boolean
isSensitive, String[] filenamesArg, String[] cachekeysArg, CacheDependency
dependency, DateTime utcStart) +1535
System.Web.Caching.CacheDependency..ctor(Boolean isSensitive, String[]
filenames, DateTime utcStart) +50

System.Web.Configuration.HttpConfigurationSystem.G etCacheDependencies(Hashta
ble cachedeps, DateTime utcStart) +144
System.Web.Configuration.HttpConfigurationSystem.C omposeConfig(String
reqPath, IHttpMapPath configmap) +697
System.Web.HttpContext.GetCompleteConfigRecord(Str ing reqpath,
IHttpMapPath configmap) +434
System.Web.HttpContext.GetCompleteConfig() +49
System.Web.HttpContext.GetConfig(String name) +195
System.Web.CustomErrors.GetSettings(HttpContext context, Boolean
canThrow) +20
System.Web.HttpResponse.ReportRuntimeError(Excepti on e, Boolean canThrow)
+40
System.Web.HttpRuntime.FinishRequest(HttpWorkerReq uest wr, HttpContext
context, Exception e) +479


1) try to give full access control to my
account on the %windir%microsoft.net\framework\v1.x.x.xxx\Tempora ry ASP.NET
Files, but the error persists. Actually this step is not necessary, since
Administrators already have full access control of the directories.

2)check the folder permissions for the application some times the respective aspnet account

http://support.microsoft.com/kb/317955