Passing A Null Date Value To Report

Hi,

I have an ASP.Net page that needs to sometimes pass a null parameter value to the querystring. It bombes out with an error that a date cannot be "" and that i need to supply the date.

Has any1 been able to get this right? How does one pass a null date through the querystring?

Regards
Mike



ADVERTISEMENT

Passing Null Date Value To Database

how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.

View Replies View Related

Drill Through Report -- Passing Date Parameters -- Resulting In RsReportParameterTypeMismatch

All,

 

I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:

 

4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)

which displays correct data.

 

However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:

 

7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)

 

The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).

 

I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?

 

Your help is much appreciated.

 

Thanks.

View Replies View Related

Passing NULL Value Not Working

I currently have a stored procedure that looks something like this  SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@Name+'%', Title)I have a form that supplies this value.  This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not.  On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,SqlDataSource1.SelectParameters.Add("@Name", TextBox1.Text);It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why.  Thank you in advance /jcarver 

View Replies View Related

SQLDataSource Parameters Passing Null

 I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!?  I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
 I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>

View Replies View Related

Passing NULL-value Into Package Variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

 

View Replies View Related

Passing NULL To DataSet Parmameter

Hey All,

    I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

    I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

    When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

 

@ID = <Null> ---- ERROR - [BC30201] Expression expected

@ID=              ---- ERROR - Return statement in function must return a value

@ID = Null     -----ERROR - Null constant not supported use System.DBNull instead

@ID = System.DBNull   ----ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@ID=System.DBNull.Value  --- NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

 

Does anyone know another magic value I can pass to get this to work?

 

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

 

Thanks in advance.

 

Eric Wild

 

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

 

 

 

ALTER PROC [dbo].[spGetLaborRole]
 @ID INT = NULL
AS
BEGIN
 SELECT ID, Descr
 FROM dbo.LaborRole
 WHERE ( (ID = @ID) OR (@ID IS NULL) )

View Replies View Related

Passing Report Parameters To An SSRS 2005 Report Hosted In Sharepoint 2007

Here is a situation in my company:

We have an Office SharePoint 2007 site, we developed a couple of web parts and added them to our site. We used SQL Server Reporting services 2005 as our reporting solution. The reports are hosted on the report center and when we need a report, we open it by sending a URL requesting the report, passing the report parameters in the URL query string. So the report is filtered based on the parameters passed from the web parts.
 
But since best practices say that you should host reports directly under SharePoint, by configuring the reporting services to run in the SharePoint integrated mode. We followed the steps and installed SharePointRS and we finally succeeded to publish the reports to a SharePoint folder, but we had a limitation: we are unable to pass the report parameters internally to the report hosted in SharePoint. If we passed them in the query string as the report center case, SharePoint neglects them totally.
 So the question is: how can I pass parameters internally between a SharePoint web part and a SQL server 2005 reporting services report hosted in SharePoint?

View Replies View Related

Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.

Here's tricky one.
 
I have a fairly complex report that was given to me that was hard coded for single parameters.  There is a dropdown for each market (created from a query in SSRS).  The users have to run for each market each week.
 
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
 
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
 
Essentially, I want to create a for each loop.
 
Any ideas?

View Replies View Related

Form Passing NULL To Database Instead Of Variable

Not sure what i'm doing wrong here. it seems to be sending nulls or no value to database in places where i want it to send the @headline and @entryhere's the .aspx code: <%@ Page Language="c#"     MasterPageFile="~/MasterPage.master"     AutoEventWireup="true"     CodeFile="NewPost.aspx.cs"     Inherits="NewPost"     Title="Scribbler Insert" %>       <asp:Content ID="PageBody" runat="server"    ContentPlaceHolderID="PageBody" ><table border="0" width="700" >  <tr>    <td width="80" valign="top">      Subject:    </td>    <td width="620" valign="top">      <asp:TextBox ID="txtHeadline" runat="server"           Width="400px"/>      <asp:RequiredFieldValidator runat="server"        ID="RequiredFieldValidator1"        ControlToValidate="txtHeadline"        ErrorMessage="Headline is required"         Display="Dynamic" />    </td>  </tr>  <tr>    <td width="80" valign="top">      Text:    </td>    <td width="620" valign="top">      <asp:TextBox ID="txtEntryText" runat="server"           TextMode="MultiLine"          Height="250px"          Width="400px" />      <asp:RequiredFieldValidator runat="server"        ID="RequiredFieldValidator2"        ControlToValidate="txtEntryText"        ErrorMessage="Text is required"         Display="Dynamic" />    </td>  </tr></table><asp:Button ID="btnPost" runat="server"     Text="Post" OnClick="btnPost_Click" /><asp:Button ID="btnCancel" runat="server"     PostBackUrl="~/Admin/Admin.aspx"     Text="Cancel" />   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:scribblerdbConnectionString %>"            ProviderName="<%$ ConnectionStrings:scribblerdbConnectionString.ProviderName %>"    InsertCommand="INSERT INTO entries_tbl (Headline, entry, PostedBy, PostedDate, PostedTime) VALUES (@Headline, @Entry, 'SteveP', '2007-12-12', '12:44');" >         <InsertParameters>            <asp:ControlParameter Name="Headline"                ControlID="txtHeadline"                PropertyName="Text"                Type="String" />            <asp:ControlParameter Name="entry"                ControlID="txtEntryText"                PropertyName="Text"                Type="String" />             </InsertParameters></asp:SqlDataSource></asp:Content> 

View Replies View Related

Passing Null Values To Stored Procedures

Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!

View Replies View Related

Textbox Not Passing Null To Stored Procedure

Hi,I have a stored procedure which expects a Null value if a user does not enter something into a TextBox. The query should return all rows if this case.The stored procedure works as expected in Query Analyzer, but my application does not seem to send a Null and no rows are returned. Any ideas?Thanks.ps I am using SqlDataSource and GridView, this is the first time I have tried to connect to a database with asp.net.

View Replies View Related

DNN DAL SqlDataProvider Passing NULL To SQL Stored Procedure

Hello,I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value.  My SQL Table boolean column allows nulls.I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table.  One of those fields is a boolean.  I create an instance of the InfoObject in my code and assigns values to the various properties.  The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything.  I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL.  If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens.  How do I pass a null to the SQL database for a boolean field?  I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean."  Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?Any help would be greatly appreciated.  I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.Thanks in advance for any help.

View Replies View Related

Passing Null Value From Front End Access To SQL Back End

Hi,
 
   I'm not sure if this question should be in this thread but anyway...
 
   I have a form (Mricosoft Access front end) in which the user enter a Pending Date in one of the fields. The date is populated into my backend (SQL Server) withouht problems.
   Whenever the issue which had the pending date in the field is resolved I'd like to delete this date from this field and also from the back end. At the moment I'm not being able to pass the "value" Null to my back end (probably because it's a datetime type). I've searched the net throughout and I couldn't find a way around it. Only found solution to when the data is not date but numbers/letters, etc... Any ideas on how can I sort this out?
 
Many thanks
Emanuel 

View Replies View Related

Passing Parameter To The Sql Server Report Using Report Viewer Control

Hi,

I want to give filtering criteria in my SSRS report.

I have drop down list control having list of Email's of clients.

So, how can i pass the value of the particular Email id in my SSRS report using Report Viewer control?

How can i pass user input as parameter in my report using visula studio 2005?

 

Thanx,

Ruja

View Replies View Related

Passing Null To Stored Procedure In Reporting Services

Hello,

 

I have a report.  I need to make a drop-down parameter optional.  I've setup the parameter to use a dataset as its source, and I need to allow the value to be null.  I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value.  But, when the entry is not selected (blank), no data ever comes back.  If the data is blank or null, my procedure works (I've tested it).  How do I send a null value to the database through reporting services report?  I was hoping to avoid creating a special query just for that purpose.

 

Thanks.

View Replies View Related

Passing Null Value Parameter To A Data-driven Subscription

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report.  One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.

 

When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine.  When I try to pass the NULL value from the DDS proc to the report, the subscription fails.

 

I do not know what the error logs say.  I've rarely looked at them and what I have seen seems a bit cryptic.  I've narrowed down through trial-and-error that passing the NULL value is the issue.

 

Has anyone else experienced this issue and do you have a resolution?

 

Thanks in advance for your assistance.

View Replies View Related

Passing Date Value

Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH

View Replies View Related

Passing A Date Name To A Database Back Up

I am trying to pass a datename in to a database backup. The date is to be retrieved from the 'monthname' column which is stored in the 'tblbill' table. I have included my ridiculous bit of code below (which funnily enough does not work!!) just so that you can see what I am trying to do. Can anyone help?

DECLARE @name varchar(10)
backup database Bill to Disk ='c:@name.dat' with init

@name =select distinct monthname from tblbill where monthname is not null

cheers,
Dennis

View Replies View Related

Passing Date Range To A Subreport

I am passing a date range to a subreport which is a table that I want to show a list of months in a specific range.  I am passing:

FromDateShipedYearMonth - ([Date Shipped].[Month].&[2006]&[4]&[12]
ToDateShippedYearMonth - ([Date Shipped].[Month].&[2007]&[4]&[11])
 
I have parameters set up in my Main report:


<Subreport Name="subreport2">

<Parameters>

<Parameter Name="FromDateShippedYearMonth">

<Value>= "(" &amp; Code.PreviousYear(Parameters!DateShippedMonth.Value) &amp; ")"</Value>

</Parameter>

<Parameter Name="ToDateShippedYearMonth">

<Value>="(" &amp; Parameters!DateShippedMonth.Value &amp; ")"</Value>

</Parameter>

</Parameters>
 
And in my Subreport:


<ReportParameter Name="FromDateShippedYearMonth">

<DataType>String</DataType>

<Prompt>FromDateShippedYearMonth</Prompt>

</ReportParameter>

<ReportParameter Name="ToDateShippedYearMonth">

<DataType>String</DataType>

<Prompt>Year - Month</Prompt>

</ReportParameter>

</ReportParameters>
 
I would like to display data from 12/2006 through 11/2007.  Instead I get a list of 1/2006 through 12/2006.
 
Any ideas?
 
 
 
 

View Replies View Related

Passing Date Parameter To Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is


WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?

View Replies View Related

Passing Date Fields From Form To Access ADP Views

I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq

View Replies View Related

Passing Parms From .Net And Using Report/query Parms In Report Viewer Control

I have a dozen Web reports that work when i pass parms via the parm collection from an app to the report and mapped as report parameters. Nice. Another dozen reports that work with reprot viewer and query parameters ("@") mapped and the automagic dropdown boxes to allow selection and regeneration of reports within the  RV control. Nice. 
 
However, when i try to create a program that passes a parm via the collection AND use query/report parms inherent in the report viewer control, it works in the DEV environment but not PRODUCTION. It works on REPORT SERVER when i unhide the program passed parm but not when i try it from the app when hidden. 
 
I have read several of the reporting books on integrating RS with .Net apps, and they all seem one or the other, programatically with SETPARAMETER or with the query/report parms mapped in the RV control. I have not seen any examples where you can use both at same time.  Maybe, i missed something, but can you mix the two - .Net app parms AND report viewer parms ?  

View Replies View Related

Passing Parameters To A Sub-report

Hi. I've got a report I'm designing for the web using a ReportViewer control and an RDLC report file. Here's what needs to happen in the report,


The main report calls one stored procedure with some user provided parameters, which returns a dataset.
A sub-report will use 2 of the columns returned from the dataset above to call another stored procedure, which then provides the dataset used for the ReportViewer control.
I'm lost in trying to implement this. How do I call the first stored procedure to get the data? Then how do I pass the returned columns to the sub-report?

The main report will have a number of these sub-reports, each on its own page. I'm just trying to implement the first sub-report. Is there an easier way to implement this without using sub-reports? Thanks.

View Replies View Related

Passing Report Parameters Through URL

Hi, I've read some threads in regards to passing report parameters through the 'URL' but I am struggling to fully understand. To keep things simple I have 1 report parameter, named par_pub which brings back either ww06 or ww07. What I require is to click on a hyperlink and it directs me to my report and then populates the par_pub paramerter with ww07.

I am trying the below

 

http://emp6/Reports/Pages/Report.aspx?ItemPath=%2fTest+Reports%2fIT+Dev%2fSales+Summary+Detail&prefixar_pub=%WW07%

 

The part in bold is the location + name of the report and the later is where I am trying to pass the parameter. Can anyone please help as this is not working.

Thanks

View Replies View Related

Sub Report Passing Data Up

Hi

Can anyone help with this. Is it possible to pass data from a sub-report to the main report. I know this was possible in Crystal.

I currently have VS2005 but may be able to justify a move to 2008 if it can do it.

Thanks

View Replies View Related

Passing 2 Parameters To Report

 

Hi,
I have created a report in VS 2003 using command type as Text for a stored procedure. The stored procedure itself takes in 2 parameters.
I have passed one of them in the exec statement but the other parameter is the one which I want to pass to the stored procedure through report.
So, in short, I have got this statement in dataset
 
EXEC SP1 1
 
Which means only 1 of the arguments is being passed to the stored procedure and not the second one. I would appreciate if some one can guide me on how to pass a report parameter to the stored procedure which using command type text

 
Cheers.

View Replies View Related

Passing Report Name When Exporting

Hi Recently we upgraded to SQL 2005 SP2 from SQL 2005. However a problem I am noticing is on Windows 2000 machines, the report name doesn't automatically pass the save as dialog box. Anybody else get this problem or know how to fix it?
 
The default value in the save as dialog box is Reserved.ReportViewerWebControl.  Also when I click export and I'm given the option to open/save the file, the file name is Reserved.ReportViewerWebControl.axd

View Replies View Related

Passing Parameters From One Report To Another Using URL

 

Hi,
 
I have a problem getting the URL string right to pass parameters from one report to another
 
This is the URL string I have set in the Navigation property of my text box:
"http://SQL/Reportserver$SQL2005/Pages/Report.aspx?%2fEarlyWarning2%2fratio+reports+-+test%2freport_upper_lower_benchmark_ratio1&rs:Command=Render&rcarameters=false
&company="&Parameters!company.Value.ToString()&Parameters!year.Value.ToString()&Fields!ratioName.Value.ToString()

 
 
The first thing is that when I just want to view the report say, with parameters hidden,  I get this URL
http://reportserver/?%2fEarlyWarning2%2fratio+reports+-+test%2freport_upper_lower_benchmark_ratio1&rs%3aCommand=Render&rc%3aParameters=false

 
with the message "Internet Explorer cannot display the webpage"
 
Why does the first part of the URL change?
 
If I run it with just one parameter, company, I get the same error message with this URL :
http://reportserver/?%2fcompany.Value.ToString()&rs%3aCommand=Render&rc%3aParameters=false&company=%22
 
It looks like no value for the company parameter being passed
 
Any suggestions appreciated
 
Ruth
 

View Replies View Related

Passing Report Parameter Through URL

I am experiencing a problem that has been posted a number of times but cannot find a solution. Passed linked report parameter value through URL:
 
Jump to URL:



Code Block="javascript:void(window.open('http://myservert/Reports/Pages/Report.aspx?ItemPath=/Sales/Enquiries/Order+Enquiry&rs:Command=Render&Cust="+Fields!Cust.Value+"'))"
 
 


 
When I click the link I am taken to the report but it is waiting for me to enter a parameter. I can see the parameter in the url address and it looks correct. I also tried Parameter!Cust.value (which has the same value as Fields!Cust.Value) but get the same result.
 
The target report has the correct name parameter: Cust. In any case, when I use "Jump to report" instead, it works correctly - although I don't see the parameter value in the url.
 
Any advice appreciated.
 
Thanks,
SQL Servant

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved