Expert help needed 
Author Message
 Expert help needed

I tried to optimize my select statement and wound up changing records
that I did not expect to change.
Is this a BUG of an ADO FEATURE???

 #1) sql_cmd = "SELECT * FROM time_log WHERE time_log.emp_id = 11"
 #2) sql_cmd = "SELECT stop_time FROM time_log WHERE time_log.emp_id =
11"
  #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log WHERE
time_log.emp_id = 11"  

prod_time.CursorType = adOpenStatic
prod_time.LockType = adLockPessimistic
prod_time.Open sql_cmd, conn_tc, , , adCmdText

If Not prod_time.EOF Then
      rc = prod_time.RecordCount
      prod_time!stop_time = "Test"
      prod_time.Update
      prod_time.Close
End If

a) In both cases (sql_cmd 1 & 2) the record count = 2
b) Running the program using sql_cmd #1 updates 1 record.
c) Running the program using sql_cmd #2 updates ALL records in the
database!!!
d) Running the program using sql_cmd #3 updates 2 record.
e) Running with sql_cmd #1 Then sql_cmd #2 only has the one record
revised!!

In my mind, I am not looping through the recordset so I only expect
one record to be updated. In any event, if the recordcount = 2, then
why would ALL the records in the database be updated?
This is all very counter-intuitive.

Another question: Do I need emp_id in the SELECT if the only reference
to it is the WHERE in the SELECT statement?

Please help this is causing some major programming concerns!

Gary...

-----------------------------------------
Gary Kahrau
VP Technology - Stellex Monitor Aerospace

-----------------------------------------



Mon, 28 Jun 2004 07:05:17 GMT
 Expert help needed

I can answer the easy part... you do NOT need emp_id in the select
statement.

Everything else looks ok...  let me give it a try.

What's your back end?


Quote:
> I tried to optimize my select statement and wound up changing records
> that I did not expect to change.
> Is this a BUG of an ADO FEATURE???

>  #1) sql_cmd = "SELECT * FROM time_log WHERE time_log.emp_id = 11"
>  #2) sql_cmd = "SELECT stop_time FROM time_log WHERE time_log.emp_id =
> 11"
>   #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log WHERE
> time_log.emp_id = 11"

> prod_time.CursorType = adOpenStatic
> prod_time.LockType = adLockPessimistic
> prod_time.Open sql_cmd, conn_tc, , , adCmdText

> If Not prod_time.EOF Then
>       rc = prod_time.RecordCount
>       prod_time!stop_time = "Test"
>       prod_time.Update
>       prod_time.Close
> End If

> a) In both cases (sql_cmd 1 & 2) the record count = 2
> b) Running the program using sql_cmd #1 updates 1 record.
> c) Running the program using sql_cmd #2 updates ALL records in the
> database!!!
> d) Running the program using sql_cmd #3 updates 2 record.
> e) Running with sql_cmd #1 Then sql_cmd #2 only has the one record
> revised!!

> In my mind, I am not looping through the recordset so I only expect
> one record to be updated. In any event, if the recordcount = 2, then
> why would ALL the records in the database be updated?
> This is all very counter-intuitive.

> Another question: Do I need emp_id in the SELECT if the only reference
> to it is the WHERE in the SELECT statement?

> Please help this is causing some major programming concerns!

> Gary...

> -----------------------------------------
> Gary Kahrau
> VP Technology - Stellex Monitor Aerospace

> -----------------------------------------



Mon, 28 Jun 2004 07:22:51 GMT
 Expert help needed
What database are you using?  (MS-Sql, Oracle, Access...)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Please reply to this newsgroup.  If you wish to e-mail me, e-mail it to

e-mail address.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 28 Jun 2004 07:23:45 GMT
 Expert help needed
I forgot to give those details in the original message.

The server database is Progress 9.1C running on a Solaris server.
Datadirect-Technologies (Merant) is the ADO to Progress Provider.
It is called Sequel Link ADO.
The Progress ODBC driver has to many bugs when working with VB and
ADO.

Gary...

Quote:

>I can answer the easy part... you do NOT need emp_id in the select
>statement.

>Everything else looks ok...  let me give it a try.

>What's your back end?



>> I tried to optimize my select statement and wound up changing records
>> that I did not expect to change.
>> Is this a BUG of an ADO FEATURE???

>>  #1) sql_cmd = "SELECT * FROM time_log WHERE time_log.emp_id = 11"
>>  #2) sql_cmd = "SELECT stop_time FROM time_log WHERE time_log.emp_id =
>> 11"
>>   #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log WHERE
>> time_log.emp_id = 11"

>> prod_time.CursorType = adOpenStatic
>> prod_time.LockType = adLockPessimistic
>> prod_time.Open sql_cmd, conn_tc, , , adCmdText

>> If Not prod_time.EOF Then
>>       rc = prod_time.RecordCount
>>       prod_time!stop_time = "Test"
>>       prod_time.Update
>>       prod_time.Close
>> End If

>> a) In both cases (sql_cmd 1 & 2) the record count = 2
>> b) Running the program using sql_cmd #1 updates 1 record.
>> c) Running the program using sql_cmd #2 updates ALL records in the
>> database!!!
>> d) Running the program using sql_cmd #3 updates 2 record.
>> e) Running with sql_cmd #1 Then sql_cmd #2 only has the one record
>> revised!!

>> In my mind, I am not looping through the recordset so I only expect
>> one record to be updated. In any event, if the recordcount = 2, then
>> why would ALL the records in the database be updated?
>> This is all very counter-intuitive.

>> Another question: Do I need emp_id in the SELECT if the only reference
>> to it is the WHERE in the SELECT statement?

>> Please help this is causing some major programming concerns!

>> Gary...

>> -----------------------------------------
>> Gary Kahrau
>> VP Technology - Stellex Monitor Aerospace

>> -----------------------------------------

-----------------------------------------------------------

VP Technology - Monitor Aerospace Corp.
-----------------------------------------------------------


Mon, 28 Jun 2004 08:02:17 GMT
 Expert help needed
Hi,

Problem could be because you recordset does not have
enough information about primary key of yoor table.
For example.

1.In first statement you select all fields and recordset
hass full information about PK in it. If you call update,
it updates all records with particular combination of PK.
It is unique for each record and thi is why it updates
exactly record which you have changed.
2. In second SQL statement probably recordset has partial
information about PK (probably emp_id field). Recordset
will update all records based on that criteria.

So to fix your problem, you would need to specify full
list of PK in WHERE clause or in list of retrieved fields.

Val

Quote:
>-----Original Message-----
>I tried to optimize my select statement and wound up
changing records
>that I did not expect to change.
>Is this a BUG of an ADO FEATURE???

> #1) sql_cmd = "SELECT * FROM time_log WHERE

time_log.emp_id = 11"
Quote:
> #2) sql_cmd = "SELECT stop_time FROM time_log WHERE
time_log.emp_id =
>11"
>  #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log
WHERE
>time_log.emp_id = 11"  

>prod_time.CursorType = adOpenStatic
>prod_time.LockType = adLockPessimistic
>prod_time.Open sql_cmd, conn_tc, , , adCmdText

>If Not prod_time.EOF Then
>      rc = prod_time.RecordCount
>      prod_time!stop_time = "Test"
>      prod_time.Update
>      prod_time.Close
>End If

>a) In both cases (sql_cmd 1 & 2) the record count = 2
>b) Running the program using sql_cmd #1 updates 1 record.
>c) Running the program using sql_cmd #2 updates ALL
records in the
>database!!!
>d) Running the program using sql_cmd #3 updates 2 record.
>e) Running with sql_cmd #1 Then sql_cmd #2 only has the
one record
>revised!!

>In my mind, I am not looping through the recordset so I
only expect
>one record to be updated. In any event, if the

recordcount = 2, then

- Show quoted text -

Quote:
>why would ALL the records in the database be updated?
>This is all very counter-intuitive.

>Another question: Do I need emp_id in the SELECT if the
only reference
>to it is the WHERE in the SELECT statement?

>Please help this is causing some major programming
concerns!

>Gary...

>-----------------------------------------
>Gary Kahrau
>VP Technology - Stellex Monitor Aerospace

>-----------------------------------------
>.



Mon, 28 Jun 2004 20:54:26 GMT
 Expert help needed
Val,

I kind of see what you mean.
However:

1) Why would the update use a different WHERE than the OPEN?
2) If the recordset.recordcount is 2. Then why would ALL the records
in the DB get updated (hundreds)?
3) Code wise, I only updated one record of the recordset.  Yet It does
all.
4) The prior contents of the field being updated effects how many
records get updated. This is crazy!!!

I think that I can hack out some code that works, but my confidence
level of how ADO and the Provider will handle it is shattered.

Gary...

Quote:

>Hi,

>Problem could be because you recordset does not have
>enough information about primary key of yoor table.
>For example.

>1.In first statement you select all fields and recordset
>hass full information about PK in it. If you call update,
>it updates all records with particular combination of PK.
>It is unique for each record and thi is why it updates
>exactly record which you have changed.
>2. In second SQL statement probably recordset has partial
>information about PK (probably emp_id field). Recordset
>will update all records based on that criteria.

>So to fix your problem, you would need to specify full
>list of PK in WHERE clause or in list of retrieved fields.

>Val

>>-----Original Message-----
>>I tried to optimize my select statement and wound up
>changing records
>>that I did not expect to change.
>>Is this a BUG of an ADO FEATURE???

>> #1) sql_cmd = "SELECT * FROM time_log WHERE
>time_log.emp_id = 11"
>> #2) sql_cmd = "SELECT stop_time FROM time_log WHERE
>time_log.emp_id =
>>11"
>>  #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log
>WHERE
>>time_log.emp_id = 11"  

>>prod_time.CursorType = adOpenStatic
>>prod_time.LockType = adLockPessimistic
>>prod_time.Open sql_cmd, conn_tc, , , adCmdText

>>If Not prod_time.EOF Then
>>      rc = prod_time.RecordCount
>>      prod_time!stop_time = "Test"
>>      prod_time.Update
>>      prod_time.Close
>>End If

>>a) In both cases (sql_cmd 1 & 2) the record count = 2
>>b) Running the program using sql_cmd #1 updates 1 record.
>>c) Running the program using sql_cmd #2 updates ALL
>records in the
>>database!!!
>>d) Running the program using sql_cmd #3 updates 2 record.
>>e) Running with sql_cmd #1 Then sql_cmd #2 only has the
>one record
>>revised!!

>>In my mind, I am not looping through the recordset so I
>only expect
>>one record to be updated. In any event, if the
>recordcount = 2, then
>>why would ALL the records in the database be updated?
>>This is all very counter-intuitive.

>>Another question: Do I need emp_id in the SELECT if the
>only reference
>>to it is the WHERE in the SELECT statement?

>>Please help this is causing some major programming
>concerns!

>>Gary...

>>-----------------------------------------
>>Gary Kahrau
>>VP Technology - Stellex Monitor Aerospace

>>-----------------------------------------
>>.

-----------------------------------------
Gary Kahrau
VP Technology - Stellex Monitor Aerospace

-----------------------------------------


Mon, 28 Jun 2004 22:02:33 GMT
 Expert help needed
Hi,

When provider wants to update all changes it bases it on
information from recordset/ If it does not have full
information about record, then it cannot update that
particular record and updates all records with selected
criteria

Val

Quote:
>-----Original Message-----
>Val,

>I kind of see what you mean.
>However:

>1) Why would the update use a different WHERE than the
OPEN?
>2) If the recordset.recordcount is 2. Then why would ALL
the records
>in the DB get updated (hundreds)?
>3) Code wise, I only updated one record of the

recordset.  Yet It does
Quote:
>all.
>4) The prior contents of the field being updated effects
how many
>records get updated. This is crazy!!!

>I think that I can hack out some code that works, but my
confidence
>level of how ADO and the Provider will handle it is
shattered.

>Gary...


>>Hi,

>>Problem could be because you recordset does not have
>>enough information about primary key of yoor table.
>>For example.

>>1.In first statement you select all fields and recordset
>>hass full information about PK in it. If you call
update,
>>it updates all records with particular combination of
PK.
>>It is unique for each record and thi is why it updates
>>exactly record which you have changed.
>>2. In second SQL statement probably recordset has
partial
>>information about PK (probably emp_id field). Recordset
>>will update all records based on that criteria.

>>So to fix your problem, you would need to specify full
>>list of PK in WHERE clause or in list of retrieved
fields.

>>Val

>>>-----Original Message-----
>>>I tried to optimize my select statement and wound up
>>changing records
>>>that I did not expect to change.
>>>Is this a BUG of an ADO FEATURE???

>>> #1) sql_cmd = "SELECT * FROM time_log WHERE
>>time_log.emp_id = 11"
>>> #2) sql_cmd = "SELECT stop_time FROM time_log WHERE
>>time_log.emp_id =
>>>11"
>>>  #3) sql_cmd = "SELECT stop_time,emp_id FROM time_log
>>WHERE
>>>time_log.emp_id = 11"  

>>>prod_time.CursorType = adOpenStatic
>>>prod_time.LockType = adLockPessimistic
>>>prod_time.Open sql_cmd, conn_tc, , , adCmdText

>>>If Not prod_time.EOF Then
>>>      rc = prod_time.RecordCount
>>>      prod_time!stop_time = "Test"
>>>      prod_time.Update
>>>      prod_time.Close
>>>End If

>>>a) In both cases (sql_cmd 1 & 2) the record count = 2
>>>b) Running the program using sql_cmd #1 updates 1
record.
>>>c) Running the program using sql_cmd #2 updates ALL
>>records in the
>>>database!!!
>>>d) Running the program using sql_cmd #3 updates 2
record.
>>>e) Running with sql_cmd #1 Then sql_cmd #2 only has the
>>one record
>>>revised!!

>>>In my mind, I am not looping through the recordset so I
>>only expect
>>>one record to be updated. In any event, if the
>>recordcount = 2, then
>>>why would ALL the records in the database be updated?
>>>This is all very counter-intuitive.

>>>Another question: Do I need emp_id in the SELECT if the
>>only reference
>>>to it is the WHERE in the SELECT statement?

>>>Please help this is causing some major programming
>>concerns!

>>>Gary...

>>>-----------------------------------------
>>>Gary Kahrau
>>>VP Technology - Stellex Monitor Aerospace

>>>-----------------------------------------
>>>.

>-----------------------------------------
>Gary Kahrau
>VP Technology - Stellex Monitor Aerospace

>-----------------------------------------
>.



Mon, 28 Jun 2004 22:57:04 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. urgent: is sql server case sensitive (EXPERT help needed)

2. Experts help needed on calculated member

3. Expert help needed for controversial project

4. urgent: is sql server case sensitive (EXPERT help needed)

5. using count - SQL expert help needed!

6. Expert help needed

7. Restore Problem - Expert help needed

8. Expert help needed - OLE field for CSV text files

9. Expert help needed: Heap problem

10. Oracle Database expert help needed

11. Expert help needed for forms problem

12. problem with mail need expert help


 
Powered by phpBB® Forum Software