Creating the User Registration Table in SQL Server

Introduction. Now you need to create the table that you will use for your user registration information. We will make use of what will become our strategy of being able to work with just about any ISP and do the creation from within an ASP page.

Now, rather than creating your own table in Access and uploading it to one of your directories on the web, you will create a table within the course ASP_SQL database.

You need to copy the following code into a file called CreateYourTable.asp.

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' Open a connection to our SQL Server database
' We will use the ADO Driver connection
Dim connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=ASP_SQL;UID=learn; PWD=learnSQL"

connfoxFire.Open

' Creating the SQL String to create the table
strSQLCreate = "CREATE TABLE dbo.yourInitials_UserRegistration " & _
"(RecordNumber int IDENTITY (1, 1) NOT NULL PRIMARY KEY , " & _
"FirstName varchar (50) NULL , " & _
"MiddleName varchar (50) NULL , " & _
"LastName varchar (50) NULL , " & _
"EMail varchar (50) NULL , " & _
"strPassword varchar (50) NULL , " & _
"DateRegistered datetime NULL , " & _
"Age int NULL , " & _
"ECommerce bit NULL , " & _
"SmallBusiness bit NULL , " & _
"WebDevelopment bit NULL , " & _
"Java bit NULL , " & _
"CPP bit NULL , " & _
"VisualBasic bit NULL , " & _
"CreditCard varchar (50) NULL , " & _
"NoCollege bit NULL , " & _
"College bit NULL , " & _
"Masters bit NULL , " & _
"PhD bit NULL , " & _
"AnnualSalary money NULL )"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

Notice how you are connecting to the ASP_SQL database. You should also notice that you don't need the exact path to its location.

You should also notice how the strSQLCreate is actually one long string that has been broken down on separate lines to make the fieldnames stand out a bit more.

The only thing you need to change is the

yourInitials_UserRegistration

in order to create a table for you.

You need to upload this to your cisdev account into a directory that contains the adovbs.inc. Then go to this CreateYourTable.asp page in your browser. This will execute the code and create the table. If you try to execute it again you will get an error informing you the table already exists.

Now you should probably remove this file from your directory on the server, though leave it on your client for future reference.

The following table gives a little bit of background on the actual table data definition. Null implies it can be omitted.

Field Name	Data Type	Null/Not Null	More Description
RecordNumber	int	not null	IDENTITY(1,1) makes it autonumber
FirstName	varchar (50)	null	varchar (50) adjusts size based on contents up to 50 characters in length
MiddleName	varchar (50)	null
LastName	varchar (50)	null
EMail	varchar (50)	null
strPassword	varchar (50)	null
DateRegistered	datetime	null
Age	int	null
ECommerce	bit	null	bit implies a Yes/No or True/False sort of entry
SmallBusiness	bit	null
WebDevelopment	bit	null
Java	bit	null
CPP	bit	null
VisualBasic	bit	null
CreditCard	varchar (50)	null
NoCollege	bit	null
College	bit	null
Masters	bit	null
PhD	bit	null
AnnualSalary	money	null

The last line of code

"CONSTRAINT PK___10__12 PRIMARY KEY CLUSTERED (RecordNumber))"

creates a primary key or constraint that is a primary key on the RecordNumber field. The clustered refers to how the key works.