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 @.@.ErrorTry 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...
> > 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
>
>|||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:
> 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...
> > > 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
> >
> >
> >|||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...
> 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:
>> 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...
>> > > 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
>> >
>> >
>> >|||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...
> > 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:
> >
> >> 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...
> >> > > 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
> >> >
> >> >
> >> >
>
>