How to call a stored procedure from ASP?

[ReportingAnalyst]

Hi,

I am trying to get a results from a stored procedure instead of a previous SQL statement.

I am getting an error:
Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub

at this line:

.Parameters.Append.CreateParameter("@OwnerPayee",adInteger,adParamInput,,strUI)

My code is:

<%
Const adCmdStoredProc = 4
Const adInteger = 3
Const adVarChar = 200
Const adBSTR = 8
Const adBoolean = 11
Const adDouble = 5
Const adParamReturnValue = 4
Const adParamInput = 1

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=Servername;UID=dbo;PWD=password;DATABASE=da  tabase "

Set rsEmployee = Server.CreateObject("ADODB.Recordset")
Set cmd = Server.CreateObject("ADODB.Command")

strUI = 4655

With cmd
	.ActiveConnection = Conn
	'Specify name of stored procedure you wish to call
	.CommandText = "sp_Org_Structure"
	.CommandType = adCmdStoredProc
	'Values needed ("@parameter_name",type_of_field,adParamInput,,value_being_passed)
	.Parameters.Append.CreateParameter("@OwnerPayee",adInteger,adParamInput,,strUI)
End with

set rsEmployee = cmd.Execute

If rsEmployee.EOF and rsEmployee.BOF  Then
	Response.Write "No records found"
else
	response.write "records found"
	while not rsEmployee.EOF
	   	response.write rsEmployee("Emp_Name") & "(" & rsEmployee("EmpID") & ")" & "<br>"
		rsEmployee.MoveNext
	wend
End If


rsEmployee.Close
set rsEmployee = Nothing

cmd.Close
Set cmd = Nothing

Conn.Close
Set Conn = Nothing
   %>

FYI, my stored procedure looks like this:

CREATE PROCEDURE [dbo].[sp_Org_Structure] 
        @OwnerPayee int
as

SELECT DISTINCT AMP_AllMySubordinates_Postings.Owner_Payee,
AMP_AllMySubordinates_Postings.Owner_DateOfPost, 
Employee.FirstName + ' ' + employee.LastName AS Emp_Name, 
Employee.EmpID 
FROM AMP_AllMySubordinates_Postings 
INNER JOIN Employee ON
AMP_AllMySubordinates_Postings.Owner_Payee = Employee.UniqueIdentifier 
WHERE AMP_AllMySubordinates_Postings.Owner_Payee = @OwnerPayee
AND AMP_AllMySubordinates_Postings.Owner_DateOfPost In 
(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPos  t) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = @OwnerPayee);


GO

 

[DotNetGnat]

try this:

.Parameters.Append .CreateParameter("@OwnerPayee",adInteger,adParamInput,,strUI)

notice the space after Append

-DNG

 

[Sheco]

You could just skip using the command object... with only 1 parameter it wouldnt be that hard to just check to make sure it was an int.

IF Not IsNumeric(strUI) THEN
  Response.Write "Yo, Bad Input."
ELSE
  strUI = Round(strUI) 'get rid of decimals, etc.

  SET Conn = Server.CreateObject("ADODB.Connection")
  Conn.open "PROVIDER=SQLOLEDB;DATASOURCE=Servername;UID=dbo;PWD=password;DATABASE=database"

  SET rsEmployee = Conn.Execute("sp_Org_Structure " & strUI) 

  IF rsEmployee.EOF THEN
    Response.Write "No Records."
  ELSE
    Do While NOT rsEmployee.EOF
      response.write rsEmployee("Emp_Name") & "(" & rsEmployee("EmpID") & ")" & "<br>"
      rsEmployee.MoveNext
    Loop
  END IF

  rsEmployee.Close
  set rsEmployee = Nothing
  Conn.Close
  SET Conn = Nothing
END IF