Header Detail Data Get Using One Stored Procedure.

HI,

I have a form using some control for displaying header data and datagridview for detail data.

1. "SELECT TOP 1 * FROM HeaderTable WHERE TrsDate = '" & FORMAT(Now.Date, "yyyy/MM/dd")
2. "SELECT * FROM DetailTable WHERE TrsNo = '" & HeaderRow("TrsNo ") & "'"

Could we do it just using one store procedure and return it into dataset?

Please see simple code I get from :http://databases.aspfaq.com/database/how-do-i-deal-with-multiple-resultsets-from-a-stored-procedure.html

How to do it using vb net like I want to do?

Thank you.




 

CREATE PROCEDURE myProc 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    SELECT columns FROM table1 
 
    SELECT columns FROM table2 
 
    SELECT columns FROM table3 
END
 
 
<% 
    ' ... 
    ' assuming valid and open object, conn 
 
    set rs = conn.execute("EXEC myProc") 
 
    ' process first resultset 
 
    if not rs.eof then 
        do while not rs.eof 
            response.write rs(0) 
            rs.movenext 
        loop 
    end if 
 
    ' move to second resultset, using nextRecordSet() 
 
    set rs = rs.nextRecordSet() 
    if not rs.eof then 
        do while not rs.eof 
            response.write rs(0) 
            rs.movenext 
        loop 
    end if 
 
    ' move to third resultset, using nextRecordSet() 
 
    set rs = rs.nextRecordSet() 
    if not rs.eof then 
        do while not rs.eof 
            response.write rs(0) 
            rs.movenext 
        loop 
    end if 
    rs.close: set rs = nothing 
    ' ... 
%>

Select allOpen in new window