Solved

pass parameter to stored procedure in loop

Posted on 2008-06-17
2
940 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
Comment Utility
'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 14

Accepted Solution

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

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

Rob
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.

762 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

13 Experts available now in Live!

Get 1:1 Help Now