dhodgdon
Posts: 80
|
| Posted: 05/05/2005, 7:12 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
|
 |
|
ryan
Posts: 41
|
| Posted: 05/05/2005, 6:51 PM |
|
ei,
i think this should belong on tips category right yessoftware support?
_________________
(\__/)
(='.'=)
(")_(") |
|
|
|
navcan
Posts: 61
|
| Posted: 05/06/2005, 1:02 PM |
|
Hi David,
Nice article. Thank you.
Regards,
navcan
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/08/2005, 11:50 AM |
|
Yes, this post should be posted to the Tips & Solutions forum.
David, if you can re-post it there I will remove it from here.
Thanks.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
dhodgdon
|
| Posted: 05/09/2005, 8:51 AM |
|
I have reposted as asked, but I completely disagree with moving it out of this
area of the forum because it directly addresses numerous questions that have
been posted in the ASP area. Moving it out of this area blocks it from
searches by people looking for answers before posting a question. This is
where ASP developers of all experience levels come looking for answers to
questions/problems. In a quick search, I found 10 recent entries in this area
of the forum that might benefit from this post in addition to the two of my own
that were never adequately answered. In many of the cases, the last response to
the post is to say that this can't be done. So am I to assume that ignorance is
preferable to knowledge in this area of the forum?
If this area does not allow for a post that provides an answer to numerous
posts with out asking a question then we have lost the spirit of "Community
Forums for users to exchange information" to quote YesSoftware’s page footer.
_________________
Regards,
David Hodgdon
---------------------------------------
Sent from YesSoftware forum
http://forums.codecharge.com/
|
|
|
|
dhodgdon
Posts: 80
|
| Posted: 05/09/2005, 8:54 AM |
|
I have reposted as asked, but I completely disagree with moving it out of this area of the forum because it directly addresses numerous questions that have been posted in the ASP area. Moving it out of this area blocks it from searches by people looking for answers before posting a question. This is where ASP developers of all experience levels come looking for answers to questions/problems. In a quick search, I found 10 recent entries in this area of the forum that might benefit from this post in addition to the two of my own that were never adequately answered. In many of the cases, the last response to the post is to say that this can't be done. So am I to assume that ignorance is preferable to knowledge in this area of the forum?
If this area does not allow for a post that provides an answer to numerous posts with out asking a question then we have lost the spirit of "Community Forums for users to exchange information" to quote YesSoftware’s page footer.
_________________
Regards,
David Hodgdon
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/09/2005, 10:24 AM |
|
David,
Of course the information you posted has good value and I'd like it to be accessible by others as well. The forum structure allows categorizing posts and separating crys for help from articles & tips. Several users asked for "Tips & Solutions" type forum, which we created but what do we put there? Prior to that forum being created some users also complained that they don't want to subscribe to everyday problems of others but to article-type solutions where they can learn something new.
Finally, your suggestion doesn't apply only to ASP but also to PHP, Java, etc., or actually MS Access itself. Therefore your post could be even more useful if posted to a generic forum rather than being ASP specific.
Though I will leave it here for now as well.
Thanks for posting to the other forum already.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
 |
|
|
|
