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

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

Post by PGETTY » Sat, 15 Nov 1997 04:00:00



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

 
 
 

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

Post by Ken Shaple » Sat, 22 Nov 1997 04:00:00


I don't know if this is worth anything or not.  If you have declared
referential integrity (DRI) in place, i.e foreign key referencing a primary
key in another table that should have a corresponding record, you cannot
update the source/target table the containing foreign key. SQL 6.5 reads
DRI and other constraints first. A trigger will also not fire if DRI
(constraints) first have been violated. However, you should have got a
message through isql window if their was a DRI violation unless your
working directly through VB and are somehow not getting the error message
indirectly.  Ofcourse, your problem is most likely something else other
than what I've described. But what the Hey!!!



Quote:> 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


 
 
 

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. Import Error during Upgrade

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

4. Table Design question

5. Delphi>>>>>>>

6. Newbie SQL question

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

8. SQL Fragmentation

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

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

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

12. HELP>>>Replication from 400-2500 database to one master database and vice versa