Nowadays, nearly every web site provides a facility for its users to register on the site. Sometimes, it may be compulsory to register on a site to avail certain services. For instance, imagine you are developing an e-commerce shopping store where your users should create a user id and password to login and begin shopping on that site.
Not only registration but also an effective authentication system should have to be developed so that users are properly and securely verified before doing any business with your site. Apart from the above features you should also provide updating, searching and deleting capabilities for your users. In a series of articles, you will learn how to perform these tasks using ASP.NET with Visual Basic .NET as the programming language and SQL Server as backend database.
If you have experience with classic ASP then you are right at home. However, there are some unique features in ASP.NET as compared to Classic ASP. Firstly, the .NET Framework provides a namespace exclusively devoted to SQL Server (System.Data.SQLClient) and functionalities like DataGrid, Templates etc. You can easily populate the relevant data in a DataGrid, which will show the information in a table like fashion. You can also modify it according to your specific needs.
In this article, you will learn how to add records to a database. I assume that you have installed the .NET Framework SDK Final Release under Windows 2000 Professional / Windows XP Professional environment. I recommend Visual Studio .NET or ASP.NET WebMatrix for coding purposes.
The WebMatrix is easy to download and install and is suitable for learning purposes; however, it doesn’t supports intellisense. Visual Studio .NET does provide intellisense, which provides advanced sensing capabilities by detecting each and every variable, methods etc. Intellisense automatically shows the syntaxes of the methods in various versions. You can also use plain Notepad, if you are prepared to enter all code from scratch.
Wintergreen is a consulting firm providing custom web development solutions. The management decides to develop a web-based application for adding employee’s information to its database. For this purpose, they have decided to create a simple user-interface as shown in Figure 1.1. As part of this project, they also require creation of a database, table and a stored procedure.
Creation of a database
create database Wintergreen
Creation of table
Listing 1.2create table Employees(id int not null identity,name varchar(50), age int,email varchar(50), address varchar(100),username varchar(50),password varchar(50))
Creation of Stored Procedure
Listing 1.3create procedure Toregister (@name varchar(50),@age int,@email varchar(50),@address varchar(100),@username varchar(50), @password varchar(50) ) asif exists-- You cannot register usernames already registered on the database twice.(select username from Employees where username = @username)return 1 elseinsert Employees(name,age,email,address,username,password) values(@name,@age,@email,@address,@username,@password)
The above interface is fully made up of ASP.NET WebForm controls. For example, the code snippet in Listing 1.4 places TextBox and Label controls on to the WebForm. A complete discussion on WebForm controls is beyond the scope of this article. The code behind our User-Interface (HTML Portion) can be downloaded from the link provided at the end of this article.
Listing 1.4<form runat = "Server"><asp:TextBox id = "txtName"> </asp:TextBox><asp:Label id = "lblStatus"> </asp:Label></form>
Simply creating a user-interface won’t suffice. You should activate them by proper means like if a user clicks the button, the entered values should have to be saved/stored in a database. In order to achieve this, you should establish a connection to the database (See Listing 1.6). But before this you should call appropriate namespaces as shown in the listing given below:
Listing 1.5<%@ Page language = "VB" %> <%@ Import namespace = "System.Data" %><%@ Import namespace = "System.Data.SQLClient" %>
Listing 1.6Sub Page_Load(Src As Object, e As EventArgs)conn = New SqlConnection( _"Data Source=localhost;User ID=sa;password=;Initial Catalog = Wintergreen")conn.open()End Sub
I assume that you are using the default SQL Server user account namely "sa". If you are using some other ID and password, substitute them in listing 1.6.
The entered inputs will be stored in the table named "Employees" after clicking the button captioned "Save". Hence, the remaining code should be entered inside the button event. Firstly, you should call the stored procedure, which we created earlier and set the CommandType property of SQLCommand object to stored procedure as shown below:
Listing 1.7cmdcommand = new sqlcommand("Toregister",conn)cmdcommand.commandtype = commandtype.storedprocedure
Notice that I had created parameterized stored procedure in step 3 above. Hence, you have to refer the parameter name while adding records, using the add method. Moreover, you should also refer the appropriate control name along with it as shown below:
If you have more than one field, simply repeat the above codes by changing the parameter names and the respective control name. The main advantage of using parameterized stored procedure is that you can avoid complex SQL statements in your ASP.NET page and moreover the page also executes faster.
Finally, if the return value from the stored procedure is 0, new user is added to the appropriate table on the database. You can either redirect users to other WebPages or simply display appropriate message in a WebForm control namely Label. Look at the code in the listing below.
If the new user is not added then the appropriate error message should have to be displayed to indicate the status. In our example, if a user feeds in a username, which is already there on the database, the script displays the appropriate message in a Label control. Look at the code snippet shown below:
Listing 1.9if cmdcommand.parameters("ReturnValue").value = 0 thenlblStatus.text = "Registered Successfully"elselblStatus.text = "Error!, Please try again"end if
The complete code for the button event is shown below in listing 1.10.
Listing 1.10cmdcommand = new sqlcommand("Toregister",conn)cmdcommand.commandtype = commandtype.storedprocedureparam = cmdcommand.parameters.add("ReturnValue",sqldbtype.int)param.direction = parameterdirection.returnvaluecmdcommand.parameters.add("@name",txtName.text)cmdcommand.parameters.add("@age",txtAge.text)cmdcommand.parameters.add("@email",txtEmail.text)cmdcommand.parameters.add("@address",txtAddress.text)cmdcommand.parameters.add("@username",txtusername.text)cmdcommand.parameters.add("@password",txtPassword.text)cmdcommand.executenonquery()if cmdcommand.parameters("ReturnValue").value = 0 thenlblStatus.text = "Registered Successfully" elselblStatus.text = "Error!, Please try again"end ifconn.close()