Extract Single Record from Dataset filled from SP Output

[Ben]

Hi

We have a Dataset that has been populated from the output parameter of a
Stored Procedure (@Output).

I understand that I can extract a single item when the dataset is populated
by a table using this code:

CType(objDataSet.Tables("MyTable").Rows(0).Item("MyField"), String)


How could I do this when there are no tables / fields as such only the
parameter "@Output"

Thanks
B

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Hi

We have a Dataset that has been populated from the output parameter of a
Stored Procedure (@Output).

I understand that I can extract a single item when the dataset is populated
by a table using this code:

CType(objDataSet.Tables("MyTable").Rows(0).Item("MyField"), String)


How could I do this when there are no tables / fields as such only the
parameter "@Output"

Thanks
B

Why do you use a DataSet if you have no result? Just use the Command
object, which has a parameter collection where you will find the output
parameter.

 

[Ben]

Hi

THanks for your post.

The SP will sometimes return a number of rows. I cannot seem to retrieve
these using another method than a dataset.

Thanks
B

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Using the Execute method of the connection gives you a data reader,
which is actually the only object that can read a result. (Any other
object that reads a result uses a data reader.)

You can use the Load method of a DataSet to load the data from a data
reader into the DataSet.

 

[Ben]

Sorry to bump the post but would there be a better way of doing this?

THanks
B

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Repeating my last posting, as it seems to not have shown up for you:

Using the Execute method of the connection gives you a data reader,
which is actually the only object that can read a result. (Any other
object that reads a result uses a data reader.)

You can use the Load method of a DataSet to load the data from a data
reader into the DataSet.

 

[Ben]

Thanks for your post,

I am struggleing to get this to work, I want to create a function to execute
SPs with one input and one output param.

I cannot pass a Data Reader back as the connection is closed.

I thought i would load the data into an array instead, but it returns
nothing. I have checked the SP.

Any advice would be much appreciated

Thanks
B



Private Function fcnExecuteSingleOutputArrayStringSP(ByVal
strStoredProcedure As String, ByVal strSP_InputParam1 As String) As String()



Dim objSQLCn As New SqlConnection(gstrSQLcnString)

Dim objSQLCmd As New SqlCommand(strStoredProcedure, objSQLCn)

Dim DataAdapter As SqlDataAdapter

Dim objDatReader As SqlDataReader

Dim strReturnData() As String

Dim i As Integer



objSQLCmd.CommandType = CommandType.StoredProcedure

objSQLCmd.Parameters.Add("@Input", SqlDbType.VarChar, 1000)

objSQLCmd.Parameters("@Input").Value = strSP_InputParam1 'Output Location to
move file to

objSQLCmd.Parameters.Add("@Output", SqlDbType.VarChar, 1000)

objSQLCmd.Parameters("@Output").Direction = ParameterDirection.Output
'Output Location to move file to

objSQLCn.Open()



objDatReader = objSQLCmd.ExecuteReader()

'Load data into array to pass back

i = 0

Do While objDatReader.Read

i = i + 1

ReDim strReturnData(i)

strReturnData(i - 1) = objDatReader.Item(i - 1)

Loop



objSQLCn.Close()

Return strReturnData




End Function