Parameters Value From One Dataset
Mar 11, 2008
Hello.
I have one dataset with a few parameters.
Each parameter get its available values from the some dataset.
So if I have the main DS whice is:
"select name,age,sex from table1 where age = @age and sex = @sex"
The available values for the parameters "age" and "sex" are those datasets:
"select distinct age from table1" and
"select distinct sex from table1".
The problem is thet the query on table1 takes a lot of time and in this case I need to run her three times.
Is there a way to initialize the parameters values in one query excution and save some time?
Thanks.
View 4 Replies
ADVERTISEMENT
Jan 2, 2007
A - Must Declare variable @ZipEntry - error generates when I run the code below. If I hard code the "WHERE ZipCode = 72364" it works (display messed up but it works - don't know how to code in the line breaks yet). But when I try to pass in the value of a TextBox called txtZipEntry on the page I get this error. Am I not delcaring @ZipCode in the correct spot ?
Thank you,
public partial class ContractorResultsDisplay : System.Web.UI.Page{string ZipEntry;protected void Page_Load(object sender, EventArgs e){
ZipEntry = txtZipEntry.Text;SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);string sql = "Select [City], [StateCode], [AreaCode], [Latitude], [Longitude], [ZipCode] From [ZipCensus11202006] Where ZipCode > @ZipEntry";SqlCommand cmd = new SqlCommand(sql, con);cmd.Parameters.AddWithValue("@ZipEntry", ZipEntry);SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();da.Fill(ds, "ZipCodeRefs");StringBuilder htmlStr = new StringBuilder("");
foreach (DataRow dr in ds.Tables["ZipCodeRefs"].Rows){htmlStr.Append(dr["City"].ToString());}
ZipContent.Text = htmlStr.ToString();
}
}
View 1 Replies
View Related
Apr 2, 2008
Hello All,
I have 2 MultiValued Parameters in my reports, our of which first is dependent on the other.
Eg. I have a SubProductGroup Parameter and depending upon the value selected in SubProductGroup, the other parameter which is Product gets filled in combo box.
Both these parameters are MultiValued and I am having "Select ALL" option in both the combo box.
Now, whenever you select any SubProductGroup values, the Dataset for Product gets executed to fill the Product combo box.
The MultiValued Parameters works using the "IN" Clause. All those products are retrieved which are "IN" the selected SubProductGroup.
Whenever user selects "Select All" option for SubProductGroup, there is no need for this "IN" clause, because we actually need all the records for Product.
Similarly, I have the Main Dataset which retrieves the Sales data for the Products selected from the Combo Box. So, the same question arises there also.
Right now I am using STRTOSET( ) and Join( ) function and this is working fine for me.
But when User Selects "Select All" in SubProductGroup, it takes a long time to fill the combo box for Product as well as when user selects "Select All" for Product, it takes a long time for the actual Dataset to retrieve records. But its working fine.
In order to improve the Performance, I am trying to eliminate this IN clause when "Select All" is selected.
How can I do this ?
Any Inputs ?
Thanks & Regards,
Kapadia Shalin P.
View 4 Replies
View Related
Oct 1, 2015
I have an MDX data-set query as follows: -
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]
,[Measures].[Measure3]
[Code] ....
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
View 2 Replies
View Related
Mar 13, 2008
Is it possible to get the reportdataset field values into parameters. dynamically when the report is generated.
Thanks.
View 1 Replies
View Related
May 21, 2007
Hi guys,
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:
- despesaTableAdapter (from myDataSetTableAdapter)
- despesaBindingSource (Windows.Forms.BindingSource)
- RelatorioDataSet (from myDataSet)
- relatorioDataSet1 (from myDataSet)
Any help will be very appreciated.
Tks and rgds,
Luis Antonio
View 1 Replies
View Related
Sep 29, 2015
So I have a report that uses an MDX query to fetch the main data for the tablix. Then I have a custom row added which pulls data in via a Lookup function and references a different dataset. The report has 3 parameters. Each dataset uses those 3 parameters in its underlying query. However, the dataset referenced in the lookup function doesnt seem to be updating when change the parameters and re-run the report.
View 2 Replies
View Related
May 26, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies
View Related
Oct 1, 2015
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows.
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
View 2 Replies
View Related
May 27, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 3 Replies
View Related
May 21, 2007
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
View 2 Replies
View Related
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
May 7, 2008
Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END
View 4 Replies
View Related
Apr 11, 2008
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
View 4 Replies
View Related
Dec 19, 2006
Hi,
I have two datasets in my report, D1 and D2.
D1 is a list of classes with classid and title
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Is there any way to do this in RS2005?
View 2 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
Apr 5, 2007
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 !
View 3 Replies
View Related
Dec 3, 2007
Hi experts,
I'm not sure my design is normal or not. Please give me some advice.
I've a dataset and query by a field name 'companyid'.
select * from companyid where companyid = @icompany which @icompany is a input field.
if user select all, i'll send 0 to @icompany then I need to select all records.
question 1. How can I get all records? (i think about this query select * from companyid <> 0)
if user select for example companyid = 1, i'll send 1 to @icompany and the query work fine.
question 2. How can I change the query to adopt this 2 condition?
Thanks a lot,
Jeff
View 8 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Jan 2, 2008
Hello all,
Given:
string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure;
Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder]
It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight?
Thanks,
View 11 Replies
View Related
Feb 22, 2007
I am faced with a common situation but there is a little twist that makes it unique and has me stumped. Hopefully someone can point me to a solution or give me enough guidance to figure it out.
Here is the situation, I am loading an XML file into a Dataset and need to update SQL tables from that Dataset but the tables are slightly different than in the DataSet. I am using C#, asp.net 2.0, visual studio 2005 and sql server 2005.
When I loaded the XML into the Dataset, the Dataset contained 19 tables. I individually loaded each table from the Dataset and determined from that which tables I needed and didn't need as well as determinined which fields I needed or not. I used this information to create my SQL tables.
Briefly, when creating the SQL tables, I used the dataset table name as the SQL table name and used the same column names as well. the only difference from the Dataset tables and SQL tables is 1 column has a different name (or to be more specific, a column exists in the datset that does not exist in the sql table and a column exists in the SQL table that does not exist in the dataset table). Also, some tables in the dataset do not exist in the sql Database.
I know that I can spin through each row of the tables I need in the Dataset and create an insert command from the row data and insert that into the SQL table but I have 11 tables to do that with. I would like to take advantage of ADO.NET and push the datset information into sql data but I am confused on how to do this.
I am confused because not all tables in the dataset are represented in the sql database and though the tablenames and most of the columns have the same name, it is not an exact fit.
Can anyone point me to a solution or give me a good game plan that I can search on to help me load my dataset info to a sql DB.
I appreciate any help I can get.
Thanks.
View 1 Replies
View Related
Aug 24, 2007
hi developers
i've a table named users where i store the city id ,
this city id -> citymaster (cityid,stateid)->state master (cityid,stateid) ->country master (countryid,stateid).
now i need to make the query fetches the columns like this
username,cityid,stateid,countryid
how can i do this
View 2 Replies
View Related
Jun 5, 2008
I using a stored procedure to update profile...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[SP_UpdateProfile]
-- Add the parameters for the stored procedure here
@regUserID nvarchar(20),
@regName nvarchar(50),
@regEmail nvarchar(50),
@regMobile nvarchar(10),
@regPinCode nvarchar(10),
@regCity nvarchar(12),
@regState nvarchar(12),
@RValue int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE tblUserDetails
SET
regName= @regName,
regEmail= @regEmail,
regMobile= @regMobile,
regPinCode= @regPinCode,
regCity= @regCity,
regState= @regState
WHERE regUserID=@regUserID
END
here i updating the profile according to the userID..
My doubt is how to get these various values selected in a dataset and how to retrieve from tat dataset... please friends help me out...
View 2 Replies
View Related
Jun 8, 2008
I am using storedprocedure for a process... i am selecting 4 variables from the stored procedure as a ouput parameters.. now please help me in working with a dataset to collect the 4 variables and read the variables from the dataset...
View 5 Replies
View Related
Jan 17, 2005
Hey,
I am using the following stored procedure, but as it is now it returns 2 datasets. I need it to return only 1.
ALTER PROCEDURE dbo.Test2
(
@Location varchar(250),
@FromDate datetime,
@ToDate datetime
)
AS
SET NOCOUNT ON
CREATE TABLE #Temp1 (themonth varchar(12), theyear varchar(12), averagedaystofilltotal float, averagefillratetotal float,
delaytimetotal float, responsetimelagtotal float)
INSERT INTO #Temp1(themonth, theyear, averagedaystofilltotal, averagefillratetotal, delaytimetotal, responsetimelagtotal)
EXEC rptStatsGraphicalYEARoverYear @Location, @FromDate, @ToDate
Create table #Temp2(averagedaystofilltarget float, averagefillratetarget float, responsetimelagtarget float)
INSERT INTO #Temp2(averagedaystofilltarget, averagefillratetarget, responsetimelagtarget)
EXEC Test1
SELECT * FROM #Temp1
Select * from #Temp2
drop table #Temp1
drop table #Temp2
Would anyone have any suggestions on how to modify this procedure so that it returns only one dataset, but yet still keeps the data seperated? Or is this even possible?
Thanks
View 3 Replies
View Related
Jul 9, 2006
ok, i have a question.
im developing an application, and want to know some pro's and con's with using a dataset or using direct connection to database...
also a breif description of what a dataset is would be very handy...
Thanks, Justin
View 4 Replies
View Related
May 22, 2008
hi
i m working on ERP with VB.Net and SQLserver2005.
i have some problem with that query.
In the here is two administrator
1- administrator
2-HR administrator
in the table,code written on login srceen and query is following.
SELECT userGroupMaster.groupName FROM userGroupDetails INNER JOIN userGroupMaster ON userGroupDetails.groupName = userGroupMaster.groupName INNER JOIN UserNameMaster ON userGroupDetails.userName = UserNameMaster.UserName WHERE (UserNameMaster.UserName = '" & gl_strUserName & "')
whenever i execute it then error comes due two administrator.
I wanna use dataset in that query to read first administrator.
Ashish
View 1 Replies
View Related
May 30, 2007
i am having a stored procedure and i want to use dataset to retrieve the first row of my table.
View 3 Replies
View Related
Jun 4, 2007
hi,
how do I get value from second dataset (in multi dataset report)
in some function it provide scope parameter like
Sum(Fields!amount.Value, "DS2")
but now I need to show the value without using function
thks,
View 2 Replies
View Related
Apr 2, 2008
Hi all,
If i have dataset in Dotnet Code,Can i use that dataset to report design?
Any body know.
View 1 Replies
View Related
