none
OLEDB Command call SQL 2000 store procedure with output parameter not work RRS feed

  • Question

  • Hello professional,

    I have a store procedure in SQL 2000 as below 

    Create procedure spTest (
    @aaa VarChar(10),
    @bbb Int Output )
    as
    Begin
    Select @aaa
    Set @bbb = @@Rowcount
    Return 0
    End
    Go

    I try to execute from query analyzer.

    Declare @a Varchar(10), @b int
    Set @a = 'Hello There'
    Exec spTest @a, @b Output
    Select @a, @b

    It return this result.

              

    ----------
    Hello Ther

    (1 row(s) affected)

    ---------- -----------
    Hello Ther 1

    (1 row(s) affected)

    However, when I try to do the program in VB2010, I cannot get the value from the output parameter.

    I try to get the output parameter thru 2 different ways using OLEDB

    Method 1 :

    Using myCon as New OLEDB.OledbConnection("Provider=SQLOLEDB;Data Source=SQLSVR1;Network Library=DBMSSOCN;Initial Catalog=TESTDB;UID=sa;PWD=1234;Connect Timeout=30;")

    Using myCmd as new OLEDB.OleDBCommand("spTest",myCon)

    myCmd.Connection.Open

                        myCmd.CommandType = CommandType.StoredProcedure
                        myCmd.CommandTimeout = 120
                        myCmd.Parameters.Add("@aaa", OleDb.OleDbType.VarChar, 10).Value = "Hello There"
                        myCmd.Parameters.Add("@bbb", OleDb.OleDbType.Integer).Direction = ParameterDirection.Output

                        Using myDr As OleDb.OleDbDataReader = myCmd.ExecuteReader
                            Debug.Print(myCmd.Parameters("@aaa").Value)
                            Debug.Print(myCmd.Parameters("@bbb").Value) <<----  return Nothing, actually should be 1

           myDr.Close

                       End Using

    End Using

    End Using

    Method 2 :

    Using myCon as New OLEDB.OledbConnection("Provider=SQLOLEDB;Data Source=SQLSVR1;Network Library=DBMSSOCN;Initial Catalog=TESTDB;UID=sa;PWD=1234;Connect Timeout=30;")

    Using myCmd as new OLEDB.OleDBCommand("spTest ?, ? Output",myCon)

    myCmd.Connection.Open

                        myCmd.CommandType = CommandType.Text
                        myCmd.CommandTimeout = 120
                        myCmd.Parameters.Add("@P1", OleDb.OleDbType.VarChar, 10).Value = "Hello There"
                        myCmd.Parameters.Add("@P2", OleDb.OleDbType.Integer).Direction = ParameterDirection.Output

                        Using myDr As OleDb.OleDbDataReader = myCmd.ExecuteReader
                            Debug.Print(myCmd.Parameters("@P1").Value)
                            Debug.Print(myCmd.Parameters("@P2").Value) <<----  return Nothing, actually should be 1

           myDr.Close

                       End Using

    End Using

    End Using

    Both method cannot retrieve the output parameter value. So I tried to trace thru SQL profiler.

    For method 1, I get this result

    declare @P1 int

    set @P1=1
    exec spTest 'Hello Ther', @P1 output     <<-- Why @P1 become output parameter
    select @P1

    For method 2, I get this result.

    declare @P1 int
    set @P1=1
    exec sp_executesql N'exec spTest @P1, @P2 Output', N'@P1 varchar(10),@P2 int OUTPUT', 'Hello Ther', @P1 output  <<-- Here also
    select @P1

    Friday, August 8, 2014 4:11 AM

Answers

  • Hello,

    Using myCmd.ExecuteNonQuery() instead of myCmd.ExecuteReader() as:

    It seems that the myCmd.ExecuteReader method does not fill the value from database to local directly. It would return the value only after reading over all records:

     Using myDr As OleDb.OleDbDataReader = myCmd.ExecuteReader
                        While myDr.HasRows
                            myDr.NextResult()
                        End While
                        myDr.Close()
                        Debug.Print(myCmd.Parameters("@aaa").Value)
                        Debug.Print(myCmd.Parameters("@bbb").Value) '<<----  return Nothing, actually should be 1
                    End Using

    This code above would print the 1:

    Hello There
    1

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 11, 2014 8:28 AM
    Moderator
  • See below link:

    Output parameters are not returned when you run an ADO.NET command in Visual Basic

    Also, just an FYI, SQL Server 2000 is no longer supported by Microsoft.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 11, 2014 6:54 PM

All replies

  • Hello,

    Using myCmd.ExecuteNonQuery() instead of myCmd.ExecuteReader() as:

    It seems that the myCmd.ExecuteReader method does not fill the value from database to local directly. It would return the value only after reading over all records:

     Using myDr As OleDb.OleDbDataReader = myCmd.ExecuteReader
                        While myDr.HasRows
                            myDr.NextResult()
                        End While
                        myDr.Close()
                        Debug.Print(myCmd.Parameters("@aaa").Value)
                        Debug.Print(myCmd.Parameters("@bbb").Value) '<<----  return Nothing, actually should be 1
                    End Using

    This code above would print the 1:

    Hello There
    1

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 11, 2014 8:28 AM
    Moderator
  • See below link:

    Output parameters are not returned when you run an ADO.NET command in Visual Basic

    Also, just an FYI, SQL Server 2000 is no longer supported by Microsoft.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 11, 2014 6:54 PM