ASP.NET Registration Page

Nice this is good stuff Paladine.

Miller

Hi

I have tried to add more fields ie: Address, Phone, Email etc everything compiles ok but when running the app it comes up with the database error (lblError)

I have updated the Stored procedure and the database with the correct fields so i think i know it is not that.

any way here is codebehind:

Imports System.Web.Security ' |||| Required Class for Authentication
Imports System.Data ' |||| DB Accessing Import
Imports System.Data.SqlClient ' |||| SQL Server Import
Imports System.Configuration ' |||| Web.Config appsettings
Public Class register
	Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

	'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

	End Sub
	Protected WithEvents txtUserName As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvUserName As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvPassword As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtFirst As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvFirstName As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtLast As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvLastName As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents cmdSubmit As System.Web.UI.WebControls.Button
	Protected WithEvents lblError As System.Web.UI.WebControls.Label
	Protected WithEvents lblResult As System.Web.UI.WebControls.Label
	Protected WithEvents txtCompany As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvCompany As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvAddress As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvCity As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtPostCode As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvPostCode As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtEmail As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvEmail As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
	Protected WithEvents rvPhone As System.Web.UI.WebControls.RequiredFieldValidator
	Protected WithEvents txtFax As System.Web.UI.WebControls.TextBox

	'NOTE: The following placeholder declaration is required by the Web Form Designer.
	'Do not delete or move it.
	Private designerPlaceholderDeclaration As System.Object

	Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
		'CODEGEN: This method call is required by the Web Form Designer
		'Do not modify it using the code editor.
		InitializeComponent()
	End Sub

#End Region

	Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		' |||| Put user code to initialize the page here
	End Sub

	Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
		If Page.IsValid Then ' |||| Meaning the Control Validation was successful!
			' |||| All Fileds have been filled in!
		    If ValidateNewUser(txtUserName.Text.Trim(), txtFirst.Text.Trim(), txtLast.Text.Trim()) Then
			    AddNewUser(txtUserName.Text.Trim(), txtFirst.Text.Trim(), txtLast.Text.Trim(), txtPassword.Text.Trim())
				Response.Redirect("login2.aspx")
			End If
		End If
	End Sub

	Function ValidateNewUser(ByVal strAlias As String, ByVal strFirst As String, ByVal strLast As String) As Boolean
		' |||| This function simply varifies that there is no existing match on
		' |||| username (alias), and that the user has not already registered!
		' |||| |||| |||| |||| |||| |||| |||| |||| |||| ||||
		' |||| Set up a connection object to the SQL DB
		Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
		' |||| Pass in the StoredProcedure or Command String, as well as the Connection object
		Dim MyCmd As New SqlCommand("sp_CheckForDuplicates", MyConn)
		' |||| Set the command type (stored procedure, text, etc)
		MyCmd.CommandType = CommandType.StoredProcedure
		' |||| Create Parameter Objects for values passed in
		Dim objParam1, objParam2, objParam3, objParam5 As SqlParameter
		' |||| Create a parameter to store your Return value from the stored procedure
		Dim objReturnParam As SqlParameter
		' |||| Add your parameters to the parameters Collection
		objParam1 = MyCmd.Parameters.Add("@UserName", SqlDbType.VarChar)
		objParam2 = MyCmd.Parameters.Add("@FirstName", SqlDbType.VarChar)
		objParam3 = MyCmd.Parameters.Add("@LastName", SqlDbType.VarChar)
		objReturnParam = MyCmd.Parameters.Add("@Duplicates", SqlDbType.Int)
		objReturnParam.Direction = ParameterDirection.ReturnValue
		' |||| Set the Parameter values to the passed in values
		objParam1.Value = strAlias
		objParam2.Value = strFirst
		objParam3.Value = strLast


		Try
		    ' |||| Check if Connection to the DB is already open , if not, then open a connection
		    If MyConn.State = ConnectionState.Closed Then
			    ' |||| DB not already open... so open it
				MyConn.Open()
				MyCmd.ExecuteNonQuery()
			End If
		    ' |||| Was the return value greater that 0?
		    If objReturnParam.Value > 0 Then
			    lblResult.Text = "UserName already exists or you are already a registered user!"
				Return False
			Else
				Return True
			End If
		    ' |||| Close the connection Closes with it
			MyConn.Close()

		Catch ex As Exception
			lblError.Text = "Error Connecting to DataBase!"
		End Try

	End Function

	Sub AddNewUser(ByVal strUser As String, ByVal strFirst As String, ByVal strLast As String, ByVal strPass As String)
		' |||| Set up new Connection To the SQL DB
		Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
		' |||| Pass in the StoredProcedure or Command String, as well as the Connection object
		Dim MyCmd As New SqlCommand("sp_RegisterNewUser", MyConn)
		' |||| Set the command type (Stored Procedure, Test, etc)
		MyCmd.CommandType = CommandType.StoredProcedure
		' |||| Create Parameter objects for values passed in
		Dim objParam1, objParam2, objPAram3, objParam4, objParam5, objParam6, objParam7, objParam8, objParam9, objParam10, objParam11 As SqlParameter
		' |||| Creat an Parameter to store your return Value from the Stored Procedure
		Dim objReturnParam As SqlParameter
		' |||| Add your parameters to the parameters Collection
		objParam1 = MyCmd.Parameters.Add("@UserName", SqlDbType.VarChar)
		objParam2 = MyCmd.Parameters.Add("@FirstName", SqlDbType.VarChar)
		objPAram3 = MyCmd.Parameters.Add("@LastName", SqlDbType.VarChar)
		objParam4 = MyCmd.Parameters.Add("@Password", SqlDbType.VarChar)
		objParam5 = MyCmd.Parameters.Add("@Company", SqlDbType.VarChar)
		objParam6 = MyCmd.Parameters.Add("@Address", SqlDbType.VarChar)
		objParam7 = MyCmd.Parameters.Add("@City", SqlDbType.VarChar)
		objParam8 = MyCmd.Parameters.Add("@PostCode", SqlDbType.VarChar)
		objParam9 = MyCmd.Parameters.Add("@Email", SqlDbType.VarChar)
		objPAram10 = MyCmd.Parameters.Add("@Phone", SqlDbType.VarChar)
		objParam11 = MyCmd.Parameters.Add("@Fax", SqlDbType.VarChar)
		' |||| Set the Parameter values to the passed in values
		objParam1.Value = strUser
		objParam2.Value = strFirst
		objPAram3.Value = strLast
		objParam4.Value = strPass

		Try
		    ' |||| Check if connection is already open
			MyConn.Open()
			MyCmd.ExecuteNonQuery()
			MyConn.Close()
		Catch ex As Exception
			lblError.Text = "Error Connecting to DataBase!"
		End Try
	End Sub
End Class

OK, couple of things.

1. Please use code blocks "[code"]["/code]" for displaying your code.
2. Please provide the error message you are getting
3. You have the "strConn" variable listed in the web.config file correct?
4. Are you able to login in?
5. Please provide the code for the stored procedure.
6. Looking are your code, if you have added the extra variables to stored procedure, then why are you not passing them in to it? You show that you are adding 11 parameters to the collection, but onlying passing in 4 ? Although that is unlikely to cause an error unless they are "required fields".

:cool:

OK, couple of things.

1. Please use code blocks "[code"]["/code]" for displaying your code.
2. Please provide the error message you are getting
3. You have the "strConn" variable listed in the web.config file correct?
4. Are you able to login in?
5. Please provide the code for the stored procedure.
6. Looking are your code, if you have added the extra variables to stored procedure, then why are you not passing them in to it? You show that you are adding 11 parameters to the collection, but onlying passing in 4 ? Although that is unlikely to cause an error unless they are "required fields".

:cool:

Hi Thanks for getting back to me.

Ok answer to question 2 is "Error Connecting to DataBase!" and also asks for a *Required field at the bottom as well, this was supplied!
i am not getting a specific .net output error just a page error lblError.text message response.

Q3, strConn is listed in the web.config file.

Q4, I am able to log in ok

Q5, here is the stored procedure:

Q6, I see what you are saying and tried to add them but VS said that it wasn't declared so i tried to add it, which would of been on lines 76, 83 and 90 for example but it still wanted to be declared!

do i need to add the fields into this section ? ( as in txtAddress.text.trim()... ) etc etc.

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        If Page.IsValid Then        '   ||||    Meaning the Control Validation was successful!
            '   ||||    All Fileds have been filled in!
            If ValidateNewUser(txtUserName.Text.Trim(), txtFirst.Text.Trim(), txtLast.Text.Trim()) Then
                AddNewUser(txtUserName.Text.Trim(), txtFirst.Text.Trim(), txtLast.Text.Trim(), txtPassword.Text.Trim())
                Response.Redirect("login2.aspx")
            End If
        End If
    End Sub

ALTER PROCEDURE dbo.sp_RegisterNewUser

	(
		@UserName VARCHAR(50) = NULL,
		@Password VARCHAR(50) = NULL,
		@FirstName VARCHAR(50) = NULL,
		@LastName VARCHAR(50) = NULL,
		@Company VARCHAR(50) = NULL,
		@Address VARCHAR(100) = NULL,
		@City VARCHAR(50) = NULL,
		@PostCode VARCHAR(10) = NULL,
		@Email VARCHAR(50) = NULL,
		@Phone VARCHAR(50) = NULL,
		@Fax VARCHAR(50) = NULL		
	)

AS
IF @UserName IS NULL OR
   @Password IS NULL OR
   @FirstName IS NULL OR
   @LastName IS NULL OR
   @Company IS NULL OR
   @Address IS NULL OR
   @City IS NULL OR
   @PostCode IS NULL OR
   @Email IS NULL OR
   @Phone IS NULL OR
   @Fax  IS NULL
		RAISERROR('Please fill in all fields', 16, 1)
ELSE
BEGIN
	INSERT INTO tblUsers
	(UserName, Password, FirstName, LastName, Company, Address, City, PostCode, Email, Phone, Fax)
	VALUES (@UserName, @Password, @FirstName, @LastName, @Company, @Address, @City, @PostCode, @Email, @Phone, @Fax)
	IF @@error <> 0
			RAISERROR('User was not added', 16, 1)
	ELSE
	BEGIN
			DECLARE @ID int
			SELECT @ID = @@IDENTITY
			SELECT Count(*)
			FROM tblUsers
			WHERE CmrID = @ID
	END
END
RETURN

Ok.

Thanks that helps clear things up.

With regards to the additional fields... of course they are not declared. Look the the AddUser Parameters, you are only declaring three things, but requesting more. Understanding the code you are copying from is important. The number of parameters in your subroutine or function must be equal to the number of parameters/values you pass into it. (some exceptions, but that is more detail that won't help).

So if you want to add strEmail etc, you have to add it to the parameters of the your subroutine. And you have to pass it in. Logic!! Make sense?

As for your error, I would have to go line by line (so put in break points) and see where it fails, and what values it is passing around. Looking at the code, it looks sound to me.

Hope this helps.

Just one quick one on stored procedure names... dont prefix a stored procedure with sp_ use anything but this as when you call a stored procedure SQL Server looks at the name and if it starts with sp_ it will go to the master database first, do a search for the stored proc and only when it fails will it then look in your database. It can be a big performance hit.

I was wondering how this would convert over for lets say an access database?

Ive tried to convert it over, but its a no go.

i want the coding in c#.net could u post a thread

I also want it in the C# code (aspx) page & coressponding (aspx.cs)

Normally when executing the procedure shouldn't it work?

exec sp_RegisterNewUser Test mypass Brian Levis

Here is the result: Incorrect syntax near 'mypass'.

What i'm doing wrong? (i'm using the procedure you made for this tutorial)

AMACHIDE. We welcome you and your post. Firstly, I would like to suggest you that do not resurrect old/solved threads. Please, read daniweb member rules.

Thread Closed.