ASP and inserting into stored procedures 2

[hpadwal]

hello all,

i have the following stored procedure set up which inserts a form objects into a table

CREATE PROCEDURE SP_EmailSend
@submissionID int,
@message varchar(5000)=null,
@sender varchar(50)=null
AS
BEGIN
INSERT INTO tbl_sendmessage (submissionID, message, sender)values(@submissionID, @message, @sender)
END
GO

am using the following ASP code but without success, im new to stored procedures so bear with me.

<%Dim objConn, SP_EmailSend, submissionid , message, sender
submissionid = "Benny"
message= "Alexander"
sender= "paul"

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
sqlInsName = "SP_EmailSend '" & submissionid & "', '" & message& "'", '" & sender& "'
objConn.Execute(SP_EmailSend) %>

can you help?

 

[Sheco]

Well the first parameter is declared as an integer;
[tt]@submissionID int[/tt]

but then you pass it a string:
[tt]submissionid = "Benny"[/tt]

 

[hpadwal]

sorry, that was just for show, it is an interger

request.form("sub")

sorry

thing your less then the person next to you.

http://www.my-porfolio.me.uk

 

[Sheco]

What error message did you get?

Take it out of quotes since it is an integer
[tt]"SP_EmailSend [red]'[/red]" & submissionid & "[red]'[/red], '" & message& "'", '" & sender& "'[/tt]

 

[DotNetGnat]

you have this line wrong..

objConn.Execute(SP_EmailSend)

it should be

objConn.Execute(sqlInsName)

-DNG

 

[DotNetGnat]

and as Sheco pointed out...you need single quotes only for the string variables and not for integer type variables...

-DNG

 

[onpnt]

crossed a bit there

sqlInsName = "SP_EmailSend '" & submissionid & "', '" & message& "'", '" & sender& "'
objConn.Execute(SP_EmailSend) %>

maybe should be

sqlInsName = "SP_EmailSend '" & submissionid & "', '" & message& "'", '" & sender& "'
objConn.Execute(sqlInsName ) %>

also, use the EXEC to execute the SP in your sql statement.
e.g.

sqlInsName = "EXEC SP_EmailSend '" & submissionid & "', '" & message& "'", '" & sender& "'
objConn.Execute(sqlInsName ) %>

if that doesn't work debug it in query analyzer. also, "without success" doesn't give us much. what exactely is going on?



General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[DotNetGnat]

yes the last option suggested by onpnt should work just fine...

-DNG

 

[Sheco]

This is sort of beside the point, but since your 2nd paramter is declared [tt]@message varchar(5000)=null[/tt] ... it will obviously take a very long string.

Well I was thinking that this is probably one of those times you want to use an ADO Command Object to make use of the Parameters Collection...

For parameters this large it seems cleaner way to do it then concatinate into a huge string to execute via the ADO Connection Object.