Date Format

In aspx

DataBinder.Eval(Container.DataItem, "DateAdded", "{0:MMM/dd/yyyy}")

this will display Nov/29/2008

Webservices - Upload a file

3 things to consider

1. In web.config

< httpRuntime maxRequestLength="10240" executionTimeout="300"/ >

2. In the client application

MyWebService ws = new MyWebService();
ws.Timeout = 300*1000;

3. In IIS (not tested)

HTTP Keep-Alives Enabled
Connection Timeout: xyz seconds

Remove FK from a table

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Clients_Users]') AND type = 'F')
ALTER TABLE [dbo].[Clients] DROP CONSTRAINT [FK_Clients_Users]
GO

-- after that we're now able to for example drop the FK column

ALTER TABLE [dbo].[Clients] DROP COLUMN [UserID]
GO

CASE SQL

-- CASE NULL --

-- Example 1:
CASE WHEN ss.SessionID IS NULL THEN 'No' ELSE 'Yes' END AS IsActive

-- Example 2:
(CASE WHEN v.[Name] is null then '' else v.[Name] end) as SomeName

-- Example 3:
SELECT g.GraphID,
g.Description,
u.UserGraphID,
CASE WHEN u.GraphTypeID IS NULL AND g.GraphID = 7 THEN 2 ELSE u.GraphTypeID END AS GraphTypeID,
u.IsActive
FROM Graphs g
LEFT JOIN UserGraphs u on (g.GraphID = u.GraphID)

Example 4

SELECT ID,

CASE TypeID

WHEN 0 THEN 1 -- Standard

WHEN 2 THEN 2 -- Enterprise

WHEN 1 THEN 3 -- Premium

ELSE 0 -- Unknown

END AS TheType

FROM Users

Get records created one hour ago

-- Get stored procedures created one hour ago

SELECT [name], create_date, modify_date FROM sys.objects
WHERE type = 'p'
AND create_date >= DATEADD(hour, -1, getdate())

Format Numbers

<td id="tdAmount" runat="server" visible="false"><%# String.Format("{0:$#,##0.00}", DataBinder.Eval(Container.DataItem, "Amount"))%>td>

Apostrophe

SELECT 'Peter''s car'

-----------
Peter's car

Division By NULL

-- Division by NULL

SELECT (1/NULL) AS Result
SELECT (100/NULL) AS Result
SELECT (0/NULL) AS Result
SELECT (NULL/NULL) AS Result


Result
-----------
NULL

Result
-----------
NULL

Result
-----------
NULL

Result
-----------
NULL

NULLIF and ISNULL (Transact-SQL)

This looks interesting:

SELECT @OptionID = nullif(@OptionID, -1)

And this:

SELECT @OptionID = ISNULL(@OptionID, -1)

Alter Column

ALTER TABLE Minutes
ALTER COLUMN Rate decimal(6,4)
GO

Update with JOIN

UPDATE cmp SET cmp.[Name] = mp.[Name]
FROM ClientPlans cmp
INNER JOIN Plans mp ON cmp.PlanID = mp.PlanID

-- A bit more complex example

UPDATE cs SET cs.[BankID] = c.[BankID], cs.[StartDate] = c.[StartDate]
FROM ClientSales cs
INNER JOIN Clients c ON (c.LocationID = cs.LocationID
AND c.LastName = cs.LastName
AND c.PhoneNumber = cs.PhoneNumber
AND c.Code = cs.Code
AND c.StartDate = DATEADD(hour, -2, cs.StartDate)
)

Creating the ASPState database

at command prompt:

sql authentication:
aspnet_regsql -S localhost -U lorem -P 1psum -ssadd -sstype p

windows authentication:
aspnet_regsql -S localhost -E -ssadd -sstype p

aspnet_regsql -S localhost -E -ssadd -sstype c -d Dial800ASPState

and then in the web.config

<sessionState mode="SQLServer" cookieless="false" timeout="1440" sqlConnectionString="data source=localhost; initial catalog=Dial800ASPState; user id=lorem; password=1psum" allowCustomSqlDatabase="true" sqlCommandTimeout="120" />

Machine key settings - web.config

Machine key settings in between system.web

< 
machineKey 
validation="SHA1"
decryption="AES"
validationKey="06B52A1252C98B3EC549F1AC96C881F07EAA4FA9261BF1844E5997FA64FD2A6503808B00FE6E79D18F559E92825AA0CE802575A0E02814DB6EDBEDFA6C8623CC"
decryptionKey="ABAA84D7EC4BB56D75D217CECFFB9628809BDB8BF91CFCD64568A145BE59719F"/ >

Recomended:

validationKey 128 char-length value for SHA1
decryptionKey 64 char-length value for AES

Guid NewGuid

id="trSummary_<%=Guid.NewGuid() %>" runat = "server"

Add a new NOT NULL Column

Adding a new bit, not null column to an existing table requires you to specify a default value.

ALTER TABLE Users
ADD IsAvailable Bit not null default 0

It's better to give the default constraint a name, so we can use it later if we want to delete the column (the default constraint must be dropped before trying to delete the column)

ALTER TABLE Users
ADD IsAvailable bit NOT NULL CONSTRAINT [DF_Users_IsAvailable] DEFAULT 0

If we dont do that the system automatically assigns a random name for the constraint and it will be a bit more dificult to do the following:

ALTER TABLE Users
DROP CONSTRAINT DF_Users_IsAvailable

ALTER TABLE Users
DROP COLUMN IsAvailable

ALTER TABLE Users
ADD IsAvailable bit NOT NULL CONSTRAINT [DF_Users_IsAvailable] DEFAULT 1

if the system named the default constraint we can use the following query to find out that name (code from http://msmvps.com/blogs/robfarley/archive/2007/11/26/two-ways-to-find-drop-a-default-constraint-without-knowing-its-name.aspx)

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N'Users'
set @col_name = N'IsAvailable'

select t.name, c.name, d.name, d.definition, d.*
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name

Single quotes Datatable.Select()

Replace single quote for two single quotes, and it will work

string filter = @"DestinationName = '" + destinationName.Replace("'", "''") + "'";
DataRow[] drs = _ds.Tables[0].Select(filter);

foreach (DataRow dr in drs)
{
...
}

Bind Repeater to an ArrayList

from http://stanleycn.blogspot.com/2006/10/how-to-bind-arraylist-to-repeater.html

aspx

< id="rptDestinations" runat="server" onitemdatabound="rptDestinations_ItemDataBound">
<>
< id="lblDestinationName" runat="server" text=""> " > < / a s p : Label >
< / ItemTemplate >
< / a s p : Repeater>

aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
rptDestinations.DataSource = GetDestinations();
rptDestinations.DataBind();
}

private ArrayList GetDestinations()
{
ArrayList arrayList = new ArrayList();

foreach (DataRow dr in _ds.Tables[0].Rows)
{
if (!arrayList.Contains(dr["DestinationName"]))
{
arrayList.Add(dr["DestinationName"]);
}
}

return arrayList;
}

Select Insert Into

SELECT DISTINCT d.DestinationID, d.ClientID, d.[Name], c.[Name] AS ClientName
INTO #query
FROM DestinationDetails dd
RIGHT JOIN Destinations d ON d.DestinationID = dd.DestinationID
INNER JOIN Clients c ON c.ClientID = dd.ClientID
WHERE d.ClientID = @ClientID
AND (d.[Name] LIKE @Name OR @Name IS NULL)
ORDER BY d.[Name] DESC

Later we can use that newly created temp table

declare @totalCount int
select @totalCount = count(DestinationID) from #query

SELECT *, @totalCount AS TotalCount FROM #query

----------------------------------------------------------------------

CREATE TABLE #tempTable
(
Number varchar(10),
[Name] varchar(100),
ClientID int,
Alias varchar(100)
)

INSERT INTO #tempTable
SELECT DISTINCT
dd.Number,
ISNULL(d.Alias, dbo.FormatNumber(dd.Number)) AS [Name],
d.ClientID,
d.Alias
FROM Destinations d
INNER JOIN DestinationDetails dd ON (dd.DestinationID = d.DestinationID)
WHERE d.UserID = @UserID

----------------------------------------------------

INSERT INTO ClientItems (ClientID, Amount, Item)
SELECT ClientID, Amount, Item
FROM Clients WHERE ClientID = 1234 AND Amount > 0

------------------------------------------------------

This will create a new table based on an existing one



SELECT *
INTO users_backup
FROM Users

Dataset validation

if(ds != null && ds.Tables[0].Rows.Count > 0)
{
...
}

Get specific field count on a table

SELECT FieldName, COUNT(FieldName)
FROM Table
GROUP BY FieldName
order by COUNT(FieldName) desc

Change GridView backgrod color

aspx

< id="grdLicensedClients" runat="server" onrowdatabound="grdLicensedClients_RowDataBound">

c#

protected void grdLicensedClients_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DataRowView drv = (DataRowView)e.Row.DataItem;

string areaCode = drv["Area"].ToString();

if (_duplicateAreaCodes.Contains(areaCode))
{
e.Row.BackColor = System.Drawing.Color.Yellow;
}
}
}

Reset IIS and Async service, batch file

iis and async service restarted



.

Hide a picklist item or disable the picklist control

Hide an item



or Lock the field.

Ektron Form Block Settings

Image



Lorem ipsum

VPC August 2007 Networking Settings

screenshot



External access from a virual enviroment.