By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,884 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,884 IT Pros & Developers. It's quick & easy.

How to fill DataSet from stored procedure?

P: n/a
How to fill DataSet from stored procedure?
Nov 21 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

"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

This discussion thread is closed

Replies have been disabled for this discussion.