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
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 19 Replies View RelatedAll,
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.
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 4 Replies View RelatedIn my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
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?
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?
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
columns are **Level** and **Salesamount**.
Values are like
**[-]category** **100**
**[-]subcat** **50**
**product** **30**
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
Here i used the expressions in parameter values
**pCategory**----iif(inscope(category),!fields.category.value,split(join(Parameters!category.value,","),","))
**psubcat**----iif(inscope(subcat),!fields.subcat.value,split(join(Parameters!subcat.value,","),","))
**pproduct**----iif(inscope(product),!fields.product.value,split(join(Parameters!product.value,","),","))
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>
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...
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) )
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
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 3 Replies View RelatedVersion: 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 4 Replies View RelatedNot 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 2 Replies View RelatedHello,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 2 Replies View RelatedHi,
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
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.
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.
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 1 Replies View RelatedI 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
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>= "(" & Code.PreviousYear(Parameters!DateShippedMonth.Value) & ")"</Value>
</Parameter>
<Parameter Name="ToDateShippedYearMonth">
<Value>="(" & Parameters!DateShippedMonth.Value & ")"</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?
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
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
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
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
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.
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.
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
I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".
Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)
[Code] ....
I want to pass a couple of date variables in British date format to a function
Here's the function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
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?
I'm currently trying to create a report that will take three parameters in the URL to generate a report: year, month, and day. The report is using an analysis services data source in RS 2005. Now, my problem is that the parameters being passed in the URL are not being selected in the drop down boxes for the parameters. The report does not throw any exceptions, and after the user selects values for the year, month, and day everything generates successfully.
Here is the URL format I am trying: http://localhost/ReportServer?/FolderName/ReportName&rs:Command=render&Year=2006&Month=6&Day=9
Currently, my parameters are all set to null for default values. They are also not multivalued, not internal, not hidden, don't allow blank, and don't allow null. I have also looked through the valid values in the drop down box for each parameter and verified that the values I supplied in the URL do exist. Also, the data types for each parameter is Integer just like the data type found in the database.
In my opinion, this problem seems directly related to using an analysis services data source. After I was getting this problem, I created a sample report using SQL Server data source, used the same three parameters, and passed them in the URL. Everything on that report worked perfectly.
Please help, I've looked thorugh almost every piece of documentation regarding parameters for Reporting Services and still no luck. Thanks!!