parameter passing with blanks - help required

Hi,

I am having a curious problem with parameter passing between

reports. I have 2 reports the first report gives employee name, Department and

in the second report I have employee details. If the user clicks on the

Employee name in the First report it should go to the second report and display

the details. My problem comes if suppose the employee name is “Don King”when the parameter gets passed it does not recognize

the space between thename and throws

an error saying King not recognized.

Would appreciate if anybody could suggest as to how I can

pass the name with spaces in the report parameters.

Thanks and regards

PMNJPassing parameter with space to Jump to report and a subreport work fine. How are you passing this parameter around?|||am passing the parameter thru the report parameter

regards
PMNJ|||I'm having the same problem. I've also got a parameter which can be one word, of more than one word, sometimes with a comma in it. I'm passing the parameter, like PMNJ, thru the report parameter.

Has someone solved this problem?|||I reiterate Brad's question. How are you passing the parameter? Are you using Jump to Report? Are you parsing together a URL link? More details.|||Sorry, here is the info you requested.

I'm passing the parameter (along with another one) with the report parameter, orinating from a jump to report link.
The parameter is correctly displayed when using a textbox and a expression with the parameter in it.
But, when using this parameter in the MDX query for the dataset it doesn't work when the parameter consists of more than one word, i.e. there are spaces (blanks) in it.

If you need more details, please say so, I'll be happy to supply them.

Parameter passing to a stored procedure

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@.domainUserID NVARCHAR(50) ,

@.EmployeeID NVARCHAR(50) ,

@.loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@.EmployeeID, @.loginType, @.domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page...

--begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
--

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?no.

Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand

insertMatchedUser = new SqlCommand;
insertMatchedUser.CommandText = "USP_MactchUser"
insertMatchedUser.CommandType = CommandType.StoredProcedure
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters("@.EmployeeID").Value = lblEmployeeID.Text.Trim()
insertMatchedUser.Parameters("@.domainUserID").Value = lblDomainuserID.Text.Trim()

insertMatchedUser.Connection = SqlConnection1;
SqlConnection1.Open()
'conn openned
insertMatcheduser.ExecuteNonQuery()

insertMatchedUser.Dispose()
SqlConnection1.Dispose()
End Sub|||Also:

CREATE PROCEDURE USP_MactchUser
@.domainUserID NVARCHAR(50) ,
@.EmployeeID NVARCHAR(50) ,
@.loginType bit = 0
AS

the bit data type literal should not have single quotes.sql

Parameter passing to a DTS package

Hi,
I have a DTS package that exports a query result from SQLServer to a text
file . I sheculed it daily.
What I want to do is give the text file name as paramaetric, I mean,
e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and second
Is it possible?Hi
The DTSRun utility has a /A parameter that allows you to set the values of a
global variable. This can then be used to set the filename. If the name is
consistent you should be able to do this in code without passing a value.
You may also want to check out http://www.sqldts.com/default.aspx?292 for
renaming a file and http://www.sqldts.com/default.aspx?234
John
"Banu_tr" wrote:

> Hi,
> I have a DTS package that exports a query result from SQLServer to a text
> file . I sheculed it daily.
> What I want to do is give the text file name as paramaetric, I mean,
> e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and secon
d
> Is it possible?

parameter passing problem

why this wont work?

error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@.cid', which was not supplied.

.............................................................
ASPX CODE:
string cid12;
cid12 = "user5";
// Connection
ConnectionStringSettings mysettings;
mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"];
string myConnectionString = mysettings.ConnectionString;
SqlConnection conn1 = new SqlConnection(myConnectionString);

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "test1";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@.cid", SqlDbType.NChar, 20);
param.Direction = ParameterDirection.Input;
param.Value = cid12;

conn1.Open();
cmd.ExecuteNonQuery();
conn1.Close();
.............................................................

STORED PROCEDURE:
ALTER PROCEDURE dbo.test1
(
@.cid nvarchar(20)
)
AS
/* SET NOCOUNT ON */
select tech_id, customer_id, issue_main from [case] where customer_id = @.cid
--GO
RETURN

.............................................................

im using asp.net, C#, and sql express
forgive me. im new to this .net
PLS HELP

pls ignore the post above. i found out that nothing is wrong with that code. i discovered that one on my grid view tables is causing the issue and it is indeed not passing a parameter and thats the reason why i have wasted 12hrs looking at my monitor. hehehe.

my fault. im just human. forgive me.

thanks to you all.

parameter passing problem

why this wont work?
error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@.cid', which was not supplied.
.................................................................................
ASPX CODE:
string cid12;
cid12 = "user5";
// Connection
ConnectionStringSettings mysettings;
mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"];
string myConnectionString = mysettings.ConnectionString;
SqlConnection conn1 = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "test1";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@.cid", SqlDbType.NChar, 20);
param.Direction = ParameterDirection.Input;
param.Value = cid12;

conn1.Open();
cmd.ExecuteNonQuery();
conn1.Close();
.................................................................................
STORED PROCEDURE:
ALTER PROCEDURE dbo.test1
(
@.cid nvarchar(20)
)
AS
/* SET NOCOUNT ON */
select tech_id, customer_id, issue_main from [case] where customer_id = @.cid
--GO
RETURN
.................................................................................
im using asp.net, C#, and sql express
forgive me. im new to this .net
PLS HELP

forgive me. i found out that the codes above are just ok. i found out that one of my grid table is the one causing the problem - it is not passing a parameter. hehehe. my fault. im just human.

thanks to you all.

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

sql