Sql Query Help - ASP

[cancer10]

Hello,
I am developing a simple calender in ASP which will display the next upcoming birthday of my member. Just like some forum have.

I have a table which has 2 column

cname | dob
-------------------
Joe | 9/12/1982
Mathew | 2/17/1997
Pat | 8/23/1880

-------------------
What SQL Query should I use to get the name of that person?

Some basic information:

Database = Access (may be I can upgrade it into SQL Server 2000)

Date Format in database = mm/dd/yyyy

[so_is_this]

Off the top of my head, and as long as you're actually using a date data type for that column, then either of the following will select member birthdays this month:

sql = "Select * from [bdayTable] Where Month([dob]) = Month();"
sql = "Select * from [bdayTable] Where Month([dob]) = Month(Now());"

[so_is_this]

To select a particular member:

sql = "Select * from [bdayTable] Where [cname] = '" & cname & "';"

[cancer10]

Off the top of my head, and as long as you're actually using a date data type for that column, then either of the following will select member birthdays this month:
sql = "Select * from [bdayTable] Where Month([dob]) = Month(Now());"


works upto some extent but selects the previous month as well

[so_is_this]

... selects the previous month as well
That shouldn't be possible. You've got some other issue in the mix that is unaccounted for.

[cancer10]

the date format in my database is mm/dd/yyyy hope u have not overlooked it

[cancer10]

Okay, it works now thanx so much for your effort.

God Bless

[cancer10]

One more problem,

if therez no b'day in the current month, how will it find the b'day for the next month?

[so_is_this]

Perhaps something like this (untested):

currmo = Month(Date())
workmo = currmo
Do Until Not rs.EOF
sql = "Select * from [bdayTable] Where Month([dob]) = " & workmo & ";"
Set rs = conn.Execute(sql)
If rs.EOF Then
workmo = workmo + 1
If workmo > 12 Then workmo = 1
If workmo = currmo Then Exit Do
rs.Close()
End If
Loop
If rs.EOF Then
Response.Write "No birthdays found."
Else
Response.Write "Birthday(s) found for month " & workmo & "."
End If

[cancer10]

Thanx so_is_this ur code works

Thanx a ton

[so_is_this]

It's no problem.

Salud!

[cancer10]

Perhaps something like this (untested):

currmo = Month(Date())
workmo = currmo
Do Until Not rs.EOF
sql = "Select * from [bdayTable] Where Month([dob]) = " & workmo & ";"
Set rs = conn.Execute(sql)
If rs.EOF Then
workmo = workmo + 1
If workmo > 12 Then workmo = 1
If workmo = currmo Then Exit Do
rs.Close()
End If
Loop
If rs.EOF Then
Response.Write "No birthdays found."
Else
Response.Write "Birthday(s) found for month " & workmo & "."
End If


Any idea why its generating this error at the third line (Do Until Not rs.EOF )of ur code?

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.


It works fine if I include a on error resume next line. Any way to avoid this error without error resume next ?


Thanx

[so_is_this]

This:

Operation is not allowed when the object is closed.

would mean that previous to this line the recordset (rs) was either never opened or was closed prematurely.

[cancer10]

I think there is a bit logical error in your code

You are trying to get the EOF of a recordset without opening it.

Do Until Not rs.EOF


Instead u r opening he recordset in the next line...
sql = "Select * from [bdayTable] Where Month([dob]) = " & workmo & ";"


Correct me if I am wrong


Thanx

[so_is_this]

So, fix it. I said it was untested. What do you want for free?

[cancer10]

Dnt take me wrong, I was just trying to figure out the fix for the logical error.

I tried opening the recordset with rs.open "select * from table",conn,1,3

but that didnt solve the problem.

Do u have any alternate method?


Thanx

[so_is_this]

Yep, try it something like this way (still untested):

Set rs = Server.CreateObject("ADODB.RecordSet")
currmo = Month(Date())
workmo = currmo
Do
If rs.State = adStateOpen Then
rs.Close()
End If
sql = "Select * from [bdayTable] Where Month([dob]) = " & workmo & ";"
Set rs = conn.Execute(sql)
If rs.EOF Then
workmo = workmo + 1
If workmo > 12 Then workmo = 1
If workmo = currmo Then Exit Do
End If
Loop Until Not rs.EOF
If rs.EOF Then
Response.Write "No birthdays found."
Else
Response.Write "Birthday(s) found for month " & workmo & "."
End If

[cancer10]

Yep, just move the Until test from the Do statement to the Loop statement and move the Close inside of a State test right after the Do statement.


Until test??


Plz explain

[so_is_this]

I edited my post above. Note, the ADO constants are available by including this library reference:

<!-- METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4" NAME="ADODB Type Library" -->

[cancer10]

Thanx

[so_is_this]

You can also change this:

Loop Until Not rs.EOF

to this:

Loop While rs.EOF

if it reads better for you.

[cancer10]

I tried your new code but get this error

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.


at this line

rsbday.Close()


Any ideas?

[so_is_this]

You made *all* the changes in blue in that edited code post of mine?

[cancer10]

However if I comment the following lines then its working fine

'If rsbday.State = adStateOpen Then
'rsbday.Close()
'End If

[cancer10]

That problem is resolved anyways, the following seems to be a bug in the code.

I have the following dates in the table

11/1/1982

11/21/1982

11/14/1982


its selecting 11/1/1982 as the upcomming b'day instead of 11/14/1982



Plz check

[so_is_this]

The code selects all three dates at the same time because thay are all in the same month.

[cancer10]

its not giving the second priority to the day?

[so_is_this]

You'd have to write that in yourself. As written, only the month is being tested. Did you not notice the word "Month" on both sides of the condition? (workmo is also created using "Month")