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

[jobs]

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]

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

[jobs]

On May 31, 3:50 pm, "sloan" <[e-mail]:

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]

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" <[e-mail]:

>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.

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

[Mark Rae]

"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