Sunday, May 22, 2011

One problem with HTML forms is it is hard to style the elements to fit into your design. The tutorial will show you how to style the hardest of them all, the select box.

The Plan

Unfortunately browsers allow limited skinning of select boxes. So the plan is to use jQuery to change a select box into a text box and a unorded list for the dropdown. When a user clicks on the text box it will display the list below the textbox, just like a regular select box, but it’s easier to style

see more and download: http://www.devirtuoso.com/2009/08/styling-drop-down-boxes-with-jquery/

Tuesday, May 17, 2011

Adding a column to a table in SQL Server is done using the ALTER TABLE tablename ADD command. When adding columns you can specify all the same settings available when creating a table.

In the example below, we will create a small sample table, then add columns using the ALTER TABLE command.

Multiple columns can be specificied by using the syntax below. First issue an alter table command then add the column name, data type, nullable, and default value.

CREATE TABLE dbo.Employees
(
EmployeeID int IDENTITY (1,1) NOT NULL PRIMARY KEY NONCLUSTERED
)
GO
ALTER TABLE dbo.Employees
ADD
FirstName varchar(50) NULL
,LastName varchar(50) NULL
,SSN varchar(9) NULL CONSTRAINT ssn_unique UNIQUE
,IsTerminated bit NOT NULL DEFAULT 0
,DateAdded datetime DEFAULT GETDATE()
,Comments varchar(255) SPARSE NULL -- SQL Server 2008 sparse column

The Alter Column statement can modify the data type and the Nullable attribute of a column. The syntax is the same for SQL Server 2005 and SQL Server 2008 except 2008 allows the sparse attribute to be changed.

For the example below, we will begin by creating a sample table, then we will modify the columns.

CREATE TABLE dbo.Employee
(
EmployeeID INT IDENTITY (1,1) NOT NULL
,FirstName VARCHAR(50) NULL
,MiddleName VARCHAR(50) NULL
,LastName VARCHAR(50) NULL
,DateHired datetime NOT NULL
)
-- Change the datatype to support 100 characters and make NOT NULL
ALTER TABLE dbo.Employee
ALTER COLUMN FirstName VARCHAR(100) NOT NULL
-- Change datatype and allow NULLs for DateHired
ALTER TABLE dbo.Employee
ALTER COLUMN DateHired SMALLDATETIME NULL
-- Set SPARSE columns for Middle Name (sql server 2008 only)
ALTER TABLE dbo.Employee
ALTER COLUMN MiddleName VARCHAR(100) SPARSE NULL

Tuesday, May 10, 2011

How to use ASP & MySQL Add/Insert Multiple Rows Record This is tutorial asp developers how to using ASP add insert multiple record to MySQL table.

ShotDev Focus:
- ASP & MySQL add insert multiple record.

Example

see more here

Thursday, May 5, 2011

DECLARE mycur CURSOR FOR select O.type_desc,schema_id,O.name
from
sys
.objects O LEFT OUTER JOIN
sys
.extended_properties E ON O.object_id = E.major_id
WHERE
O
.name IS NOT NULL
AND ISNULL(O.is_ms_shipped, 0) = 0
AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
AND ( O.type_desc = 'SQL_STORED_PROCEDURE' OR O.type_desc = 'SQL_SCALAR_FUNCTION' )
ORDER BY O.type_desc,O.name;

OPEN mycur;

DECLARE @schema_id int;
DECLARE @fname varchar(256);
DECLARE @sname varchar(256);
DECLARE @ftype varchar(256);

FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sname = SCHEMA_NAME( @schema_id );
IF @ftype = 'SQL_STORED_PROCEDURE'
EXEC( 'DROP PROCEDURE "' + @sname + '"."' + @fname + '"' );
IF @ftype = 'SQL_SCALAR_FUNCTION'
EXEC( 'DROP FUNCTION "' + @sname + '"."' + @fname + '"' );

FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;
END

CLOSE mycur
DEALLOCATE mycur

GO

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR

SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)

BEGIN

Exec SP_EXECUTESQL @Sql

FETCH NEXT FROM @Cursor INTO @Sql

END

CLOSE @Cursor DEALLOCATE @Cursor

GO

EXEC sp_MSForEachTable 'DROP TABLE ?'

GO

Monday, May 2, 2011

Get all day in month

--**********************
DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '2008-02-01', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CONVERT(char(8), CalendarDay, 112) FROM Days
--**********************