?
Solved

A number is required here. Error in File C:\WINDOWS\TEMP\Rpt...

Posted on 2011-03-24
40
Medium Priority
?
2,750 Views
Last Modified: 2012-08-14
I have a series of reports that the user enters and ID# and selects a date and the report gets created, well they worked a few months ago, all of a sudden they started displaying this error: Server Error in '/' Application.

--------------------------------------------------------------------------------

A number is required here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {2B4B597A-D260-4A96-99DE-D50CFE675F59}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}='36170' and DateValue({Products.Eff_date})=DateValue (2011,1,1)'
A number is required here.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: A number is required here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {2B4B597A-D260-4A96-99DE-D50CFE675F59}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}='36170' and DateValue({Products.Eff_date})=DateValue (2011,1,1)'
A number is required here.

Source Error:


Line 208:
Line 209:        ObjASPNET_Crys.setSelectionFormula(sformula)
Line 210:        If ObjASPNET_Crys.Obj_CrRepDoc.Rows.Count = 0 Then
Line 211:            lblError.Text = "No Records Found."
Line 212:            tbl.Visible = True
 
 RptCertibyExpdate.rpt
Dim dteff As Date
        dteff = txtEff_Date.Text
        Dim sformula As String = ""
        If txtCoNum.Text <> "" Then
            dteff = txtEff_Date.Text
sformula = "{Company.CoNum}=" & txtCoNum.Text & " and DateValue({Products.DateApplied})=DateValue (" & dteff.Year & "," & dteff.Month & "," & dteff.Day & ")"
        End If

 If txtAZID.Text <> "" Then
            dteff = txtEff_Date.Text
            sformula = "{Products.AZID#}=" & txtAZID.Text & " and DateValue({Products.DateApplied})=DateValue (" & dteff.Year & "," & dteff.Month & "," & dteff.Day & ")"
        End If

        ObjASPNET_Crys.setSelectionFormula(sformula)
        If ObjASPNET_Crys.Obj_CrRepDoc.Rows.Count = 0 Then
            lblError.Text = "No Records Found."
            tbl.Visible = True
            divrpt.Visible = False
            Exit Sub
        Else
            lblError.Text = ""
        End If
        CRViewer.ReportSource = ObjASPNET_Crys.Obj_CrRepDoc
        Session("RptCertibyEffdate") = ObjASPNET_Crys.Obj_CrRepDoc
        tbl.Visible = False
        divrpt.Visible = True
    End Sub

Open in new window

0
Comment
Question by:Mansink
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 18
  • 2
  • +1
40 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35210063
If "all of a sudden they" ALL "started displaying this error: Server Error in '/' Application." this may be due to an environment change - OS or SQL or Crystal report patch, NT domain authentication or network changes. Other than that I suggets try some reports using parameters that "worked a few months ago"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35210566
Is  {Products.AZID#} a number field?  If so try using

sformula = "{Products.AZID#}=" & Val(txtAZID.Text) & " and DateValue({Products.DateApplied})=DateValue (" & dteff.Year & "," & dteff.Month & "," & dteff.Day & ")"

mlmcc
0
 

Author Comment

by:Mansink
ID: 35211057
@lcohan: we have verified and no changes have occured, no patches or up dates since.

@mlmcc: I ran the report with your code change but instead of displaying just one line which included the unique AZID# it ran all the AZID#'s on the selected date. Also when I ran just the company number with a date, it provided the results but displayed each result 4 times,  very odd indeed.

Any more ideas?

ps. thanks for your help so far.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 101

Expert Comment

by:mlmcc
ID: 35211555
What Crystal references are you including?

How is the report object declared?

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35211764
Since this just started, do you have some bad data or is someone entering bad data?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35211852
that is the first thing I thought, but I haven't found any bad data
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35211946
How is the selected value entered?
Does the user simply type it in?
Could they be typing the ' ' around the value?
VB normally wouldn't add the ' ' when concatenating the vlaues together.

mlmcc
0
 

Author Comment

by:Mansink
ID: 35212150
it's only one user, and she enters the values into the form without the quotes. I just spent a few minutes with her observing how she entered info into the form.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35212197
Is it happening all the time?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35212246
yes and it is affecting a few other reports, and it started 1 week ago. But no patches or updates have been applied.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35212598
The error message shows the ' ' around the number.  For some reason those are being added.  It isn't in the code you are showing .

Can you add a msgbox to the code showing the selection formula just before it is used?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35212988
sorry, i dont understand your question?
0
 
LVL 35

Expert Comment

by:James0628
ID: 35213621
In CertificatesEffectiveDate.aspx.vb, you have this code near the end:

        If txtAZID.Text <> "" Then
            dteff = txtEff_Date.Text
            sformula = "{Products.AZID#}='" & txtAZID.Text & "' and DateValue({Products.Eff_date})=DateValue (" & dteff.Year & "," & dteff.Month & "," & dteff.Day & ")"
        End If


 Single-quotes are being added before and after txtAZID.Text, and, as has already been discussed, those quotes seem to be the problem.  They may not be that obvious when viewing the code here, but if you look at it in something like Notepad, they're easy to see.  The code in your original post does not have those quotes, but they're there in CertificatesEffectiveDate.aspx.vb .

 Also, the code in your original post uses {Products.DateApplied}, but CertificatesEffectiveDate.aspx.vb uses {Products.Eff_date}.  Maybe the code in your original post was the wrong code and you couldn't see the problem (the single-quotes) because you were looking at the wrong code?

 James
0
 

Author Comment

by:Mansink
ID: 35215833
the single quotes removed didn't make a difference.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35215978
Is Products.Eff_date a datetime field?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35216747
yes it is
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35216794
Can the values be NULL?

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35216811
What types are dteff.Year, dteff.Month, and  dteff.Day

mlmcc
0
 

Author Comment

by:Mansink
ID: 35216937
actually it is a sql2008 date value and it can't be null
0
 

Author Comment

by:Mansink
ID: 35217036
dteff.Year, dteff.Month, and  dteff.Day  make up Products.Eff_date, which is selected from an ajax calendar.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35217074
Are they treated as numbers or characters?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35217101
DateValue({Products.Eff_date})=DateValue (" & dteff.Year & "," & dteff.Month & "," & dteff.Day & ")

I believe crystal treats them as part of a date
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35217323
The values need to be numbers not strings that are numbers.

Are you still getting the error?

If so can you copy it and post it again?

mlmcc
0
 

Author Comment

by:Mansink
ID: 35218171
same error as before, just one report looks for expiration date and the other report is just like it but looks for effective date but it still the same error. I dont know why it would just stop running, maybe I just need to rewrite them as SSRS...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35219587
How about trying it as

sformula = "{Products.AZID#}='" & txtAZID.Text & "' and DateValue({Products.Eff_date})= dteff

mlmcc
0
 

Author Comment

by:Mansink
ID: 35220421
just get a slightly different error:

Server Error in '/' Application.
A number, currency amount, boolean, date, time, date-time, or string is expected here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {0F9754BD-C344-430F-8E9A-59D3A3DEC8DC}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}='36170' and DateValue({Products.Eff_date})= dteff'
A number, currency amount, boolean, date, time, date-time, or string is expected here.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: A number, currency amount, boolean, date, time, date-time, or string is expected here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {0F9754BD-C344-430F-8E9A-59D3A3DEC8DC}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}='36170' and DateValue({Products.Eff_date})= dteff'
A number, currency amount, boolean, date, time, date-time, or string is expected here.

Source Error:

Line 215:
Line 216:        ObjASPNET_Crys.setSelectionFormula(sformula)
Line 217:        If ObjASPNET_Crys.Obj_CrRepDoc.Rows.Count = 0 Then
Line 218:            lblError.Text = "No Records Found."
Line 219:            tbl.Visible = True


Source File: C:\Inetpub\wwwroot\NewPest\CertificatesEffectiveDate.aspx.vb    Line: 217

Stack Trace:

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35220585
You still have the ' ' around the number.  Are you sure you changed that?
Or is the AZID# field a string

mlmcc
0
 

Author Comment

by:Mansink
ID: 35220703
yes ive removed those single quotes
0
 

Author Comment

by:Mansink
ID: 35220715
i had posted the wrong error message. here is the correct one.

Server Error in '/' Application.
--------------------------------------------------------------------------------

A number, currency amount, boolean, date, time, date-time, or string is expected here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {F419A90D-E45C-4317-89C5-C2D987F3E65B}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=36170 and DateValue({Products.Eff_date})= dteff'
A number, currency amount, boolean, date, time, date-time, or string is expected here.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: A number, currency amount, boolean, date, time, date-time, or string is expected here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {F419A90D-E45C-4317-89C5-C2D987F3E65B}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=36170 and DateValue({Products.Eff_date})= dteff'
A number, currency amount, boolean, date, time, date-time, or string is expected here.

Source Error:


Line 209:
Line 210:        ObjASPNET_Crys.setSelectionFormula(sformula)
Line 211:        If ObjASPNET_Crys.Obj_CrRepDoc.Rows.Count = 0 Then
Line 212:            lblError.Text = "No Records Found."
Line 213:            tbl.Visible = True
 

Source File: C:\Inetpub\wwwroot\NewPest\CertificatesEffectiveDate.aspx.vb    Line: 211

Stack Trace:


[COMException (0x80041019): A number, currency amount, boolean, date, time, date-time, or string is expected here.
Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {F419A90D-E45C-4317-89C5-C2D987F3E65B}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=36170 and DateValue({Products.Eff_date})= dteff'
A number, currency amount, boolean, date, time, date-time, or string is expected here.]
   CrystalDecisions.ReportAppServer.Controllers.RowsetControllerClass.CreateCursor(ISCRGroupPath GroupPath, RowsetMetaData MetaData, Int32 Reserved) +0
   CrystalDecisions.ReportAppServer.CrystalReportDataView.EnsureDataTableIsFilled() +1193
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_DataView() +50
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_InternalCollection() +31
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_Count() +31
   CertificatesEffectiveDate.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewPest\CertificatesEffectiveDate.aspx.vb:211
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3615; ASP.NET Version:2.0.50727.3618
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35220786
Try

sformula = "{Products.AZID#}='" & txtAZID.Text & "' and DateValue({Products.Eff_date})= dteff.date

mlmcc

0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 35221460
 mlmcc,

 FYI, those formulas that you've been posting include the single-quotes.  Mansink apparently removed them from the previous formula that you posted.  I just wanted to mention that they're there.

 And you're leaving out a closing quote.  I assume that Mansink added one, or presumably the code wouldn't run, but he seems to have added it at the wrong place (at the end).  I think your last two formulas should be something like:

sformula = "{Products.AZID#}=" & txtAZID.Text & " and DateValue({Products.Eff_date})=" & dteff


sformula = "{Products.AZID#}=" & txtAZID.Text & " and DateValue({Products.Eff_date})=" & dteff.date


 However, if dteff is a date value, then can it just be appended to sformula like that?  I'm guessing that it would probably need to be converted to a string, and the string put in "#"s (to make it a datetime value in CR), or converted to a date in CR using something like DateValue (like the code was originally doing with dteff.Year, dteff.Month and dteff.Day).



 Mansink,

 Going back to my post where I pointed out the single-quotes in one of the files that you had posted, you said that removing the quotes didn't make a difference.  Did you actually get the same error as before (A number is required here), or did you get a different error?

 James
0
 

Author Comment

by:Mansink
ID: 35222830
@James it was the same error, it just didnt display the single quotes in the error message.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 35223014
Did you try this one where James fixed the ' '

sformula = "{Products.AZID#}=" & txtAZID.Text & " and DateValue({Products.Eff_date})=" & dteff.date


mlmcc
0
 

Author Comment

by:Mansink
ID: 35225852
different error:

Server Error in '/' Application.
A date is required here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {E4931866-95F9-4AF6-B86B-7E5E26298C4A}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=10 and DateValue({Products.Eff_date})=1/1/2011'
A date is required here.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: A date is required here.Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {E4931866-95F9-4AF6-B86B-7E5E26298C4A}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=10 and DateValue({Products.Eff_date})=1/1/2011'
A date is required here.

Source Error:

Line 210:
Line 211:        ObjASPNET_Crys.setSelectionFormula(sformula)
Line 212:        If ObjASPNET_Crys.Obj_CrRepDoc.Rows.Count = 0 Then
Line 213:            lblError.Text = "No Records Found."
Line 214:            tbl.Visible = True


Source File: C:\Inetpub\wwwroot\NewPest\CertificatesEffectiveDate.aspx.vb    Line: 212

Stack Trace:

[COMException (0x80041019): A date is required here.
Error in File C:\WINDOWS\TEMP\RptCertibyEffdate {E4931866-95F9-4AF6-B86B-7E5E26298C4A}.rpt:
Error in formula  <Record Selection>.
'{Products.AZID#}=10 and DateValue({Products.Eff_date})=1/1/2011'
A date is required here.]
   CrystalDecisions.ReportAppServer.Controllers.RowsetControllerClass.CreateCursor(ISCRGroupPath GroupPath, RowsetMetaData MetaData, Int32 Reserved) +0
   CrystalDecisions.ReportAppServer.CrystalReportDataView.EnsureDataTableIsFilled() +1193
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_DataView() +50
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_InternalCollection() +31
   CrystalDecisions.ReportAppServer.CrystalReportDataView.get_Count() +31
   CertificatesEffectiveDate.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewPest\CertificatesEffectiveDate.aspx.vb:212
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


Version Information: Microsoft .NET Framework Version:2.0.50727.3615; ASP.NET Version:2.0.50727.3618
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35226187
Try it this way

sformula = "{Products.AZID#}=" & txtAZID.Text & " and DateValue({Products.Eff_date})= #" & dteff.date & "#"

mlmcc

0
 

Author Comment

by:Mansink
ID: 35227215
ok that seems to have done the trick, but I am now getting double results, and idea on what to do to the report to avoid this? I clicked on "supress if duplicated" for all the fields but it leave a large white space between results. Right now I left the AZID field with "supress if duplicated" on. Any ideas? screen result with double results
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35227941
You could try that on all the fields then in the section expert use the SUPPRESS BLANK SECTION

It would be better to determine why you are getting duplicate records.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35233214
Obviously that is the solution to the folow on question and not the original question asked.

You need to select the comment(s) that answer the original question and reward those questions.

The comment 35221460 from James was the first to suggest using the # # around the date.  He also pointed out you had ' ' around the number even though the code you posted didn't show them.

Please click the REQUEST ATTENTION link in the question block and request this be reopened so you can reward the appropriate comments and experts.

mlmcc
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question