?
Solved

pass parameter to stored procedure in loop

Posted on 2008-06-17
2
Medium Priority
?
947 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

764 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