Hi,
I want make user sign-up form in my project. for that i was created one table as Customer, and stored procedure as Customer_Signup. refer the code below. am not able insert data in to the table using asp web-form, help me how to load signup data to sqlserver database.
/****************** Object: Table [dbo].[Customer] Script 16-01-2017 **************/
CREATE TABLE [dbo].[Customer]
(
[Cus_SKY] [BIGINT] IDENTITY(1,1) NOT NULL,
[Email] [NVARCHAR] (100) NOT NULL,
[Phone] [BIGINT] NOT NULL,
[Key] [NVARCHAR] (32) NOT NULL,
[DATE_Created] DATETIME NOT NULL,
[Last_Login] DATETIME NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([Cus_SKY] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
)
GO
/**************** OBJECT: PROCEDURE [dbo].[Customer_Signup] Script 16-01-2017 *******************/
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Customer_Signup]
@Email [NVARCHAR] (100),
@Phone [BIGINT],
@Key [NVARCHAR] (32)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT Cus_SKY FROM Customer WHERE Email = @Email)
BEGIN
RETURN -1 --Customer email exists already
END
IF EXISTS(SELECT Cus_SKY FROM Customer WHERE Phone = @Phone)
BEGIN
RETURN -2 -- Customer phone number alredy exists
END
ELSE
BEGIN
INSERT INTO [Customer]
([Email],
[Phone],
[Key],
[DATE_Created])
VALUES (@Email,
@Phone,
@Key,
GETDATE())
--SELECT SCOPE_IDENTITY() -- Identity of Cus_SKY Scope()
END
END
GO
use Holidays
SELECT * FROM Customer
here is the background code to execute the procedure, but it's inserted first time, there after not inserting, not showing error message also.
int userId = 0;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Customer_Signup"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim());
cmd.Parameters.AddWithValue("@Phone", txtPhone.Text.Trim());
cmd.Parameters.AddWithValue("@Key", txtPassword.Text.Trim());
cmd.Connection = con;
con.Open();
userId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
string message = string.Empty;
switch (userId)
{
case -1:
message = "Supplied email address has already been used \\nPlease choose a different email.";
break;
case -2:
message = "Supplied Phone number has already been used.";
break;
default:
message = "Registration successful. Activation email has been sent.";
//SendActivationEmail(userId);
break;
}
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);