Error in this statement-->sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day ="& date() &""

[divya]

Hi,
I have a table tblbwday with 2 fields Name and Birthday.I have written
this script for displaying evryday names of the people on that day.

<%
set objConn =server.createobject("ADODB.connection")
objConn.open "DSN=Photo"
Dim sqlSELsite,ObjRSSel

sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day ="& date() &" " '
Error in this line
Set ObjRSSel = Server.CreateObject("ADODB.Recordset")
ObjRSSel.Open sqlSELsite,objConn
Do While Not objRS.EOF
Response.Write "Name: " &objRSel("Name")
Response.Write "<P><HR><P>"
objRSel.MoveNext
Loop
%>
Error is:-

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in
query expression 'B'day =7/31/2006'.
/Project1_Local/ASP Page4.asp, line 15

I have added a record with today's date in the table.I know this logic
is wrong we need to compare only day and month.and this will compare
year also.Plz send me some tips which can help me in comparing only day
and month.
intDays = DatePart("y",now) will give me the curent day in the year.Can
I compare intdays with the b'day's in the table?

Regards
Divya

 

[divya]

Hi
I changed the code to this

<%set objConn =server.createobject("ADODB.connection")
objConn.open "DSN=Photo"
Dim sqlSELsite,ObjRS
sqlSELsite = "SELECT * FROM tblbwday "
Set ObjRS = Server.CreateObject("ADODB.Recordset")
ObjRS.Open sqlSELsite,objConn
Do While Not ObjRS.EOF
If Datepart("y",objRS("B'day")) =DatePart("y",now) then
Response.Write objRS("Name")
Response.Write "<BR>"
End If
objRS.MoveNext
Loop
%>

this works.

but I want to know if I want to use the date function inside the Sql
query how do I do?

sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day =#"& date() &"# "
This doesn't work.Plz tell me.
Regards
Divya

 

[Mike Brind]

Your prblem stems from the use of B'day as a field name. The
apostrophe makes ADO think it has come to the begining of a text value
in an SQL statement. Change the field name to get rid of the
apostrophe.

Don't use SELECT *. It is poor practice, and returns more data than
you probably need. The reason it works for you here is you didn't name
B'day in your select statement.

sqlSELsite = "SELECT Name FROM tblbwday WHERE Bday = Date()" should do
it.

Something to consider: You are using an outdated, deprecated,
inefficient DSN and the ODBC drivers to connect to your database. You
should use the native OLEDB driver instead.

http://www.aspfaq.com/show.asp?id=2126

This is nothing to do with your problem, but should help generally in
future

--
Mike Brind

Hi
I changed the code to this

<%set objConn =server.createobject("ADODB.connection")
objConn.open "DSN=Photo"
Dim sqlSELsite,ObjRS
sqlSELsite = "SELECT * FROM tblbwday "
Set ObjRS = Server.CreateObject("ADODB.Recordset")
ObjRS.Open sqlSELsite,objConn
Do While Not ObjRS.EOF
If Datepart("y",objRS("B'day")) =DatePart("y",now) then
Response.Write objRS("Name")
Response.Write "<BR>"
End If
objRS.MoveNext
Loop
%>

this works.

but I want to know if I want to use the date function inside the Sql
query how do I do?

sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day =#"& date() &"# "
This doesn't work.Plz tell me.
Regards
Divya

Hi,
I have a table tblbwday with 2 fields Name and Birthday.I have written
this script for displaying evryday names of the people on that day.

<%
set objConn =server.createobject("ADODB.connection")
objConn.open "DSN=Photo"
Dim sqlSELsite,ObjRSSel

sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day ="& date() &" " '
Error in this line
Set ObjRSSel = Server.CreateObject("ADODB.Recordset")
ObjRSSel.Open sqlSELsite,objConn
Do While Not objRS.EOF
Response.Write "Name: " &objRSel("Name")
Response.Write "<P><HR><P>"
objRSel.MoveNext
Loop
%>
Error is:-

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in
query expression 'B'day =7/31/2006'.
/Project1_Local/ASP Page4.asp, line 15

I have added a record with today's date in the table.I know this logic
is wrong we need to compare only day and month.and this will compare
year also.Plz send me some tips which can help me in comparing only day
and month.
intDays = DatePart("y",now) will give me the curent day in the year.Can
I compare intdays with the b'day's in the table?

Regards
Divya

 

[Bob Barrows [MVP]]

Hi,
I have a table tblbwday with 2 fields Name and Birthday.
I have written
this script for displaying evryday names of the people on that day.

<%
set objConn =server.createobject("ADODB.connection")
objConn.open "DSN=Photo"

Nothing to do with your problem, but you should stop using DSN's:
http://www.aspfaq.com/show.asp?id=2126

Dim sqlSELsite,ObjRSSel

sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day ="& date() &" " '
Error in this line
Set ObjRSSel = Server.CreateObject("ADODB.Recordset")
ObjRSSel.Open sqlSELsite,objConn
Do While Not objRS.EOF
Response.Write "Name: " &objRSel("Name")
Response.Write "<P><HR><P>"
objRSel.MoveNext
Loop
%>
Error is:-

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in
query expression 'B'day =7/31/2006'.
/Project1_Local/ASP Page4.asp, line 15

I have added a record with today's date in the table.I know this logic
is wrong we need to compare only day and month.and this will compare
year also.Plz send me some tips which can help me in comparing only
day and month.
intDays = DatePart("y",now) will give me the curent day in the
year.Can I compare intdays with the b'day's in the table?

You've got several problems:

1. The use of nonstandard characters in your field names. "B'Day"???
What's wrong with BDay, or even BirthDay?
If you can't change the name of this field (and you really should), you
are going to need to surround it with brackets when using it in queries
called by ADO:
[B'Day]

2. Is [B'Day] a Date/Time field? If so, it does not store just the date:
it stores both date and time. If no time is entered, thhen it defaults
to midnight. Dates are stored as Double numbers, with the integer
portion representing the number of days since the seed date, and the
decimal portion represnting the time of day (.0=midnight, .5 = noon)

Given that it's a date/time field this will work:

sqlSELsite =" ... WHERE [B'Day] >= Date() AND " & _
"[B'Day] < dateadd(1,'d',Date())"