sproc question -- 1

[link9]

Hello all --

I have a very simple stored procedure in SQL Server 7.0 that looks like:

CREATE PROCEDURE [getClientIDFromName]

@fName	varchar(50), 
@lName	varchar(50),
@id		int  		OUTPUT

AS

SELECT clientID = @id
FROM
	client
WHERE
	fName = @fName
	AND
	lName = @lName

and what I want to do is access my @id from my ASP page -- I've just started meddling in stored procedures using output values to be accessed via ADO -- so I'm hoping the answer to this one is gonna be super easy --

Here's what I'm currently using, but it doesn't work...

dim con, comObj
set con = server.CreateObject("ADODB.Connection")
con.Open("DSN=internal;UID=;PWD=")
set comObj = server.CreateObject("ADODB.Command")
comObj.ActiveConnection = con
comObj.CommandText = "getClientIDFromName"
comObj.CommandType = 4
comObj.Parameters("@fName").Value = nameArray(0)
comObj.Parameters("@lName").Value = nameArray(1)
comObj.Execute

'this line gives me blank output
Response.Write("return value is: " & comObj.Parameters("@id").Value)

'this control statement always results in false
if comObj.Parameters("@id").Value > 0 then
	clientExists = true
else
	clientExists = false
end if

It doesn't raise an error or anything -- just doesn't work when I know that the client exists in the database -- I'm sure I'm just trying to access the output parameter in the wrong way --

If you must, then tell me I have to use a recordset to retrieve the value -- but that was sort of the reason I started doing this -- to avoid having to create an object for something that all I really need is a variable --

Can anyone put me on the straight and narrow?

thx!
Paul Prewett

 

[Cagliostro]

what do you think about following?
set @id = (SELECT clientID FROM
client
WHERE
fName = @fName
AND
lName = @lName) John Fill

ivfmd@mail.md

 

[tlbroadbent]

Your SP is the culprit. It should be...

SELECT @id = clientID
FROM
client
WHERE
fName = @fName
AND
lName = @lName

Thatis, if ClientID is the name of the column in the table. Terry

http://members.home.net/tlbroadbent/index.htm

 

[link9]

beautimus, terry

thx again, my friend.