DataAdapter always returns 0 or empty rows

Good day,

I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.

I thought the connection is not the problem since I got my columns right. Below is my code snippet.

Thanks!

conststring COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";SqlDataAdapter adapter =newSqlDataAdapter(COMMAND_TEXT, connection);DataTable dt =newDataTable(sometablename);

adapter.Fill(dt);

Hi,

I might be wrong, but don't you need a 'Return dt;' at the end of all this?

Hope this helps.

Paul

|||

You can not write aggregate function like count in where condition. You need to use subquery.

conststring COMMAND_TEXT =@."select distinct matchcode,recordcount from
(select distinct somefield as matchcode, count(somefield)as recordcount from filteredaccount where StateCode = 0 group by somefield ) As mytbl
whererecordcount > 1

|||Thanks for the replies. I will try your recommended SQL update, my question now is why my SQL is returning rows when run on Query Analyzer but not on the web application?|||

Hi,

The reason is that in your code you are returning nothing! Personally, I didn't find anything wrong with the original query, it worked fine in my tests. I was using an ObjectDataSource calling an object and my function was pretty much identical to yours - except it had a Return statement and mine is in VB rather than C#. However, the code is:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _&"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTabledad.Fill(dt)' Return resultsReturn dt

This worked without any issues on my system. By the way, the function returns a DataTable (i.e. Function nameOfFunction() As DataTable).

Hope this helps.

Paul

|||

Hi Paul,

I do return the datatable I just didn't put it there, sorry about that. I put logs after the adapter.fill and checked columns.count and rows.count.

columns were ok - 2 columns but rows.count is always zero.

I'm wondering if anybody has experienced the same, maybe a bug/ setting in SQL server 2005 or ADO.NET that I can check?

Thanks.

|||

Hi,

If I'm understanding correctly, the problem isn't the returning of your data table, but is in giving you a rows.count value? If so, I've just tried it on my code without any ptoblems. The adapted code looks like this:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _ &"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTable dad.Fill(dt)' Log result & Return log.Write("Columns & Rows","Test Grid","Columns= " & dt.Columns.Count.ToString &" Rows= " & dt.Rows.Count.ToString, 0,"Test","test")Return dt

If you can't get this to work (I'm sure your logging code won't be the same as mine, but hopefully you get the idea), you could try writing to your log file from the OnDataBound event. Strangely, if you have AutoGenerateColumns set to true, the column count returns zero here, but the row count is OK. This problem is fixed by ensuring you have set out the columns in your GridView.

I hope this (at last!) helps sort your problem. If not, I suggest you post all of the relevant code.

Cheers,

Paul

|||

Thanks Paul. The problem is my datatable when filled always has zero rows. While the SQL query when run on Query Analyzer returns a number of rows.

I'm sure that there's nothing wrong with my logging.

Anyway, I kind of implemented a workaround, my SQL selects from a view.. what I did was to select directly from the table instead. Hence, i believe my SQL view is somewhat corrupt or problematic.

Thanks again for the replies.

What bothers me though is ADO.NET does not fire an exception or anything... it just does not fills the datatable...

|||

Hi,

Do you mean that you can't get the return with a number of rows by selecting from the view in QA ? Try to select from the table directly and fill your datatable with the DataAdapter , then check if you can get a number of rows. If you can, the problem should be caused by the view you use. So what you should do is to rebuild the view and check the selecting in QA first. My personal view is that there should not be anything wrong with your DataAdapter or DataTable. Thanks.

|||

Yes that's what I did I select directly from the table rather than from the view. It is working fine now.

My SQL select from view run on Query Analyzer works fine returns a number of rows

BUT my SQL select from view run on my application always returns 0 rows.

What's strange is that I didn't get any errors when filling up my datatable, rows are just zeroed out.

|||

Hi,

i. You may open your SQL Profiler and start a trace. In the meantime, pls run your application and show us the textdata in the profiler.

ii. Pls show us the whole code and including your view in your database.

Thanks.

|||

Thanks Michael appreciate your suggestions... below is the whole method I'll try the SQL Profiler... what filter should I use to catch the sql statement?

public

DataTable RetrieveAllGroups(int entityTypeCode)

{

const

string COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1 order by recordcount desc, matchcode asc";

SqlConnection connection = GetDataConnection();

if

(connection !=null)

{

SqlDataAdapter adapter =new SqlDataAdapter(COMMAND_TEXT, connection);

DataTable dt =

new DataTable(entityTableName);

adapter.Fill(dt);

return dt;

}

}