stored procedure to insert new row

[brutis2ka13]

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.

[jhermiz]

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

[brutis2ka13]

Well, almost.

The stored procedure does work and data from the form is populating the database if the email address is not in the database and is not populating the database if the email is already in the database. Oh yeah!

Now the next task. It's like pulling teeth, I know.

I want a lable to inform the user if the request was successful or not. For example, if the row was added then write to the lable "thank you". If the row was not added, then write to the lable "please choose another email address".


This can be done with an if statement?

If id.Value = -1 Then
status.Text = "Thank you, your information request packet will shipped as soon as possible."
Else
status.Text = "The email address you entered already exists, please use another email address."

End If



Something is not right here. If I enter an email that exists, I get the "The email address you entered already exists, please use another email address."

But, if I change the address and the row is infact populated, I still get the same message, "The email address you entered already exists, please use another email address."


Can someone please shed light as to why?

[jhermiz]

Take a step back...
learn how to do it properly...

Your SPROC should return @@Scope_Identity() should the insert succeed. This means it will be non-zero. In fact it will be greater than one.

So you simply need to return an output parameter and then check that parameter.

Here is an example

Function InsertUser() As Boolean
        'function to insert a new user into the database
 
        Dim conMyData As SqlConnection
        Dim cmdSelect As SqlCommand
        Dim reader As SqlDataReader
        Dim parmReturnValue As SqlParameter
        Dim intResult As Integer
        Dim bNew As Boolean
 
        'try and make a connection
        Try
            conMyData = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
            cmdSelect = New SqlCommand("insert_register_user", conMyData)
 
            With cmdSelect
                .CommandType = CommandType.StoredProcedure
                parmReturnValue = .Parameters.Add("RETURN_VALUE", SqlDbType.Int)
                parmReturnValue.Direction = ParameterDirection.ReturnValue
                'add the parameters
                .Parameters.Add("@FirstName", txtFirstName.Text)
                .Parameters.Add("@LastName", txtLastName.Text)
                .Parameters.Add("@Login", LCase(txtUserName.Text))
                .Parameters.Add("@ClientID", ddlClient.SelectedItem.Value)
                .Parameters.Add("@EmailAddress", txtEmail.Text)
                .Parameters.Add("@Password", txtPassword.Text)
                .Parameters.Add("@Title", txtTitle.Text)
                .Parameters.Add("@Telephone", txtTelephone.Text)
                conMyData.Open()
                .ExecuteNonQuery()
                intResult = .Parameters("RETURN_VALUE").Value
            End With
 
            'check if it was a good insert
            If intResult = -1 Then
                bNew = False
            Else
                'do nothing
                bNew = True
            End If
        Catch e As Exception
            Response.Write("An Error Occurred: " & e.ToString())
            'clean up and close resources
        Finally
            cmdSelect = Nothing
            conMyData.Close()
            conMyData = Nothing
        End Try
 
        InsertUser = bNew
    End Function

Then you can do:

If InsertUser() Then
                    'then do the email send
                    SendConfirmation()
                    lblMessage.ForeColor = Color.Green
                    'finally give the user a message
                    lblMessage.Text = "Thank You for registering as an IMS User." & vbCrLf & "Your username to enter the system is: " & LCase(Me.txtUserName.Text) & "." & vbCrLf & "An e-mail message will be sent to you with login details."
                    hlLogin.Text = "Go back to the IMS Login page."
                    hlLogin.NavigateUrl = "login.aspx"
                Else
                    'already a user
                    lblMessage.ForeColor = Color.Red
                    lblMessage.Text = "This user already is registered in the IMS system!"
                End If

Jon

[brutis2ka13]

Thank you, your example helped out alot. Everything works well in IE6.0 but not Mozilla or nutscrape. I want to use the domvalidation controls, but am having a hard time uderstanding how to get them to work. Surely their must be an easy way to validate if a field in empty or an email is correct without going through all of this>

Microsoft!

[jhermiz]

Originally posted by brutis2ka13
Thank you, your example helped out alot. Everything works well in IE6.0 but not Mozilla or nutscrape. I want to use the domvalidation controls, but am having a hard time uderstanding how to get them to work. Surely their must be an easy way to validate if a field in empty or an email is correct without going through all of this>

Microsoft!

Validation controls work in IE 5+

The next release of VS.net the patch will take care of other browsers such as mozillas / netscapes.

You can validate anything, I dont know what you mean by "Going through all this"...

Regular Expression Validatior is what you need for email.

Try this:
\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

As your validation expression for an email.

Jon

[brutis2ka13]

Originally posted by jhermiz
Validation controls work in IE 5+

The next release of VS.net the patch will take care of other browsers such as mozillas / netscapes.

You can validate anything, I dont know what you mean by "Going through all this"...

Regular Expression Validatior is what you need for email.

Try this:
\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

As your validation expression for an email.

Jon

Jon, I was reffering to getting the domvalidation controls to work. I really don't know if I should use the validation controls at all because I would like for validation to work with at least the top five browsers or so, not just IE. What I'm trying to do is simple enough, I think. I just want to make sure a user fills out a form with a few field like name and email that I want to make sure are filled in.

Can this be done with if, then and display to a lable else?

[jhermiz]

Originally posted by brutis2ka13
Jon, I was reffering to getting the domvalidation controls to work. I really don't know if I should use the validation controls at all because I would like for validation to work with at least the top five browsers or so, not just IE. What I'm trying to do is simple enough, I think. I just want to make sure a user fills out a form with a few field like name and email that I want to make sure are filled in.

Can this be done with if, then and display to a lable else?

Yes if you want you can avoid validation controls and you yourself can validate. However, this is a burden to you as a developer since it means you will have to right the code.

If I were you I'd create a validator class, create an object of that class and call functions on what needs to be tested. In many types of tiered applications many developers include or write a dll / class that validates controls before pushing any data to the business tier (which then sends it to the db).

You are using a tiered approach I assume? If so, it is very simple to create a class / dll and reference it in your code. Then before you call the business tier you validate the information. The validation is a simple get / set property which returns whether data exists. The nice thing about .net is the way that objects and the properties of those objects are created, you can also cast and wrap the objects into different types should you need various values.

If you dont want to write a class simply create a function to validate a control on a page, if the condition is not true or the control doesnt meet your needs set IsValid to false. This way when you do a save to the database, do this before the save:

If Page IsValid....then
else...
'dont save
end if


Jon