Dataset returning ZERO results

Question

I am trying to fill in a combobox on my winform app from the database. I know there is info in the DB. I know the SP works. It returns the correct ColumnNames. But the DataSet itself is empty? Help!?!?

Call from my form-->

cboDiagnosisDescription.Properties.DataSource = myDiagnosis.RetrieveDiagnosisCodes();

The RetrieveDiagnosisCodes -->

public DataSet RetrieveDiagnosisCodes()
    {
        string tableName = "tblDiagnosisCues";
        string strSQL = null;
        DataSet ds = new DataSet(tableName);
        SqlConnection cnn = new SqlConnection(Settings.Default.CMOSQLConn);
        //strSQL = "select * from " & tableName & " where effectivedate <= getdate() and (termdate >= getdate() or termdate is null)"
        strSQL = "select tblDiagnosisCues.*, tblDiagnosisCategory.Description as CategoryDesc, tblDiagnosisSubCategory.Description as SubCategoryDesc " + "FROM dbo.tblDiagnosisCategory INNER JOIN " + "dbo.tblDiagnosisSubCategory ON dbo.tblDiagnosisCategory.Category = dbo.tblDiagnosisSubCategory.Category INNER JOIN " + "dbo.tblDiagnosisCues ON dbo.tblDiagnosisSubCategory.SubCategory = dbo.tblDiagnosisCues.SubCategoryID " + "where effectivedate <= getdate() and (termdate >= getdate() or termdate is null) order by tblDiagnosisCues.Description";
        SqlCommand cmd = new SqlCommand(strSQL, cnn) {CommandType = CommandType.Text};
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        try
        {
            //cnn.Open();
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            cmd.Dispose();
            da.Dispose();
            //ds.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
        return ds;
    }

The reason I know it is returning the correct column names is that I tried the following with a DevExpress LookUpEdit box and it populates the correct columns from the DB -->

            var myDiagnosis = new Diagnosis();
        var ds = myDiagnosis.RetrieveDiagnosisCodes();
        lkuDiagnosis.Properties.DataSource = ds;
        lkuDiagnosis.Properties.PopulateColumns();
        lkuDiagnosis.Properties.DisplayMember = ds.Tables[0].Columns[1].ColumnName;
        lkuDiagnosis.Properties.ValueMember = ds.Tables[0].Columns[0].ColumnName;

Ideas? Mainly, I don't even know how to proceed tracking this down...How to debug it?

EDIT 1

Based on a comment I ran the following SQL by itself and it returned 650 results...

select tblDiagnosisCues.*, 
 tblDiagnosisCategory.Description as CategoryDesc, 
 tblDiagnosisSubCategory.Description as SubCategoryDesc 
FROM dbo.tblDiagnosisCategory 
    INNER JOIN dbo.tblDiagnosisSubCategory 
    ON dbo.tblDiagnosisCategory.Category = dbo.tblDiagnosisSubCategory.Category 
     INNER JOIN dbo.tblDiagnosisCues ON dbo.tblDiagnosisSubCategory.SubCategory = dbo.tblDiagnosisCues.SubCategoryID 
where effectivedate <= getdate() and (termdate >= getdate() or termdate is null) order by tblDiagnosisCues.Description

Answer 1

If the data is properly binding to another control, it indicates that there is an issue with the databinding process. What does your databinding setup look like for the combobox in question? Are all the column names properly spelled and setup?

Answer 2

//cnn.open();

...

//ds.dispose();

There is no need to specify a table name in the dataset constructor. The fill method will add a table. Also no need to open the connection since the sqldataadapter will open and close the connection for you. Also, I prefer to return a datatable as opposed to dataset with one table.

The code could be refactored to the following...of coure add the try catch if you want to log the exception.

public DataTable RetrieveDiagnosisCodes()
{
    //string tableName = "tblDiagnosisCues";
    DataSet ds = new DataSet();
    Datatable dt = null;
    //strSQL = "select * from " & tableName & " where effectivedate <= getdate() and (termdate >= getdate() or termdate is null)"
    string strSQL = "select tblDiagnosisCues.*, tblDiagnosisCategory.Description as CategoryDesc, tblDiagnosisSubCategory.Description as SubCategoryDesc " + "FROM dbo.tblDiagnosisCategory INNER JOIN " + "dbo.tblDiagnosisSubCategory ON dbo.tblDiagnosisCategory.Category = dbo.tblDiagnosisSubCategory.Category INNER JOIN " + "dbo.tblDiagnosisCues ON dbo.tblDiagnosisSubCategory.SubCategory = dbo.tblDiagnosisCues.SubCategoryID " + "where effectivedate <= getdate() and (termdate >= getdate() or termdate is null) order by tblDiagnosisCues.Description";

    using(SqlDataAdapter da = new SqlDataAdapter(strSQL, Settings.Default.CMOSQLConn))
    {
      da.Fill(ds);
    }
    if (ds.Tables.Count > 0)
    {
      dt = ds.Tables[0];
    }

    return dt;
}