none
problem in fetching data ????? RRS feed

  • Question

  • hi,
    i have a stored procedure as follows :
    -------------------------------------------------------------------------------------------------------------------------------------------------
        Create PROCEDURE SelectFrom_tblGoldDetails @ApplicationID numeric(18) AS
    BEGIN
    SET NOCOUNT ON
        Begin
             Select [LoanType], [LoanAmount], [NoofInstallments], [Description], [Answer1], [Answer2], [Answer3], [Answer4], [Answer5], [Answer6], [Answer7], [Answer8], [Answer9], [Answer10], [GuarantorID1], [GuarantorID2], [DOA], StatusId, StatusDescription, GoldAmount, ValuationDate, InstallmentAmount
                Into #temp1 From tblApplication Where ApplicationID = @ApplicationID
           
             Select [Particulars], [Qty], [Karat]   Into #temp2  From [tblGoldDetails] Where ApplicationID = @ApplicationID
        End

        Select * from #temp1
        Select * from #Temp2
    END
    ----------------------------------------------------------------------------------------------------------------------------------------------------------

    when i run this procedure in Query Analyzer it is showing 2 resultsets.
    I am getting only records from temp1 using datatable.
    How can i access records from temp2 or records from both tables ??????


    t
    hanx in advance
    Saturday, April 12, 2008 11:11 AM

Answers

  • DataAdapter is capable of loading multiple tables in the DataSet when multiple result sets are returned.

     

    You need to set up the TableMappings on the DataAdapter.  For mapping purposes first result set is known as "Table".  Subsequent result sets are known as "Table1", "Table2", "Table3", etc.

     

    Here is an example in code.  It is possible to set up TableAdapters in the properties of the Visual Studio designer as well.

     

    Code Snippet

    Using selectCommand As New SqlCommand("YourStoredProcedure", conn)

    selectCommand.CommandType = CommandType.StoredProcedure

    Using da As New SqlDataAdapter(selectCommand)

    da.TableMappings.Add("Table", "FirstTable")

    da.TableMappings.Add("Table1", "SecondTable")

    da.Fill(ds)

    End Using

    End Using

     

     

    Saturday, April 12, 2008 1:27 PM

All replies

  • DataAdapter is capable of loading multiple tables in the DataSet when multiple result sets are returned.

     

    You need to set up the TableMappings on the DataAdapter.  For mapping purposes first result set is known as "Table".  Subsequent result sets are known as "Table1", "Table2", "Table3", etc.

     

    Here is an example in code.  It is possible to set up TableAdapters in the properties of the Visual Studio designer as well.

     

    Code Snippet

    Using selectCommand As New SqlCommand("YourStoredProcedure", conn)

    selectCommand.CommandType = CommandType.StoredProcedure

    Using da As New SqlDataAdapter(selectCommand)

    da.TableMappings.Add("Table", "FirstTable")

    da.TableMappings.Add("Table1", "SecondTable")

    da.Fill(ds)

    End Using

    End Using

     

     

    Saturday, April 12, 2008 1:27 PM
  • If you are using SqlDataReader, you can call NextResult() method to advance to next result set.
    Saturday, April 12, 2008 6:28 PM