Solved

pass parameter to stored procedure in loop

Posted on 2008-06-17
2
943 Views
Last Modified: 2013-11-06
How can I pass parameters with different values to the same stored procedure in a loop like:

cmd.CommandText = "MySP"
cmd.CommandType = System.Data.CommandType.StoredProcedure

For Each v_row As DataRowView In dv
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@P1", v_row.Item(0)))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@P2", v_row.Item(1)))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@P3", v_row.Item(2)))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@P4", v_row.Item(3)))
Next v_row

I'm getting error after first loop:
Procedure or function  MySP has too many arguments specified.
Should I use something else instead of .Parameters.Add ?
0
Comment
Question by:jurekm61
2 Comments
 
LVL 10

Expert Comment

by:athapa
ID: 21809690
'declare 4 different (or whatever number you need) params

dim p1 as new System.Data.SqlClient.SqlParam("@P1")
dim p2 as new System.Data.SqlClient.SqlParam("@P2")
dim p3 as new System.Data.SqlClient.SqlParam("@P3")
dim p4 as new System.Data.SqlClient.SqlParam("@P4")

'add them to command

cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
cmd.Parameters.Add(p3)
cmd.Parameters.Add(p4)

'within the loop just assing values to the param
For Each v_row As DataRowView In dv
p1.value = v_row.Item(0)
p2.value = v_row.Item(1)
p3.value = v_row.Item(2)
p4.value = v_row.Item(3)
'execute your command here
Next v_row

'You may need to provide the param type in the constructor
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 125 total points
ID: 21809699
Add the parameters outside the loop, and just assign values to the parameters inside the loop:

cmd.Parameters("@P1").Value = ...

Rob
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unable to save view in SSMS 21 69
Advice in Xamarin 21 78
Help with adding data element is a specific row of an xml file using VB.NET 4 23
VB.NET 2008 Publish Error 2 24
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now