What wrong with this simple code snippit?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve1 - 29 Sep 2005 11:32 GMT

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();
}

Reply to this Message

Landley - 29 Sep 2005 13:21 GMT

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 all,
>
[quoted text clipped - 13 lines]
> string str_CurrentRecord = obj_ReaderLayoutsPound["Name"].ToString();
> }

Reply to this Message

Steve1 - 29 Sep 2005 14:09 GMT

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?

Reply to this Message

AMDRIT - 29 Sep 2005 14:52 GMT

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

> 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?

Reply to this Message

Darren Kopp - 30 Sep 2005 15:13 GMT

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

You can do something similar for all other items.

Reply to this Message

What wrong with this simple code snippit?

Free Magazines