I am facing a huge problem. I did a registration page and i want to store the data into SQL Server. I created two stored procedures in SQL and two functions in visual basic to call the stored procedures before inserting the data into the database. My codes are based on http://daniweb.com/tutorials/tutorial23605.html
After the execution of codes, the page redirected to a registration successful page and i have checked my SQL connection which i am sure is not the issue. But, my database table remains empty
First of all, my table is made up of 8 columns, username, password, membername, member date of birth, identification pass, member address, member phone and member email.
Would someone kindly debug the problem and explain what is wrong with my codes, PLEASE :cry:
The two stored procedures i created are as followed:
sp_CheckFor Duplicates
CREATE PROCEDURE sp_CheckForDuplicates
(
@MemberUserName VARCHAR(50) = NULL,
@MemberPassword VARCHAR(50) = NULL,
@MemberName VARCHAR(50) = NULL,
@MemberDOB VARCHAR(50) = NULL,
@NRIC VARCHAR(50) = NULL,
@MemberAddress VARCHAR(50) = NULL,
@MemberEmail VARCHAR(50) = NULL,
@MemberPhone VARCHAR(50)=Null,
@Duplicates INT =0
)
AS
SET @Duplicates =(SELECT COUNT(*) FROM Member1
WHERE MemberUserName = @MemberUserName
OR MemberPassword = @MemberPassword
OR MemberName = @MemberName
OR MemberDOB =@MemberDOB
OR NRIC =@NRIC
OR MemberAddress = @MemberAddress
AND MemberEmail = @MemberEmail)
RETURN @Duplicates
GO
sp_RegisterNewUser
CREATE PROCEDURE sp_RegisterNewUser
(
@MemberUserName VARCHAR(50) = NULL,
@MemberPassword VARCHAR(50) = NULL,
@MemberName VARCHAR(50) = NULL,
@MemberDOB VARCHAR(50) = NULL,
@NRIC VARCHAR(50) = NULL,
@MemberAddress VARCHAR(50) = NULL,
@MemberPhone VARCHAR(50) =NULL,
@MemberEmail VARCHAR(50) = NULL
)
AS
IF @MemberUserName IS NULL OR
@MemberPassword IS NULL OR
@MemberName IS NULL OR
@MemberDOB IS NULL OR
@NRIC IS NULL OR
@MemberAddress IS NULL OR
@MemberPhone IS NULL OR
@MemberEmail IS NULL
RAISERROR('Please fill in all fields', 16, 1)
ELSE
BEGIN
INSERT INTO Member1
(MemberUserName, MemberPassword, MemberName, MemberDOB, NRIC, MemberAddress, MemberPhone, MemberEmail)
VALUES (@MemberUserName, @MemberPassword, @MemberName, @MemberDOB, @NRIC, @MemberAddress,@MemberPhone, @MemberEmail)
IF @@error <> 0
RAISERROR('User was not added', 16, 1)
ELSE
BEGIN
DECLARE @ID int
SELECT @ID = @@IDENTITY
SELECT Count(*)
FROM Member1
WHERE NRIC = @ID
END
END
RETURN
GO
These are the codings in my Visual Basic:
Imports System.Web.Security 'Required Class for Authentication
Imports System
Imports System.Data 'DB Accessing Import
Imports System.Data.DataSet
Imports System.Data.SqlClient 'SQL Server Import
Imports System.Configuration 'Required for Web.Config appSettings
.................
Public Class Registration
Inherits System.Web.UI.Page
Dim strConn As String = "Server=MyServer;Database=Cristwork ;Trusted_Connection=Yes"
Private Sub btn_submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_submit.Click
If Page.IsValid Then ' ||||| Meaning the Control Validation was successful!
' ||||| All fields have been filled in!
If ValidateNewUser(txtbox_user.Text.Trim(), txtbox_confirmpass.Text.Trim(), txtbox_name.Text.Trim(), txtbox_birthdate.Text.Trim(), txtbox_nric.Text.Trim(), txtbox_address.Text.Trim(), txtbox_contact.Text.TrimEnd(), txtbox_email.Text.TrimEnd()) Then
AddNewUser(txtbox_user.Text.Trim(), txtbox_confirmpass.Text.Trim(), txtbox_name.Text.Trim(), txtbox_birthdate.Text.Trim(), txtbox_nric.Text.Trim(), txtbox_address.Text.Trim(), txtbox_contact.Text.TrimEnd(), txtbox_email.Text.TrimEnd())
Response.Redirect("RegistrationSuccess.htm")
End If
End If
End Sub
Function ValidateNewUser(ByVal strUsername As String, ByVal strPassword As String, ByVal strName As String, ByVal strDOB As String, ByVal strNric As String, ByVal strAddress As String, ByVal strPhone As String, ByVal strEmail As String) As Boolean
'<summary>
'This function simply verifies that there is no existing match on
'username (alias), and that the user has not already registered!
' </sumary>
'Set up a Connection Object to the SQL DB
'SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
'Pass in the StoreProcedure or Command String, as well as the Connection object
SqlConnection.Open()
Dim MyCmd As New SqlCommand("sp_CheckForDuplicates", SqlConnection)
'Set the Command Type (Stored Procedure, Text, etc)
MyCmd.CommandType = CommandType.StoredProcedure
'Create Parameter Objects for values passed in
Dim objParam1, objParam2, objParam3, objParam4, objParam5, objParam6, objParam7, objParam8 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(New SqlParameter("@MemberUserName", SqlDbType.VarChar, 254))
'MyCmd.Parameters("@MemberUserName").Direction = ParameterDirection.Input
'MyCmd.Parameters("@MemberUserName").Value = txtbox_user.Text
objReturnParam = MyCmd.Parameters.Add("@Duplicates", SqlDbType.Int)
objReturnParam.Direction = ParameterDirection.ReturnValue
txtbox_user.Text = strUsername
objParam1.Value = strUsername
' ||||| Was the return value greater than 0 ???
If objReturnParam.Value > 0 Then
lblstatus.Text = "UserName already exists or you are already a registered user!"
Return False
Else
Return True
End If
' ||||| Close the Connection Closes with it
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
MyCmd.ExecuteNonQuery()
If SqlConnection.State = ConnectionState.Open Then
' ||||| DB not already Open...so open it
SqlConnection.Close()
End If
Catch ex As Exception
lblError.Text = "Error Connecting to Database!"
End Try
End Function
Function AddNewUser(ByVal strUsername As String, ByVal strPassword As String, ByVal strName As String, ByVal strDOB As String, ByVal strNric As String, ByVal strAddress As String, ByVal strPhone As String, ByVal strEmail As String) As Boolean
' ||||| Set up a Connection Object to the SQL DB
'Dim SqlConnection As New SqlConnection(Convert.ToString(Application("strConn")))
' ||||| Pass in the StoreProcedure or Command String, as well as the Connection object
Dim MyCmd As New SqlCommand("sp_RegisterNewUser", SqlConnection)
' ||||| Set the Command Type (Stored Procedure, Text, etc)
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1 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(New SqlParameter("@MemberUserName", SqlDbType.VarChar, 254))
'MyCmd.Parameters("@MemberUserName").Direction = ParameterDirection.Input
'MyCmd.Parameters("@MemberUserName").Value = txtbox_user.Text
txtbox_user.Text = strUsername
objParam1.Value = strUsername
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
' ||||| DB not already Open...so open it
MyCmd.ExecuteNonQuery()
' ||||| Close the Connection Closes with it
SqlConnection.Close()
Catch ex As Exception
lblError.Text = "Error Connecting to Database!"
End Try
End Function
End Class
