none
myReader = myCommand.ExecuteReader() returns no rows from stored procedure, but should. Only get column headings.

    Question

  • myReader = myCommand.ExecuteReader() returns no rows from stored procedure, but should. Only get column headings.

    I get no error, and I get column headings, but myReader.HasRows == false.

    Do I need a special setting on a reader to accommodate dynamic row data types or something?

    Thanks,


    -------------- Bill Ross
    Wednesday, March 30, 2011 9:02 PM

Answers

  • Okay, the problem ACTUALLY was that I have values in my combobox.text, and I was looking at selecteditem.
    -------------- Bill Ross
    Thursday, March 31, 2011 1:21 PM

All replies

  • Can you show us your code and your sp?
    John Grove, MCC - Senior Software Engineer
    Wednesday, March 30, 2011 9:04 PM
  • /*
    usage:
    exec dbo.PEC_EDI_Claims_Search
    @tin = null,
    @NPI = '1629262274',
    @MemberID = null,
    @DOS = NULL,
    @MemberLastName = ''
    */
    ALTER PROCEDURE [dbo].[PEC_EDI_Claims_Search]
    (
    @tin VARCHAR(10) = '',
    @NPI VARCHAR(10) = '',
    @MemberID VARCHAR(30) = '',
    @DOS VARCHAR(12) = '',
    @MemberLastName VARCHAR(50) = ''
    )
    AS 
    BEGIN
    DECLARE @QueryString VARCHAR(8000);
    DECLARE @WhereClause VARCHAR(8000);
    DECLARE @OrderByClause VARCHAR(1000);
    SET @OrderByClause = ' ORDER BY peic.memberID, peic.DateOfService ';
    SET @QueryString = 'SELECT peic.billing_provider_TIN, peic.billing_provider_NPI, peij.run_id, peic.seq, (CASE WHEN peic.[status] = 0 THEN ''Accepted'' WHEN peic.[status] = 1 THEN ''Pended'' ELSE ''Rejected'' END) AS [Status], peic.ProviderClaimID, peic.ClearinghouseClaimID, peij.Run_date, peij.InputFileName, peic.claim_ud, cs.claim_status_ud, peic.providerlastname, peic.providerfirstname, peic.DateOfService, peic.PrimaryDiagnosis, peic.MemberID, peic.MemberLastName, peic.MemberFirstName, peic.DateOfBirth, e.eligibility_ud, peic.totalCharges, peic.reject_reason, peic.pend_reason, peic.acceptedAmount, peic.rejectedAmount, poc.fax FROM dbo.pec_edi_837_inbound_claims AS peic LEFT OUTER JOIN dbo.claim_submitter_file AS csf ON peic.claim_submitter_file_id = csf.claim_submitter_file_id LEFT OUTER JOIN dbo.pec_edi_837_inbound_jobs AS peij ON peic.run_id = peij.Run_id LEFT OUTER JOIN dbo.claim AS c ON peic.claim_id = c.claim_id LEFT OUTER JOIN dbo.claim_procedure AS cp ON c.claim_id = cp.claim_id LEFT OUTER JOIN dbo.claim_status AS cs ON c.claim_status_id = cs.claim_status_id LEFT OUTER JOIN dbo.claim_diagnosis AS cd ON c.claim_id = cd.claim_id AND sequence = 1 LEFT OUTER JOIN dbo.member AS m ON c.member_id = m.member_id LEFT OUTER JOIN dbo.eligibility AS e ON c.eligibility_id = e.eligibility_id LEFT OUTER JOIN dbo.practice_office AS po ON CAST(peic.billing_provider_NPI AS VARCHAR(10)) = po.practice_office_ud LEFT OUTER JOIN dbo.practice_office_contact AS poc ON po.practice_office_id = poc.practice_office_id AND poc.primary_contact = 1 ';
    SET @WhereClause = ' Where 1 = 1 '
    IF (ISNULL(@tin,'') > '')
    begin
    SET @WhereClause = @WhereClause + ' and peic.billing_provider_tin = ''' + @tin + '''';
    END;
    IF (ISNULL(@NPI,'') > '')
    begin
    SET @WhereClause = @WhereClause + ' and peic.billing_provider_NPI = ' + @NPI;
    END;
    IF (ISNULL(@MemberID,'') > '')
    begin
    SET @WhereClause = @WhereClause + ' and peic.memberID like ''' + @MemberID + '%' + '''';
    END;
    IF (ISNULL(@DOS,'') > '')
    begin
    SET @WhereClause = @WhereClause + ' and peic.DateOfService = ''' + @DOS + '''';
    END;
    IF (ISNULL(@MemberLastName,'') > '')
    begin
    SET @WhereClause = @WhereClause + ' and peic.MemberLastName like ''' + @MemberLastName + '%' + '''';
    END;
    SET @QueryString = @QueryString + @WhereClause + @OrderByClause;
    PRINT @QueryString;
    EXEC(@QueryString);
    END;
    
    //Here's the C#:
    myCommand.CommandType = System.Data.CommandType.StoredProcedure;
    myCommand.CommandText = "PEC_EDI_Claims_Search";
    myCommand.Parameters.Clear();
    myCommand.Parameters.AddWithValue("@tin", cBoxBillingProviderTIN.SelectedItem);
    myCommand.Parameters.AddWithValue("@NPI", cboxNPI.SelectedItem);
    myCommand.Parameters.AddWithValue("@MemberID", cboxMemberID.SelectedItem);
    myCommand.Parameters.AddWithValue("@DOS", sTempDate);
    myCommand.Parameters.AddWithValue("@MemberLastName", cboxMemberLastName.SelectedValue);
    myCommand.CommandTimeout = 0;
    myReader = myCommand.ExecuteReader();
    if (myReader.IsClosed)
    {
    MessageBox.Show("Reader is closed.");
    };
    if (myReader.HasRows == false)
    {
    MessageBox.Show("No Claims Found. ");
    };
     
    ?
    ?Result is always "No Claims Found."
    ?
    
    

    -------------- Bill Ross
    Thursday, March 31, 2011 12:25 PM
  • Note: I get results as expected if I execute the sample.
    -------------- Bill Ross
    Thursday, March 31, 2011 12:27 PM
  • I figured it out...

    I was not handling the null date correctly in the version prior to the one one I posted. This one returns rows (albeit, it is not filtering all of the fields correctly... another, unrelated bug).

    Thanks anyway!


    -------------- Bill Ross
    Thursday, March 31, 2011 12:50 PM
  • Okay, the problem ACTUALLY was that I have values in my combobox.text, and I was looking at selecteditem.
    -------------- Bill Ross
    Thursday, March 31, 2011 1:21 PM
  • So is it salved? Let us know.
    Mitja
    Thursday, March 31, 2011 2:02 PM
  • Yes, I was not obtaining my parameters correctly from the combo boxes!
    -------------- Bill Ross
    Thursday, March 31, 2011 2:03 PM
  • Dynamically building a sql query this way, even in a stored procedure, is vulnerable to a sql injection attack.  Be sure to sanitize your inputs very carefully.
    Thursday, March 31, 2011 8:27 PM