ADO Stored Procedure Question

For the stored procedure I have below (with part of the VB code) that runs in
Query Analyzer and returns a value, but when I run it from a VB DLL, I do not
get anything back in my output parameter and I do not get an error. Can
someone tell me what I am doing wrong?
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("returnValue", adInteger, adParamReturnValue)
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
rs("site_id"))
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter("PropertyValue", adVarChar, adParamOutput,
25)
cmdImageProperty.Execute
Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
Output) As
SELECT @.propertyValue = property_value
FROM site_properties
WHERE site_id = @.siteID AND property_type_id = 18
If @.propertyValue = ''
SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
Return @.@.Error
Try adding SET NOCOUNT ON to the beginning of your proc. This will suppress
DONE_IN_PROC messages that can cause issues with ADO apps.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
> For the stored procedure I have below (with part of the VB code) that runs
> in
> Query Analyzer and returns a value, but when I run it from a VB DLL, I do
> not
> get anything back in my output parameter and I do not get an error. Can
> someone tell me what I am doing wrong?
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("returnValue", adInteger,
> adParamReturnValue)
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("siteID", adInteger, adParamInput, ,
> rs("site_id"))
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter("PropertyValue", adVarChar,
> adParamOutput,
> 25)
> cmdImageProperty.Execute
> Alter Procedure GetImageProperty (@.siteID Int, @.propertyValue varchar(25)
> Output) As
> SELECT @.propertyValue = property_value
> FROM site_properties
> WHERE site_id = @.siteID AND property_type_id = 18
> If @.propertyValue = ''
> SELECT @.propertyValue = client_site_id FROM sites WHERE site_id = @.siteID
> Return @.@.Error
|||I just tried this this morning and unfortunately it did not work. Funny thing
is...if I only have one select statement in my procedure, it works fine. I
don't want to have to run back and forth to SQL Server two times for a simple
lookup. Any other suggestions?
Also, if you don't mind me piggy backing on this one, I have another
question posted that seems it is being ignored. Can you please look for a
post on the 23rd with the subject "Update one column from another column and
table".
You've answered a few of my questions before...thanks a lot for your
continued help.
"Dan Guzman" wrote:

> Try adding SET NOCOUNT ON to the beginning of your proc. This will suppress
> DONE_IN_PROC messages that can cause issues with ADO apps.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:27532132-6943-441D-9117-80148A2E4036@.microsoft.com...
>
>
|||In case anyone reads this and has the same issue. It seems that there is no
way to do this with the command object. I was able to accomplish this using a
recordset object and the method NextRecordSet. So in my stored procedure, I
am explicitly doing two select statements (I don't care at this point which
one is blank). Then in my VB app, I am checking the primary recordset that I
want and if it is blank, I then use the Set rs = rs.NextRecordSet to use the
next recordset that was returned, which has the item I want.
Since I am only returning one field (in this instance), If anyone knows how
to do this with a command object, I would really appreciate an example.
"Mike Collins" wrote:
[vbcol=seagreen]
> I just tried this this morning and unfortunately it did not work. Funny thing
> is...if I only have one select statement in my procedure, it works fine. I
> don't want to have to run back and forth to SQL Server two times for a simple
> lookup. Any other suggestions?
> Also, if you don't mind me piggy backing on this one, I have another
> question posted that seems it is being ignored. Can you please look for a
> post on the 23rd with the subject "Update one column from another column and
> table".
> You've answered a few of my questions before...thanks a lot for your
> continued help.
> "Dan Guzman" wrote:
|||Mike, I ran the following code snippet and was able to retrieve the output
parameter without NextRecordset. I'm not sure what might be different in
your environment, though.
cmdImageProperty.Parameters.Append _
cmdImageProperty.CreateParameter( _
"returnValue", adInteger, adParamReturnValue)
cmdImageProperty.Parameters.Append
cmdImageProperty.CreateParameter( _
"siteID", adInteger, adParamInput, ,2)
cmdImageProperty.Parameters.Append _
cmdImageProperty.CreateParameter( _
"PropertyValue", adVarChar, adParamOutput,25)
cmdImageProperty.Execute
MsgBox cmdImageProperty.Parameters(2)
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C70CC9A8-2DDC-43E1-970B-0C43E353DBFF@.microsoft.com...[vbcol=seagreen]
> In case anyone reads this and has the same issue. It seems that there is
> no
> way to do this with the command object. I was able to accomplish this
> using a
> recordset object and the method NextRecordSet. So in my stored procedure,
> I
> am explicitly doing two select statements (I don't care at this point
> which
> one is blank). Then in my VB app, I am checking the primary recordset that
> I
> want and if it is blank, I then use the Set rs = rs.NextRecordSet to use
> the
> next recordset that was returned, which has the item I want.
> Since I am only returning one field (in this instance), If anyone knows
> how
> to do this with a command object, I would really appreciate an example.
> "Mike Collins" wrote:
|||Thank you. I'm not sure what is different in my system yet, but I will
continue to look. I was wondering if it has anything to do with me setting
the connection's cursor location to client side. I was able to set up a
recordset, and return what I needed by expliciting running both select
statements and checking the recordset of each using NextRecordset. Not the
way I wanted, but it will work for now.
"Dan Guzman" wrote:

> Mike, I ran the following code snippet and was able to retrieve the output
> parameter without NextRecordset. I'm not sure what might be different in
> your environment, though.
> cmdImageProperty.Parameters.Append _
> cmdImageProperty.CreateParameter( _
> "returnValue", adInteger, adParamReturnValue)
> cmdImageProperty.Parameters.Append
> cmdImageProperty.CreateParameter( _
> "siteID", adInteger, adParamInput, ,2)
> cmdImageProperty.Parameters.Append _
> cmdImageProperty.CreateParameter( _
> "PropertyValue", adVarChar, adParamOutput,25)
> cmdImageProperty.Execute
> MsgBox cmdImageProperty.Parameters(2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:C70CC9A8-2DDC-43E1-970B-0C43E353DBFF@.microsoft.com...
>
>