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

Discussion in 'ASP General' started by divya, Jul 31, 2006.

  1. divya

    divya Guest

    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, Jul 31, 2006
    #1
    1. Advertising

  2. divya

    divya Guest

    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


    divya wrote:
    > 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, Jul 31, 2006
    #2
    1. Advertising

  3. divya

    Mike Brind Guest

    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

    divya wrote:
    > 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
    >
    >
    > divya wrote:
    > > 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
    Mike Brind, Jul 31, 2006
    #3
  4. divya wrote:
    > 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())"



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 31, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    585
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,226
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    503
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    705
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    396
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page