write back to database

[pmgatwork]

I am trying to write back data to a dbase databse through odbc using the sql command with crystal 9 using microsoft foxpro VFP version 6.01

I keep getting an error Failed to open a rowset

The command that i am trying is
update transfil set ticket_nbr=124 where ticket_nbr=123

This works if I execute it in WinSQL
Am I doing something wrong

Thanks

Peter

 

[crystalvictim]

Have you checked if it is really possible to directly manipulate data via an sql command? As far as I know, sql commands are only designed to retrieve data and therefore can only contain select clauses.
The only way to write back data to a database I could imagine is to write a function (i.e. a stored procedure in oracle) that handles the actual writing process and access it using an sql command.

 

[pmgatwork]

According to business objects article c2011921.asp you can use INSERT, DELETE and UPDATE but I cannot get this to work

 

[crystalvictim]

okay, so this seems to be a new feature in CR9.
We're actually using CR8.5 :-(
So the only guess I can make would be that it depends on the syntax you're using. WinSQL might implicitely convert your syntax while CR might not. As you're using an ODBC driver, there might also be (other) conversions being done automatically or the driver might even expect another syntax than your original datasource.

 

[dgillz]

I have done this successfully using a SQL Stored Procedure to do the updating of the database, and using the SP as a subreport to my main report.

I did not want to actually print anything from the SP, so I formatted it white. also the SP must have at least one SELECT statement or Crystal will not be able to use it.

Bottom line thought this is doable, but a lot easier in crystal 9 or newer.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[pmgatwork]

dgillz
Any chance of seeing the sp that you have written for ideas