Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses
I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works
The cutdown version off the query is...
Select Top @Param_TopNo SaleDate, Consultant, Productname
From tblSales
where product = 2000
Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.
I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is
select Hue from AvailableColors whereColor = @ColorParam.
For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
I've seen questions similar to this posted, but I'm unable to find a clear answer to what I need to do.
We are currently using Crystal Reports, generating a dataset in our application and passing it to Crystal. Due to the many problems we have with Crystal we are now looking to move to Reporting Services 2005, but I can't find out how to pass it a dataset.
Although we do use SQL Server for data storage, we have many reports with calculations that are way too complicated to use calculated fields, and which change too often to want to store them in the database. One solution might be to store the data temporarily and regenerate it each time it's required, but this doesn't seem very elegant. So please could someone tell me if there is a way to do what I'm after and point me at a page that gives a nice clear explanation.
HI everyone; i have a problem , is there a possible to pass a dataset to a ssrs report programmatically ? or at runtime without specifying it at design time ...
I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?
My VBA code in Access used to be this:
Code Snippet
Dim ws As Workspace Dim strConnection As String Dim dbs As Database
Set dbs = ws.OpenDatabase("", True, True, strConnection)
Set rst_chg = dbs.OpenRecordset("SELECT ...")
'process the recordset ...
I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?
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= ---- 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) )
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts] Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])
There are two parameters; the value of lag passed to the LastPeriods function, and tenancy types passed in the where clause
I have a third parameter I would like to use called FirstDate and this I would like to pass to the LastPeriods function as the second argument
FirstDate is a value from my Time dimension, something like [Time].[Fiscal Time].[Sep. 15]
When editing the query to: -
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts] Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit]) Select {[Measures].[Measure1] ,[Measures].[Measure2]
[Code] ....
The code errors. I have tried all sorts of formats such as LastPeriods(@LagMonths,[@FirstDate]) and LastPeriods(@LagMonths,[Time].[Fiscal Time].[@FirstDate]), but nothing works
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
Hi every body... I have a probleme I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime)) I need to call this method. But my problem is how pass parameter ? I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example... now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression : =First(Fields!idEq.Value, "EquipmentDataSet") but when i execute the query, the promter display <NULL>... So I don't know how to do and if it is possible ! I hope someone can help me ! Thank you !
I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.
"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField
I have two datasets (for querying active directory but this is kind of irrelevant for my problem which is a basic one)
The first gets a list of groups and I want to display GroupName in a dropdown list:
SELECT DistinguishedName, GroupName, GroupDescription FROM tvf_GetListOfGroups('LDAP://OU=Applications,OU=Groups,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree') ORDER BY GroupName
The above is fine and works but then I want to use the corresponding DistinguishedName of the GroupName selected in the dropdown to drive the second query to populate the report
SELECT * FROM tvf_GetGroupMembers('LDAP://' + @DistinguishedName) ORDER BY UserLogin
My problem is how to populate @DistinguishedName with the correct value to run run the query.
Hi !!! I have a problem, I don't know how to do... I have 2 dataset from 2 differents XML dataSource... I want the result of one dataSet to be a parameter for the second !
I try to add a parameter in param tab, with expression, I choose DataSet and =Sum(Fields!ID.Value, "DataSetID") but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...
Someone have ideas to resolve this problem ? thank you for your help !
In VB.NET this doesn't work either: dim s as string = "'cn01','cn02','cn03','cn04'" cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000)) cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done: 1) Use SQL task to include "Select Count(*) from TableA" 2) In parameter mapping tab of SQL Task, define the following Parameter name : Table::Count Direction: Input Data Type: Numeric Parameter Name : @Count 3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out] 4) The constraints has been set as Evaluation Operation : Expression and Constraints Value: Success Expression : @Count >"50000"
In SSIS tab > Variable I have set the following Variable Name : Count DataType : Int32
Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
I don't understand how to pass a parameter to a dts package from another. In SQL 2000 you can map a variable with a variable of the package you will call.
As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report...
Hi, Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004''' Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very. Thanks Julio D
I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.
How can I do this?
I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.
Now I can query dataset and bind to report parameter. But I have some problem about setting default value. I don't know, how to select max value from dataset by using expression.
Example, I query disticnt year from my table. The result has 2 years "2007" and "2008" select distinct year(mytime) as myyear from mytable.
I set available parameter by using this dataset. Then, I set default value for this parameter by the other dataset ...
select max (distinct year(mytime)) as maxyear from mytable.
I want to select max value by using expression, not select max by using dataset.
My data table contains a column (a foreign key) with numbers from 0 and up. (it could be null or a number. I used ISNULL to convert it.) I want to be able to select in three ways. 1) equals zero (or is null)2) greater than zero (or is not null)3) equal to a specific number.How do I do this?My application setup is Presentation Layer, BLL, DAL and Sql Server.I don't want to create another Query because this is only part of a larger query with other parameters.If I could just do something like @IDOperator @IDValue in the Filter of the Sql it would be great.
Hello, Can someone kindly point out what is wrong with the following code file. I'm trying to: - fill a dropdown from a db on page load (this works!) - when user selects from the list and hits a button, pass the dropdown value to a second query - use the second query to make another call to the db and fill a data grid
In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.
Thanks
PS comments about my coding approach are welcome - I'm new to aspx...
<%@ Language="VBScript" Debug="true"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.data.SqlClient" %> <html> <head> <script language="vb" runat="server"> Dim dbConnection As SqlConnection Dim ds_Teams,ds_Agents As DataSet Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter
Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"
Dim sql_select_agents = "" & _ "SELECT last_name + ', ' + first_name AS name, agent_no " & _ "FROM dbo.users " & _ "WHERE (team_no = @Team) AND (NOT (agent_no = '1029')) " & _ " AND (NOT (last_name IS NULL)) " & _ " AND (NOT (first_name IS NULL)) " & _ " AND (NOT (first_name = 'FirstName')) " & _ "ORDER BY last_name"
Dim teamList = "teamList" Dim agentList = "agentList"
Sub Page_Load(Sender As Object, E As EventArgs) if not (IsPostBack) ds_Teams = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection) sqlCmd_Teams.Fill(ds_Teams, teamList) dbConnection.close()
dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView dropdownlist_Teams.DataBind() end if End Sub
sub Get_Agents(Sender As Object, E As EventArgs) ds_Agents = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)
Interesting problem, selecting and updating (with reference to a Form View) works great. Whenever I try to delete I get this error:Must declare the variable '@template_id'.
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.Data.SqlClient.SqlException: Must declare the variable '@template_id'. Here is my code:<asp:SqlDataSource ID="SqlTemplateDS" runat="server"SelectCommand="SELECT [template_id], [client_id], [language], [label], [email_default], [email_template], [event_template] FROM [CMT] ORDER BY [template_id]"DeleteCommand="DELETE FROM CMT WHERE (template_id = @template_id)" ConnectionString="<%$ ConnectionStrings:BrandMSConnectionString %>"> </asp:SqlDataSource><asp:GridView ID="grdTemplates" runat="server" AutoGenerateColumns="False" DataKeyNames="template_id" DataSourceID="SqlTemplateDS" Font-Names="Verdana" Font-Size="Small" ForeColor="Black" Width="100%" OnSelectedIndexChanged="grdTemplates_SelectedIndexChanged" CellPadding="2"> <Columns> <asp:BoundField DataField="template_id" HeaderText="ID" ReadOnly="True" SortExpression="template_id" /><asp:BoundField DataField="client_id" HeaderText="Client" SortExpression="client_id" /><asp:BoundField DataField="language" HeaderText="Language" SortExpression="language" /><asp:BoundField DataField="label" HeaderText="Label" SortExpression="label" /><asp:CheckBoxField DataField="email_default" HeaderText="Default" SortExpression="email_default" /><asp:CheckBoxField DataField="email_template" HeaderText="Email" SortExpression="email_template" /><asp:CheckBoxField DataField="event_template" HeaderText="Event" SortExpression="event_template" /> <asp:TemplateField HeaderText="Options"><ItemTemplate> <asp:LinkButton ID="btnModify" CommandName="Select" runat="server" ForeColor="Blue">Edit</asp:LinkButton> | <asp:LinkButton ID="btnDelete" CommandName="Delete" runat="server" ForeColor="Blue">Delete</asp:LinkButton></ItemTemplate></asp:TemplateField> </Columns><HeaderStyle BackColor="Khaki" /><AlternatingRowStyle BackColor="Beige" /></asp:GridView>Any ideas?Chris
