Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

pass parameter to stored procedure in loop

Posted on 2008-06-17
2
Medium Priority
?
949 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

609 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