Home All Groups Group Topic Archive Search About

Extract Single Record from Dataset filled from SP Output

microsoft.public.dotnet.languages.vb
Author
11 Nov 2006 3:28 PM
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

Author
11 Nov 2006 4:05 PM
Göran_Andersson
Ben wrote:
Show quoteHide quote
> 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.
Author
11 Nov 2006 4:13 PM
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
Show quoteHide quote
"Göran Andersson" <gu***@guffa.com> wrote in message
news:%23FDOItaBHHA.996@TK2MSFTNGP02.phx.gbl...
> Ben wrote:
>> 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.
Author
12 Nov 2006 12:17 AM
Göran_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 wrote:
Show quoteHide quote
> 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
> "Göran Andersson" <gu***@guffa.com> wrote in message
> news:%23FDOItaBHHA.996@TK2MSFTNGP02.phx.gbl...
>> Ben wrote:
>>> 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.
>
>
Author
15 Nov 2006 10:37 PM
Ben
Sorry to bump the post but would there be a better way of doing this?

THanks
B

Show quoteHide quote
"Ben" <B**@Newsgroups.microsoft.com> wrote in message
news:%23lPF6xaBHHA.992@TK2MSFTNGP03.phx.gbl...
> 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
> "Göran Andersson" <gu***@guffa.com> wrote in message
> news:%23FDOItaBHHA.996@TK2MSFTNGP02.phx.gbl...
>> Ben wrote:
>>> 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.
>
>
Author
17 Nov 2006 12:01 AM
Göran_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 wrote:
Show quoteHide quote
> Sorry to bump the post but would there be a better way of doing this?
>
> THanks
> B
>
> "Ben" <B**@Newsgroups.microsoft.com> wrote in message
> news:%23lPF6xaBHHA.992@TK2MSFTNGP03.phx.gbl...
>> 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
>> "Göran Andersson" <gu***@guffa.com> wrote in message
>> news:%23FDOItaBHHA.996@TK2MSFTNGP02.phx.gbl...
>>> Ben wrote:
>>>> 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.
>>
>
>
Author
17 Nov 2006 8:29 PM
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



Show quoteHide quote
"Göran Andersson" <gu***@guffa.com> wrote in message
news:%23yP7FudCHHA.3916@TK2MSFTNGP06.phx.gbl...
> 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 wrote:
>> Sorry to bump the post but would there be a better way of doing this?
>>
>> THanks
>> B
>>
>> "Ben" <B**@Newsgroups.microsoft.com> wrote in message
>> news:%23lPF6xaBHHA.992@TK2MSFTNGP03.phx.gbl...
>>> 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
>>> "Göran Andersson" <gu***@guffa.com> wrote in message
>>> news:%23FDOItaBHHA.996@TK2MSFTNGP02.phx.gbl...
>>>> Ben wrote:
>>>>> 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.
>>>
>>



Post Other interesting topics
Location for shared Access database
Make copy of arraylist
Creating text boxes on the fly?
newbie: is this the appropriate use of a class?
String manipulation question
Getting the current procedure name?
for each step order reverse direction ?
single line string maninuplation substring, trim, indexof ?
Installing .net assemblies windows installer
Shared database without network tech required