This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
Crystal Reports - updating database via SQL statement
This relates to the question here:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27380729.html
Can someone explain the technicals of this approach.
Where is the sql statement going in the report? Are there specific scenarios where this does or doesn't work?
I had previously believed that the drivers used in CR were specifically read only on the selected tables. So this is a new thing for me.
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27380729.html
Can someone explain the technicals of this approach.
Where is the sql statement going in the report? Are there specific scenarios where this does or doesn't work?
I had previously believed that the drivers used in CR were specifically read only on the selected tables. So this is a new thing for me.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Very interesting .
It has set me thinking as to why I thought what I thought and I'm fairly sure it was based on 'authoritative' comments in a particular CR specialist site. There is an extremely remote ((:-)) possibility that I have misinterpreted such comments and I might have a search to see if I can find it again, although it will be quite a long time ago that I saw it.
So the situation is that if I can write the code in SQL Server Management Studio I should be able to run it in a CR 'Add Command' box - provided it returns something.
It has set me thinking as to why I thought what I thought and I'm fairly sure it was based on 'authoritative' comments in a particular CR specialist site. There is an extremely remote ((:-)) possibility that I have misinterpreted such comments and I might have a search to see if I can find it again, although it will be quite a long time ago that I saw it.
So the situation is that if I can write the code in SQL Server Management Studio I should be able to run it in a CR 'Add Command' box - provided it returns something.
Yes as I understand it that is correct, the only difficult comes when you want to include paramter values in the command as some DB's / connections require you to add the single quotes for string parameters and some will add handle the SQL without them.
FWIW, I'm not aware of any inherent limitations on updating tables from CR, but I've never tried to update "regular" tables either (never needed to). I did have one report that executed an MS SQL stored procedure that created a temp table, inserted rows into it, did a couple of UPDATE's to set some columns in the temp table, and then did a couple of DELETE's to delete some unnecessary rows from the temp table. So, table updates are possible, in general. Based on that, my thinking has been like GJParker, that it's possible to update regular tables, if you have the permissions. But I've never actually tried it.
James
James
I haven't found the document that sent me off this way, but I can now recall that the situation was a bit different to this in that there was a potential requirement to update the database based on the results calculated in the report. Clearly that is not what we are talking about here and unfortunately I have, over time, mistakenly generalised that experience to a 'no updates' position.
I have never had anyone else ask me for a database update feature with a report so I've never had to review my thinking.
Very useful lesson learnt.
I have never had anyone else ask me for a database update feature with a report so I've never had to review my thinking.
Very useful lesson learnt.
Yeah, that would be a different situation. Funny how things gets stuck in your head a certain way sometimes (well, my head anyways :-).
James
James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Securing… 324 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
Crystal Reports will run any SQL statement against a database as long the SQL is valid, the user account being used has the priveleges to execute the statement and most importantly it must return a recordset i.e.
The following statement would run from a CR command as long as the conditions above are met
DROP table tblEmployee
GO
SELECT * FROM tblAddress
In the past I have used a command in a subreport in the main report header to insert a record into an Audit table in the database every time the report is run i.e.
INSERT INTO tblAudt (ReportName, RunDate, Param1, Param2)
VALUES ('Report1', GetDate(), '{?Param1}', '{?Param2}')
GO
Select 1
The technique will only work for databases that support running multiple statements, these are usually server databases such as MS SQL Server, Oracle etc this won't work for an MS Access database.
HTH