Saturday, December 6, 2008

Date Format

In aspx

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

this will display Nov/29/2008

Wednesday, November 26, 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

Wednesday, November 12, 2008

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

Wednesday, October 22, 2008

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())

Monday, October 20, 2008

Format Numbers

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

Tuesday, October 14, 2008

Apostrophe

SELECT 'Peter''s car'

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

Sunday, October 5, 2008

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

Wednesday, October 1, 2008

NULLIF and ISNULL (Transact-SQL)

This looks interesting:

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

And this:

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

Tuesday, September 30, 2008

Alter Column

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

Thursday, September 25, 2008

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)
)

Monday, August 4, 2008

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

Wednesday, July 23, 2008

Guid NewGuid

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

Saturday, July 19, 2008

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

Wednesday, July 16, 2008

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)
{
...
}

Tuesday, July 15, 2008

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;
}

Wednesday, July 9, 2008

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

Thursday, May 29, 2008

Dataset validation

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

Tuesday, May 20, 2008

Get specific field count on a table

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

Monday, May 19, 2008

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;
}
}
}

Monday, February 25, 2008

Reset IIS and Async service, batch file

iis and async service restarted



.

Saturday, February 23, 2008

Friday, January 4, 2008

Wednesday, January 2, 2008

VPC August 2007 Networking Settings

screenshot



External access from a virual enviroment.