Hi.

I want to create a Function that returns the result of a parameter Stored Procedure in a DataTable if that's possible. I've tried a couple of things with no success. The reason I need to do this is I have to close the SQL connection before exiting the Function. If I do this, my returned recordset disappears.

Here's my code to get the recordset using SQLCommand:

Public Function GetRecordSet() as New DataTable
Dim sConnString As String = "Data Source=" & SqlServerName & "; User ID=" & SqlLogin & "; Password=" & SqlPWD & "; Initial Catalog=" & mcWebPPO
Dim oConn As New SqlConnection(sConnString)
Dim oComm As New SqlCommand("MySP", oConn)
oComm.CommandType = CommandType.StoredProcedure

'*** Allow for SP's Return Value
Dim oParm As SqlParameter = oComm.Parameters.Add(mcReturnValue, SqlDbType.Int)
oParm.Direction = ParameterDirection.ReturnValue

'*** Setup the input parameter
oParm = oComm.Parameters.Add("@SubjectSearch", SqlDbType.VarChar, 50)
oParm.Value = "What my parameter is"

oConn.Open()
Dim oDR As SqlDataReader = oComm.ExecuteReader

Do While oDR.Read()
   Console.WriteLine("{0}, {1}", oDR.GetName(0), oDR.GetName(1))
   Console.WriteLine("{0}, {1}", oDR.GetValue(0), oDR.GetValue(1))
Loop

******** HERE IS WHERE I NEED HELP TO SET THE DATATABLE = DataReader
GetRecordSet = ?????
Return GetRecordSet

oDR.Close()
oConn.Close()

Console.WriteLine(mcReturnValue & ": {0}", oComm.Parameters(mcReturnValue).Value)

End Function

TIA!

Rita

Set the return Parameter Value to variable and return that variable.It wont disappear.If you want to set those values in DataTable .Create an dataset and add those two return values in the DataTable and return the dataset.

Thanks Santhi for the suggestion, I never thought of using a Variable.
I figured out a way to use the SQLCommand and SQLDataAdapter to run a Stored Procedure that returns the results in a DataTable. That works for me.

Rita