Wrox Programmer Forums
|
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 23rd, 2005, 08:21 PM
Authorized User
 
Join Date: Jan 2005
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default Hard (recordset field) reading


Hello Imar and everyone else!

I created a recordset that populates a form from data extracted from an Access query, named "qryDistrMgtFeePerShare3". This query is rather complex because it has an expression field, named "CumulSumOfpurchShare", that uses the "DSUM" function to calculate accumulated sums of a field extracted from the (previous created) query named "qryDistrMgtFeePerShare2".

Explicitly:

CumulSumOfpurchShare: DSum("[SumOfpurchShares]","[qryDistrMgtFeePerShare2]"," ([qryDistrMgtFeePerShare2].[MFeePerInvShareDate] -10) < #" & [qryDistrMgtFeePerShare2].[dealingDate] & "# AND [qryDistrMgtFeePerShare2].[userDistributor]=" & [qryDistrMgtFeePerShare2].[userDistributor] & "")

What is the problem? Well, when I try to get this field shown in my asp-dreamweaver page it is the only one that does not appear. I checked it and the "cell" is considered empty! (There is no error messages, the page appears but this field, in every row, is simply missing.)

It is rather odd because in the inetpub/wwwroot localhost "testing page" everything is working perfectly. Needless to say, in the Access database the fields are all ok, also.

After many tests I realized that the problem is of "reading". Dreamweaver seems to have difficulty to get the values from an access query based on another one "dinamically"... I repeat: all other fields, extracted from the same "qryDistrMgtFeePerShare3" access query are shown normally... :(

I have been trying to solve this problem for more than 2 weeks now and I ask you all to give me some advice, please!

Thanks in advance!!
fskilnik

P.S.: I tried "googling" a lot, also. I could not find anything but easy tutorials related to populating forms with Dreamweaver-Access queries relationships...

 
Old November 24th, 2005, 01:09 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I am not sure I understand what the problem is. You say you use DSum and it doesn't work, but it seems to work in a localhost testing page.

How does the code in that page look like?
It's worth to see if you can use DSum in a very simple statement, e.g.:

myConnection.Execute(DSum..... etc)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old November 24th, 2005, 01:54 PM
Authorized User
 
Join Date: Jan 2005
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello, Imar. Thanks for the quick reply!

Sorry for my difficulties in explaining the problem. Let me try and be clearer:

01) The access query works fine and calculates the DSUM field without any problem. The expression I used in it is given in my first message.

02) I created a very easy recordset, just to get the values that were given/calculated in the access query, as below:

<%
Dim rsDistrMgtFeePerShare3__MMColParam
rsDistrMgtFeePerShare3__MMColParam = "1"
If (Session("UserID") <> "") Then
  rsDistrMgtFeePerShare3__MMColParam = Session("UserID")
End If
%>
<%
Dim rsDistrMgtFeePerShare3
Dim rsDistrMgtFeePerShare3_numRows

Set rsDistrMgtFeePerShare3 = Server.CreateObject("ADODB.Recordset")
rsDistrMgtFeePerShare3.ActiveConnection = MM_baseconnection_STRING
rsDistrMgtFeePerShare3.Source = "SELECT * FROM qryDistrMgtFeePerShare3 WHERE userDistributor = " + Replace(rsDistrMgtFeePerShare3__MMColParam, "'", "''") + " ORDER BY dealingDate ASC"
rsDistrMgtFeePerShare3.CursorType = 0
rsDistrMgtFeePerShare3.CursorLocation = 3
rsDistrMgtFeePerShare3.LockType = 1
rsDistrMgtFeePerShare3.Open()

rsDistrMgtFeePerShare3_numRows = 0
%>

When I test this recordset (in Dreamweaver) , it works fine and "shows" all fields properly, the DSUM-related one also ;

03) When I test the whole dreamweaver page in the localhost testing server it works perfectly ;

04) At the external server, it works almost perfectly: all fields appear as they should, except the calculated one. When I change the field (presented in a table contained in a form in an asp-page in dreamweaver) , binding it to another access field from the same query, it works perfectly! (Because it is not DSUM related???)

My feeling is that, in some way, the localhost is able to manage a calculated field in access "before" getting the value to send it to dreamweaver but when at the server, it seems that dreamweaver asks for the calculated field "before" it was really calculated (by access).

Well, I hope you got the picture. I could try to do a small situation to simulate the same error and pass all the code for your analysis. Please feel free to ask if this could help.

Once more, thanks a lot!

Best Regards,
Fábio.


 
Old November 24th, 2005, 03:57 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I may be thick, but I still don't get it.

What does Dreamweaver has to do with this? Why would Dreamweaver access the database are try to parse or display the query results? Are you using Live Data View?

Maybe this issue is not related to DW at all, but to your difference in servers. Is there a chance your remote server is using an older version of the Access drivers that doesn't support DSum? I never used DSum before, so I don't know since what version it is supported.

Can't you do a simple SELECT SUM(...) statement and skip DSum altogether?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old November 24th, 2005, 05:06 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Fábio,

I just sent you a reply to your personal message. However, as a response I got a message that I should confirm my email address to be allowed by your anti-spam service. I don't like / trust these services and believe they often make the problem worse, so I haven't confirmed.

Is there another way to contact you? Let me know through my profile page please.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old November 24th, 2005, 05:20 PM
Authorized User
 
Join Date: Jan 2005
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default


If YOU were thick, we would be all doomed, Imar! ;)

Let me (try to) answer your questions:

> Why would Dreamweaver access the database are try to parse or display the query results? Are you using Live Data View?

Good point. First of all, I have always created the query(ies) in Access, copied the SQL generated, changed a bit (to use the session variables) and pasted it in dreamweaver, in the recordset definition. That is the traditional approach, I guess.

This time, because the SQL generated code was much more delicate (strongly because of the DSUM function) I begun to have difficulties with the former mentioned approach. The "double quotes" , where to put them, was getting me in trouble... Then I (stupidly?) realized that I could just "read" the query as I was used to "read" the tables! I created the Access queries correctly and proceeded to have their values extracted as if they were given in tables! I would never imagine I couldn´t do it (couldn´t I??) even because I can do it for ALL fields BUT this devil one, the one related to the DSUM!

Is this the whole problem??? Please don´t say me so! (Joking, if it is, please explain! And if you could explain how I should put the double quotes to be able to use the Access SQL inside the recordset, I would be very very grateful. (The real SQL generated in Access is at the end of this message, if you need to use it -- and have patience enough, sorry for the trouble!)

This "Live Data View" you mentioned, well, I started to follow some tutorial I found googling (very interesting indeed) but... it didn´t seem to have anything related to my difficulties, I (we?!) guess...

> Is there a chance your remote server is using an older version of the Access drivers that doesn't support DSum?

Another very good try, I think. I thought the same (because I suffered a lot with the LCID business and the local x server date format configurations, remember?) The MDAC at the server is 2.6 SP1
(I asked them two days ago) and all the troubles I saw googling were related to MDAC 2.1 or older...

But... I still don´t know if their access drivers do support the DSUM function. I will check and come back in less than 24h with the answer, ok?!


> Can't you do a simple SELECT SUM(...) statement and skip DSum altogether?

I simply don´t know. The situation is the following:

  Base Date ! Date | Certain Amount ! Accumulated Amount
   4 oct 05 ! 10 mar 05 ! $ 100 ! $ 100
   session ! 2 sep 05 ! $ 200 ! $ 300
   variable ! 4 nov 05 ' $ 500 ! $ 300
    given ! ! !
(this date !
for example)!

The dates are compared to the base date (dynamically changed according to a session variable given) , in a repeat region where the dates move row by row. I used the Dsum to do as above. It is easy to imagine how it works. If date < base date, sum that value with the previous ones, if not, don´t do it and simply copy the last one.

If you can use the Select Sum(...) please explain (briefly) with this example in mind, please!

Well, sorry for the lengthly message.

Thanks for your time and expertise, Imar!

------------------------------------------------------------------
Access SQL (The number "6" at the end I know how to deal with!)

SELECT qryDistrMgtFeePerShare2.userDistributor, qryDistrMgtFeePerShare2.dealingDate, qryDistrMgtFeePerShare2.DistrMFeePerShare, qryDistrMgtFeePerShare2.MFeePerInvShareValue, qryDistrMgtFeePerShare2.MFeePerInvShareDate, qryDistrMgtFeePerShare2.SumOfpurchShares, (DSum("[SumOfpurchShares]","[qryDistrMgtFeePerShare2]"," ([qryDistrMgtFeePerShare2].[MFeePerInvShareDate] -10) < #" & [qryDistrMgtFeePerShare2].[dealingDate] & "# AND [qryDistrMgtFeePerShare2].[userDistributor]=" & [qryDistrMgtFeePerShare2].[userDistributor] & ""))*[qryDistrMgtFeePerShare2].[DistrMFeePerShare] AS TotDistrFee, DSum("[SumOfpurchShares]","[qryDistrMgtFeePerShare2]"," ([qryDistrMgtFeePerShare2].[MFeePerInvShareDate] -10) < #" & [qryDistrMgtFeePerShare2].[dealingDate] & "# AND [qryDistrMgtFeePerShare2].[userDistributor]=" & [qryDistrMgtFeePerShare2].[userDistributor] & "") AS CumulSumOfpurchShare
FROM qryDistrMgtFeePerShare2 INNER JOIN tblNAVs ON qryDistrMgtFeePerShare2.dealingDate = tblNAVs.dealingDate
GROUP BY qryDistrMgtFeePerShare2.userDistributor, qryDistrMgtFeePerShare2.dealingDate, qryDistrMgtFeePerShare2.DistrMFeePerShare, qryDistrMgtFeePerShare2.MFeePerInvShareValue, qryDistrMgtFeePerShare2.MFeePerInvShareDate, qryDistrMgtFeePerShare2.SumOfpurchShares, (DSum("[SumOfpurchShares]","[qryDistrMgtFeePerShare2]"," ([qryDistrMgtFeePerShare2].[MFeePerInvShareDate] -10) < #" & [qryDistrMgtFeePerShare2].[dealingDate] & "# AND [qryDistrMgtFeePerShare2].[userDistributor]=" & [qryDistrMgtFeePerShare2].[userDistributor] & ""))*[qryDistrMgtFeePerShare2].[DistrMFeePerShare]
HAVING (((qryDistrMgtFeePerShare2.userDistributor)=6));



 
Old November 25th, 2005, 11:32 AM
Authorized User
 
Join Date: Jan 2005
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I´m back, Imar.

The guys in the remote server haven´t answered your access drivers question yet and I decided to abandon the DSUM for good! Good decision, isn´t it?

I found marvellous links to the cumulated sums in SQL server and Access:

http://www.sqlteam.com/item.asp?ItemID=3856

and "GreySky" mini-class(!) comment in

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6027

(Have a look in this last one if you have 5min, very interesting!)

Please give me some days to use the ideas I found there in my situation. I will let you know if I solved the problem (and how) or if I need your always-great assistance in this particular matter, ok?!

Have a very nice weekend and.. Cheers!
Fábio
 
Old November 26th, 2005, 01:17 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You too. And good luck....;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Let Your Good Heart Lead You Home by Editors (Track 1 from the album: Cuttings) What's This?
 
Old November 28th, 2005, 04:17 PM
Authorized User
 
Join Date: Jan 2005
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default


More-than-great good news, Imar!

It seems to me that you were 100% right, as usual!

I still don´t know about the remote server Access drivers but... it doesn´t matter. The fact is that the problem was certainly DSUM-related, because I used EXACTLY your marvellous suggestion

"Can't you do a simple SELECT SUM(...) statement and skip DSum altogether?"

(as explicitly given below) and the accumulated sums were calculated by Access AND read by Dreamweaver´s recordset without any difficulty!

Thank you VERY much for your support, Imar!

Best regards and Cheers!
Fábio.

------ Calculated expression used in an Access query to substitute the DSUM function -------

CumulSumOfpurchShare: (Select SUM(qryDistrMgtFeePerShare.SumOfpurchShares) FROM qryDistrMgtFeePerShare
Where (qryDistrMgtFeePerShare.MFeePerInvShareDate -10 < qryDistrMgtFeePerShare2.dealingDate) AND ([qryDistrMgtFeePerShare].[userDistributor]= [qryDistrMgtFeePerShare2].[userDistributor] ))

 
Old November 28th, 2005, 05:08 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
quote:Originally posted by fskilnikIt seems to me that you were 100% right, as usual!
Haha, tell that to my girlfriend... ;)

Glad it's working now.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Sorry by Madonna (Track 3 from the album: Confessions On A Dance Floor) What's This?





Similar Threads
Thread Thread Starter Forum Replies Last Post
SSIS :: Reading from XML source into Recordset grrajoo BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 1 January 12th, 2016 07:48 AM
Values from recordset lost after reading vanik.cz Classic ASP Databases 11 January 4th, 2008 07:36 AM
bind recordset to field ibgreen SQL Language 0 October 24th, 2006 12:48 PM
Retrieve recordset field value tryntolearn Classic ASP Databases 1 August 8th, 2005 10:22 AM
Remove Hard Returns from Memo Field darinsee Classic ASP Databases 4 July 7th, 2004 04:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.