How to fill DataSet from stored procedure?

Nikolay Petrov

How to fill DataSet from stored procedure?

Nov 21 '05 #1

One Handed Man \( OHM - Terry Burns \)

It goes a bit like this . . .

Dim cmd As New OleDb.OleDbCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "MyStoredProcedure"

cmd.Connection = MyConnection
--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Nov 21 '05 #2

Nikolay Petrov

I know this, but how actually to read the data returned by the stored
procedure and put it in the dataset?
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

It goes a bit like this . . .

Dim cmd As New OleDb.OleDbCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "MyStoredProcedure"

cmd.Connection = MyConnection
--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Nov 21 '05 #3

Shiva

I assume you are using MS SQL Server.

-- Code Start --
Dim sqlCon As New SqlConnection (<connection string>)
Dim sqlAdp As New SqlDataAdapter
Dim sqlCmd As New SqlCommand
Dim ds As New DataSet

sqlCmd.Connection = sqlCon
sqlCmd.CommandText = <sp name>
sqlCmd.CommandType = CommandType.StoredProcedure
' Add any parameters to the stored proc here using sqlCmd.Parameters
collection
sqlAdp.SelectCommand = sqlCmd
sqlAdp.Fill (ds)
-- Code End --

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...
How to fill DataSet from stored procedure?

Nov 21 '05 #4

Jeff Johnson [MVP: VB]

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

Nov 21 '05 #5

Matt S

Try this.....

' Open db connection

Dim cnn As New SqlConnection(CONNECTION_STRING)

cnn.Open()

' Set command for the stored procedure

Dim cmd As New SqlCommand

With cmd

..CommandType = CommandType.StoredProcedure

..Connection = cnn

..CommandText = "CustOrderHist"

..Parameters.Add("@CustomerID", "ALFKI")

End With

' Create data adapter

Dim da As New SqlDataAdapter(cmd)

' Fill dataset from adapter

Dim ds As New DataSet

da.Fill(ds)

Hope this helps.

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

It goes a bit like this . . .

Dim cmd As New OleDb.OleDbCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "MyStoredProcedure"

cmd.Connection = MyConnection
--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Nov 21 '05 #6

Nikolay Petrov

Maybe my question was wrong.
I needed to know does any data will be returned from stored procedure select
statment to dataset.
I mean when the sqlcommand.commandType is text and .CommandText is the SQL
statemen everything works ok, but when the same sql statment is in stored
procedure does the DataSet iss filled properly?
"Jeff Johnson [MVP: VB]" <i.***@enough.spam> wrote in message
news:O4*************@TK2MSFTNGP12.phx.gbl...

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

Nov 21 '05 #7

Matt S

Whatever the stored procedure returns will be populated in the dataset -
exactly the same as if the commandType is Text and a SQL statement is
provided. Make sure the stored procedure you are using is returning what
you are expecting in query analyser or similar then use the code posted
previously - it should work.

Hope this helps.

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:O7**************@TK2MSFTNGP10.phx.gbl...

Maybe my question was wrong.
I needed to know does any data will be returned from stored procedure select statment to dataset.
I mean when the sqlcommand.commandType is text and .CommandText is the SQL
statemen everything works ok, but when the same sql statment is in stored
procedure does the DataSet iss filled properly?
"Jeff Johnson [MVP: VB]" <i.***@enough.spam> wrote in message
news:O4*************@TK2MSFTNGP12.phx.gbl...

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

Nov 21 '05 #8

Nikolay Petrov

Thank you all guys
"Jeff Johnson [MVP: VB]" <i.***@enough.spam> wrote in message
news:O4*************@TK2MSFTNGP12.phx.gbl...

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:eA**************@TK2MSFTNGP09.phx.gbl...

How to fill DataSet from stored procedure?

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

Nov 21 '05 #9

Jeff Johnson [MVP: VB]

"Nikolay Petrov" <jo******@mail.bg> wrote in message
news:O7**************@TK2MSFTNGP10.phx.gbl...

Maybe my question was wrong.
I needed to know does any data will be returned from stored procedure select statment to dataset.
I mean when the sqlcommand.commandType is text and .CommandText is the SQL
statemen everything works ok, but when the same sql statment is in stored
procedure does the DataSet iss filled properly?

Yes, IF the stored procedure returns a resultset. If it's a stored procedure
that only sends back a return value and/or fills output parameters then it
won't fill the DataSet, but then you wouldn't choose such a stored procedure
in the first place, would you?

Nov 21 '05 #10