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

J

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
 
S

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

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

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top