Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Invalid operator for data type boolean - datetime PLEASE HELP

Status
Not open for further replies.
jennypretty

jennypretty

IS-IT--Management
Apr 13, 2005
45
US
HELLO FRIENDS,
I am trying to select only 4 digits of year but it keeps generating errors. This is the date of my table "04/13/2005", and I am trying to select the year ONLY. I used DATEPART to convert in sql server.
Below is the block that generates errors.
Can you please help me?
Thanks,
Jenny.

rptStr = "select Ref_Date_Id, ref_begin_date, Ref_Begin_Date & ' - ' & Ref_End_Date from Ref_Date"
rptStr = rptStr & " order by Ref_Date_Id Asc"
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
if (fyValue = 0) or (DatePart("YYYY",rs(1)) = Cint(fyValue)) then
Response.Write("<OPTION value=" & rs(0) & ">")
Response.Write(rs(2))
Response.Write("</OPTION>")

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals boolean AND, type equals datetime.
 
Sort by date Sort by votes
You mean I need to change this line to:
if (fyValue = 0) or (right(rs("Ref_Begin_date"),4) = Cint(fyValue)) then
 
Upvote 0 Downvote
try and see

ex: of if

if (0) or (2003 = 2003) then

is that what your trying to do
 
Upvote 0 Downvote
Yes, I am trying to convert to Integer "Cint".
Jenny.
 
Upvote 0 Downvote
The VBScript Year() function might help you.

Below is cut-n-paste directly from the VBScript Language Reference:
Code: 
Microsoft® Visual Basic® Scripting Edition
[b]Year Function[/b]   Language Reference 
Version 1 
 
------------------------------------------------------------
Description: Returns a whole number representing the year.
Syntax:  Year(date)

The date argument is any expression that can represent a date. If date contains Null, Null is returned.

Remarks
The following example uses the Year function to obtain the year from a specified date: 
Dim MyDate, MyYear
MyDate = #October 19, 1962#   ' Assign a date.
MyYear = Year(MyDate)         ' MyYear contains 1962.
 
Upvote 0 Downvote
I did try it but the same error occur.
I think something wrong with the datatype conversion.
Jenny.
 
Upvote 0 Downvote
Lets add some more feedback to help with debugging:

Code: 
[red]    Response.Write "rptStr = " & rptStr & "<BR>"[/red]
    rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
    While Not rs.EOF
[red]
       For Each Foo in rs.Fields
         Response.Write Foo.Name & " = " & Foo.Value & "<BR>"
       Next
[/red]
       ....

We might be barking up the wrong tree...
 
Upvote 0 Downvote
It doesn't work.
rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
That line generates error, I don't know why.
Please help.
Jenny.
 
Upvote 0 Downvote
how about we can this line
Code: 
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly

to

Code: 
Set rs = dbConn.execute(rptStr)
 
Upvote 0 Downvote
The same errors occur, below is the script. Can you please take a look at it.
Thanks,
Jenny.

<%@ Language=VBScript %>
<SCRIPT id=DebugDirectives runat=server language=javascript>
// Set these to true to enable debugging or tracing
@set @debug=false
@set @trace=false
</SCRIPT>
<HTML>
<HEAD>
<META name=VI60_defaultClientScript content=VBScript>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>Summary</TITLE>
</HEAD>
<BODY bgColor=LightSkyBlue>

<DIV ALIGN=CENTER>
<FORM METHOD = "post" id=form1 name=form1>
<TABLE cellSpacing=1 cellPadding=1 border=0 align=center >
<TD>Fiscal Year:</TD>
<TD><SELECT id=fySelect style="WIDTH: 100px" name=fySelect>
<OPTION Value=0>All Years</OPTION>
<%
dbConn.Open

Dim fyValue
fyValue = Request.Form("fySelect")
'
' Load Year Combo Box
'
rptStr = "select jenid, jenfy from jenfy order by jenid desc"
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
if (cint(rs(0)) = Cint(fyValue)) then
Response.Write("<OPTION value=" & rs(0) & " SELECTED>")
else
Response.Write("<OPTION value=" & rs(0) & ">")
end if
Response.Write(rs(1))
Response.Write("</OPTION>")
rs.MoveNext
Wend
rs.Close
set rs = Nothing
%>
</SELECT></TD>
<TD><INPUT type="submit" value="Apply Filter" id=Submit name=Submit></TD></TD>
</TABLE>
</FORM>
</DIV>
<DIV ALIGN=CENTER>
<TABLE cellSpacing=1 cellPadding=1 border=0 align=center >
<TD>Directorate:</TD>
<TD><SELECT id=dirSelect style="WIDTH: 250px" name=dirSelect>
<%
'
' Load Directorate Combo Box
'
rptStr = "select jendesc from jenfilter"
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
Response.Write("<OPTION>")
Response.Write(rs(0))
Response.Write("</OPTION>")
rs.MoveNext
Wend
rs.Close
set rs = nothing
%>
</SELECT></TD>
<TD><INPUT type="button" value="Update Chart" id=updButton name=updButton></TD>
<TD><INPUT type="button" value="EXCEL Report" id=excelButton name=excelButton></TD></TD></TR>
</TR>
<TR>
<TD>Begin Period Within:</TD>
<TD><SELECT id=rDateSelect style="WIDTH: 200px" name=rDateSelect>
<%
'
' Load Period Combo Box
'
rptStr = "select jenDate_Id, jenbegin_date, jenBegin_Date & ' - ' & jenEnd_Date from jenDate"
rptStr = rptStr & " order by jenDate_Id Asc"

'Set rs = dbConn.execute(rptStr)
Set rs = Server.CreateObject ("ADODB.Recordset")

'rs.Open rptStr, dbConn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF

if (fyValue = 0) or (DatePart("YYYY",rs(1)) = Cint(fyValue)) then
Response.Write("<OPTION value=" & rs(0) & ">")
Response.Write(rs(2))
Response.Write("</OPTION>")
end if
rs.MoveNext
Wend
rs.Close
set rs = Nothing
dbConn.Close
%>
</SELECT></TD>
</TR>
</TABLE>
</DIV>
<DIV align=center>
<OBJECT id=CSpace style="DISPLAY: inline; LEFT: 0px; MARGIN: 0.5cm; TOP: 0px" height=400
width=820 classid="CLSID:0002E556-0000-0000-C000-000000000046" VIEWASTEXT>
<PARAM NAME="ScreenUpdating" VALUE="True">
<PARAM NAME="EnableEvents" VALUE="0"></OBJECT>
</DIV>

<SCRIPT LANGUAGE=vbscript>
function updButton_OnClick()
Dim dirStr, rDateStr

dirStr = dirSelect.selectedIndex
rDateStr = rDateSelect.Options(rDateSelect.selectedIndex).Value
'
' Configure the client side Chart control and request from server.
'
if (CSpace.Style.Display = "none") then
CSpace.Style.Display = "inline"
end if
CSpace.Clear
CSpace.EnableEvents = False
CSpace.Load " & dirStr & "&jeDate=" & rDateStr
CSpace.EnableEvents = True
CSpace.ScreenUpdating = True
CSpace.jeresh

end function

function excelButton_OnClick()
Dim dirStr, rDateStr

dirStr = dirSelect.selectedIndex
rDateStr = rDateSelect.Options(rDateSelect.selectedIndex).Value

window.open(" & dirStr & "&jeDate=" & rDateStr)
end function

</SCRIPT>
</BODY>
</HTML>
 
Upvote 0 Downvote
dbConn appears to be the name of your ADO Connection object variable... except it is never defined or created.
 
Upvote 0 Downvote
Do you have a meta tag in your global.asa that references the ADO GUID?

The reason I ask is because I dont see any reference to it here and I also don't see the adovbs.inc file...

Without one of these, how will your script know the values of constants such as adOpenForwardOnly and adLockReadOnly?
 
Upvote 0 Downvote
I found the code from your original post within the code for that page that you just posted and altered it as follows:

Code: 
<%
'
'   Load Period Combo Box
'
    IF (dbConn.State = 1) THEN
      Response.Write "<BR><BR>Debug: ADO Connection is Open.<BR><BR>"
    ELSE
      Response.Write "<BR><BR>Debug: ADO Connection is Closed!<BR><BR>"    
    END IF
    
    rptStr = "select jenDate_Id, jenbegin_date, jenBegin_Date & ' - ' & jenEnd_Date from jenDate"
    rptStr = rptStr & " order by jenDate_Id Asc"
        
    Response.Write "BR><BR>rptStr = " & rptStr & "BR><BR>"
    
    Set rs = dbConn.execute(rptStr)

    IF (rs.State = 1) THEN
      Response.Write "<BR><BR>Debug: ADO RecordSet is Open.<BR><BR>"
    ELSE
      Response.Write "<BR><BR>Debug: ADO RecordSet is Closed!<BR><BR>"    
    END IF


    IF rs.EOF THEN
      Response.Write "<BR><BR>Debug: No Records Found!<BR><BR>"
    END IF

    
    While Not rs.EOF        
        if (fyValue = 0) or (Year(rs(1)) = Cint(fyValue)) then
            Response.Write("<OPTION value=" & rs(0) & ">")
            Response.Write(rs(2))
            Response.Write("</OPTION>")
        end if
        
        rs.MoveNext
    Wend

    rs.Close
    set rs = Nothing
    dbConn.Close
%>

The addition of the Debug lines will help to isolate the error. You'll remove them when the issue is resolved.
 
Upvote 0 Downvote
The same error occurred.
Can you please take a look at datatype?
The data at the field is "1/13/2003", and I am trying to get the year "2003" ONLY, and it keeps getting errors.
Jenny.
 
Upvote 0 Downvote
What did you see in response to the new Response.Writes ?
 
Upvote 0 Downvote
There is no error on RS. I don't see anything on the new Response.Write so I think the error is not from RS. The error is from the datatype.
Jenny.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
470
DiDYRO_
DiDYRO_
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
1K
GriffMG
GriffMG
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
544
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
437
leifoet
leifoet
Swi
  • Locked
  • Question
Best/Quickest Grid to display data
Replies
1
Views
554
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top