dhodgdon
Posts: 80
|
| Posted: 05/09/2005, 8:14 AM |
|
There are a number of posts in this forum that ask for help calculating, displaying and/or storing those calculated values. There are also a number of posts with problems using “custom” SQL. This has been a challenge for me for nearly a year and I have finally found a solution that I haven’t seen proposed in this forum, that has made my life much easier and given me functionality that I didn’t have until now. While this information is specific to MS Access, I suspect that it can be applied to other databases.
In MS Access, there are many functions that are available as a part of that database and the JET engine the MS Access application uses. I found that these functions were not available to my CCS generated project, even using Custom SQL. The errors returned when this was attempted were ambiguous. Many (most??) of us use ODBC and DSN as their data source provider. For those, like me, who are learning on the fly, we follow the CCS help that focuses on ODBC. I assumed that ODBC simply provided me with a connection to the Access database and its functions. So I assumed that I could use SQL generated in MS Access using the CCS Execute function. I did not realize that it actually replaced the JET database engine used by the MS Access application. As a result, useful functions available in the MS Access application are often not available because ODBC doesn’t provide them. This was pointed out to me after several rounds of discussion with Yes Software Tech Support.
The solution – use a different database provider. In this case, I found that I could configure my project to use the JET engine (which the MS Access application uses) instead of ODBC in my database connection configuration in the Project Settings Connection tab by building a connection string. Previously, I thought that this was simply a way of explicitly assigning the location of your database. I didn’t understand about assigning database providers. You do lose some portability and flexibility offered by DSN and ODBC but I gained functional capability. And of course, your web server must have the JET engine available to it (in my case, IIS on Win 2K Server does). CCS allows for assigning different server and design database locations and providers on the same connection to increase flexibility during development and deployment.
Example that prompted this:
I needed to calculate the average of a database column with criteria specified from another column in the same table. I then needed to store this calculated value in a different table conditioned by the criteria from the table used in the calculation and/or display the calculated average. I could use the CCS Summ examples and record count to calculate the average and assign it to a variable that displayed in a grid or form. But, I could never get it stored in the database because I could never get the calculated value sent “up” to the server to be stored since it was a “locally” calculated value. Lots of suggestions from various ASP forums on how to do this but none worked. Thinking outside the box, a custom SQL update query could be written that accomplished this. However, I don’t know SQL and was never successful despite help from lots of suggestions and examples. I went back to my MS Access application roots and tried using the DAvg function which makes this extremely easy using the MS Access query builder. This worked great in MS Access but the SQL generated by the MS Access application wouldn’t work when using the CCS Execute Method because ODBC didn’t support the JET DAvg function. Changing from ODBC to the JET provider solved this problem and hasn’t presented any problems (so far) during my testing.
For those like me who know a little about a lot but aren’t experts in very much, this approach allows capabilities that reach beyond our limitations. I hope others find this useful.
_________________
Regards,
David Hodgdon
|
 |
|
|
 |
|
|
|
