ASP.NET and Stored Procs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

ASP.NET and Stored Procs

Author

Topic

molebrain
Starting Member

18 Posts

Posted - 2004-01-01 : 23:36:17

Ok, I am creating a web signup form in ASP.NET(VB) and I have a table that has username,passsword, email. Once the form is submitted, it executes a stored procedure that performs an insert and then sends an email to the user. But this is what I want to do...I want to check to see if the user exists and if it does, then return a value back to the webpage that says "user exists". If the user doesnt exist, it performs the insert in the proc and then returns a message that says "You are entered" Any idea how I can do this? here is how I call my proc in the asp code:

mailSQL="execute sendemail_parm """ & email.Text & """,""" & message & """,""" & username.Text & """,""" & email.Text & """,""" & pwd & """"
objCommand = New SqlCommand(mailSQL, objConnection)

and here is a snippet of my proc:

CREATE PROCEDURE DBO.sendemail_parm
(
@RecipientAddress varchar(255),
@Body varchar(8000),
@username varchar(255),
@email varchar(255),
@password varchar(255)

)
AS

DECLARE @SenderAddress varchar(100)
DECLARE @Subject varchar(200)
DECLARE @oMail int --Object reference
DECLARE @resultcode int

BEGIN EMAIL SEND CODE:
...
END

insert into tblUsers(username,email,password) values ('' + @username + '','' + @email +'','' + @password + '')

GO

ANy ideas on how to incorporate all of this?
Thanks!
-Tony

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-02 : 10:15:25

-- Here's an outline of whatcha-do, but there's lotsa room for variations....
Sending email from SQL is generally higher overhead. Better to send from .NET (Dave Wanta makes a slick .NET email package). If you need to send the email in SQL, then better to queue the email request in a table and let a DTS VBscript send the email (search SQLTEAM on keyword "email" for an article about this).

The result (found/notfound) can be a return value, bit output parameter or output string. I'll set all 3:

CREATE PROCEDURE DBO.sendemail_parm
(
@RecipientAddress varchar(255),
@Body varchar(8000),
@username varchar(255),
@email varchar(255),
@password varchar(255),
@OutputMessage VARCHAR(255) OUTPUT,
@boolFound BIT OUTPUT

)
AS

-- Let's see if the user's already here

IF EXISTS(SELECT 1 FROM dbo.tblUsers WHERE Username=@Username AND Password=@Password) BEGIN -- User Found
  -- Statements for found user
  SET @boolFound = 1
  SET @OutputMessage = 'User Exists'

END ELSE BEGIN -- User not found, INSERT User
  -- Statements for not-found user
  Set @boolFound = 0
  SET @OutputMessage = 'You are entered'
  INSERT INTO dbo.tblUsers (Username, Password, Email)
    SELECT @Username, @Password, @Email
END

RETURN (CAST(@boolFound AS INT)) -- Returns 0 for not found, 1 for found
GO

molebrain
Starting Member

18 Posts

Posted - 2004-01-02 : 10:45:08

OH! cool. thanks for the sql mail info and for the quick response
to my question. Now, this is what my problem is. How can I grab that boolean value in my asp page? This is what I have now:

objCommand = New SqlCommand(strSQL, objConnection)
message = "Hey There! <br /> You stink! Here is your password:" & pwd & "<br />Log in here jerk. <br /><br />-Tony"
mailSQL="execute sendemail_parm """ & email.Text & """,""" & message & """,""" & username.Text & """,""" & email.Text & """,""" & pwd & """"
objCommand = New SqlCommand(mailSQL, objConnection)
objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objCommand.Connection.Close()
'Send the Password to the user
' Display Confirmation Message:
'lblSQL.Text = strSQL
pnlConfirm.Visible = True

Where in this great vastness of .NET can I pull in that value? Im used to doing stuff like valu=RS(0) etc... im in a fog now.

Sorry for sounding stupid

molebrain
Starting Member

18 Posts

Posted - 2004-01-02 : 11:29:27

On another note, I try this:

execute sendemail_parm "recip@email.com","body","username","email","password"

and i get this:

Server: Msg 201, Level 16, State 3, Procedure sendemail_parm, Line 0
Procedure 'sendemail_parm' expects parameter '@OutputMessage', which was not supplied.

Sinced @outputMessasge is defined as OUTPUT, why is it yelling at me?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-02 : 12:30:21

You need to send it a variable to RECEIVE the OUTPUT value...

have a look at the samples in BOl re calling a SP with an OUTPUT variable....

or do a search here for similar.

Granick
Starting Member

46 Posts

Posted - 2004-01-02 : 16:19:18

Your problem with not being able to get to the values is likely due to using the following:

objCommand.ExecuteNonQuery()

This is mainly used when you want a SQL statement to be run and not worry about any results of the statement. You can either return a Dataset of one kind or other, or you can actually add parameters to the command object, setting one of them so that it's direction is output, matching your Stored Proc output parameter, and get it that way. Then you would just have to check the parameter's value after running the SQL statement.

I tend to do the dataset method, as it I find it the easiest. If you search any major search engine, you should be able to find lots of info for output parameters if that is the way you want to go. An example search is:

asp.net parameter direction

This would give several examples from which to work.

Hope that helps.
Shannon

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-01-02 : 16:42:10

If all you want is the output params, a dataset is a bit too "heavy."

Try something like this:


Dim oCmd As New SqlCommand("sendemail_parm ", objConnection)
Dim sOutputMessage as String 
Dim bFound as boolean

        oCmd.CommandType = CommandType.StoredProcedure

@RecipientAddress varchar(255),
@Body varchar(8000),
@username varchar(255),
@email varchar(255),
@password varchar(255),
@OutputMessage VARCHAR(255) OUTPUT,
@boolFound BIT OUTPUT

        '@RecipientAddress
oCmd.Parameters.Add("@RecipientAddress", SqlDbType.VarChar, 255).Value = sParamName

'  Add the rest of your VARCHAR params here  I left them out to make this shorter  :)

'Here are the output params

        '@OutputMessage 
        oCmd.Parameters.Add("@OutputMessage ", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output

 '@boolFound 
        oCmd.Parameters.Add("@boolFound ", SqlDbType.Bit).Direction = ParameterDirection.Output


        Try
            oCmd.Connection.Open()  'Depending on the state of your connection you may or may not need to open it.  You prob need to modify this line
            oCmd.ExecuteNonQuery()  

            sOutputMessage  = oCmd.Parameters("@OutputMessage").Value 
            bFound = oCmd.Parameters("@boolFound").Value 

        Catch e As Exception
            Throw e
        Finally
            If oCmd.Connection.State = ConnectionState.Open Then
                oCmd.Connection.Close()
            End If

        End Try

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources