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]

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.

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.

 

[Guest]

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

 

[Guest]

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

 

[Mark Rae]

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

I've seen worse...

Admittedly, not much worse...