asp count question (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

chadb -> asp count question (2/1/2008 13:36:15)

I would like to count the total # of days in the current month, then apply that to the follwoing, so the # of records it returns in the # of days. Example would be, let's today it would only loop through 1 time. On Monday it would loop 2 times. I would like to exclude Sat and Sun if possible. So on Monday it would return 2 MyRs("goal") records, Tuesday 3, etc....

MyDNS = "select * from SGJ"
Set MyRs = MyConn.Execute(MyDNS)
While Not MyRs.Eof
strXML = strXML & "<set value ='" & MyRs("Goal") & "' />"
'Close recordset
MyRs.MoveNext



chadb -> RE: asp count question (2/5/2008 12:02:55)

Any ideas on this? Is it possible?

Thanks
Chad



rdouglass -> RE: asp count question (2/5/2008 12:41:15)

You can get an integer value representing the day of the week like so:

DatePart("w", myDate)

Not really clear what you want to do with it but that should give you an integer from 1 to 7 (Sun to Sat) of myDate.

That help any?



chadb -> RE: asp count question (2/5/2008 13:47:02)

I will try to explain what I am trying to do. I am using fusionCharts. I am pulling data from the fusion charts fron an Excel spreadsheet to xml, using this:
While Not MyRs.Eof
strXML = strXML & "<set value ='" & MyRs("Actual") & "' />"
MyRs.MoveNext
Wend

MyRs("Actual") is a value, which changes each day. What I want is to only movenext the amount of days so far in the month, excluding Sat & Sun. I want to do this so my Chart drops off at the current day. So I have 20 cells with data- MyRs("Actual") -For example toady is the 3rd day of the month (Friday, Monday, Tuesday) So I only want to display the first 3 cells, so the MoveNext 3 times. Does that make sense?



rdouglass -> RE: asp count question (2/5/2008 14:29:41)

Take a look at this:

For i = 1 TO DatePart("d", Date()) 'do this once for each day of the month to today
If (DatePart("w",Date()) <> 1) AND (DatePart("w",Date()) <> 7) Then ' dont' do Sat or Sun
'do what each loop does here
End if
Next

Do you see my logic there?

1st line "do something for each day"
2nd line "but not if Sat or Sun"

That help any?



rdouglass -> RE: asp count question (2/5/2008 14:34:58)

quote:

If (DatePart("w",Date()) <> 1) AND (DatePart("w",Date()) <> 7) Then


Maybe after I read your post a little closer, something like this might work:

If (DatePart("w",MyRs("WhateverTheDateFieldIs")) <> 1) AND (DatePart("w",MyRs("WhateverTheDateFieldIs")) <> 7) AND (MyRs("WhateverTheDateFieldIs") <= Date()) Then

Do I understand it any better now? [;)]



chadb -> RE: asp count question (2/5/2008 15:15:03)

quote:

For i = 1 TO DatePart("d", Date()) 'do this once for each day of the month to today
If (DatePart("w",Date()) <> 1) AND (DatePart("w",Date()) <> 7) Then ' dont' do Sat or Sun
'do what each loop does here
End if
Next


This would work, but it is not taking away for Saturday and Sunday?? I am going to look at your other option, but I think this is what I need if I can get it right.



rdouglass -> RE: asp count question (2/5/2008 15:17:41)

quote:

(DatePart("w",Date()) <> 1) AND (DatePart("w",Date()) <> 7)


There's where I should have used the date field from the recordset item as in:

(DatePart("w",MyRs("WhateverTheDateFieldIs")) <> 1)

That any better?



chadb -> RE: asp count question (2/5/2008 15:20:17)

quote:

(DatePart("w",MyRs("WhateverTheDateFieldIs")) <> 1)

OK, sorry I forgot to mention the MyRs is not a date field, just a number. 1 for first day, 2 for second day, etc...



rdouglass -> RE: asp count question (2/5/2008 15:25:18)

quote:

OK, sorry I forgot to mention the MyRs is not a date field, just a number. 1 for first day, 2 for second day, etc...


Ahem...well, that is rather important. [;)]

So we need to convert first that number back into a date to determine if it's a Saturday or Sunday or not, right? Or is there another field we can tell the date from?

It would be easier IMO if you could just include the date that represents as well in the query. Don't get rid of what you have but adding the real date into that query would make it easier IMO.



chadb -> RE: asp count question (2/5/2008 15:27:19)

So are you saying a beginning date? Every Month it will be the 1st, so if I added a Date cell in Excel, and used that, would that help?



rdouglass -> RE: asp count question (2/5/2008 15:52:32)

If you *always* have data for the current month and not try to show other months, you could use the Date() and assume it was this month. That just seems too restrictive to me.

However, if you can somehow pass the year and month (in a querystring perhaps when you click the link to launch this), then you could do something like this:

IF (DatePart("w",(Request.querystring("month") & "/" & MyRs("WhateverTheDayOfTheMonthFieldIs") & "/" & Request.querystring("year"))) <> 1) AND ...

Does that make any sense? What I'm doing there is re-creating the date based on querystrings of "month" and "year" and whatever field value you're passing for that day representative. I'd use that for all 3 'date' criteria. Then I'm calculating whether it is a Sunday in that particular case above.

If the logic there makes sense, you probably could do it before the IF..THEN like so:

myNewDate = Request.querystring("month") & "/" & MyRs("WhateverTheDayOfTheMonthFieldIs") & "/" & Request.querystring("year")

Then:

IF (DatePart("w",myNewDate) <> 1) AND (DatePart("w",myNewDate) <> 7) Then

That help any?



chadb -> RE: asp count question (2/5/2008 16:19:36)

OK, I got it. Thanks so much. I have this page Automatically updating an XML page from an excel spreadsheet every so often throughout the day. A few people get into this spreadsheet to do updates in it, is there a way I can check the status before I try to access it, now if someone is in it, the page won't display.



rdouglass -> RE: asp count question (2/5/2008 17:49:33)

AFAIK as long as it's an Excel file you can't. At least I don't know how to use an Excel file as multi-user like you want.

You may be able to catch the error and display something else. (I'm assuming the remote user that has the file open is locking your query out.)



chadb -> RE: asp count question (2/5/2008 17:50:23)

Yes, that is exactly what is happening, could I use something like ON ERROR RESUME NEXT?



rdouglass -> RE: asp count question (2/6/2008 8:42:39)

I think you could. What I have in mind is before the script, set a variable. For instance:

Dim isSucess
isSucess = 0

Now *right after* a sucessfull DB read, put something like:

isSucess = 1

Now by checking isSucess, you should be able to determine if the script worked or not. That help any?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875