C# Code to execute a parameterized stored procedure

Question

question

palum asked Nov 1, '12

C# Code to execute a parameterized stored procedure

SqlConnection MyConnection=(SqlConnection)Dts.Connections["ADO Source"].AcquireConnection(null); SqlCommand Storproc = new SqlCommand(); Storproc.Connection = MyConnection; Storproc.CommandType = CommandType.StoredProcedure; Storproc.CommandText = "StudentProcedure"; SqlParameter Parameter1 = new SqlParameter("@Name", SqlDbType.VarChar); SqlParameter Parameter2 = new SqlParameter("@ID", SqlDbType.VarChar); Storproc.Parameters.Add(Parameter1); Storproc.Parameters.Add(Parameter2); MyConnection.Open(); Storproc.ExecuteNonQuery(); MyConnection.Close(); I am trying to execute a stored procedure which accepts 2 parameters from SSIS Script task. While execute the above code it giving an error the stored procedure 'StudentProcedure' has no parameter named Parameter2. Please help me how to pass a parameters in C#. Thanks

ssis stored-procedures c#paramaters

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski commented · Nov 02, 2012 at 09:49 PM

Only a question related to the stored proc execution? Why you are executing the stored proc inside SSIS using the Script Task or Script Component? Much easier you can use **Execute SQL Task** (Control Flow) or **OLE DB Command** (Data Flow). In your case you mentioned Script Task, so you are in Control Flow, so Much Easier you would have to execute the stored proc using the Execute SQL Task.

1 ·

mjharper commented · Nov 01, 2012 at 11:01 PM

I think you need to assign a value to each of those parameters, before adding it - something like: Parameter1.Value = "myName"; I'm not sure that's the problem though as I don't think it quite fits with the error message you're getting.

0 ·

Answer 1 · 2012-11-02T02:28:34Z

Magnus Ahlkvist answered Nov 2, '12

I would pass the parameters with AddWithValue instead of declaring them and adding them. Like this: SqlConnection MyConnection=(SqlConnection)Dts.Connections["ADO Source"].AcquireConnection(null); SqlCommand Storproc = new SqlCommand(); Storproc.Connection = MyConnection; Storproc.CommandType = CommandType.StoredProcedure; Storproc.CommandText = "StudentProcedure"; Storproc.Parameters.AddWithValue("@Name","SomeValue"); Storproc.Parameters.AddWithValue("@ID","SomeOtherValue"); MyConnection.Open(); Storproc.ExecuteNonQuery(); MyConnection.Close(); In your code you declare both parameters as VarChar. Is the parameter \@ID really a VarChar?

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

palum commented · Nov 02, 2012 at 07:14 PM

parameter @ID is VarChar but it is a System.Object

0 ·

palum commented · Nov 02, 2012 at 07:24 PM

When i change the @ID to a variable to type String it executes. when i change the @ID to a System.Object variable in SSIS, I am getting the folling error: System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

0 ·

question