Advisory Services   |   IT Solutions   |   Free Articles   |   Training   |   Resource Center

• Download source Code - 3k

Introduction

Many sites require users to register with the site before granting access to member only areas. The goal is to capture basic user information to generate a useful business email & subscription database. A great example is Code Project's request for a few basic details before granting full access to their articles and member only areas.

Site registration is also a core tenant of Marketing 101: the use of Fear, Greed, & Inclusion -- sometimes referred to as the "Holy Three".

Automating the registration step is an important element to delivering effective customer relationship management and web marketing promotion systems. The automation process using an ASP.NET & SQL Server based web solution is an effective means of achieving this principle.

The code in this section introduce the basics of using ASP.NET & stored procedures to add information to a SQL Server Database. We have stripped away many of the error checks, etc that might be used to build a more robust solution so that we can focus on the insertion step itself.

Solution Overview

The solution requires 3 essential steps:

• Create a logon or user table in SQL Server to store our user's information;
• Create a stored procedure that performs the insertion of the data into the table;
• The server side script that accomplishes the actual registration.

Pre-code Steps

Our first goal is to create a logon or user table in SQL Server to store user information. While the logon and password can be stored directly in the customer table, creating a generic people table to hold this information for customers, employees, suppliers, etc. has its advantages.

In this situation we limited our table to 4 columns to reduce & simplify the example:

CREATE TABLE dbo.User_ID (
	[u_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[u_UserName]  [varchar] (50) NOT NULL ,
	[u_PassWord]  [varchar] (50) NOT NULL ,
	[u_FirstName] [varchar] (50) NOT NULL ,
	[u_LastName]  [varchar] (50) NOT NULL 
) ON [PRIMARY]
GO

In this next step our goal is to create the stored procedure that adds our data to the User_ID table. Using a stored procedure makes the script more reusable as well as improves performance:

CREATE PROCEDURE Register_User
(
  @UserName  Varchar( 50 ),
  @PassWord  Varchar( 50 ),
  @FirstName Varchar( 50 ),
  @LastName  Varchar( 50) 
)
AS

IF EXISTS( SELECT u_ID
  FROM User_ID
  WHERE u_UserName=@UserName )
  RETURN - 1
ELSE
  INSERT User_ID (
    u_UserName,
    u_PassWord,
    u_FirstName,
    u_LastName
    ) VALUES (
    @UserName,
    @PassWord,
    @FirstName,
    @LastName
    )
  RETURN @@IDENTITY
GO

Using the Code

At this point we can focus our attention on the ASP.NET code that adds a user to the database. Comments have been included to explain the coding:

' Add the appropriate Namespaces for our project 

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

    script runat="server">

    Sub Button_Click( s As Object, e As EventArgs )
       Dim MyConn As SqlConnection
       Dim Mycmd As SqlCommand
       Dim intResult As Integer
       Dim parm_Return_Value As SqlParameter
    
       If IsValid Then
    
        ' 1. Create the connection & command objects... then set the paramaters
         MyConn = New SqlConnection( "server=(local);database=Northwind;
		  trusted_connection=true" )
         Mycmd = New SqlCommand( "Register_User", MyConn )
         Mycmd.CommandType = CommandType.StoredProcedure
         parm_Return_Value = Mycmd.Parameters.Add( "RETURN_VALUE", SqlDbType.Int)
         parm_Return_Value.Direction = ParameterDirection.ReturnValue
    
        ' 2. Input from the text box is next linked to the procedure's arguments
         Mycmd.Parameters.Add( "@UserName", txtLogin.Text )
         Mycmd.Parameters.Add( "@PassWord", txtPassword.Text )
         Mycmd.Parameters.Add( "@FirstName", txtFirstName.Text )
         Mycmd.Parameters.Add( "@LastName", txtLastName.Text )
    
        ' 3. Open the connection & execute the procedure
         MyConn.Open()
           Mycmd.ExecuteNonQuery()
           intResult = Mycmd.Parameters( "RETURN_VALUE" ).Value
         MyConn.Close()
    
        ' 4. Check for errors before redirecting to the referral page
         If intResult = - 1 Then
           Msg.Text = "** Please select another user name."
         Else
           Response.Redirect ("Default.aspx")
         End If
       End If
    End Sub

</script>

Step 1
We create a connection & command object that specifies the database that we wish to use as well as the work to be done, i.e., execute a stored procedure.

The name MyConn refers to the connection object and is set to the Northwind database using a trusted connection. In the connection string "server=(local);database=Northwind..." you will need to replace the word local with the name of your server, and Northwind with the name of the database where the table User_ID resides. 

The MyCmd is a command object that allows us to execute the stored procedure Register_User that was created earlier.

Step 2
The web page itself has four text boxes labeled txtLogin, txtPassword, etc.

In this step we tell .NET to use the value entered into the txtLogin as the argument to be passed to @UserName of our stored procedure...

Step 3
We open the connection and execute the stored procedure.

Step 4
We need to check to see if someone has already used a particular logon, and if so, request the user to try a different logon. If the logon has not been used we redirect the user to [whatever page].

To keep the example simple, we chose not to use form authentification to ensure that a visitor logons prior to granting access to the subdirectory.  (See step 4 in "How to use it" for some details on adding forms authentification.)

How to use it

The demo application includes an ASP.NET page that we have pre-tested on our server... however, there are a couple of things that must be done prior to using the code:

1. Execute the table creation script to add the User_ID table to your SQL Server instance. Also, be sure to set any necessary permissions to permit your web visitors to access the table.
2. Execute the Register_User procedure script & set SQL Server permissions to allow users to execute the newly created stored procedure.
3. The Register.ASPX page should be placed in your web directory. We chose to place the page in a subdirectory .../Register.  As noted earlier, be sure to modify the connection string of MyConn to point to the SQL Server and database for your project.
4. If you wish to enforce forms authentication, some additional steps are needed:
• add or modify a web config file to deny unauthorized user access.
• Create  a logon page for your registered users....
• Replace the generic redirect in the code with a forms authentification redirect:

' Replace ---> Response.Redirect ("Default.aspx") 
' With    ---> FormsAuthentication.RedirectFromLoginPage( txtLogin.Text, False )

' Add to web config file"
<configuration>
  <system.web>
    <authorization>
      <allow users="?" />
    </authorization>
  </system.web>
</configuration>

Conclusion

The  application enables a user to add their registration to a SQL Server database. We have tried to balance an overview with the necessary details to build this site.   From experience, we probably were too detailed for some, and not enough for others.