Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > A vb.net function that resturns a dataset from stored procedure?

Reply
Thread Tools

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

 
 
jobs
Guest
Posts: n/a
 
      05-31-2007
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

 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      05-31-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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
>



 
Reply With Quote
 
 
 
 
jobs
Guest
Posts: n/a
 
      05-31-2007
On May 31, 3:50 pm, "sloan" <(E-Mail Removed)> 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.




 
Reply With Quote
 
sloan
Guest
Posts: n/a
 
      05-31-2007

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> On May 31, 3:50 pm, "sloan" <(E-Mail Removed)> 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.
>
>
>
>



 
Reply With Quote
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      06-01-2007
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/QuickStar...a/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
>
>

 
Reply With Quote
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      06-01-2007
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/QuickStar...a/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
>
>

 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      06-01-2007
"Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...

> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem in running a basic code in python 3.3.0 that includes HTML file Satabdi Mukherjee Python 1 04-04-2013 07:48 PM
Urgent : Populate a dataset from a stored proc in .NET 2.0 John ASP .Net 5 02-20-2006 03:41 PM
Populating a dataset from a stored proc in .NET 2.0 John ASP .Net 0 02-17-2006 05:49 PM
Oracle and .NET stored procedures returning dataset... Ricardo Magalhaes ASP .Net 4 06-21-2005 08:03 PM
Trying to read DataSet that is stored in Session Guadala Harry ASP .Net 5 03-01-2004 10:36 PM



Advertisments