trigger stored procedures from asp

Kornel · June 28th, 2004, 06:28 PM

Hello

I have searched the forums but havent really seen answer to mu question.
I am trying to trigger a stored procedure from an asp site. The stored procedure gets 2 variable (supposedly from the asp site) then it updates a field in a selected table. Could anyone help me out with a link or a sniplet please? thanx

happygv · June 28th, 2004, 08:32 PM

You can define your connection and recordset objects and trigger the procedure as shown below.

Code:

set Conn = Server.CreateObject("ADODB.CONNECTION")
Conn.Open "Your Connection string" 
' For connection strings you can check Connection Strings

strSql = "Execute YourProcedure @StringParam1='" & StringParam1Value & "', @intParam2=" & intParam2Value

Conn.Execute(strSql)

In case you are returning a result from your Procedure, you can use the above red code as

Code:

Set Rs = Conn.Execute(strSql)
strResult=Rs("Result_That_You_Return_From_Proc")
Response.write strResult

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

Kornel · June 29th, 2004, 03:50 PM

Excelent. Thanx!

However...
I cant make it work.
I am using 3 parameters instead of 2. I use "centeralized" (lol) conn string which works fine for the rest of the asp pages.(those pages are successfully accessing/editing a table in the DB), but the stored proc triggering just dont work.
my sql str is something like this:

sql = "Execute docalc @num1='"&number1&"',@num2="&number2&"',@num3='"&nu mber3&"'"

Right after that I have an error report script.

I am getting " Unclosed quotation mark before the character string ',@num3=''."
or num3 is excepted by the procedure but havent ben included,
or just a syntax error.
I know its my bad putting the quotation marks to the wrong place, but so far I couldnt figure it out the correct way.

bmains · June 29th, 2004, 09:12 PM

Hey,

You are missing a single quote after @num2=

That's what's throwing it off.

Brian

happygv · June 29th, 2004, 09:18 PM

If you are passing numeric type(Int, Float, etc...) parameters, then you don't really have to use SINGLE QUOTEs around the variables. That can be used only when you pass on STRING type(VARCHAR, nVARCHAR, etc...) values.

Cheers!

_________________________
- Vijay G

Strive for Perfection

Kornel · June 30th, 2004, 12:58 AM

thanx for helping guys. Finally I got it to work this way:

set rsl = Server.CreateObject("ADODB.Recordset")
sql = "Execute docalc @num1='"& number1 &"', @num2='"& number2 &"', @num3='"& number3 &"'"
set rsl=conn.execute(sql)

muralikeane · July 6th, 2004, 01:47 AM

This is good but u must use Command object for stored proc instead connection object coz command object is the best suit for storeproc

Kornel · July 15th, 2004, 10:47 PM

cool thanx, but what do you mean I should/must use command object? Could you explain it please?