Hi!
In our database we have this stored procedure that inserts a new user into the user table:
However, when trying to insert a new user through the aspx-page nothing happens. Maybe, I have missed something in my code? Please see below.
create or replace
PROCEDURE NEW_USER (user_id OUT NUMBER,
p_username IN users.username%TYPE,
p_password IN users.password%TYPE,
p_salutation users.salutation%TYPE,
p_academic_title users.academic_title%TYPE,
p_first_name users.first_name%TYPE,
p_last_name users.last_name%TYPE,
p_dob users.dob%TYPE,
p_street_address users.street_address%TYPE,
p_company_name users.company_name%TYPE,
p_street_address_2 users.street_address_2%TYPE,
p_country users.country%TYPE,
p_postal_code users.postal_code%TYPE,
p_city users.city%TYPE,
p_email_address users.email_address%TYPE,
p_country_code users.country_code%TYPE,
p_prefix users.prefix%TYPE,
p_mobile_phone users.mobile_phone%TYPE,
p_home_phone users.home_phone%TYPE,
p_language users.language%TYPE,
p_newsletter users.newsletter%TYPE,
p_specials users.specials%TYPE,
p_membership users.membership%TYPE,
p_remarks users.remarks%TYPE)
AS
g_insert_err_dups EXCEPTION;
g_insert_err_unknown EXCEPTION;
v_user_id NUMBER;
BEGIN
SELECT new_user_seq.nextval INTO v_user_id FROM dual;
-- Check if the password is same as the username
IF NLS_LOWER(p_password) = NLS_LOWER(p_username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF NVL(LENGTH(p_password), 0) < 8 THEN
raise_application_error(-20002, 'Password length less than 8 characters');
END IF;
IF NVL(LENGTH(REGEXP_REPLACE(p_password, '[^0-9]', NULL)), 0) < 2 THEN
raise_application_error(-20003, 'Password must contain at least two digits');
END IF;
IF NVL(LENGTH(REGEXP_REPLACE(p_password, '[^a-zA-Z]', NULL)), 0) < 2 THEN
raise_application_error(-20003, 'Password must contain at least two letters');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(p_password) IN ('password99aa') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Insert new user
INSERT INTO USERS
(user_id, username, password, salutation, academic_title, first_name,
last_name, dob, street_address, company_name, street_address_2,
country, postal_code, city, email_address, country_code, prefix, mobile_phone,
home_phone, language, newsletter, specials, membership, remarks)
VALUES (new_user_seq.nextval, p_username, p_password, p_salutation, p_academic_title, p_first_name,
p_last_name, p_dob, p_street_address, p_company_name, p_street_address_2,
p_country, p_postal_code, p_city, p_email_address, p_country_code, p_prefix, p_mobile_phone,
p_home_phone, p_language, p_newsletter, p_specials, p_membership, p_remarks);
dbms_output.put_line('User successfully inserted!');
dbms_output.put_line('Your ID number is:' || v_user_id);
-- user_id := v_user_id;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
RAISE g_insert_err_dups;
WHEN OTHERS
THEN
RAISE g_insert_err_unknown;
END NEW_USER;
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="new_user.aspx.cs" Inherits="new_user" %>
<%@ Import Namespace="System.Data.OracleClient" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void button1_click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection("uid=Test;pwd=xxxx");
try
{
con.Open();
OracleCommand spcmd = new OracleCommand("New_User");
spcmd.CommandType = CommandType.StoredProcedure;
spcmd.Connection = con;
// Get nextval
OracleParameter user_id = new OracleParameter();
user_id.Direction = System.Data.ParameterDirection.ReturnValue;
user_id.OracleType = OracleType.Number;
spcmd.Parameters.Add("user_id", OracleType.Number).Value = ParameterDirection.Output;
spcmd.Parameters.Add("p_salutation", OracleType.VarChar).Value = txtSalutation.Text;
spcmd.Parameters.Add("p_academic_title", OracleType.VarChar).Value = txtTitle.Text;
spcmd.Parameters.Add("p_first_name", OracleType.VarChar).Value = txtFirstname.Text;
spcmd.Parameters.Add("p_last_name", OracleType.VarChar).Value = txtLastname.Text;
spcmd.Parameters.Add("p_dob", OracleType.DateTime).Value = txtDOB.Text;
spcmd.Parameters.Add("p_language", OracleType.VarChar).Value = txtLanguage.Text;
spcmd.Parameters.Add("p_country_code", OracleType.VarChar).Value = txtCountrycode.Text;
spcmd.Parameters.Add("p_prefix", OracleType.Number).Value = txtPrefix.Text;
spcmd.Parameters.Add("p_home_phone", OracleType.VarChar).Value = txtPhonenumber.Text;
spcmd.Parameters.Add("p_company_name", OracleType.VarChar).Value = txtCompanyname.Text;
spcmd.Parameters.Add("p_street_address", OracleType.VarChar).Value = txtStreet.Text;
spcmd.Parameters.Add("p_street_address_2", OracleType.VarChar).Value = txtCompanystreet.Text;
spcmd.Parameters.Add("p_postal_code", OracleType.VarChar).Value = txtZip.Text;
spcmd.Parameters.Add("p_city", OracleType.VarChar).Value = txtCity.Text;
spcmd.Parameters.Add("p_country", OracleType.VarChar).Value = txtCountry.Text;
spcmd.Parameters.Add("p_mobile_phone", OracleType.VarChar).Value = txtMobilephone.Text;
spcmd.Parameters.Add("p_email_address", OracleType.VarChar).Value = txtEmailaddress.Text;
spcmd.Parameters.Add("p_username", OracleType.VarChar).Value = txtUsername.Text;
spcmd.Parameters.Add("p_password", OracleType.VarChar).Value = txtPassword.Text;
spcmd.ExecuteNonQuery();
// now try to insert a new record
OracleCommand qcmd = new OracleCommand("select user_id from users;", con);
Object new_user = qcmd.ExecuteScalar();
lblMsg.Text = String.Format("Your ID = {0}", new_user.ToString());
}
catch (Exception ex)
{
lblMsg.Text = "Error --> " + ex.Message;
}
finally
{
con.Close();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 100%;
}
.style2
{
width: 157px;
}
.style3
{
width: 275px;
}
.style4
{
width: 137px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="font-weight: normal; font-family: Arial, Helvetica, sans-serif; font-size: 11px;">
<h4 style="color: #001E75">Become a member</h4>
Fields in bold are manditory<br />
<br />
Personal information<br />
<br /><asp:Label ID="lblMsg" runat="server" Font-Bold="True" BorderColor="Red"
BorderStyle="Solid" BorderWidth="1px" Font-Size="12px" ForeColor="Red"></asp:Label>
<br />
<table cellspacing="2px" class="style1" width="500px">
<tr style="border: 1px solid #C0C0C0">
<td class="style2" style="font-weight: bold">
Salutation</td>
<td rowspan="29" width="2px">
</td>
<td colspan="4">
<asp:TextBox ID="txtSalutation" runat="server" Width="60px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Title</td>
<td colspan="4">
<asp:TextBox ID="txtTitle" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
First name</td>
<td colspan="4">
<asp:TextBox ID="txtFirstname" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Last name</td>
<td colspan="4">
<asp:TextBox ID="txtLastname" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Middle initials</td>
<td colspan="4">
<asp:TextBox ID="TextBox5" runat="server" Width="60px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" rowspan="2" style="font-weight: bold" valign="top">
Date of birth</td>
<td class="style3" rowspan="2" width="120px">
<asp:TextBox ID="txtDOB" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="3" rowspan="2">
(e.g. 15.04.1978)</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Correspondence language</td>
<td colspan="4">
<asp:TextBox ID="txtLanguage" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Country code</td>
<td colspan="3" width="120px">
<asp:TextBox ID="txtCountrycode" runat="server" Width="70px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td class="style4">
(e.g. 001)</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Prefix</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtPrefix" runat="server" Width="70px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
(e.g. 905)</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Phone number</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtPhonenumber" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
(e.g.1234567)</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Address</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Company name</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtCompanyname" runat="server" Width="200px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Street</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtStreet" runat="server" Width="200px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Company street</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtCompanystreet" runat="server" Width="200px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Zip/Postal code</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtZip" runat="server" Width="70px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
City/Town</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtCity" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
State/Province</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="TextBox16" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Country</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtCountry" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Mobile phone number</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtMobilephone" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
E-mail address</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtEmailaddress" runat="server" Width="180px" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
E-newsletter(e-News)</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Specials</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
Yes, I would like to receive information on specials by e-mail</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Membership</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
Yes, I would like to become a member</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Username</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtUsername" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
Password</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="txtPassword" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px" TextMode="Password"></asp:TextBox>
</td>
<td colspan="2">
(max. 16 characters)</td>
<td>
</td>
</tr>
<tr>
<td class="style2" style="font-weight: bold">
</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
Special requests</td>
<td colspan="2" style="width: 0" width="120px">
<asp:TextBox ID="TextBox22" runat="server" BorderColor="#E2E2E2"
BorderStyle="Solid" BorderWidth="1px" Font-Size="10px" Width="275px"></asp:TextBox>
</td>
<td colspan="2">
</td>
<td>
</td>
</tr>
<tr>
<td class="style2">
</td>
<td colspan="2" style="width: 0" width="120px">
</td>
<td colspan="2">
</td>
<td>
<asp:Button ID="button1" runat="server" Text="Confirm"
ViewStateMode="Enabled" />
</td>
</tr>
</table>
<br />
</div>
</form>
</body>
</html>