p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB.NET 2002/2003 Basics (http://p2p.wrox.com/forumdisplay.php?f=76)
-   -   how to capture 2 SQL queries into 2 datatables (http://p2p.wrox.com/showthread.php?t=62117)

hendyhanusin September 17th, 2007 06:21 AM

how to capture 2 SQL queries into 2 datatables
Dear friend,

i create one stored procedure with 2 sql statement like below :

SELECT Merek,COUNT(Merek) AS 'QtyMerek'

SELECT Merek, Tipe, COUNT(Tipe) AS 'QtyTipe'

in vb .net side, how we can capture these queries into 2 datatables ?


Chris Van Duin January 17th, 2008 06:42 PM

I think that it will only get the results of the last query. What you might need to do is use the dateset object to have two command objects..one for each query. A dataset can contain multiple tables. You usually only use the first one, eg, myDataSet.Tables(0). By performing two queries on the same dataset you can access them individually. Eg myDataSet.Tables(0) for the first table and myDataSet.Tables(1) for the second resultset. If you gave the tables a name in the command object, eg, "Orders" and "Products", you can use the name rather than the index number, eg, myDataSet("Products"). By having the two tables within a dataset object you can also create a relationship between them based on a key, eg, ProductId.

Chris Van Duin January 17th, 2008 06:54 PM

Also, if you use this method, you can use the same connection object so there shouldn't be much of a time penalty performing the two queries. Most of the time is actually taken up connecting to a database.

Remember to set all database objects (connection, command and datareader)to nothing when finished otherwise you will consume resources. You usually do this as the last step after you populate your dataset, eg, in the Finally step if you wrap your code in a try/cathc block.

eins March 25th, 2008 11:47 PM

Since you mentioned that you are using Stored Procedure. You may use cursor to pass the result back, right? If not, try to use it. It could allow you to define two cursors and declare them as in/out parameters.

Eins Tsang

BrianWren March 26th, 2008 09:31 AM

I thought that in a stored procedure you needed to use SELECT queries as

      SELECT <flds>
      FROM  <tbl>
      WHERE  <conditions>
      INTO  <a cursor, or variable>

All times are GMT -4. The time now is 04:22 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.