How to call a parameterized stored procedure within a loop in ASP.NET

I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET.

Scenario:

I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables.

I can not use the following syntax:

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.storedprocedurename"

With cmd

.Parameters.Add(New SqlClient.SqlParameter("@.param1", paramValue1))

.Parameters.Add(New SqlClient.SqlParameter("@.param2", paramValue2))

End With

What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?

Thanks,

Carlos

As far as i know u can not execute stored procedure with out"CommandType.StoredProcedure"property.

other wise just write a insert query (that also needs parameters) thru loop and execute them by using execute non query

|||

I agree:

The problem is not the following lines:

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.storedprocedurename"

The problem is the following lines:

With cmd

.Parameters.Add(New SqlClient.SqlParameter("@.param1", paramValue1))

.Parameters.Add(New SqlClient.SqlParameter("@.param2", paramValue2))

End With

I was trying to find the alternative syntax to the lines above and the solution is to use:

With cmd

.Parameters.Add("@.param1", DbDataType.Varchar())

.Parameters.Add("@.param2", DbDataType.Varchar())

End With

Thanks,

Carlos

|||

You said you are going to execute the sproc in a loop, so be careful to clear the parameter collection after executing the sproc, otherwise the parameter's collection in your command object will keep on increasing and on the second loop itself it'll give you and error. This is not needed if you are nullifying and re-setting the command object every time.

Hope this will help.

|||

hi,

As you said you are using for loop for executing stored procedure, so clear the parameters collection after call execute nonquery method like this

cmd.ExecuteNonQuery();
lCmd.Parameters.Clear();

Hope it helps