What wrong with this simple code snippit?

  • Thread starter Thread starter Steve1 via DotNetMonster.com
  • Start date Start date
S

Steve1 via DotNetMonster.com

Hi all,

Whats wrong with the below code? On every loop it seems to be jumping 3
sometimes 4 records. As you can see the SQL query is asking for all records
with no filters. I want to loop each record as I asked. Is there something
with wrong with my code? Thanks in adavnce, Steve.

string str_SQLLayoutsPound = "SELECT * FROM Layouts";.
OleDbCommand obj_SQLLayoutsPound = new OleDbCommand( str_SQLLayoutsPound,
obj_SourceConn );

OleDbDataReader obj_ReaderLayoutsPound = obj_SQLLayoutsPound.ExecuteReader();.

while( obj_ReaderLayoutsPound.Read())
{
string str_CurrentRecord = obj_ReaderLayoutsPound["Name"].ToString();
}
 
How do know it is jumping records?

Is it not bringing back all rows but in a random order due to the absence of
an ORDER BY clause?

Landers.
 
Hi Landley
I've tried using the Order By in the SQL query but still no joy. Infact the
first record in the loop was the same record as if I hadn't used the Orber By
statement. Do you know what else it could be?
 
Add the SequentialAccess flag to the executereader statement, Add order by
to the SQL statement on a predictable index, and match your select fields to
match the expected covering index. Recompute statistics of all your
indexes, drop all the temporary indexes.

If you are reading from a heap, data may not come out in the expected order
because the index that is in use orders the data in a different manner. If
you are reading from a cluster, the data will come out in the clustered
order, unless you specify an order by. Query optimizer will pick an index
that works best for the the statement, barring a good fit, it will then
create a temporary index use. The temporary indexes are not cleaned up
however and you may have to go clean them out manually.

Example

Q1 select Col1, Col2, Col3 from MyTable Order By Col1
Q2 select Col1, Col2, Col3 from MyTable Order By Col3, Col2
Q3 select Col1, Col2, Col3 from MyTable Order By Col1, Col2, Col3

Indexes:

idx1: Col1 (Unique, Not Null, Primary)
idx2: Col3, Col2 (Index Only)

Q1 Expected covering index: idx1
Q2 Expected covering index: idx2
Q3 Expected covering index: <new temporary index>

HTH
 
string str_CurrentRecord =
obj_ReaderLayoutsPound.GetString(obj_ReaderLayoutsPound.GetOrdinal("Name"));

You can do something similar for all other items.
 
Back
Top