Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Error in this statement-->sqlSELsite = "SELECT Name FROM tblbwday WHERE B'day ="& date() &""

Reply
Thread Tools

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

 
 
divya
Guest
Posts: n/a
 
      07-31-2006
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

 
Reply With Quote
 
 
 
 
divya
Guest
Posts: n/a
 
      07-31-2006
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


 
Reply With Quote
 
 
 
 
Mike Brind
Guest
Posts: n/a
 
      07-31-2006
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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-31-2006
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding a variable name to a hash to name is part of the variable name Bobby Chamness Perl 2 04-22-2007 09:54 PM
print("my name is {name}, and {age}-year old {gender}", name, age, gender); =?iso-8859-1?B?bW9vcJk=?= Java 7 01-02-2006 04:39 PM
IE name="name" & form.name property bug Java script Dude Javascript 5 06-30-2004 03:07 AM
name = name.substring(0, name.lastIndexOf('.')); Help please Jack-2 Javascript 3 12-24-2003 04:39 PM
Re: Urgent! how to get object name, method name and attribute name based on the strings? ding feng C++ 2 06-25-2003 01:18 PM



Advertisments