Creating DB Tables in ASP

[Harag]

Hi all

I'm fairly new to asp & sql.

using win 2k pro, IIS5, VBscript asp, & SQL SERVER 2k

I want to know how to create a table in my DB in ASP, the following
script (below) is what SQLserver2k Generated for me but it seems
long. Is it possible to put it into 1 create table statement, rather
than the 3 it has ? any good urls where I can get more info about
creating DB tables via vbscript?

thanks
Al

'************************************************* ****
CREATE TABLE [dbo].[TEST] (
[MemberID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Pass] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [tinyint] NOT NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ParentID] [int] NOT NULL ,
[DateEntry] [smalldatetime] NOT NULL ,
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[MemberID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
CONSTRAINT [DF_TEST_DateEntry] DEFAULT (getdate()) FOR
[DateEntry],
CONSTRAINT [DF_TEST_DOB] DEFAULT (getdate()) FOR [DOB],
CONSTRAINT [IX_TEST] UNIQUE NONCLUSTERED
(
[UserName]
) ON [PRIMARY]
GO

[Bob Barrows]

Put it into a stored procedure and execute the stored procedure from ASP.
Harag wrote:

> Hi all
>
> I'm fairly new to asp & sql.
>
> using win 2k pro, IIS5, VBscript asp, & SQL SERVER 2k
>
> I want to know how to create a table in my DB in ASP, the following
> script (below) is what SQLserver2k Generated for me but it seems
> long. Is it possible to put it into 1 create table statement, rather
> than the 3 it has ? any good urls where I can get more info about
> creating DB tables via vbscript?
>
> thanks
> Al
>
> '************************************************* ****
> CREATE TABLE [dbo].[TEST] (
> [MemberID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
> ,
> [UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Pass] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Age] [tinyint] NOT NULL ,
> [Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ParentID] [int] NOT NULL ,
> [DateEntry] [smalldatetime] NOT NULL ,
> [DOB] [smalldatetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
> CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
> (
> [MemberID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
> CONSTRAINT [DF_TEST_DateEntry] DEFAULT (getdate()) FOR
> [DateEntry],
> CONSTRAINT [DF_TEST_DOB] DEFAULT (getdate()) FOR [DOB],
> CONSTRAINT [IX_TEST] UNIQUE NONCLUSTERED
> (
> [UserName]
> ) ON [PRIMARY]
> GO

[Harag]

k Thanks - why didn't I think of that :p

I've just noticed I got 2 foreign keys (relationships) linking TEST
table to test_2 & test_3 tables

Name:
FK_TEST_2_TEST

Primary: TEST
Foreign: TEST_2

On "MemberID" with both tables
with Enforce relationship for replication
& Enforce relationship for Inserts & Updates
with Cascade delete related records.


In this example I got TEST_2 holding a Homepage Url as not all members
have a homepage I've put the url into a seperate table


How would I create the above key when I create table TEST & TEST_2 via
the Stored Proc?

I do have this extra bit at the top now:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_TEST_2_TEST]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TEST_2] DROP CONSTRAINT FK_TEST_2_TEST
GO


Thanks for the help.
Al

On Tue, 12 Aug 2003 11:34:31 -0400, "Bob Barrows"
<reb_01501@yahoo.com> wrote:

>Put it into a stored procedure and execute the stored procedure from ASP.
>Harag wrote:

>> Hi all
>>
>> I'm fairly new to asp & sql.
>>
>> using win 2k pro, IIS5, VBscript asp, & SQL SERVER 2k
>>
>> I want to know how to create a table in my DB in ASP, the following
>> script (below) is what SQLserver2k Generated for me but it seems
>> long. Is it possible to put it into 1 create table statement, rather
>> than the 3 it has ? any good urls where I can get more info about
>> creating DB tables via vbscript?
>>
>> thanks
>> Al
>>
>> '************************************************* ****
>> CREATE TABLE [dbo].[TEST] (
>> [MemberID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
>> ,
>> [UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
>> NOT NULL ,
>> [Pass] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>> NULL ,
>> [Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Age] [tinyint] NOT NULL ,
>> [Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>> NULL ,
>> [ParentID] [int] NOT NULL ,
>> [DateEntry] [smalldatetime] NOT NULL ,
>> [DOB] [smalldatetime] NOT NULL
>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
>> CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
>> (
>> [MemberID]
>> ) ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[TEST] WITH NOCHECK ADD
>> CONSTRAINT [DF_TEST_DateEntry] DEFAULT (getdate()) FOR
>> [DateEntry],
>> CONSTRAINT [DF_TEST_DOB] DEFAULT (getdate()) FOR [DOB],
>> CONSTRAINT [IX_TEST] UNIQUE NONCLUSTERED
>> (
>> [UserName]
>> ) ON [PRIMARY]
>> GO

>