SqlDataReader does not return data for a query

Question

Need some SqlDataReader pros here :)

I have the code working for other queries with no issues, however the following query returns nothing thru READER, however returns 4 rows executing thru other tools (Visual Studio) So the query does work. I'm guessing there's a SqlDataReader issue related to parameters?

Thank you!

Code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    var comm = new SqlCommand(Sql.Sql_Get_Expected(), connection);

    var reader = comm.ExecuteReader();

    var table = new DataTable();
    table.Load(reader);

    return table; 
}

Query:

declare @BATCHNUMBER_RUNTIME CHAR(5)
declare @BATCHNUMBER_CONTROL CHAR(5)
declare @CLIENTKEY VARCHAR(48)
declare @REPLACE_CRLF_WITH VARCHAR(48)

Set @BATCHNUMBER_RUNTIME = (SELECT TOP 1 BATCHNUMBER FROM [dbo].[IX_BATCH] where plugin_id = 'MT940' order by BATCHNUMBER desc)
Set @BATCHNUMBER_CONTROL = ""
Set @CLIENTKEY = (SELECT top 1 clientkey FROM [report].[Actual Balance] order by [Transaction INC] desc)
Set @REPLACE_CRLF_WITH = ' \r\l '

(select 'A' as 'Type', [Bank Code],[Transaction Currency],[Transaction Currency - Grouping],[Transaction Currency Description],[Transaction Amount],replace([Description], char(13)+char(10), @REPLACE_CRLF_WITH) as 'Balance Description',[As Of date Modifier],[As Of date Modifier Description],[Reason Code],[Reason Code - Grouping],[Reason Code Description],[User Reference],[ACT_MAIN].Status as 'TRN_BXXX.Status',replace([ACT_MAIN].Description, char(13)+char(10), @REPLACE_CRLF_WITH) as 'Description1',[Creation Module],[Post To Worksheet],[Wires In Status],[Value Date],[Transaction Date],[Process Date],[Local Amount],[Item Type],[In-House Account],[Float 1 Date],[Float 1 Amount],[Float 1 User Code INC],[Float 2 Date],[Float 2 Amount],[Float 2 User Code INC],[Comments],[User Code INC],[User Code],[User Code - Grouping],[User Code Description],[Bank INC],[Bank ID],[Bank - Grouping],[Bank Name],[Account INC],[Account ID],[Account - Grouping],[Account Name],[Account Number],[Account Currency],[In-House Account ID],[In-House Account - Grouping],[In-House Account Name],[In-House Account Number],[Balance Model ID],[Balance Model - Grouping],[Balance Model Description],[FX Rate],[FX Rate Scenario ID],[FX Rate Source ID],[FX Rate Scenario Description],[FX Rate Source Description],[GL Offset Assignment],[GL Status Pre Reco],[GL Status Post Reco],[Reco Number],[Reco Date],[Import Plugin ID],[Transaction Type for GL],[TA Assignment Batch],[TA Created This Record],[Credit Local Amount],[Debit Local Amount],[Bank Code Item Type],[Transaction Base Rate],[Transaction Base Amount],[Local Base Rate],[Local Base Amount],[Base Currency],
                        ACT_REF1.[Reference Type ID] as 'Reference BS20', ACT_REF1.[Reference Value] as 'Reference BS20 Value',
                        ACT_REF2.[Reference Type ID] as 'Reference BS21', ACT_REF2.[Reference Value] as 'Reference BS21 Value',
                        ACT_REF3.[Reference Type ID] as 'Reference BS28', ACT_REF3.[Reference Value] as 'Reference BS28 Value',
                        ACT_REF4.[Reference Type ID] as 'Reference BS61', ACT_REF4.[Reference Value] as 'Reference BS61 Value',
                        ACT_REF5.[Reference Type ID] as 'Reference BRCR', ACT_REF5.[Reference Value] as 'Reference BRCR Value', 
                        ACT_REF6.[Reference Type ID] as 'Reference BRTN', ACT_REF6.[Reference Value] as 'Reference BRTN Value',
                        ACT_HIST.[Sequence], ACT_HIST.[History Stamp Type], ACT_HIST.[History Stamp Description], ACT_HIST.[History Stamp - Grouping], ACT_HIST.[Status] as 'TRN_BXXX_HISTORY.Status', ACT_HIST.[Status Description], ACT_HIST.[Operator ID], ACT_HIST.[Operator Name], ACT_HIST.[Operator - Grouping], ACT_HIST.[History Date], ACT_HIST.[History Time], ACT_HIST.[History Description], ACT_HIST.[Batch Number], ACT_HIST.[Batch Plugin ID], ACT_HIST.[Batch Option ID], ACT_HIST.[Batch File Name], ACT_HIST.[Batch Plugin Description], ACT_HIST.[Internal/External]
from 
    report.[actual balance] ACT_MAIN
left join 
    report.[Actual Balance Reference] ACT_REF1 on ACT_REF1.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF1.[Reference Type ID] = 'BS20' and act_ref1.clientkey = @clientkey
left join 
    report.[Actual Balance Reference] ACT_REF2 on ACT_REF2.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF2.[Reference Type ID] = 'BS21' and act_ref2.clientkey = @clientkey
left join 
    report.[Actual Balance Reference] ACT_REF3 on ACT_REF3.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF3.[Reference Type ID] = 'BS28' and act_ref3.clientkey = @clientkey 
left join 
    report.[Actual Balance Reference] ACT_REF4 on ACT_REF4.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF4.[Reference Type ID] = 'BS61' and act_ref4.clientkey = @clientkey
left join 
    report.[Actual Balance Reference] ACT_REF5 on ACT_REF5.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF5.[Reference Type ID] = 'BRCR' and act_ref5.clientkey = @clientkey
left join 
    report.[Actual Balance Reference] ACT_REF6 on ACT_REF6.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF6.[Reference Type ID] = 'BRTN' and act_ref6.clientkey = @clientkey
left join 
    report.[Actual Balance History] ACT_HIST on ACT_HIST.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_HIST.clientkey = @clientkey
where 
    ACT_MAIN.clientkey = @CLIENTKEY
    and [import batch] = @BATCHNUMBER_RUNTIME)

union

(select 'E' as 'Type', [Bank Code],[Transaction Currency],[Transaction Currency - Grouping],[Transaction Currency Description],[Transaction Amount],replace([Description], char(13) +char(10), @REPLACE_CRLF_WITH) as 'Balance Description',[As Of date Modifier],[As Of date Modifier Description],[Reason Code],[Reason Code - Grouping],[Reason Code Description],[User Reference],[ACT_MAIN].Status as 'TRN_BXXX.Status',replace([ACT_MAIN].Description, char(13) +char(10), @REPLACE_CRLF_WITH) as 'Description1',[Creation Module],[Post To Worksheet],[Wires In Status],[Value Date],[Transaction Date],[Process Date],[Local Amount],[Item Type],[In-House Account],[Float 1 Date],[Float 1 Amount],[Float 1 User Code INC],[Float 2 Date],[Float 2 Amount],[Float 2 User Code INC],[Comments],[User Code INC],[User Code],[User Code - Grouping],[User Code Description],[Bank INC],[Bank ID],[Bank - Grouping],[Bank Name],[Account INC],[Account ID],[Account - Grouping],[Account Name],[Account Number],[Account Currency],[In-House Account ID],[In-House Account - Grouping],[In-House Account Name],[In-House Account Number],[Balance Model ID],[Balance Model - Grouping],[Balance Model Description],[FX Rate],[FX Rate Scenario ID],[FX Rate Source ID],[FX Rate Scenario Description],[FX Rate Source Description],[GL Offset Assignment],[GL Status Pre Reco],[GL Status Post Reco],[Reco Number],[Reco Date],[Import Plugin ID],[Transaction Type for GL],[TA Assignment Batch],[TA Created This Record],[Credit Local Amount],[Debit Local Amount],[Bank Code Item Type],[Transaction Base Rate],[Transaction Base Amount],[Local Base Rate],[Local Base Amount],[Base Currency],
                        ACT_REF1.[Reference Type ID] as 'Reference BS20', ACT_REF1.[Reference Value] as 'Reference BS20 Value',
                        ACT_REF2.[Reference Type ID] as 'Reference BS21', ACT_REF2.[Reference Value] as 'Reference BS21 Value',
                        ACT_REF3.[Reference Type ID] as 'Reference BS28', ACT_REF3.[Reference Value] as 'Reference BS28 Value',
                        ACT_REF4.[Reference Type ID] as 'Reference BS61', ACT_REF4.[Reference Value] as 'Reference BS61 Value',
                        ACT_REF5.[Reference Type ID] as 'Reference BRCR', ACT_REF5.[Reference Value] as 'Reference BRCR Value', 
                        ACT_REF6.[Reference Type ID] as 'Reference BRTN', ACT_REF6.[Reference Value] as 'Reference BRTN Value',
                        ACT_HIST.[Sequence], ACT_HIST.[History Stamp Type], ACT_HIST.[History Stamp Description], ACT_HIST.[History Stamp - Grouping], ACT_HIST.[Status] as 'TRN_BXXX_HISTORY.Status', ACT_HIST.[Status Description], ACT_HIST.[Operator ID], ACT_HIST.[Operator Name], ACT_HIST.[Operator - Grouping], ACT_HIST.[History Date], ACT_HIST.[History Time], ACT_HIST.[History Description], ACT_HIST.[Batch Number], ACT_HIST.[Batch Plugin ID], ACT_HIST.[Batch Option ID], ACT_HIST.[Batch File Name], ACT_HIST.[Batch Plugin Description], ACT_HIST.[Internal/External]
                        from report.[Estimated Balance] ACT_MAIN
                        left join report.[Estimated Balance Reference] ACT_REF1 on ACT_REF1.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF1.[Reference Type ID] = 'BS20' and act_ref1.clientkey = @clientkey
                        left join report.[Estimated Balance Reference] ACT_REF2 on ACT_REF2.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF2.[Reference Type ID] = 'BS21' and act_ref2.clientkey = @clientkey
                        left join report.[Estimated Balance Reference] ACT_REF3 on ACT_REF3.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF3.[Reference Type ID] = 'BS28' and act_ref3.clientkey = @clientkey    
                        left join report.[Estimated Balance Reference] ACT_REF4 on ACT_REF4.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF4.[Reference Type ID] = 'BS61' and act_ref4.clientkey = @clientkey
                        left join report.[Estimated Balance Reference] ACT_REF5 on ACT_REF5.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF5.[Reference Type ID] = 'BRCR' and act_ref5.clientkey = @clientkey
                        left join report.[Estimated Balance Reference] ACT_REF6 on ACT_REF6.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_REF6.[Reference Type ID] = 'BRTN' and act_ref6.clientkey = @clientkey
                        left join report.[Estimated Balance History] ACT_HIST on ACT_HIST.[Transaction INC] = ACT_MAIN.[Transaction INC] and ACT_HIST.clientkey = @clientkey
                    where ACT_MAIN.clientkey = @CLIENTKEY
                        and [import batch] = @BATCHNUMBER_RUNTIME
                    )";

Executing queries through SQL Server Management Studio can sometimes give different results from ADO.NET, often because of differences in settings such as ANSI NULLS. This may explain the fact that other tools return a different result. You could try adding SET statements to your ADO.NET query to match the default settings in SQL Server Management Studio (Tools/Options/Query Execution/SQL Server). — to StackOverflow
– to StackOverflow, Commented Apr 12, 2017 at 18:51

LONG · Accepted Answer · 2017-04-12 18:54:52Z

3

Try this below:

   using (SqlConnection sqlCon = new SqlConnection("your_Sql_Connection"))
                {
                    sqlCon.Open();
                    using (SqlCommand comm= sqlCon.CreateCommand())
                    {
                        comm.CommandText = "Your_stored_procedure_name"; //Create a store procedure for your query
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.Add(new SqlParameter("BATCHNUMBER_RUNTIME", value1)); //parse the actual value to value1
                        comm.Parameters.Add(new SqlParameter("BATCHNUMBER_CONTROL ", value));//parse the actual value to value2
                        comm.Parameters.Add(new SqlParameter("CLIENTKEY", value));//parse the actual value to value3
                        comm.Parameters.Add(new SqlParameter("REPLACE_CRLF_WITH", value));//parse the actual value to value4

                        SqlDataReader reader = comm.ExecuteReader();
                        var table = new DataTable();
                        table.Load(reader);
                        return table;   
                    }
                   sqlCon.Close();
                }

edited Apr 12, 2017 at 18:54

answered Apr 12, 2017 at 18:15

LONG

4,6302 gold badges21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ylerner Over a year ago

Thank you. I dont really have permissions to create store proc. I will see what I can do here

ylerner Over a year ago

Added store proc and this worked like a charm. Thank you!

LONG Over a year ago

Glad to help:) @YuryLerner

Collectives™ on Stack Overflow

SqlDataReader does not return data for a query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related