By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

May 31 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <jo**@webdos.comwrote in message
news:11**********************@q69g2000hsb.googlegr 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

May 31 '07 #2

P: n/a
On May 31, 3:50 pm, "sloan" <s...@ipass.netwrote:
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.


May 31 '07 #3

P: n/a

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" <jo**@webdos.comwrote in message
news:11*********************@p47g2000hsd.googlegro ups.com...
On May 31, 3:50 pm, "sloan" <s...@ipass.netwrote:
>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.


May 31 '07 #4

P: n/a
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

Jun 1 '07 #5

P: n/a
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

Jun 1 '07 #6

P: n/a
"Peter Bromberg [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:F6**********************************@microsof t.com...
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

Jun 1 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.