469,271 Members | 1,776 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

fill dataset from Sql Server stored procedure?

I have a stored procedure on Sql Server2k. I can fill a data table which I
can append to a dataset using an ADODB recordset object which gets populated
from a command object that runs the sp. I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements. I could write the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich
Nov 21 '05 #1
10 13049
OK. Now I remember my dilema. ADO.Net doesn't use recordset objects. So
how can I fill my data table/dataset from a stored procedure?

"Rich" wrote:
I have a stored procedure on Sql Server2k. I can fill a data table which I
can append to a dataset using an ADODB recordset object which gets populated
from a command object that runs the sp. I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements. I could write the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich

Nov 21 '05 #2
Of course, the SqlDataReader. It's all coming back to me. Just haven't done
this for a while. Actually, I want to populate a datagrid with this. So I
am thinkin I will populate the datatable from the Reader object and set the
datagrid datasource to the datatable. Am I on track here? can a reader be
used as a datasource?

"Rich" wrote:
OK. Now I remember my dilema. ADO.Net doesn't use recordset objects. So
how can I fill my data table/dataset from a stored procedure?

"Rich" wrote:
I have a stored procedure on Sql Server2k. I can fill a data table which I
can append to a dataset using an ADODB recordset object which gets populated
from a command object that runs the sp. I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements. I could write the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich

Nov 21 '05 #3
> how can I fill my data table/dataset from a stored procedure?

Hi,
I am not an expert, but set the CommandType
to StoredProcedure.
CommandText to your StoredProc name.
I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements

DataAdapter has Select, Insert and Delete statements.

Then, DataAdapter.Fill(DataTable)
Of course, Connection must be open.
Hope that helps,
Roger

Nov 21 '05 #4
The following code will connect to an SQL Server 2000 database, retrieve
records from a table, fill a dataset, and bind the dataset to a datagrid
object.

dim conn as SqlConnection = new SqlConnection("Data
Source=(local);Integrated Security=SSPI; Initial Catalog=northwind")

dim da as SqlDataAdapter = new SqlDataAdapter("SELECT CustomerID,
ContactName FROM Customers", thisConnection)

dim ds as dataset = new DataSet

da.Fill(ds, "Customers")
datagrid1.datasource = ds
datagrid1.databind

It isn't necessary to call the databind method if you're creating a Windows
Forms application, but is required for ASP.NET.

"Rich" <Ri**@discussions.microsoft.com> wrote in message
news:20**********************************@microsof t.com...
I have a stored procedure on Sql Server2k. I can fill a data table which I
can append to a dataset using an ADODB recordset object which gets
populated
from a command object that runs the sp. I was hoping to use a
DataAdapter.
But I think the data adapter only uses select statements. I could write
the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever.
I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich

Nov 21 '05 #5
Oops.. when converting this code from C# to VB.NET, I missed changing
"thisconnection" to "conn". Change that and the code should work.

"Steve" <sp*********@yahoo.com> wrote in message
news:O7**************@TK2MSFTNGP15.phx.gbl...
The following code will connect to an SQL Server 2000 database, retrieve
records from a table, fill a dataset, and bind the dataset to a datagrid
object.

dim conn as SqlConnection = new SqlConnection("Data
Source=(local);Integrated Security=SSPI; Initial Catalog=northwind")

dim da as SqlDataAdapter = new SqlDataAdapter("SELECT CustomerID,
ContactName FROM Customers", thisConnection)

dim ds as dataset = new DataSet

da.Fill(ds, "Customers")
datagrid1.datasource = ds
datagrid1.databind

It isn't necessary to call the databind method if you're creating a
Windows Forms application, but is required for ASP.NET.

"Rich" <Ri**@discussions.microsoft.com> wrote in message
news:20**********************************@microsof t.com...
I have a stored procedure on Sql Server2k. I can fill a data table which
I
can append to a dataset using an ADODB recordset object which gets
populated
from a command object that runs the sp. I was hoping to use a
DataAdapter.
But I think the data adapter only uses select statements. I could write
the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql
Sever. I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich


Nov 21 '05 #6
Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor
Nov 21 '05 #7
Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

"Cor Ligthert" wrote:
Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

Nov 21 '05 #8
Rich,

Here is an example. Note that this particular stored procedure requires a
parameter. Also note that this code uses OleDb objects, but you can easily
change it to use SQLClient:

Public Function GetRentedTapesByCustomerID(ByVal CustomerID As Integer)
As DataTable

Dim cn As OleDb.OleDbConnection = Settings.GetConnection()
Dim cmd As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "RentedTapesByCustomerID"
cmd.Parameters.Add("@CustomerID", CustomerID)
cn.Open()
cmd.Connection = cn
da.SelectCommand = cmd
da.Fill(dt)
cn.Close()

Return dt

End Function

Kerry Moorman
"Rich" wrote:
Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

"Cor Ligthert" wrote:
Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

Nov 21 '05 #9
why recode for what is already coded? use a data adapter it will do all the
reader work and such for you... using a reader to do it alone is dangerous
because you can only have one open at once and have to make sure you close
it when done, etc...
"Rich" <Ri**@discussions.microsoft.com> wrote in message
news:72**********************************@microsof t.com...
Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

"Cor Ligthert" wrote:
Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

Nov 21 '05 #10
Thanks all for your replies. I didn't think the datareader was the way to
go. Also, I broke down and used the SqlDataAdapter wizard. It had stored
procedures as an optioin (I am such a lamo). Anyway, now the explanations
all make sense. Everything working OK, finally.

Thanks all for your help.

"Rich" wrote:
Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

"Cor Ligthert" wrote:
Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

Nov 21 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Mervin Williams | last post: by
15 posts views Thread by JIM.H. | last post: by
9 posts views Thread by Nikolay Petrov | last post: by
1 post views Thread by Nikolay Petrov | last post: by
5 posts views Thread by moondaddy | last post: by
1 post views Thread by SomebodyElse | last post: by
2 posts views Thread by john wright | last post: by
6 posts views Thread by Laura K | last post: by
5 posts views Thread by John | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.