Insert Stored Procedure does not Create Database Record
- by SidC
Hello All,
I have the following stored procedure:
ALTER PROCEDURE Pro_members_Insert
@id int outPut,
@LoginName nvarchar(50),
@Password nvarchar(15),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@signupDate smalldatetime,
@Company nvarchar(100),
@Phone nvarchar(50),
@Email nvarchar(150),
@Address nvarchar(255),
@PostalCode nvarchar(10),
@State_Province nvarchar(100),
@City nvarchar(50),
@countryCode nvarchar(4),
@active bit,
@activationCode nvarchar(50)
AS
declare @usName as varchar(50)
set @usName=''
select @usName=isnull(LoginName,'') from members where LoginName=@LoginName
if @usName <> ''
begin
set @ID=-3
RAISERROR('User Already exist.', 16, 1)
return
end
set @usName=''
select @usName=isnull(email,'') from members where Email=@Email
if @usName <> ''
begin
set @ID=-4
RAISERROR('Email Already exist.', 16, 1)
return
end
declare @MemID as int
select @memID=isnull(max(ID),0)+1 from members
INSERT INTO members (
id,
LoginName,
Password,
FirstName,
LastName,
signupDate,
Company,
Phone,
Email,
Address,
PostalCode,
State_Province,
City,
countryCode,
active,activationCode)
VALUES (
@Memid,
@LoginName,
@Password,
@FirstName,
@LastName,
@signupDate,
@Company,
@Phone,
@Email,
@Address,
@PostalCode,
@State_Province,
@City,
@countryCode,
@active,@activationCode)
if @@error <> 0
set @ID=-1
else
set @id=@memID
Note that I've "inherited" this sproc and the database.
I am trying to insert a new record from my signup.aspx page. My SQLDataSource is as follows:
<asp:SqlDataSource runat="server" ID="dsAddMember"
ConnectionString="rmsdbuser"
InsertCommandType="StoredProcedure" InsertCommand="Pro_members_Insert"
ProviderName="System.Data.SqlClient">
The click handler for btnSave is as follows:
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
Try
dsAddMember.DataBind()
Catch ex As Exception
End Try
End Sub
When I run this page, signup.aspx, provide required fields and click submit, the page simply reloads and the database table does not reflect the newly-inserted record.
Questions:
1. How do I catch the error messages that might be returned from the sproc?
2. Please advise how to change signup.aspx so that the insert occurs.
Thanks,
Sid
