A vb.net function that resturns a dataset from stored procedure?

Discussion in 'ASP .Net' started by jobs, May 31, 2007.

  1. jobs

    jobs Guest

    How do you return dataset from a vb.net function?

    my sp:

    ALTER FUNCTION [dbo].[GetAllUsers_test_fn]
    (

    )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT * from users

    )



    my function:

    Function GetAllUsers() As DataSet
    Dim cmd As New SqlCommand
    With cmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "GetAllUsers_test_fn"
    .CommandTimeout = 0
    .Connection = p_cnn
    .Parameters("@return").Direction =
    ParameterDirection.ReturnValue

    End With
    cmd.ExecuteScalar()
    Dim result As DataSet
    result = cmd.Parameters("@return").Value
    Return result
    cmd.Dispose()
    p_cnn.Close()
    End Function

    I'm getting this error:

    An SqlParameter with ParameterName "@return" is not contained by this
    SqlParameterCollection
     
    jobs, May 31, 2007
    #1
    1. Advertising

  2. jobs

    sloan Guest

    You have a function (table function) , not a stored procedure there.

    ALTER FUNCTION [dbo].[GetAllUsers_test_fn]
    (

    )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT a,b,c from users

    )


    ........

    Create a wrapper stored procedure if you'd like

    CREATE PROC dbo.uspGetAllUsers
    AS


    Select a,b from [dbo].[GetAllUsers_test_fn]


    GO



    Then you'll call the .LoadDataSet method ... and throw "dbo.uspGetAllUsers"
    into it, and you'll eventually get a dataset.


    If your "select *" for just for demo purposes, that's fine.

    But if you're actually doing it, specify columns ( a, b, c) and not "*".
    Select * is pretty sloppy.






    "jobs" <> wrote in message
    news:...
    > How do you return dataset from a vb.net function?
    >
    > my sp:
    >
    > ALTER FUNCTION [dbo].[GetAllUsers_test_fn]
    > (
    >
    > )
    > RETURNS TABLE
    > AS
    > RETURN
    > (
    > SELECT * from users
    >
    > )
    >
    >
    >
    > my function:
    >
    > Function GetAllUsers() As DataSet
    > Dim cmd As New SqlCommand
    > With cmd
    > .CommandType = CommandType.StoredProcedure
    > .CommandText = "GetAllUsers_test_fn"
    > .CommandTimeout = 0
    > .Connection = p_cnn
    > .Parameters("@return").Direction =
    > ParameterDirection.ReturnValue
    >
    > End With
    > cmd.ExecuteScalar()
    > Dim result As DataSet
    > result = cmd.Parameters("@return").Value
    > Return result
    > cmd.Dispose()
    > p_cnn.Close()
    > End Function
    >
    > I'm getting this error:
    >
    > An SqlParameter with ParameterName "@return" is not contained by this
    > SqlParameterCollection
    >
     
    sloan, May 31, 2007
    #2
    1. Advertising

  3. jobs

    jobs Guest

    On May 31, 3:50 pm, "sloan" <> wrote:

    > Then you'll call the .LoadDataSet method ... and throw "dbo.uspGetAllUsers"
    > into it, and you'll eventually get a dataset.


    Thank you! yes just testing.

    sorry, lost me there.. LoadDataSet method?

    okay. say i just have the sp:

    CREATE PROCEDURE [dbo].[GetAllUsers_test_sp]
    AS
    SELECT * from users


    and my function would like like what? (below not working of course)

    Function GetAllUsers() As DataSet
    Dim cmd As New SqlCommand
    With cmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "GetAllUsers_test_sp"
    .CommandTimeout = 0
    .Connection = p_cnn
    .Parameters("@return").Direction =
    ParameterDirection.ReturnValue

    End With
    cmd.ExecuteScalar()
    Dim result As DataSet
    result = cmd.Parameters("@return").Value
    Return result
    cmd.Dispose()
    p_cnn.Close()
    End Function




    Thanks for any help.
     
    jobs, May 31, 2007
    #3
  4. jobs

    sloan Guest

    You are running:
    cmd.ExecuteScalar()

    That method is for 1 single value.

    Like
    Select count(*) from dbo.Employee

    that would be an appropriate .ExecuteScalar query.

    ...

    Look for cmd.LoadDataSet or something like that.





    "jobs" <> wrote in message
    news:...
    > On May 31, 3:50 pm, "sloan" <> wrote:
    >
    >> Then you'll call the .LoadDataSet method ... and throw
    >> "dbo.uspGetAllUsers"
    >> into it, and you'll eventually get a dataset.

    >
    > Thank you! yes just testing.
    >
    > sorry, lost me there.. LoadDataSet method?
    >
    > okay. say i just have the sp:
    >
    > CREATE PROCEDURE [dbo].[GetAllUsers_test_sp]
    > AS
    > SELECT * from users
    >
    >
    > and my function would like like what? (below not working of course)
    >
    > Function GetAllUsers() As DataSet
    > Dim cmd As New SqlCommand
    > With cmd
    > .CommandType = CommandType.StoredProcedure
    > .CommandText = "GetAllUsers_test_sp"
    > .CommandTimeout = 0
    > .Connection = p_cnn
    > .Parameters("@return").Direction =
    > ParameterDirection.ReturnValue
    >
    > End With
    > cmd.ExecuteScalar()
    > Dim result As DataSet
    > result = cmd.Parameters("@return").Value
    > Return result
    > cmd.Dispose()
    > p_cnn.Close()
    > End Function
    >
    >
    >
    >
    > Thanks for any help.
    >
    >
    >
    >
     
    sloan, May 31, 2007
    #4
  5. Yikes! I don't know how you could have come up with that horrible code (in
    any language). Do yourself a favor and start with the ASP.NET QUICKSTARTS:

    http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx

    Have fun.
    Peter
    --
    Site: http://www.eggheadcafe.com
    UnBlog: http://petesbloggerama.blogspot.com
    Short urls & more: http://ittyurl.net




    "jobs" wrote:

    > How do you return dataset from a vb.net function?
    >
    > my sp:
    >
    > ALTER FUNCTION [dbo].[GetAllUsers_test_fn]
    > (
    >
    > )
    > RETURNS TABLE
    > AS
    > RETURN
    > (
    > SELECT * from users
    >
    > )
    >
    >
    >
    > my function:
    >
    > Function GetAllUsers() As DataSet
    > Dim cmd As New SqlCommand
    > With cmd
    > .CommandType = CommandType.StoredProcedure
    > .CommandText = "GetAllUsers_test_fn"
    > .CommandTimeout = 0
    > .Connection = p_cnn
    > .Parameters("@return").Direction =
    > ParameterDirection.ReturnValue
    >
    > End With
    > cmd.ExecuteScalar()
    > Dim result As DataSet
    > result = cmd.Parameters("@return").Value
    > Return result
    > cmd.Dispose()
    > p_cnn.Close()
    > End Function
    >
    > I'm getting this error:
    >
    > An SqlParameter with ParameterName "@return" is not contained by this
    > SqlParameterCollection
    >
    >
     
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Jun 1, 2007
    #5
  6. Yikes! I don't know how you could have come up with that horrible code (in
    any language). Do yourself a favor and start with the ASP.NET QUICKSTARTS:

    http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx

    Have fun.
    Peter
    --
    Site: http://www.eggheadcafe.com
    UnBlog: http://petesbloggerama.blogspot.com
    Short urls & more: http://ittyurl.net




    "jobs" wrote:

    > How do you return dataset from a vb.net function?
    >
    > my sp:
    >
    > ALTER FUNCTION [dbo].[GetAllUsers_test_fn]
    > (
    >
    > )
    > RETURNS TABLE
    > AS
    > RETURN
    > (
    > SELECT * from users
    >
    > )
    >
    >
    >
    > my function:
    >
    > Function GetAllUsers() As DataSet
    > Dim cmd As New SqlCommand
    > With cmd
    > .CommandType = CommandType.StoredProcedure
    > .CommandText = "GetAllUsers_test_fn"
    > .CommandTimeout = 0
    > .Connection = p_cnn
    > .Parameters("@return").Direction =
    > ParameterDirection.ReturnValue
    >
    > End With
    > cmd.ExecuteScalar()
    > Dim result As DataSet
    > result = cmd.Parameters("@return").Value
    > Return result
    > cmd.Dispose()
    > p_cnn.Close()
    > End Function
    >
    > I'm getting this error:
    >
    > An SqlParameter with ParameterName "@return" is not contained by this
    > SqlParameterCollection
    >
    >
     
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Jun 1, 2007
    #6
  7. jobs

    Mark Rae Guest

    "Peter Bromberg [C# MVP]" <> wrote in
    message news:...

    > Yikes! I don't know how you could have come up with that horrible code


    I've seen worse...

    Admittedly, not much worse...


    --
    http://www.markrae.net
     
    Mark Rae, Jun 1, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    552
    Sarmad Aljazrawi
    Dec 16, 2003
  2. Mike P
    Replies:
    0
    Views:
    3,372
    Mike P
    Jun 19, 2006
  3. Chris
    Replies:
    0
    Views:
    416
    Chris
    Mar 1, 2007
  4. SethM
    Replies:
    6
    Views:
    166
    Bob Barrows [MVP]
    Sep 22, 2008
  5. phpCodeHead

    Generic call stored procedure function

    phpCodeHead, Aug 7, 2009, in forum: Javascript
    Replies:
    1
    Views:
    177
    Erwin Moller
    Aug 7, 2009
Loading...

Share This Page