Insert Stored Procedure does not Create Database Record

Question

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">
   <InsertParameters>
     <asp:ControlParameter ControlID="txtLoginName" Type="String" />
     <asp:ControlParameter ControlID="txtPassword" Type="String" />
     <asp:ControlParameter ControlID="txtEmail" Type="String" />
     <asp:ControlParameter ControlID="txtCompany" Type="String" />
     <asp:ControlParameter ControlID="txtFirstName" Type="String" />
     <asp:ControlParameter ControlID="txtLastName" Type="String" />
     <asp:ControlParameter ControlID="txtAddress" Type="String" />
     <asp:ControlParameter ControlID="txtCity" Type="String" />
     <asp:ControlParameter ControlID="ddlState" type="String" />
     <asp:ControlParameter ControlID="ddlcountryCode" Type="String" />
     <asp:ControlParameter ControlID="txtPostalCode" Type="String" />
     <asp:ControlParameter ControlID="txtPhoneNumber" Type="String" />
   </InsertParameters>
 </asp:SqlDataSource>

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

Answer 1

You are swallowing all the exceptions thrown by the database in your btnSave_Click event handler. You should either have no Try/Catch block or do something with the exception (like log it) - the code means you loose the exception (as you have already noted).

As for changing your code to get the insert to occur - first figure out what the exception is, that will tell you what the error is and probably inform you (and us, if you post it) of how to fix it.

Answer 2

Questions:

1.How do I catch the error messages that might be returned from the sproc?

the question ,i think you can test the proc in the database. you test it pass,so it's right

2.Please advise how to change signup.aspx so that the insert occurs.

if you insure the proc which you create is right ,so you can search the information how to call the proc from the internet

Answer 3

You're not ever calling any method to actually insert that new row! Calling .DataBind() will only load the existing data in the data source into the "bound" UI elements (like textboxes and grids and stuff).

How are those textboxes etc. on your signup.aspx connected to the datasource??

You need to:

• read out the values the user entered
• actually call the stored procedure in question (directly or via SqlDataSource)
• then maybe re-bind the form to show the new data