Solved

pass parameter to stored procedure in loop

Posted on 2008-06-17
2
945 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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
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 NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

726 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