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

thats positivly the longest way i have ever seen to add data to a database....

im impressed

why dont you just have 1 page that houses a form when the user clicks add it runs an add procedure. once it has run it redirects to a succesfull message page.

unless you have thevb doing something else...

Okay, here's how I've implemented a user registration form:

<%@ Language="VBScript" %>
<%reg=Request("reg")%>
<%
if reg <> 0 then
  sub checkuser(value)
  %>
  <html>
  <head>
  <meta name="Microsoft Theme" content="profile 111, default">
</head>
  <body></body>
</html>
    <!-- #INCLUDE FILE="loggedin.asp"-->
  <%
    Response.End
  end if
 else
%>

<html>
<title>Registration Form</title>
<head>
</head>
<body>
<SCRIPT LANGUAGE="Javascript">

var digit="0123456789";

function val()
{
  if(username_v() && password_v())
    if(name_v() && add_v())
      if(telno_v() && email_v())  
          return true;
  return false;
}

function username_v()
{
  if(document.register.nam.value=="")
  {
    alert("*** Error ***\n\nPlease enter username");
    document.register.nam.focus();
    return false;
  }
  return true;
}

function password_v()
{
  if(document.register.pass.value=="")
  {
    alert("*** Error ***\n\nPlease enter password");
    document.register.pass.focus();
    return false;
  }

  if(document.register.repass.value=="")
  {
    alert("*** Error ***\n\nPlease re-type password");
    document.register.repass.focus();
    return false;
  }

  if(document.register.pass.value!=document.register.repass.value)
  {
    alert("*** Error ***\n\nPlease re-type password correctly");
    document.register.pass.select();
    document.register.repass.value="";
    return false;
  }
  return true;
}

function name_v()
{
  if(document.register.name.value=="")
  {
    alert("*** Error ***\n\nPlease enter your name");
    document.register.name.focus();
    return false;
  }
  return true;
}

function add_v()
{
  if(document.register.street.value=="")
  {
    alert("*** Error ***\n\nPlease enter your streetname");
    document.register.street.focus();
    return false;
  }

  if(document.register.city.value=="")
  {
    alert("*** Error ***\n\nPlease enter your city");
    document.register.city.focus();
    return false;
  }

  if(document.register.zip.value=="")
  {
    alert("*** Error ***\n\nPlease enter your postal code");
    document.register.zip.focus();
    return false;
  }
  for(i=0;i<document.register.zip.value.length;i++)
  {
    for(j=0;j<digit.length;j++)
    {
      if(document.register.zip.value.charAt(i)==digit.charAt(j))
        break;
      else
      {
        if(j==digit.length-1)
        {
          alert("*** Error ***\n\nPlease enter only digits(0-9)");
          document.register.zip.select();  
          return false;
        }
      }
    }
  }

  if(document.register.state.value=="")
  {
    alert("*** Error ***\n\nPlease enter your state");
    document.register.state.focus();
    return false;
  }

  if(document.register.country.value=="")
  {
    alert("*** Error ***\n\nPlease enter your country");
    document.register.country.focus();
    return false;
  }
  return true;
}

function telno_v()
{
  if(document.register.telcode.value=="")
  {
    alert("*** Error ***\n\nPlease enter your tel. code");
    document.register.telcode.focus();
    return false;
  }

  if(document.register.telno.value=="")
  {
    alert("*** Error ***\n\nPlease enter your tel. number");
    document.register.telno.focus();
    return false;
  }
  for(i=0;i<document.register.telcode.value.length;i++)
  {
    for(j=0;j<digit.length;j++)
    {
      if(document.register.telcode.value.charAt(i)==digit.charAt(j))
        break;
      else
      {
        if(j==digit.length-1)
        {
          alert("*** Error ***\n\nPlease enter only digits(0-9)");
          document.register.telcode.select();  
          return false;
        }
      }
    }
  }

  for(i=0;i<document.register.telno.value.length;i++)
  {
    for(j=0;j<digit.length;j++)
    {
      if(document.register.telno.value.charAt(i)==digit.charAt(j))
        break;
      else
      {
        if(j==digit.length-1)
        {
          alert("*** Error ***\n\nPlease enter only digits(0-9)");
          document.register.telno.select();  
          return false;
        }
      }
    }
  }
  return true;
}

function email_v()
{
  if(document.register.email.value=="")
  {
    alert("*** Error ***\n\nPlease enter your email address");
    document.register.email.focus();
    return false;
  }

  if(document.register.email.value.indexOf("@")==-1)
  {
    alert("*** Error ***\n\nPlease enter valid email address");
    document.register.email.select();      
    return false;
  }

  if(document.register.ccnumber.value=="")
  {
    alert("*** Error ***\n\nPlease enter your Credit card number");
    document.register.ccnumber.focus();
    return false;
  }

  for(i=0;i<document.register.ccnumber.value.length;i++)
  {
    for(j=0;j<digit.length;j++)
    {
      if(document.register.ccnumber.value.charAt(i)==digit.charAt(j))
        break;
      else
      {
        if(j==digit.length-1)
        {
          alert("*** Error ***\n\nPlease enter only digits(0-9)");
          document.register.ccnumber.select();  
          return false;
        }
      }
    }
  }

  if(document.register.ccname.value=="")
  {
    alert("*** Error ***\n\nPlease enter the credit card name");
    document.register.ccname.focus();
    return false;
  }
  return true;
}
</script>
<b><b><center><h1><b style="COLOR: mediumvioletred">REGISTRATION FORM</b></center></b></b></h1>
<table  width=1000  cellpadding=5 align="left" style="COLOR: white; BACKGROUND-COLOR: white"><TBODY>
<tr>
<td bgcolor=lightsalmon style="BACKGROUND-COLOR: mediumvioletred"><font color="white" face="arial"><b style="BACKGROUND-COLOR: mediumvioletred">Register</b></font></td>
</tr>
<tr>
<td style="BACKGROUND-COLOR: white; TEXT-DECORATION: blink"><table width="100%" id=TABLE1 style="BACKGROUND-COLOR: white" ><TBODY>
<tr>
<td colspan="2" style="COLOR: white; BACKGROUND-COLOR: white" bgcolor=black><font face=cursive size="2" style="COLOR: black; TEXT-DECORATION: blink">If you are a new user,please register by completing the form</font></td>
</tr>
<tr style="BACKGROUND-COLOR: gainsboro"  >
<td colspan="2" style="BACKGROUND-COLOR: violet" ><font face="Arial" size="2" color="red"><p><b>Login Information:</b> </font></P></td>
</tr>

<tr>
<td><font face="Arial"><b>User Name :</b></font></td>
<td><font face="Arial"><input name="nam" width="15" size="20" ></font></td>
</tr>

<tr>
<td><font face="Arial"><b>Password :</b></font></td>
<td><input type="password" name="pass" width="15" size="20" ></td>
</tr>

<tr>
<td><font face="Arial"><b>Re-type Password :</b></font></td>
<td><input type="password" name="repass" width="15" size="20" ></td>
</tr>


<tr>
<td colspan="2" style="BACKGROUND-COLOR: violet"><font face="arial" size="2" color="red">
<p><b>Personnel Information:</b> </font></P></td>
</tr>

<tr>
<td><font face="Arial"><b>Name :</b></font></td>
<td><font face="Arial"><input name="name" width="15" size="20" ></font></td>
</tr>

<tr>
<td><b>Email Address :</b></td>
<td><input name="email" width="15" size="20" ></td>
</tr>

<tr>
<td><b>Address :</b></td>
<td><TEXTAREA name=addr rows=3 cols=16></TEXTAREA></td>
</tr>

<tr>
<td><b>Street :</b></td>
<td><input name="street" width="15" size="20" ></td>
</tr>

<tr>
<td><b>City :</b></td>
<td><input name="city" width="15" size="20" ></td>
</tr>
<tr>
<td><b>State :</b></td>
<td><input name="state" width="15" size="20" ></td>
</tr>

<tr>
<td><b>Country :</b></td>
<td><input name="country" width="15" size="20" ></td>
</tr>

<tr>
<td><b>Zip Code :</b></td>
<td><input name="zip" width="15" size="20" ></td>
</tr>

<tr>
<td><b>Tel. no.</b></td>
<td><input type="text" size="4" name="telcode" > - <input type="text" size="10" name="telno" ></td>
</tr>

<tr style="BACKGROUND-COLOR: violet">
                    <td style="BACKGROUND-COLOR: violet" colspan =2><font face="Arial" size="2" color="red">
                        <b style="BACKGROUND-COLOR: violet">Payment Information:</b> </font></td>
                </tr>
                <tr>
                    <td><b>credit card number:</b> </td>
                    <td><input name="ccnumber" maxlength=20 size="20" > </td>
                </tr>    

<tr>
                    <td><b>name on credit card:</b> </td>
                    <td><input name="ccname" maxlength=20 size="20" > </td>
                </tr>
<tr>
                    <td  align="middle" ><input type="submit" value="Submit" onClick="return val();" name=submit1> </font></TD>
                        <td><input type="reset" value="Reset" id=reset1 name=reset1></td>
                        </TR></font></font></TD></TR></TBODY></TABLE></FORM><PRE></PRE></TD></TR></TBODY></TABLE>

</pre></body>
</html>
<% end if
%>

This works perfectly. See if this helps you...

Edited 3 Years Ago by pyTony: fixed formating

This article has been dead for over six months. Start a new discussion instead.