473,721 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Return multiple result in a SQL SP into a dataset(s)

Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select
Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?

Thanks
Nov 20 '05 #1
15 6507
* "AussieRule s" <so*****@nowher e.com> scripsit:
I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select
Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?


ADO.NET group:

<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://msdn.microsoft. com/newsgroups/default.asp?url =/newsgroups/loadframes.asp? icp=msdn&slcid= us&newsgroup=mi crosoft.public. dotnet.framewor k.adonet>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>
Nov 20 '05 #2
"AussieRule s" <so*****@nowher e.com> schrieb
I have a need to do two selects against to stored proc's in my SQL
db.

At the moment, each SP is called and two different dataset are
populate. Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two
Select Statements, and effectivly returns two result sets, and I
could call this with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is
returning to my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and
give me both datasets ?


There's an ADO.NET group:
microsoft.publi c.dotnet.framew ork.adonet
--
Armin

Nov 20 '05 #3
Hey Mr Tourist Guide,

I like your new uniform, it's much nicer than that other one you were
wearing.

;-))

Regards,
Fergus
Nov 20 '05 #4
* "Fergus Cooney" <fi*****@post.c om> scripsit:
I like your new uniform, it's much nicer than that other one you were
wearing.


Please tell things like this by mail and don't annoy the community with
posts like this. Thanks!

There is nothing to add to Armin's post.

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>
Nov 20 '05 #5


It's so much easier to just answer the gentlman's question

Yes you can call one stored procedure that returns 2 or mor result sets and
the DataSet will have an array of all the rowsets returned. The following
code just that:

Dim MyData As New DataSet

Dim MyAdapter As SqlClient.SqlDa taAdapter

Dim MyCommand As SqlClient.SqlCo mmand

Dim MyConnection As New SqlClient.SqlCo nnection(Connec t)

MyCommand = New SqlClient.SqlCo mmand("TableTes t")

MyCommand.Comma ndType = CommandType.Sto redProcedure

MyCommand.Conne ction = MyConnection

MyAdapter = New SqlClient.SqlDa taAdapter(MyCom mand)

MyAdapter.Fill( MyData)

Me.DataGrid1.Da taSource = MyData

The DataGrid shows all three tables. (in this case...non-related to each
other.)

The SP looks like this:

CREATE PROCEDURE [TableTest]

AS

Select * From Persons

Select * From Businesses

Select * From Addresses
GO


Ibrahim Malluf



"AussieRule s" <so*****@nowher e.com> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?

Thanks

Nov 20 '05 #6
"Fergus Cooney" <fi*****@post.c om> schrieb
Hey Mr Tourist Guide,
Hey Mr Offtopic,
I like your new uniform, it's much nicer than that other one you were
wearing.

;-))


It's less nicer. Before I said "please". Now I'm not as friendly anymore.
--
Armin

Nov 20 '05 #7
Boy, sorry to have started all of this!

Will be a better girl next time........

(but), since you where kind enough to answer me, I have a littel question to
close this off.

It would seem that your code returns the result sets into a single VB.net
dataset. I need to either be able to access the different results sets
within the one dataset, or have each result set put into a seperate dataset
somehow, all within one round trip to the sql server

"IbrahimMal luf" <Ib*****@malluf .com> wrote in message
news:#R******** *****@TK2MSFTNG P11.phx.gbl...


It's so much easier to just answer the gentlman's question

Yes you can call one stored procedure that returns 2 or mor result sets and the DataSet will have an array of all the rowsets returned. The following
code just that:

Dim MyData As New DataSet

Dim MyAdapter As SqlClient.SqlDa taAdapter

Dim MyCommand As SqlClient.SqlCo mmand

Dim MyConnection As New SqlClient.SqlCo nnection(Connec t)

MyCommand = New SqlClient.SqlCo mmand("TableTes t")

MyCommand.Comma ndType = CommandType.Sto redProcedure

MyCommand.Conne ction = MyConnection

MyAdapter = New SqlClient.SqlDa taAdapter(MyCom mand)

MyAdapter.Fill( MyData)

Me.DataGrid1.Da taSource = MyData

The DataGrid shows all three tables. (in this case...non-related to each
other.)

The SP looks like this:

CREATE PROCEDURE [TableTest]

AS

Select * From Persons

Select * From Businesses

Select * From Addresses
GO


Ibrahim Malluf



"AussieRule s" <so*****@nowher e.com> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two

Select
Statements, and effectivly returns two result sets, and I could call this with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me both datasets ?

Thanks


Nov 20 '05 #8
Hi Armin,

Nooooo, sob, sob, I'm toooo tired..... :-(

LOL. Ok. Last try. ;-)

As it looks to me, it's gone from a command to a suggestion. That is a
major change. Perhaps I was being a bit strong with the compliment, but I
wanted to applaud that change.

Saying 'please' suggests that someone is doing something for you. Please
is an asking word. That's why I talk about Policeman. He says 'go there' and
he adds 'please' to make it polite - but it's actually a <command>.

In the command version - "This is the wrong place, please go there" - the
please can actually be a pointy stick to add emphasis. Believe it or not, that
is how it can sound, and <will> to some people (maybe just a few, maybe more).

The Tourist Guide doesn't issue a command - in fact they aren't even
making a request. There's therefore no need to say please. The Guide approach
is to <offer> something - an alternative. That's what you were doing with that
simple line. 'Please' isn't used when offering something unless it's of the
'please be nice to me and accept the gift' type. And that's not appropriate
here because it's take or leave it - your choice - and I don't mind, I'm just
letting you know.

=============== =============== ==========

This is what you said.

|| There's an ADO.NET group:
|| microsoft.publi c.dotnet.framew ork.adonet

It's sparse, but it's perfect!!

The OP is not wrong to be here. Nothing suggests that. They are not
unwelcome. Nothing suggests that either. They are not being told they <won't>
get an answer here, for nothing suggests <that>. There's no negative in it at
all, that I can see.

On the other hand, there is useful information - if they care to use it -
another place to try <in addition> to being here. It's a perfect, and neutral,
offering.
The old style.

|| This is a VB.NET group. Please turn to xyz.

One interpretation:
OP. You are in the wrong place. There is nothing for you here. I command
you to go to xyz (but politely, of course).
To me, the difference between the two is glaring. And while not everyone
will construe the above interpretation, enough will. But you won't know it
because there will be scant feedback - very, very few will tell you directly.
You'll get apologetic gestures from some. The others will just carry away
'unhappy' thoughts.

Hence ;-)) with the new version.

=============== =============== ==========

How does this sound to you?

Armin, please change the way that you speak to people.

Does that sound like I'm asking you nicely or does it come across as a
command, with every expectation of being obeyed, despite the please? It could
be either - it's ambiguous. But, whether taken as a plea, a request or a
demand, it's still me wanting you to do something <for me>. I'm not offering a
choice that is intended to be useful <to you>.
Now how about this one?

Armin, I appreciate that you are a man of few words. ;-) It might be
useful to ponder on how the fewer words you use, the more ambiguous things may
be. And to ponder on how some things come across as commands and others as
offered suggestions.

There may be as much desire on my part as in the previous version, but I'm
offering it to you to use as you please. There is of course hope in my heart,
but also hopefully it doesn't sound like a command requiring compliance. It
may sound that way, of course, but the choice of words is intended to lessen
the chances of that considerably.

=============== =============== ==========

So if you can't use 'please' to make it polite, what can you add that will
turn the volume up on the friendliness scale?

LOL, I won't give myself as an example because that volume level may blow
your speakers.

But here's a form that Jay uses.

|| Dave,
|| Have you tried asking this "down the hall" in the
|| microsoft.publi c.dotnet.framew ork.interop newsgroup?
||
|| Unfortunately I suspect most regulars in this newsgroup
|| do not do enough interop to offer much assistance.
|| ...
|| Hope this helps
|| Jay

There are several ways in which this conveys friendliness.
Use of the OP's name (even when it's a ridiculous one).

A suggestion (and most obviously so) of somewhere else to try. I
wonder what image "down the hall" conveys to you? For me it fits both my views
of this place - as a drop-in help centre and as a place of learning.

A regret (with no loss of face on Jay's part) that help will very
possibly not be forthcoming

The possibility of no help is <qualified> - no absolutes. It's true to
say that we <may> be able to help but not necessarily. It's false to say that
we can (though knowing who lives here helps), and false to state that we can't
(the right person may have just logged on).

A sign-off that says "I'm here to be helpful"

All together there is no possibility of taking this the wrong way. No
ambiguity because there are plenty of parts and words - all complementing each
other.

=============== =============== ==========

This is my last outpouring on the subject - the only thing that I could
add would be more examples of how other people have redirected.

The thing is, as noted above - you are a man of few words. Asking you to
spend your time creating a niceness for someone who you are 'getting rid of'
is not something I can do, if that is how you see it. Why should you be nice
to a pest? But do you see them that way? Somehow I don't think so. But it does
boil down to how important the feelings of the OPs are versus the effort
required to be helpful in a nice way.

I'm not suggesting that you start calling people by their name, though it
is a politeness, even a friendliness. I'm not suggesting that you sign off
with a HTH or a regards or anything. I'm not etc, the other parts. These are
all options. You may decide that none are of use to. You might think it
desirable but would take too much time.

I've given plenty of (eat more than you can carry for $1.99!!) food for
thought. You've read all my words on this in the past and I thank you for your
consideration then and now. You may not see the use in making changes... at
this time.

One possible difficulty that I anticipate is due to this having been such
a massively blown-up issue. [And not helped by the fact of a simultaneous
(completely over the top) debacle taking place]. This means that if you do
decide to make any changes in how the world sees Armin, there will be a level
of self-consciousness which may be uncomfortable. There may also be an issue
with making changes because of Fergus which may also be uncomfortable. I don't
know how you are with such things, but I'm aware that many people would feel
that way.

To make it easier, perhaps, I'm going to block messages from you for a
week or so*. That way you can stay or change without needing to think of me
watching. When I unblock your messages (after all ;-) I want to keep gaining
from your solutions) I will make no further comments on this matter - neither
compliments** nor moans.

Regards,
Mr Offtopic,

* I'll wait for any reply and then put the block on. Maybe you'll tell me it's
not necessary but I'll do it anyway just for a week or so.

** If you eventually come to realise that the 'rewards' from the OPs have
increased, I will not be the slightest bit surprised. You won't need my
appreciation.
Nov 20 '05 #9
Hi AussieRules, Steve,

Aussie, I echo Steve's words. As one of the boys throwing his toys around
the playground it is for me to say sorry to you (and, by way of this
opportunity, to others), and to regret that you have felt uncomfortable. As an
innocent bystander, please accept my apologies and my pledge that normal
service has been resumed.

Steve, thank you for your words last night and now. They were/are
appreciated. ;-) After a quick blush of shame, and a brushing off and
smoothing down of attitudes.

There <is> a longish and seriously toned off-topic message appearing in
this thread (just posted). It is nothing to do with the 'fun and games' of
this last weekend and, as it's me disguised as an adult talking to another
adult, I hope it's not seen as contradicting the words above.

Regards both, and all,
Fergus
Nov 20 '05 #10

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

Similar topics

2
13053
by: JS | last post by:
I'm trying to create a data layer and having problems returning a DataSet from my code that's in a class module. Please forgive me. I'm new to C# (VB'er). I decided to create my data layer in small steps. Right now, I'm just trying to attach a ComboBox to a dataset that's in my class module. In the class, I call a Stored Procedure. I know how to set up the connection, command, adapter, and dataset, what I'm having a problem with is,...
1
11581
by: Ahmet Karaca | last post by:
Hi. myds.Reset(); mycommand.SelectCommand.CommandText= "Select att1 from Ing as Ingredient, Pro as Product "+ "where Pro.ad='apple' and Pro.id=Ing.id"; mycommand.Fill(myds, "Product"); // Here is the problem listbox.DataSource = myds.Tables.DefaultView; // Here again listbox.DataTextField = "invid"; // Here again listbox.DataBind(); mycon.Close()
5
1771
by: D. Shane Fowlkes | last post by:
This may be a very basic question but it's something I've never done before. I've looked at a couple of my favorite sites and books and can't find an answer either. I can write a Function to return a single value. No big deal. But I want to call a Function from another Sub and the function finds and returns an entire db record. Using ASP.NET (VB!), how can this be done and how can I differentiate between the fields/columns? For...
9
2770
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and wizards. But, I have found that trying to do something "outside the norm" adds a rather large level of complexity and/or data replication. Background I have been commissioned to create a web-based application for a client. It has a formsaunthentication...
11
27429
by: Tim Frawley | last post by:
I need to return a DataRow or the Row Index in a DataSet wherein the value I am attempting to find is not a primary key. I have to do this often, more than 200 times when importing a file so it needs to be fast. Could I use a Dataview to filter for the value (which is unique) and return either the DataRow object so I can modify it and put it back into the DataSet the view is based on or somehow get the RowIndex in the DataSet that the...
4
33399
by: R.A. | last post by:
Hi, I have an web service method that accept an xml document and returns a different xml document. Based on the input xml I fill a dataset with information from a database. If the dataset has rows then I need to return those rows to the consumer. I can't tell if the consumer of the web service will use .Net or maybe java. 1) If the web service method returns a Dataset then the consumer will get information on the Dataset. What if the...
5
11686
by: samoore33 | last post by:
I use the code below to search through a DataSet: Dim t As DataTable t = result.Tables("State") Dim strExpr As String strExpr = "id = '" & theState.ToString() & "'" Dim foundRows() As DataRow foundRows = t.Select(strExpr) This of course returns foundRows. My problem is that I need to return
2
5093
by: =?Utf-8?B?VGVycnk=?= | last post by:
I have coded multiple select statements in a single stored procedure, and when I execute this procedure on SQL Server Management Express, I correctly get multiple result sets. But, if I try to add a new Data Source to to my VB 2005 project, and point to this stored procedure, the data source wizard only sees the 'first' select statement. Is there a way to load multiple tables in a DataSet from a single stored procedure with multiple...
2
5573
by: jaffar.kazi | last post by:
Hi All. This might be the wrong group to post this query, since it is ADO.NET related, but I couldn't find any ADO.NET group. My problem is that I want to write some queries, which use temp table because of their complexity. My final results are in the temp tables. How can I return a single result set from these multiple queries? Regards,
0
8840
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
8730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9215
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9064
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
8007
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...
0
5981
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
4484
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3189
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.