stored procedure to insert new row
Hello all,
I'm new to asp.net and stored procedures and could really use some help and guidance.
Here is what I'm trying to accomplish:
I have a request infromation form with several text boxes, a submit button and a stored procedure. I'm using asp.net and ms SQL.
I want the users to be able to fill out the form and get a thank you message, if they have not already requested information from an earlier date. I thought that I could just check to see if the their supplied email address was already in a row in the database and if it was give the user a message to choose a different email address. If the email was not in the database then give a thank you message.
Can someone please help me get this off the ground?
Here is the stored procedure:
CREATE Procedure sp_requestInfo
@firstName varchar(50),
@middleName varchar(50),
@lastName varchar(50),
@address varchar(50),
@city varchar(50),
@state varchar(50),
@zipCode varchar(50),
@country varchar(50),
@email varchar(50),
@phone varchar(50),
@fax varchar(50),
@id int output
As
set nocount on
if not exists(select id from xxxx.dbo.xxxx where email=@email)
begin
INSERT INTO xxxx(firstName,middleName,lastName,address,city,state,zipCode,country,email,phone,fax)
VALUES (@firstname, @middleName, @lastName, @address, @city,@state, @zipCode, @country, @email, @phone, @fax)
select @id=@@identity
end
else
select @id=-1
return
GO
Here is the buttons click event:
Dim myconnection As SqlConnection
myconnection = New SqlConnection("data source=xxxx; User ID=xxxx; Password=xxxx; Persist Security Info=True;packet size=4096")
myconnection.Open()
Dim MyCommandd As SqlCommand
MyCommandd = New SqlCommand("sprequestInfo", myconnection)
MyCommandd.CommandType = CommandType.StoredProcedure
What I get: Nothing! no error, no new data, nothing. Please help.
Re: stored procedure to insert new row
You need to execute the procedure,
Your procedure is ok, although I would suggest putting in some defaults just in case. You need to Execute the stored procedure, I think it's ExecuteNonQuery() or something to that effect...
Then you can either set a label to say Thank You for registering,
or you can auto send an email using System.Web.Mail...or you can use jscript to display a thank you message box.
Jon