.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Parameter Help in my Dataset query

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :ASP.Net
I have a qurey in a data set in reporting services. What im trying to do is use the shipdateyear field as a parameter but i keep getting an error when im running it. I think its because how im getting the name shipdate year its coming from a field where im formatting the data to just show year.heres my query. its erroring in the where Clause i believe. any help would be awesome.   I have underlinded where i think my isues are.  Thanks.select bol.loadnumber, bol.ldhshipdate,bol.shiptocode,bol.destination,bol.shiptoname,bol.itemdesc,bol.abbrev,Datepart(week,bol.ldhshipdate) as 'weeknum',Datepart(weekday,bol.ldhshipdate) as 'weekdaynum',--case statement--Case when Datepart(weekday,bol.ldhshipdate) = 1 then 'Sunday'else case when Datepart(weekday,bol.ldhshipdate) = 2 then 'monday'else case when Datepart(weekday,bol.ldhshipdate) = 3 then 'tuesday'else case when Datepart(weekday,bol.ldhshipdate) = 4 then 'wednesday'else case when Datepart(weekday,bol.ldhshipdate) = 5 then 'thursday'else case when Datepart(weekday,bol.ldhshipdate) = 6 then 'Friday'else case when Datepart(weekday,bol.ldhshipdate) = 7 then 'saturday'else 'none'end end endendendendend as 'dayofweek',--case statement--ofstm.ostabr, ofstm.ostnam,ofstm.ostccd,Datepart(year,bol.ldhshipdate) as shipdateyear,--caseCase when d

View Complete Post

More Related Resource Links

Passing parameter to Subreport dataset query returns no results



I'm using the subreport as a workaround to not being able to join two disparate datasets. The "tickler" file  of keys comes from an Access DB defined as a dataset in my main report. The subreport has a dataset query using parameters from the main report. 

When subreport returns no results, I want to display an error message in the main report and continue to the next row key processing. How do I accomplish that?


MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

How To Remove a Query Parameter from a URL


for a string such as this:


how would I remove the parameter id 12?


adding parameter to query string


hi all,

how do i add a new parameter to an existing query string?


now i need to add a new parameter say, showsearch.

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Duplicate URLs in Source Parameter of Query String for All SharePoint Lists

After performing a database attach method of upgrading our existing web application from MOSS 2007 to SharePoint Server 2010, all of our lists that migrated now exhibit an interesting but undesirable behavior.  The DispForm.aspx and EditForm.aspx pages that get loaded are given the Source parameter of the query string but instead of the URL being a single entry like: Source=http://sharepointsite.domain.com/Pages/Default.aspx It winds up putting the URL twice like this: Source=http://sharepointsite.domain.com/Pages/Default.aspx,http://sharepointsite.domain.com/Pages/Default.aspx Naturally this makes processing of the form -- either by choosing OK or Cancel buttons -- result in an HTTP 404 error.  The question we're left with is how in the world did the Source parameter get set that way to begin with.   It wasn't that way in 2007. We had thought that perhaps there was something that didn't migrate over correctly for the existing lists, so I tried creating a new list and then added a new item to the list.  So far so good.  Then attempted to Edit Properties or Edit Item or even View Item, etc. -- essentially triggering the load of the EditForm.aspx and DispForm.aspx  -- and we received the 404 again.  At this point we're not sure what's going on or how to fix it.  We've performed the migration three times so far with t

MDX Query for Defualt Value in SSRS Parameter

Hello everybody, I am new to MDX and struggling to come up with a query that works.  I have a  SSRS report  that filters by week. I need to write a query that will run for last week as default. The query i have cooked up is: WITH MEMBER Measures.Lastweek as [Time Dimension].[Report Week].currentmember.lag(1) Select Measures.lastweek on 0, [Time Dimension].[Report Week] on 1 from [sales] This returns a null value. I have searched and searched the internet for the answer but i have found nothing. I would really appreciate some help.   Thanks.

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Need to Change a Query String in a DataSet

I need help in pointing me in the right direction...   I have a microsoft Report (ViewDelete.rdlc) that is feeding from a DataSet(ViewDeleteDataSet.xsd). A Web Form has been created with 3 Textboxes: -Search by VehicleNr -Search By VIN -Search By TagNr The Same Web Form as Microsoft ReportViewer loading the above mentioned report.   What Needs to Happen: 1. User entered search information into one of the textboxes and clicks Search 2. Query string for the Report is updated to list only the information the user is wanting to see and reloads the report based on this query.   I have been searching for about 4 hours with no luck on how to do this.  I maybe way off on how this should work, can someone help get me going?

AD Parameter Query

I have a lookup page that displays basic user data from AD in a grid control. I would like to create one of the grid columns as a hyperlink column and pass the username to a new page to display more user details from ad. Any assistance to be able to get the parameter from the query string and call the data from AD would be much appreciated. Here is the page I have been working on to populate the grid on page load based on the parameter query.  Imports System.Data Imports System.DirectoryServices Partial Class test Inherits System.Web.UI.Page Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs) Dim displayName As String Dim samAccountName As String Dim p_userGUID As String Dim objDE As New DirectoryEntry() objDE.Path = "LDAP://DC=tusd,DC=local/<sAMAccountName=" + p_userGUID + ">" objDE.Username = "abrowning" objDE.Password = "ab9156" objDE.AuthenticationType = AuthenticationTypes.Secure If objDE.Properties.Contains("sAMAccountName") Then ' make the People table and its columns Dim tblPeople As New DataTable("People") ' Holds the columns and rows as they are being Dim col As DataColumn Dim row As DataRow ' Create LogonNam

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

sp_send_dmail doesn't work in Agent job with query parameter


I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):

 EXEC msdb.dbo.sp_send_dbmail
 @recipients = N'name@email.com'
 ,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
 ,@subject = N'Disabled Jobs on SERVER1'
 ,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
 ,@attach_query_result_as_file = 1

This query works fine when running from a query window.  however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):

Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

If I try other queries in the @query parameter, everything works fine when running from the job.  As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails.  I ran a profiler trace, and I found a more detailed error message that occurs during the execution of the job, unfortunately it involves an undocument SP:

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status

Create dataset from LINQ query of DataView


Is there a straightforward way to create a dataset after querying a dataview?  The query itself returns DataRowView objects, which I cannot get to work with CopyToDataTable().

The query looks like:

Dim dvQueryView As DataViewdvQueryView = (dataview object of large dataset)

detailTableQuery = From rowView As DataRowView In dvQueryView _

Order By sSortOrder _

Select rowView _

Skip nQuerySkipRow _

Take nQueryCountRows


I only want a small s

Assign Query String Values to Selectcommand parameter in




I have two pages EventList.aspx and EventDetail.apsx. When user selects any events [basically list item] on EventList.aspx page [all events are rollup in single sote collection] in am redirected to EventDetail.aspx page with QueryString ListID and ListItemId.

On EventDetail.aspx page i am using


"<Webs Scope='SiteCollection'></Webs><Lists><List ID='{ListID}'></Li

How to change the Default Value of the Parameter to "Query Based"


Hi I have a Report which i deployed into my localhost.

In the Report Manager, Report Parameter Properties, I unchecked the HasDefault option for which Default value is coming from some Query.

When I retry to Set the Default Value to Query Based, Its not taking.

Need Help.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend