none
DataTable or SqlDataAdapter loses rows RRS feed

  • Question

  •  

    I have some extreamly straight forward code:

     

    Code Snippet

    string cmdText = "db_build_GetTable";

    DataTable objDT = new DataTable(cmdText);

    SqlConnection conn = new SqlConnection(GetConnectionString());

    SqlDataAdapter objDA = null;

    SqlCommand objCmd = new SqlCommand(cmdText, conn);

    objCmd.CommandType = CommandType.StoredProcedure;

    objCmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

    objDA = new SqlDataAdapter(objCmd);

    objDA.Fill(objDT);

     

     

    When doing a profiling on the database I see that 79 rows are returned, but the datatable only contains 13 rows. And it is not the 13 first or last rows. It's rows in the middle of the result.

     

    I wrote a where clause (just to test) selecting one of the row wich was never returned, then the row count returned was 0.

     

    Help.

    Wednesday, October 1, 2008 9:48 AM

All replies

  • What happens if you execute same SQL statement from SQL Server Management Studio? How many rows do you get in this case? How do you populate @RowCount inside of your stored procedure? I believe that @RowCount is not populated properly.
    Wednesday, October 1, 2008 9:59 AM
    Moderator
  • Thanks for your reply.

     

    When executing the stored procedure from the Query Analyzer I get all the rows from the select.

     

    if I remove the row

     

    Code Snippet
    objCmd.Parameters.Add(
    "@RowCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

     

     

     

    I get the same result.

     

    The select in the stored procedure looks like this:

     

    Code Snippet
    SELECT [TABLE_NAME]
    FROM INFORMATION_SCHEMA.TABLES
    WHERE [TABLE_TYPE] = 'BASE TABLE'

     

     

     

    Is it because I do a select from a system table it dosn't work? If I change to

     

    Code Snippet
    select fname as 'TABLE_NAME' from [Persons]

     

     

     

    I get all the rows.

     

     

     

    Wednesday, October 1, 2008 12:22 PM