Dear friend,

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

SELECT Merek,COUNT(Merek) AS 'QtyMerek'
FROM SERTIFIKATUJITIPE A

SELECT Merek, Tipe, COUNT(Tipe) AS 'QtyTipe'
FROM SERTIFIKATUJITIPE A
.............................

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

Tks,
hendy

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.

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.

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

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