x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 6 replies
  • Subscribers 118 subscribers
  • Views 3105 views
  • Users 0 members are here
Browse Forums
Announcements
271
topics
260
replies
Databases and Operating Systems
767
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
854
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
877
topics
3k
replies
Sage CRM for Sage 300
183
topics
415
replies
Ianus

Using the CS0120 superview to execute a SQL transaction

Posted By

If I use a transaction that has two statements in it, only the first statement executes
In the example below only the UPDATE statement executes and I am not able to retrieve the result


csUpdateW.Browse "BEGIN TRANSACTION; " & _
  "UPDATE OWID SET LASTVALUE = LASTVALUE + 1; " & _
  "SELECT LASTVALUE FROM OWID; " & _
  "COMMIT TRANSACTION;", True
csUpdateW.InternalSet 256

Do While csUpdateW.Fetch
  resultval = csUpdateW.Fields(0).Value
Loop
csUpdateW.Close

I am trying to use the superview instead of an ADO connection for various reasons.

Any suggestions are appreciated, thank you.

Parents Reply Children
  • 0 in reply to Jay Converse Acumen

    Thanks Jay; I know that update statements have no results but the transaction also contains a select statement which does not seem to execute . If I put the select statement first then it executes it and it gives me the result  but it does not execute the Update statement.

    I would like both executed, ideally in this order : update first and then SELECT and then get the results of the select statement

    If I use the transaction as it is in SQL server management studio I executes the update statement and then gives me the result of the select statement

    The same SQL transaction used through the CS0120 executes only the first statement in the transaction.

  • 0 in reply to Ianus

    Yes, CS0120 has lots of limits.  For your example I would probably have added a simple trigger to the Sage table.

*Community Hub is the new name for Sage City