473,856 Members | 1,676 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 13323
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.Fil l(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=northwi nd")

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

dim ds as dataset = new DataSet

da.Fill(ds, "Customers" )
datagrid1.datas ource = ds
datagrid1.datab ind

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**@discussio ns.microsoft.co m> wrote in message
news:20******** *************** ***********@mic rosoft.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*********@ya hoo.com> wrote in message
news:O7******** ******@TK2MSFTN GP15.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=northwi nd")

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

dim ds as dataset = new DataSet

da.Fill(ds, "Customers" )
datagrid1.datas ource = ds
datagrid1.datab ind

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**@discussio ns.microsoft.co m> wrote in message
news:20******** *************** ***********@mic rosoft.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 GetRentedTapesB yCustomerID(ByV al CustomerID As Integer)
As DataTable

Dim cn As OleDb.OleDbConn ection = Settings.GetCon nection()
Dim cmd As New OleDb.OleDbComm and
Dim da As New OleDb.OleDbData Adapter
Dim dt As New DataTable
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = "RentedTapesByC ustomerID"
cmd.Parameters. Add("@CustomerI D", CustomerID)
cn.Open()
cmd.Connection = cn
da.SelectComman d = 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**@discussio ns.microsoft.co m> wrote in message
news:72******** *************** ***********@mic rosoft.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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1614
by: Mervin Williams | last post by:
I have several tables involved in my application, but the two in question here are the company and address tables. The company table has business_address_id and mailing_address_id columns, which are both foreign keys to the address table. So, the stored procedure to which my SelectCommand points to reads as: ALTER PROCEDURE dbo.CompanyInfoByCompanyID ( @companyid int
15
2258
by: JIM.H. | last post by:
Hello, Can I send a dataset as a parameter into stored procedure and import data to a table in the stored procedure? Thanks, Jim.
9
47209
by: Nikolay Petrov | last post by:
How to fill DataSet from stored procedure?
1
4875
by: Nikolay Petrov | last post by:
How to fill dataset with multiple tables and set their relaition? Can I get the relations from the SQL server? Also I would like to do it using stored procedures. TIA
5
2308
by: moondaddy | last post by:
I have a website where cataloge pages are populated by calling a stored procedure on sql server. I use the sql data adapter's fill method to call this stored procedure and fill the dataset. about 6 to 15 times a day the server hangs and times out when this fill method is called. The SP is simple and uses very little resources (as tested using client statistics in query analyzer). Here's data from my error log which includes the...
1
3504
by: SomebodyElse | last post by:
Hi. Apologies if this has been asked here before - I've searched & searched but can't find anything. It's probably my serach parameters, but I'm having trouble even describing it to a search engine! I'm sure this is a very simple problem, with straighforward solution. It certainly is in classic asp/ado, but I'm finally learning this .NET business. I like it, but can't get my head around some concepts. Anyway....
2
4100
by: john wright | last post by:
I have a stored procedure that returns a dataset. I want this dataset to fill a tree view. Here is the Stored Procedure Query SELECT Product.Product_#, Product.Part_Number, Status.Status, Traveler_Step.Step, Station.Station_Name FROM Product INNER JOIN Traveler_Step ON Product.Traveler_Step_# = Traveler_Step.Traveler_Step_# INNER JOIN
6
2487
by: Laura K | last post by:
This is probably a simple question but I want to make sure I am doing it right. I have a spoc with two select statements which results in two tables. Very Basic --------------------------------------------------------------------------------- @strProductCode nvarchar (50)
5
3135
by: John | last post by:
Hi, I am developing a windows app using C# 2005. This app uses SQL Server or Oracle database depending on the what the user is using. Can I create one typed dataset and use it for SQL Server and Oracle? Probably not. How can I structure my data access layer so the app will be switching easily between those two databases with a locally stored db connection string? How about the following? Thanks for your suggestions.
0
9908
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11052
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10379
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9530
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7930
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7090
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4172
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3198
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.