Solved

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

Posted on 2011-03-24
40
2,638 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
  • 18
  • 18
  • 2
  • +1
40 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
@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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What Crystal references are you including?

How is the report object declared?

mlmcc
0
 

Author Comment

by:Mansink
Comment Utility
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:Mansink
Comment Utility
that is the first thing I thought, but I haven't found any bad data
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Is it happening all the time?

mlmcc
0
 

Author Comment

by:Mansink
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
sorry, i dont understand your question?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
the single quotes removed didn't make a difference.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Is Products.Eff_date a datetime field?

mlmcc
0
 

Author Comment

by:Mansink
Comment Utility
yes it is
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can the values be NULL?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What types are dteff.Year, dteff.Month, and  dteff.Day

mlmcc
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Author Comment

by:Mansink
Comment Utility
dteff.Year, dteff.Month, and  dteff.Day  make up Products.Eff_date, which is selected from an ajax calendar.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are they treated as numbers or characters?

mlmcc
0
 

Author Comment

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

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

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
How about trying it as

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

mlmcc
0
 

Author Comment

by:Mansink
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
yes ive removed those single quotes
0
 

Author Comment

by:Mansink
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Try

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

mlmcc

0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
Comment Utility
 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
Comment Utility
@James it was the same error, it just didnt display the single quotes in the error message.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
Try it this way

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

mlmcc

0
 

Author Comment

by:Mansink
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now