ADODB.Command Output Params return null for GUID types 
Author Message
 ADODB.Command Output Params return null for GUID types

I am using ADO 2.7 in a .Net VB Web Service application. I instanciate an
ADODB.Connection, then I instantiate an ADODB.Command that uses the
connection as it's active connection and then I execute a stored proc on a
SQL 2000 (MSDE 2000) Server.

I have 3 input parameters and one output parameter... 4 in total. (Nothing
hectic at this stage)

My stored proc: ---------------------------- Start here -------------->
CREATE PROCEDURE dbo.RegisterPear




AS

 IF EXISTS(
  SELECT
    *
   FROM
    Peers
   WHERE

  BEGIN
   SELECT

    FROM
     Peers
    WHERE

  END
 ELSE
  BEGIN
   INSERT INTO
    Peers
    (
     PeerUID,
     PeerName,
     PeerIPAddress,
     [Description]
    )VALUES(
     NEWID(),



    )

   SELECT

    FROM
     Peers
    WHERE

  END

 Return(0)
GO

My stored proc: ----------------------------- End here --------------->

The problem is that it never returns the damn PeerUID when it hits the else
statement..... It's as if only the first statement in the BEGIN..END block
ever fires.

I posted this in a VB group aswell because: This works when I execute the
sored proc in Query Analyzer, but it refuses to return the damn thing from
my code in VB. In VB I always get a DBNULL back...

I would apreciate any help....

Thanks
David



Sat, 22 Oct 2005 14:48:27 GMT  
 ADODB.Command Output Params return null for GUID types
I had a problem like this before where I had a SELECT
statement in a stored proc and was trying to get the value
of the sp execution return code.

The explanation I got was that with ADO, all returned
parameter values are stored at the end of the recordset
stream so you have to use the .Next method of the
recordset object to get to the next block of data.

From the code you sent, it looks like the records returned
will be in the first block of the stream, when you've
processed these, use rs.next to view the results of the
output parameter.

Quote:
>-----Original Message-----
>I am using ADO 2.7 in a .Net VB Web Service application.
I instanciate an
>ADODB.Connection, then I instantiate an ADODB.Command
that uses the
>connection as it's active connection and then I execute a
stored proc on a
>SQL 2000 (MSDE 2000) Server.

>I have 3 input parameters and one output parameter... 4
in total. (Nothing
>hectic at this stage)

>My stored proc: ---------------------------- Start here --
------------>
>CREATE PROCEDURE dbo.RegisterPear




>AS

> IF EXISTS(
>  SELECT
>    *
>   FROM
>    Peers
>   WHERE

>  BEGIN
>   SELECT

>    FROM
>     Peers
>    WHERE

>  END
> ELSE
>  BEGIN
>   INSERT INTO
>    Peers
>    (
>     PeerUID,
>     PeerName,
>     PeerIPAddress,
>     [Description]
>    )VALUES(
>     NEWID(),



>    )

>   SELECT

>    FROM
>     Peers
>    WHERE

>  END

> Return(0)
>GO

>My stored proc: ----------------------------- End here ---
------------>

>The problem is that it never returns the damn PeerUID

when it hits the else

- Show quoted text -

Quote:
>statement..... It's as if only the first statement in the
BEGIN..END block
>ever fires.

>I posted this in a VB group aswell because: This works
when I execute the
>sored proc in Query Analyzer, but it refuses to return
the damn thing from
>my code in VB. In VB I always get a DBNULL back...

>I would apreciate any help....

>Thanks
>David

>.



Sat, 22 Oct 2005 17:59:14 GMT  
 ADODB.Command Output Params return null for GUID types
I'm not passing it back to a recordset....

Here is the VB code that executes the SP:

------------------------ Starts Here -------------------->
Try

If CheckIP(IPAddress) Then

Dim cnRegister As New ADODB.Connection()

With cnRegister

.ConnectionString = getConnection()

.Open()

End With

Dim cmRegister As New ADODB.Command()

Dim PeerUID As String

With cmRegister

.ActiveConnection = cnRegister

.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

.CommandText = "RegisterPear"

.Parameters(1).Type = ADODB.DataTypeEnum.adVarChar

.Parameters(1).Value = PeerName

.Parameters(2).Type = ADODB.DataTypeEnum.adVarChar

.Parameters(2).Value = IPAddress

.Parameters(3).Type = ADODB.DataTypeEnum.adVarChar

.Parameters(3).Value = Description

.Parameters(4).Type = ADODB.DataTypeEnum.adVarChar

' .Parameters(4).Direction = ADODB.ParameterDirectionEnum.adParamOutput

' .Parameters(4).Size = 36

.Execute()

End With

If cmRegister(0).Value = 0 Then

PeerUID = cmRegister.Parameters(4).Value.ToString()

Debug.Write(vbCrLf & "-" & PeerUID & "-" & vbCrLf)

Register = PeerUID

Else

Register = "An unkown Error occured!"

End If

cmRegister = Nothing

cnRegister.Close()

cnRegister = Nothing

Else

Register = "Bad IP Address!"

End If

Catch e As Exception

Debug.Write(e.Message)

End Try

------------------------- End Here --------------------->

I am just trying to read back the output param. I have done this a 1000
times using Visual Basic 6 without any problems... What am I doing wrong, or
what do I need to do extra in VB.Net?

Thanks
David


Quote:
> I had a problem like this before where I had a SELECT
> statement in a stored proc and was trying to get the value
> of the sp execution return code.

> The explanation I got was that with ADO, all returned
> parameter values are stored at the end of the recordset
> stream so you have to use the .Next method of the
> recordset object to get to the next block of data.

> From the code you sent, it looks like the records returned
> will be in the first block of the stream, when you've
> processed these, use rs.next to view the results of the
> output parameter.

> >-----Original Message-----
> >I am using ADO 2.7 in a .Net VB Web Service application.
> I instanciate an
> >ADODB.Connection, then I instantiate an ADODB.Command
> that uses the
> >connection as it's active connection and then I execute a
> stored proc on a
> >SQL 2000 (MSDE 2000) Server.

> >I have 3 input parameters and one output parameter... 4
> in total. (Nothing
> >hectic at this stage)

> >My stored proc: ---------------------------- Start here --
> ------------>
> >CREATE PROCEDURE dbo.RegisterPear




> >AS

> > IF EXISTS(
> >  SELECT
> >    *
> >   FROM
> >    Peers
> >   WHERE

> >  BEGIN
> >   SELECT

> >    FROM
> >     Peers
> >    WHERE

> >  END
> > ELSE
> >  BEGIN
> >   INSERT INTO
> >    Peers
> >    (
> >     PeerUID,
> >     PeerName,
> >     PeerIPAddress,
> >     [Description]
> >    )VALUES(
> >     NEWID(),



> >    )

> >   SELECT

> >    FROM
> >     Peers
> >    WHERE

> >  END

> > Return(0)
> >GO

> >My stored proc: ----------------------------- End here ---
> ------------>

> >The problem is that it never returns the damn PeerUID
> when it hits the else
> >statement..... It's as if only the first statement in the
> BEGIN..END block
> >ever fires.

> >I posted this in a VB group aswell because: This works
> when I execute the
> >sored proc in Query Analyzer, but it refuses to return
> the damn thing from
> >my code in VB. In VB I always get a DBNULL back...

> >I would apreciate any help....

> >Thanks
> >David

> >.



Sat, 22 Oct 2005 21:34:00 GMT  
 ADODB.Command Output Params return null for GUID types
Hi David

It does seem as if the stored proc. is O.K. and everything works there.
Could you maybe post the code that you are using, the problem might be
there?

Regards
Bothma


Quote:
> I am using ADO 2.7 in a .Net VB Web Service application. I instanciate an
> ADODB.Connection, then I instantiate an ADODB.Command that uses the
> connection as it's active connection and then I execute a stored proc on a
> SQL 2000 (MSDE 2000) Server.

> I have 3 input parameters and one output parameter... 4 in total. (Nothing
> hectic at this stage)

> My stored proc: ---------------------------- Start here -------------->
> CREATE PROCEDURE dbo.RegisterPear




> AS

>  IF EXISTS(
>   SELECT
>     *
>    FROM
>     Peers
>    WHERE

>   BEGIN
>    SELECT

>     FROM
>      Peers
>     WHERE

>   END
>  ELSE
>   BEGIN
>    INSERT INTO
>     Peers
>     (
>      PeerUID,
>      PeerName,
>      PeerIPAddress,
>      [Description]
>     )VALUES(
>      NEWID(),



>     )

>    SELECT

>     FROM
>      Peers
>     WHERE

>   END

>  Return(0)
> GO

> My stored proc: ----------------------------- End here --------------->

> The problem is that it never returns the damn PeerUID when it hits the
else
> statement..... It's as if only the first statement in the BEGIN..END block
> ever fires.

> I posted this in a VB group aswell because: This works when I execute the
> sored proc in Query Analyzer, but it refuses to return the damn thing from
> my code in VB. In VB I always get a DBNULL back...

> I would apreciate any help....

> Thanks
> David



Sun, 23 Oct 2005 20:51:19 GMT  
 ADODB.Command Output Params return null for GUID types
Hi David

I'm not sure why this exactly is, but as soon as you set the cursorlocation
property of the connection to be client side, then it seems to work (it did
for me). Basically the code I used was:

        With cnRegister
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .ConnectionString = conString
            .Open
        End With

        Dim cmRegister As New ADODB.Command()

        'Dim PeerUID As String

        With cmRegister
            .ActiveConnection = cnRegister
            .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
            .CommandText = "RegisterPeer"
            .Parameters(1).Type = ADODB.DataTypeEnum.adVarChar
            .Parameters(1).Value = PeerName
            .Parameters(2).Type = ADODB.DataTypeEnum.adVarChar
            .Parameters(2).Value = IPAddress
            .Parameters(3).Type = ADODB.DataTypeEnum.adVarChar
            .Parameters(3).Value = Description
            .Parameters(4).Type = ADODB.DataTypeEnum.adVarChar
            .Execute()
        End With

Hope it works for you to.

Kind Regards

Bothma


Quote:
> I am using ADO 2.7 in a .Net VB Web Service application. I instanciate an
> ADODB.Connection, then I instantiate an ADODB.Command that uses the
> connection as it's active connection and then I execute a stored proc on a
> SQL 2000 (MSDE 2000) Server.

> I have 3 input parameters and one output parameter... 4 in total. (Nothing
> hectic at this stage)

> My stored proc: ---------------------------- Start here -------------->
> CREATE PROCEDURE dbo.RegisterPear




> AS

>  IF EXISTS(
>   SELECT
>     *
>    FROM
>     Peers
>    WHERE

>   BEGIN
>    SELECT

>     FROM
>      Peers
>     WHERE

>   END
>  ELSE
>   BEGIN
>    INSERT INTO
>     Peers
>     (
>      PeerUID,
>      PeerName,
>      PeerIPAddress,
>      [Description]
>     )VALUES(
>      NEWID(),



>     )

>    SELECT

>     FROM
>      Peers
>     WHERE

>   END

>  Return(0)
> GO

> My stored proc: ----------------------------- End here --------------->

> The problem is that it never returns the damn PeerUID when it hits the
else
> statement..... It's as if only the first statement in the BEGIN..END block
> ever fires.

> I posted this in a VB group aswell because: This works when I execute the
> sored proc in Query Analyzer, but it refuses to return the damn thing from
> my code in VB. In VB I always get a DBNULL back...

> I would apreciate any help....

> Thanks
> David



Sun, 23 Oct 2005 22:44:57 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Optional StorecProc params using ADODB::Command.

2. Stored Proc Return values / Output Params w ADO and SQL Server 7

3. Stored Proc Return values / Output Params w ADO and SQL Server 7

4. Stored Proc Return values / Output Params w ADO and SQL Server 7

5. Stored Proc Return values / Output Params w ADO and SQL Server 7

6. ADODB select with GUID data type

7. ADO-Returning recordset and output params procedure

8. Executing stored proc with Output params without ADO Command object

9. NULL GUID passed to command parameter

10. vbscript passing a Memo field from adodb connection returns Null after reading once

11. ADODB.Command - Output parameters problem

12. How to convert a GUID string to a GUID data type

 

 
Powered by phpBB® Forum Software