|
| |
|
|
bowker
Posts: 34
Joined: 6/30/2003
Status: offline
 |
Invoking a Stored Procedure via ASP - 7/7/2003 16:25:38
I have a stored procedure that accepts an email address as a parameter (see below) and then sends an email to the address that was entered in the form. I' d like to invoke this stored procedure from an ASP page where I have a form that has the " email" field filled out. Upon submitting the form, I' d like the stored procedure to run using the contents from the form.
Ultimately I want to have a list of checkboxes (or a drop down with multi-select) in a form that when selected, have the corresponding values for employee email addresses. That way when the form is submitted, it will send the stored procedure all of the parameters (email addresses chosen in the form) and then issue emails to all of the people who were selected. I know I will need to change my stored procedure to accomplish this, but right now I just want to know how I can invoke the procedure via ASP in Frontpage using MS-SQL 2000 as the data store.
********STORED PROCEDURE********
CREATE PROC sp_bowker_email @email VARCHAR (50) AS
DECLARE @_subject varchar (50)
DECLARE @_query varchar (50)
DECLARE @_message varchar (200)
Declare @_mailadd varchar (50)
SET @_subject = ' Your email is ' + @email
SET @_message = ' This is a test for address ' + @email
--Set @_mailadd = ' select emailaddress from atestemail where atestmail.emailcode = aproject.status'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = @_subject,
@message = @_message,
@query = @_query,
@dbuse = ' workrequests' ,
@width = 160,
@attach_results = False
exec master.dbo.xp_stopmail
GO
***************
Thank you in advance.
|
|
|
|
alveyuk
Posts: 80
Joined: 4/4/2002
From: Lincs United Kingdom
Status: offline
|
RE: Invoking a Stored Procedure via ASP - 7/7/2003 19:10:36
I have done a similar thing with an Informix database where I had an insert trigger invoking the stored procedure. The insert trigger was on a table I created to hold the details from the FrontPage form (name and email address etc) and the insert trigger passed these details into the stored procedure.
I have had a look at the MS-SQL help files and it should be possible to do the same type of thing. The most relevant portion of the help file seems to be under keyword of ' Trigger object'
Once the trigger is in place all that is required from the FrontPage is to insert the details collected by the form into this table.
_____________________________
Ken Alvey
| |
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Invoking a Stored Procedure via ASP - 7/7/2003 20:04:12
Or you can use straight asp to execute the sp. Assuming an ado connection con:
con.execute " EXEC sp_browser_email @subject=" & varSubj & " , @query=" & varQuery & " , @message=" & varMsg & " , @mailadd=" & varAddy" , , adExecuteNoRecords
This is assuming your sp variables are in the appropriate asp variables. This is a theoretical example not for cut & paste.
Check your syntax for your sp too, I don' t think it' s correct but I don' t do that many sp' s. I think all the variable declarations need to be above the AS
< Message edited by Doug G -- 7/7/2003 8:05:51 PM >
_____________________________
======
Doug G
======
| |
|
|
|
bowker
Posts: 34
Joined: 6/30/2003
Status: offline
|
RE: Invoking a Stored Procedure via ASP - 7/7/2003 22:56:34
Thanks to everyone' s replies, I believe I am one step closer.....bear with me as I am still a beginner and am not the sharpest tool in the shed either.
Here is what I came up with:
<%
Dim objConn
Set objConn = Server.CreateObject(" ADODB.Connection" )
objConn.Open (" myconnectionstring" )
objConn.Execute " EXEC sp_bowker_email_novar"
objconn.Close
Set objconn = Nothing
%>
This worked perfectly (with " myconnectionstring" replaced with the appropriate info). I even amended the execute statement to include a single parameter and, after changing my SP slightly, was able to send an email address as a parameter and have it send correctly. Aside from the fact that it worked, am I way off base here? Did I break some golden rule? Is there a better way?
Now, I' d like to take a form from one page and have it' s " entries" be the parameters that the SP is looking for (not necessarily an email based SP). I looked at Spooky' s reply, but since I am not using the DRW anywhere on this page I wasn' t sure how to add it and make it all fly or even why.
I am thinking that it would be cool if I could simply make the line
objConn.Execute " EXEC sp_bowker_email_novar"
read
objConn.Execute " EXEC sp_bowker_email_novar <%=formvariable%>"
But I know that won' t work. Do you get what I am after though?
Again, thanks in advance. And thanks to all of you for putting up with us newbies who don' t always get it. I love this site.....
| |
|
|
|
bowker
Posts: 34
Joined: 6/30/2003
Status: offline
|
RE: Invoking a Stored Procedure via ASP - 7/8/2003 1:47:18
Here is the objconn line for the other SP that took a parameter...
objConn.Execute " exec sp_bowker_email ' someone@coolsite.com, someonelse@coolsite.com, spookyrocks@coolsite.com' "
This allowed the SP to send mutliple emails...
Going to go try your syntax..but first I' ve got to finish up CSI! :)
| |
|
|
|
bowker
Posts: 34
Joined: 6/30/2003
Status: offline
|
RE: Invoking a Stored Procedure via ASP - 7/8/2003 3:08:31
That worked beautifully! Thank you! Now, to raise the bar a bit, or at least to clarify it some for me, how would that objconn line look if I had three parameters to send from the form--assuming that the SP was setup appropriately.
Would this work?
objConn.Execute " exec sp_bowker_email ' " & Request.Form(" Email" ) & Request.Form(" vartwo" ) & Request.Form(" varthree" ) &" ' "
Thank you again,
-Voting Spooky for President
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 