Simple Procedure Calls Fail....

[GDX]

Guys for MONTHS now MONTHS ive been trying to call MSSQL Stored procedures with my Webserver and i cannot accomplish this...I am running Windows 2000 Advance Server SP2....If i place this same ASP Page on my NT4 Server box it works fine any ideas???!?! I am so lost and furious about the while situation....I reinstalled the whole OS, Reapplied MDAC it still fails...Here is a copy of my SP and ASP call anyone see anything wrong??


Stored Procedure
-----------------------------------
ALTER PROCEDURE LISTCONBYLOC @LocId int,@LocationName as varchar(200) output AS

select * from consultants where approved=1 and city=@LocId
SELECT @LocationName = (SELECT CITY FROM CITIES WHERE [ID]=@LocId)

GO

ASP Page Call
------------------------------------
LocId=64
Set oCommand = Server.Createobject("Adodb.Command&quot
oCommand.ActiveConnection=oConn
oCommand.CommandType=adCmdStoredProc
'oCommand.CommandText = "LISTCONBYLOC"
Set param = oCommand.CreateParameter("@LocId", AdInteger, adParamInput, 15, LocId)
oCommand.Parameters.Append(param)
Set param = oCommand.CreateParameter("@LocationName", AdVarchar, adParamOutput, 200, "&quot
oCommand("@LocId&quot = LocId
oCommand("@LocationName&quot = "s"
set rsConsultants = oCommand.Execute()

(Keep in mind i tried diffrent ways of calling this procedure also, I read 3 diffrent ways on the MS website area)


Here is the error i recieve....

Syntax error or access violation

/ConsultantsByLoc.asp, line 19

Line 19 is the .Execute line.

Also when i run this procedure via Query Analyzer i get back 2 rows of data.


Thanking you in advance,
Gordon

PLEASEEEEE HELPPPPP!!!!!! :0)

Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[TootingBoy]

Hi there

I know you've mentioned that you've tried running this in a number of different ways,but here are a couple of comments:

i) I think that the length of @LocID should be defined as 4 rather than 15,i.e. it's a 32 bit integer.

ii) You are creating the @LocationName parameter but do not appear to be adding it to the parameters collection.

iii)At the risk of stating the obvious, Line 13, 'oCommand.CommandText = "LISTCONBYLOC" is commented out


The path to freedom is seldom trodden by the multitude.