rdo-->ODBC-->SQL6.0 (Errors on Addnew, Update)

rdo-->ODBC-->SQL6.0 (Errors on Addnew, Update)

Post by Mark Rosari » Fri, 21 Feb 1997 04:00:00



'
'  MyConn is an rdoConnection to a SQL6.0 datasource
'

MySql = "Select * from MyTbl where MyId = NULL"

Set MySet = MyConn.OpenResultset(MySql,rdOpenDynamic,rdConcurRowVer)

MySet.AddNew

MySet!MyId = 1
        '
        '
        '
        '
MyStatus = "A"

MySet.Update

'
'  At this point I generally encounter an ODBC error.
'

 Attempting to open this table with the Visdata sample application provided
with Visual Basic with recordset type set to dynaset and
 from type set to Data Control yields Error 3072:Can't update. Database or
object is read-only.
 MyTbl has several fields of only int, and varchar type.  Each field has a
constraint of NOT NULL and default of either 0 or ' ' depending
 on type.
 There are no indexes defined nor a primary key defined for the table.
 Interestingly enough,  I am able to circumvent the errors if I define a
primary key for the table.  While this is acceptable for an application
with
 a few tables (1 or 2) I think it very unweildly for many tables (such as
the 40 I have to deal with).  This is even more so in tables with one to
many
 relationships.
 I am trying to avoid using MyConn.Execute SQLString because of the long
SQLString that needs to be generated.

If anybody has any explanation or suggestions as to how I can overcome this
problem I would be happy to hear from them.

Tanking you in advance,

Mark Rosario

 
 
 

rdo-->ODBC-->SQL6.0 (Errors on Addnew, Update)

Post by Bob McDona » Fri, 21 Feb 1997 04:00:00



says...

Quote:> '
> '  MyConn is an rdoConnection to a SQL6.0 datasource
> '

> MySql = "Select * from MyTbl where MyId = NULL"

> Set MySet = MyConn.OpenResultset(MySql,rdOpenDynamic,rdConcurRowVer)

> MySet.AddNew

> MySet!MyId = 1
>    '
>    '
>    '
>    '
> MyStatus = "A"

> MySet.Update

> '
> '  At this point I generally encounter an ODBC error.
> '

>  Attempting to open this table with the Visdata sample application provided
> with Visual Basic with recordset type set to dynaset and
>  from type set to Data Control yields Error 3072:Can't update. Database or
> object is read-only.
>  MyTbl has several fields of only int, and varchar type.  Each field has a
> constraint of NOT NULL and default of either 0 or ' ' depending
>  on type.
>  There are no indexes defined nor a primary key defined for the table.

This is your problem.

Quote:>  Interestingly enough,  I am able to circumvent the errors if I define a
> primary key for the table.

This is your only solution if you want to use a resultset.  RDO requires
it, apparently just as DAO did.

Quote:> While this is acceptable for an application
> with
>  a few tables (1 or 2) I think it very unweildly for many tables (such as
> the 40 I have to deal with).  This is even more so in tables with one to
> many
>  relationships.

Bummer.

Quote:>  I am trying to avoid using MyConn.Execute SQLString because of the long
> SQLString that needs to be generated.

This is another solution.

Quote:> If anybody has any explanation or suggestions as to how I can overcome this
> problem I would be happy to hear from them.

> Tanking you in advance,

I really don't feel like getting tanked, thank you!

--
o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=
Bob McDonald
Correct reply address by replacing "STOPSPAMMING" with "com".
o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=

 
 
 

1. VB4(16bit)-->ODBC-->SQL6.5 -- One update strangles the server

I'm developing a rather complex system for tracking a Loan Repayment
program.  I have an Applicants form which handles all aspects of an
application.  I have a Save button which saves an application as new or as
an update to an existing app.  I use all unbound controls, and
sql-passthrough queries.  I pass all related values to a stored procedure
which handles all of the data validation and acts appropriately.  It passes
back a message indicating success or a specific error, through an output
parameter.  I do the same thing on all of my forms and everything works
fine, except when I try to save an updated application.

When I save an updated application, everything responds as if it has
worked.  I can clear the form, then search for the app, and it comes up
just as I saved it.  I can go to other forms, return, and bring up the app
just as I saved it.  However, the data is not really being saved!  The call
to update_applic causes the SQL server to be completely inaccessible from
anywhere until I shut down the lrp application, then I find that the data
was never saved.  I put an update trigger on the table, which simply notes
the time and app_id in a small table, and it never fires.  The odd thing
is, if I copy the SQL statement calling update_applic from VB and run it
from SQL enterprise manager it works without a hitch every time!

The SQL statement is very long (over 400 characters), but I run others
nearly as long without any trouble.  If statement length were the problem
for ODBC, I would think it would return an error, instead of faking
success.

Has anyone run into anything like this before?  Can anyone help me out?  I
would try VB5, or VB4, 32-bit, but we have to do this in 16-bit to support
some of our customer base.
--
Paul Gettys

2. DAO version 3.5 vs. 3.51 .dbengine properties

3. !US - Seattle, WA - Informix Application Developers

4. >>>SYBASE/UNIX ENIGNEERS WANTED>>>>Bay Area, CA.

5. Trigger Generator?

6. Delphi>>>>>>>

7. PLEASE.. PHILA AREA PICK PROFESSIONAL/PGMR/ANALYST/MIS MGR AVAILABLE..

8. Check out the Web below for great Computer Prices >>>>>>>>>>>>

9. >>>>>>UNIX/SYBASE ENGINEERS WANTED>>>>>> Bay Area, CA

10. ------>>>>>>CASH IN ON THE NET