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 %>
<!--#include File=""-->

' 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};;" & _
"Database=ASP_SQL;UID=learn; PWD=learnSQL"


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


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


in order to create a table for you.

You need to upload this to your cisdev account into a directory that contains the  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


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