Combining Update and Select in Crystal's SQL Command 2

[begin2know]

I've read through the various threads with much interest in using Crystal's Add Command capability, but can't figure out how to combine Update and Select statements in one query. I've tried using a semicolon and parentheses-- with the semicolon I get "Characters found after end of SQL statement," and with parentheses I get "Syntax error in union query." I'm connecting with ODBC to an Access database. Here's what I have without the punctuation...

update user_values
set amount1={?amt1}, amount2={?amt2}, numeric1={?num1}
where user_values.user_values={?userval}
select * from user_values

Any help would be greatly appreciated. Thanks.
~stephe

 

[IdoMillet]

Stephe,

See:

http://support.businessobjects.com/library/kbase/articles/c2011921.asp

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[begin2know]

I have tried everything that I can imagine. I even simplified matters by creating a test database with no security (thinking that might be part of the problem). My query is now...

UPDATE "Customer"
SET "Customer"."Contact" = '{?Name}'
WHERE "Customer"."Customer" = '{?Company}'
SELECT * FROM "Customer"

Looks to me like the format from the example listed, but it still gets errors. I get an invalid syntax error if I don't separate the two statements, and if I place a semicolon between them, I'm told there are characters after the end of the SQL statement. I'm lost!

 

[synapsevampire]

I would guess that the problem is with Access, as I did test an update/select using SQL Server a few months ago.

The optimizer probably can't combine SQL Statements as Access uses the ; to delineate parameters in Queries.

-k

 

[begin2know]

Ido and k~ Thank you each for your reply. Perhaps it is a limitation of the Access ODBC driver. I'll pursue other options for now.
Regards ~stephe

 

[ddnh]

nevermind, I found it finally...

http://support.businessobjects.com/library/kbase/articles/c2012566.asp

 

[ddnh]

sorry i replied to the wrong post