Passing Parameters Using IN Statement
Dec 7, 2006
The SQL for a dataset in ASP.NET 2.0 is as follows....
SELECT DISTINCT StudentData.StudentDataKey
FROM Student2Roster INNER JOIN
StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecID
WHERE (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs)
ClassRosterRecIDs are giuds
At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime.
At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier.
If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.
How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'?
Thanks
View 2 Replies
ADVERTISEMENT
Jul 12, 2007
Hi all of you,
I'd like to know how could I add parameters to CREATE TABLE from a Execute Sql Task.
Is it possible?
TIA
View 3 Replies
View Related
Mar 14, 2008
Hi,
how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn); //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");
View 2 Replies
View Related
Apr 17, 2004
I have a dts package file. I am able to execute the file thro C#. I want to pass userid, password, datasource through program. How to do this?
DTS.PackClass package = new DTS.PackClass();
string filename = @"c:abc.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTSPack";
object pVarPersistStfOfHost = null;
package.LoadFromStorageFile(filename, password, packageID,
versionID, name, ref pVarPersistStfOfHost);
package.Execute();
package.UnInitialize();
package = null;
Thanks
Jtamil
View 2 Replies
View Related
May 30, 2006
Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].
If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.
View 1 Replies
View Related
Jul 28, 2006
A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows:
------
TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter();
// Add a new modem
modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN );
--------
And the error I get when loading the ASP.NET page is as follows:
Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments
---------------
Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter?
-Ed
View 4 Replies
View Related
Jul 22, 2007
Hi,I want to pass parameter in to LIKE functionIs there anyway to do thatThanks,Janaka
View 6 Replies
View Related
Apr 10, 2008
Hi
I'm trying to add 2 date parameters to the where caluse but not sure how to structure it:
Can anybody help?
Public Function GetCategoryPerformance(ByVal PriorityType As String) As DataSet
'*******************************************************
'This web service will return a specific row for a table
'*******************************************************Dim cn As New SqlConnection("data source=CADTRAINING;persist security info=True;initial catalog=pathwaysreporting;user id=cad;password=cad;")
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * from sp_category_performance where where priority = '" & PriorityType & "'", cn)Dim ds As DataSet = New DataSet
Try
da.Fill(ds, "CategoryPerformance")Catch ex As Exception
Throw New Exception(ex.Message)
End TryReturn ds
End Function
End Class
View 2 Replies
View Related
Mar 10, 2005
Hello All,
I am executing a DTS package from an asp page using the following code. I would like to also pass DTS Global variables along. i assume this is possible but can't seem to find an example.
Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "HOFDBMCRM4","TraubGar","ripley",DTSSQLStgFlag_Default,"","","","DSC_CalculateBOS"
oPkg.Execute()
Thanks, Gary
View 1 Replies
View Related
Aug 30, 2001
Hi,
I would like to pass parameters from isql command line to an input query file. I am working on SQL Server 7.0. How can I accomplish this?
My eg. is like this:
> isql.exe (all the server options etc.,) /iInputfile /oOutput file
I need the output depend on the query given in the input file with the query depending on the parameter I supply on the command line.Thanks for your help in advance.
thanks,
Sravan.
View 1 Replies
View Related
Jul 21, 2000
I'm trying to execute this statement but due to some missing paremters its creating table in that fashion
Table created = '+@string+'_'+@string1+'
but I want table to be create as what parameters passed, can you have a look pls...
exec sp_test 'master','table'
Starts here-----------
if exists (select * from sysobjects where id
= object_id(N'[dbo].['+@string+'_'+@string1+'])
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop table [dbo].['+@tblName+'_'+@table1+']
CREATE TABLE [dbo].['+@string+'_'+@string1+']
(
[HESClrId] [nvarchar] (20) NULL ,
[HESClrName] [nvarchar] (60) NULL ,
[HESClrSeq] [smallint] NULL
)
ON [PRIMARY]
View 1 Replies
View Related
Sep 18, 2002
Can I pass a parameter to a view? Can something like this even be done?
CREATE view co_interlinks1
AS
SELECT [TDirectorships].[IDDir], [TDirectors].[DirLName] + ', ' + [TDirectors].[DirFName] AS DirectorName, [TDirectorships].[Ticker], [TCompanies].[CompanyName]
FROM TDirectorships
INNER JOIN TCompanies
ON [TCompanies].[Ticker]=[TDirectorships].[Ticker]
INNER JOIN TDirectors
ON [TDirectorships].[IDDir]=[TDirectors].[IDDir]
WHERE [TDirectorships].[IDDir] in (SELECT [TDirectorships].[IDDir]
FROM TDirectorships
WHERE Ticker=@Ticker)--This line requires a variable value corresponding to Ticker for the chosen company
and [TDirectorships].[Ticker] <> =@Ticker --This line requires a variable value corresponding to Ticker for the chosen company
View 2 Replies
View Related
Oct 24, 2005
Hi
I created a report based on category,time and stores as report parameters.The datasets are created by using mdx queries and the provider I selected is olap services.I would like to add a bar chart in the layout and the resultant chart should be based on the selected parameters.I am not able to get this.First of all is it possible? If so, what should I do in the chart properties?
Regards
Ranjit
View 2 Replies
View Related
Mar 1, 1999
Is it possible to pass a parameter to an NT shell batch script when using the xp_cmdshell extended stored procedure?
View 1 Replies
View Related
Mar 20, 2008
Hi,
I have a requirement to load tables in the database from files on a shared server (lets say 50 tables from 50 files). I do not want to hardocde the file path anywhere in the package since this would mean changing 50 packages everytime the path changes (say when moving to to a diff server).
In SQL Server 2000, I used a .ini file to pass the path and used a Dynamic properties task to set run time variables. That way, evertime the path changes, I only had to change 1 ini file and all packages picked up the new path from it. How do I do this in SQL Server 2005 ?
Appreciate any assistance / suggestion in this regard.
Thanks,
Bhaskar
View 3 Replies
View Related
Mar 20, 2008
Hi,
I have a requirement to load tables in the database from files on a shared server (lets say 50 tables from 50 files). I do not want to hardocde the file path anywhere in the package since this would mean changing 50 packages everytime the path changes (say when moving to to a diff server).
In SQL Server 2000, I used a .ini file to pass the path and used a Dynamic properties task to set run time variables. That way, evertime the path changes, I only had to change 1 ini file and all packages picked up the new path from it. How do I do this in SQL Server 2005 ?
Appreciate any assistance / suggestion in this regard.
Thanks,
Bhaskar
View 8 Replies
View Related
May 30, 2008
How does we pass dynamic number of parameters to a procedure?
Eg.
DECALRE @IDs varchar(50);
SET @IDs = '1,2,3'
SELECT Description FROM MyTable WHERE ID IN (@IDs )
Here ID is int. When I tested this, it shows an error 'Cannot covert '1,2,3' to int'. No errors while it is one number ie '1'
How I can manage this type of problem in a stored procedrue that receive the @IDs as parameters?
View 1 Replies
View Related
May 22, 2007
Hello Friends,
I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.
Till this everything is fine.
Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.
Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.
Thanx Friends,
Raktim
View 2 Replies
View Related
Mar 10, 2008
I have an OLE DB Source that talks to an Oracle Db and returns back some data. I need to be able to pass some parameters to my query from the SSIS variables.
How can accomplish this ? Do I need to create a stored proc in my Oracle DB..is that the only way I can pass parameters ?
Is so how ?
Any examples would be really appreciated
Thanks
Smx
View 13 Replies
View Related
May 31, 2007
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 10 Replies
View Related
Jan 14, 2008
How can we pass variables by reference to a UDF? Is there any way in SQL Server to achive this objective?
View 2 Replies
View Related
Nov 12, 2007
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 9 Replies
View Related
Feb 13, 2008
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 5 Replies
View Related
Feb 6, 2008
Hai
I am havng a DTS Package name MFT_Disb... I am invoking this package from my VB.NET(2003) Application..
What i want to do is ....
1 ) i want to pass a variable named Sl_No from my Application to the DTSPackage ..
How can i pass the parameters to the package ?
2 ) Also i ve a text file named Disb.txt... i want to rename this text file to the value contained in the variable Sl_No...
For renaming the text file i want to write ActiveX Script in the same DTSPackage ... How to write the ActiveX Script ?
How can i rename the text file ?
Anybody knows plz do help me ...
I am new to SQL Server and DTSPackage
Any help appreciated
Thanx in advance
View 1 Replies
View Related
Jun 1, 2007
I am trying to make a SSIS package..
In the dataflow of my package, I must check from one table whether a row exists, and if that row exists, I should get some other row from another table, and update that..
I think to check whether a row exists, i should use "Look Up"
But cant we pass parameters to LookUP?
I am trying to use this SQL:
SELECT count(*) FROM ServicePackets where ID = ? and CHANGEDATE > ? and status = 1
I should get if that row exists or not only... (true or false)
How can i use parameters in LookUps?
View 7 Replies
View Related
Feb 20, 2006
Hello,
I have a SQL task wich executes the following statement on a OLEDB connection (SQL 2005 DB):
TRUNCATE TABLE DimTime
GO
DECLARE @CurrentDate AS Datetime
DECLARE @EndDate AS Datetime
SET @CurrentDate = '10-20-2003';
SET @EndDate = '10-20-2005';
while(@CurrentDate < @EndDate)
begin
INSERT INTO DimTime SELECT
DATEPART(month, @CurrentDate) AS MonthNumberOfYear
,DATEPART(quarter, @CurrentDate) AS CalendarQuarter
,DATEPART(year, @CurrentDate) AS CalendarYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester
,DATEPART(quarter, @CurrentDate) AS FiscalQuarter
,DATEPART(year, @CurrentDate) AS FiscalYear
,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester
set @CurrentDate = dateadd(month, 1, @CurrentDate)
end
GO
Now I want to parametrize the statement creating an external variable:
1. I created a datetime package variable (DimTimeStartDate)
2. Add update the above SQL statement to
SET @CurrentDate = ?;
3. I mapped the package variable to a new parameter (name 0)
Executing I get the following error:
[Execute SQL Task] Error: Executing the query " DECLARE @CurrentDate AS Datetime DECLARE @EndDate AS Datetime SET @CurrentDate = ?; SET @EndDate = '10-20-2005'; while(@CurrentDate < @EndDate) begin INSERT INTO DimTime SELECT DATEPART(month, @CurrentDate) AS MonthNumberOfYear ,DATEPART(quarter, @CurrentDate) AS CalendarQuarter ,DATEPART(year, @CurrentDate) AS CalendarYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) as CalendarSemester ,DATEPART(quarter, @CurrentDate) AS FiscalQuarter ,DATEPART(year, @CurrentDate) AS FiscalYear ,dbo.GetSemester(DATEPART(month, @CurrentDate)) AS FiscalSelester set @CurrentDate = dateadd(month, 1, @CurrentDate) end " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Not clear what is wrong. Any help ?
Thanks,
Pierre
View 12 Replies
View Related
Aug 29, 2007
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 4 Replies
View Related
May 31, 2006
Hi,
1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.
When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.
Can anyone suggest a workaround?
2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.
2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.
Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?
Please suggest a solution for this too.
Thanks in advance.
Regards,
Aman Anand
View 9 Replies
View Related
Aug 23, 2007
Hi,
I am using SQL Reporting Services 2000 on a SQL 2000 Server. My problem is with passing parameters to a report url.
This is my existing report url
http://ServerName/Reports/Pages/Report.aspx?ItemPath=%2fFolderName%2fReportName
There are two parameters on the report namely A and B.
Given this info, how can i pass a url that disables the parameter on the report and pass it through the url of the report. What should be the correct syntax or what changes i have to do the url above?
Thanks and Rgds
Sk
View 3 Replies
View Related
Feb 23, 2006
Here is the problem.
I have a report that accepts multiple parameters. Two of them are "hidden" from the user. I don't want them prompted for these values.
I set up the parameters so that it says Hidden but I get the following message.
The report parameter 'CustomerID' is read-only and cannot be modified. (rsReadOnlyReportParameter)
Here is the source in my ASPX page:
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("CustomerID", CustomerID.ToString());
parameters[1] = new ReportParameter("UserID", UserID.ToString());
parameters[0].Visible = false;
parameters[1].Visible = false;
rv.ServerReport.SetParameters(parameters);
So I found all sorts of stuff about an issue in SSRS 2000 and a work around. Which means that when the report is deployed you have to go to the report on the server and select properties and uncheck hidden and check prompt and put a space in the prompt text box. You also have to include the parameter.Visible = false statment in the code (above).
And I FINALLY got that to work.
I can't believe this wasn't addressed in SSRS 2005.
Am I missing something or is this still the only way to pass parameters to your report that you don't want your users to see?
View 6 Replies
View Related
Apr 11, 2007
I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
View 1 Replies
View Related
May 25, 2007
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
View 8 Replies
View Related
May 31, 2007
When someone executes my ssis package, I want the user to be prompted to fill in a few parameters. How do i do that?
View 4 Replies
View Related
