Update statement creates Error, when statement is fine 
Author Message
 Update statement creates Error, when statement is fine

I am getting one of those great 2147467259 (80004005) Unspecified
error

the statement that is causing this error is

Dim numOfRecords As ADODB.Recordset
Set numOfRecords = CCSMain.CCSConnection.Execute(sql)

We are using Oracle 9i
Where sql is the following update statement:

update ccs_detail set project_id = 4207, cc_number = '0027',
revision_number = '00', description = 'CHANGE ORDER #2 (DATA)',
creation_date = to_date('11/28/01','MM/DD/YY'), billing_code = 'N',
cc_status = 'A', estimate_date = to_date('11/28/2001','MM/DD/YYYY'),
estimate_amount = 3255, submit_date =
to_date('11/28/2001','MM/DD/YYYY'), submit_amount = 3255, budget_date
= to_date('02/15/2002','MM/DD/YYYY'), budget_amount = 3255,
approval_date = to_date('11/30/2001','MM/DD/YYYY'), approval_amount =
3255, paid_date = '', paid_amount = Null, billing_reference =
'01019-20 CO #3 {D}', note_id = 2783, operator = 'KTW', complete =
0.00 where detail_id = 115543

However, if I put this query into SQLPlus or Toad and run it against
the database it runs fine. Why is VB having a problem with it, and how
can I solve the problem?

Thanks
Mark



Sat, 07 Aug 2004 00:44:46 GMT  
 Update statement creates Error, when statement is fine

ADODB Recordset is only for Select Query.

For Update Query just use the sql with ConnectionName.Execute Sql.

Like:-

CCSConnection.Execute(sql)

Best Regards,

Luqman


Quote:
> I am getting one of those great 2147467259 (80004005) Unspecified
> error

> the statement that is causing this error is

> Dim numOfRecords As ADODB.Recordset
> Set numOfRecords = CCSMain.CCSConnection.Execute(sql)

> We are using Oracle 9i
> Where sql is the following update statement:

> update ccs_detail set project_id = 4207, cc_number = '0027',
> revision_number = '00', description = 'CHANGE ORDER #2 (DATA)',
> creation_date = to_date('11/28/01','MM/DD/YY'), billing_code = 'N',
> cc_status = 'A', estimate_date = to_date('11/28/2001','MM/DD/YYYY'),
> estimate_amount = 3255, submit_date =
> to_date('11/28/2001','MM/DD/YYYY'), submit_amount = 3255, budget_date
> = to_date('02/15/2002','MM/DD/YYYY'), budget_amount = 3255,
> approval_date = to_date('11/30/2001','MM/DD/YYYY'), approval_amount =
> 3255, paid_date = '', paid_amount = Null, billing_reference =
> '01019-20 CO #3 {D}', note_id = 2783, operator = 'KTW', complete =
> 0.00 where detail_id = 115543

> However, if I put this query into SQLPlus or Toad and run it against
> the database it runs fine. Why is VB having a problem with it, and how
> can I solve the problem?

> Thanks
> Mark



Sat, 07 Aug 2004 00:54:26 GMT  
 Update statement creates Error, when statement is fine
Hi,

UPDATE SQL statement is action query, which does not
return any recordset. Try to do next

CCSMain.CCSConnection.Execute sql,,adCmdText

Val

Quote:
>-----Original Message-----
>I am getting one of those great 2147467259 (80004005)
Unspecified
>error

>the statement that is causing this error is

>Dim numOfRecords As ADODB.Recordset
>Set numOfRecords = CCSMain.CCSConnection.Execute(sql)

>We are using Oracle 9i
>Where sql is the following update statement:

>update ccs_detail set project_id = 4207, cc_number
= '0027',
>revision_number = '00', description = 'CHANGE ORDER #2
(DATA)',
>creation_date = to_date('11/28/01','MM/DD/YY'),
billing_code = 'N',
>cc_status = 'A', estimate_date = to_date

('11/28/2001','MM/DD/YYYY'),

- Show quoted text -

Quote:
>estimate_amount = 3255, submit_date =
>to_date('11/28/2001','MM/DD/YYYY'), submit_amount = 3255,
budget_date
>= to_date('02/15/2002','MM/DD/YYYY'), budget_amount =
3255,
>approval_date = to_date('11/30/2001','MM/DD/YYYY'),
approval_amount =
>3255, paid_date = '', paid_amount = Null,
billing_reference =
>'01019-20 CO #3 {D}', note_id = 2783, operator = 'KTW',
complete =
>0.00 where detail_id = 115543

>However, if I put this query into SQLPlus or Toad and run
it against
>the database it runs fine. Why is VB having a problem
with it, and how
>can I solve the problem?

>Thanks
>Mark
>.



Sat, 07 Aug 2004 01:37:52 GMT  
 Update statement creates Error, when statement is fine
HI
No need to use recordset here.
Execute method of both ADO conection and Command Objects gives you an
option to include "RecordsAffected" parameter.
It is a Long variable to which the provider returns the number of
records that the operation affected.

Harinatha Reddy Gorla MCSD,MCDBA
        Software Engineer
Smart Software Technologies,
Hyderabad, India

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



Sat, 07 Aug 2004 13:14:57 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. UPDATE Statement Error

2. Error in UPDATE Statement

3. Syntax error in Update statement (with a date)

4. Syntax Error in Update Statement.

5. Syntax error in UPDATE statement

6. ASP Syntax error in UPDATE statement.

7. Syntax error in UPDATE statement

8. Syntax Error in Update Statement

9. Creating Table with ADO Errors out on SQL statement

10. Expected:line number or label or statement or end of statement

11. running sql code record by record instead of statement by statement

12. With...End With statement bombing out in For...Next statement

 

 
Powered by phpBB® Forum Software