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