DataGridView fail to retrieve stored procedure with temp table-CodePudding

I built a stored procedure with two input parameters, wish to fill it into the datatable that my DataGridView bind with.

My Visual Basic code to fill the datatable looks like this:

With adSearch.SelectCommand
    .Parameters("trace_txn_no").Value = // Text Value
End With

Me.adSearch.Fill(Me.DsTraceMixPack.trace_rtn_dtl)

The stored procedure looks like this:

ALTER PROCEDURE sp_RTN_DTL_Trace
    (@trace_txn_no varchar(12),
     @stock_code varchar(8) = '')
AS
BEGIN
    -- Select Data Here
END

When I type a single line for selection, such as

SELECT
    '00546599' AS mixtxnno, 
    '000000513761' AS txn_no, 
    '81050119' AS stock_code, 
    5 AS qty, 
    '20022' AS to_loc, 
    '2021-09-06' AS txn_date, 
    3 AS onhand_qty, 
    'Department A' AS txn_loc

The datagridview works fine.

But when I use a temp table, such as

CREATE TABLE #report  
(
    mixtxnno varchar(12),
    txn_no varchar(20),
    stock_code varchar(8),
    qty int,
    to_loc varchar(5),
    txn_date datetime,
    onhand_qty int,
    txn_loc varchar(12)
);

INSERT INTO #report(mixtxnno,txn_no, stock_code,qty, to_loc, txn_date, onhand_qty, txn_loc)
VALUES ('00546599', '000000513761', '81050119', 5, '20022', '2021-09-06', 3, 'Department A')

SELECT 
    mixtxnno AS mixtxnno,
    txn_no AS txn_no,
    stock_code AS stock_code,
    qty AS qty,
    to_loc AS to_loc,
    txn_date AS txn_date,
    onhand_qty AS onhand_qty,
    txn_loc AS txn_loc
FROM
    #report

The datagridview stop working, and when I trace the results properties of DataTable 'trace_rtn_dtl', it throws this message

System.Linq.SystemCore_EnumerableDebugViewEmptyException

Screenshots:

Why is this happening and How can I resolve this? I wish to do some manipulation on multiple data and return them to the DataGridView, which I chose to use temp table in my stored procedure. Thank you

CodePudding user response：

I Found the Solution

If anyone working with stored procedure not returning Record Set when using temp tables, this might helps.

Add this at the very first in the stored procedure.

SET NOCOUNT ON

What seems to happen is that empty RecordSets are generated while manipulating temporary tables, assigning variables etc.

Using "SET NOCOUNT ON" prevents these empty sets from being generated, you just need to set it off at the end of the Procedure.