Re: Iterating through rows of repeating table with a For Each loop

kfisher — Wed, 16 Jun 2010 17:39:59 GMT

Brilliant! That was the problem - I moved the cmd declaration to within the loop and it worked like a charm.

THANKYOU!

Re: Iterating through rows of repeating table with a For Each loop

Hilary Stoupa — Wed, 16 Jun 2010 17:13:03 GMT

Your error makes me think that your stored procedure takes fewer arguments than you are providing. Does the AddContact stored procedure have 11 parameters? Hmmm... and it seems to me that you might want to declare you cmd variable after your "For Each", so that it is new for each row. It seems your current code may just keep adding the same parameters over and over again to your cmd object.

Iterating through rows of repeating table with a For Each loop

kfisher — Wed, 16 Jun 2010 16:11:12 GMT

I know this topic has been well discussed on this forum and others, but I banging my head against a wall trying to get this to work, and could use some help. Here's the situation: I have an Infopath form receiving data from a database connection, and submitting through a parametrized stored procedure attached to a button with code. My goal is to iterate through each line of the repeating table, feeding data to the stored procedure one row at a time.

This is what I've come up with, based on some searches. It returns an error that too many arguments are specified; no data is submitted... Any guidance would be sincerely appreciated.

Thanks very much.

_____________________________________

Public Sub CTRL13_7_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=abc;Initial Catalog=abc;Persist Security Info=True;User ID=abc;Password=abc"
Dim cmd As New SqlCommand()
Dim Nav As XPathNavigator = MainDataSource.CreateNavigator()
Dim rows As XPathNodeIterator = Nav.Select("/dfs:myFields/dfs:dataFields/d:Contacts", NamespaceManager)
Dim row As XPathNavigator

For Each row In rows

With cmd
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "AddContact"
.Parameters.Add("@contactID", SqlDbType.Int).Value = row.SelectSingleNode("@ContactID", Nav).Value
.Parameters.Add("@emplID", SqlDbType.Int).Value = row.SelectSingleNode("@EmplID", Nav).Value
.Parameters.Add("@companyname", SqlDbType.NVarChar).Value = row.SelectSingleNode("@CompanyName", Nav).Value
.Parameters.Add("@contacttitle", SqlDbType.NVarChar).Value = row.SelectSingleNode("@ContactTitle", Nav).Value
.Parameters.Add("@contactname", SqlDbType.NVarChar).Value = row.SelectSingleNode("@ContactName", Nav).Value
.Parameters.Add("@phone", SqlDbType.NVarChar).Value = row.SelectSingleNode("@Phone", Nav).Value
.Parameters.Add("@reltype", SqlDbType.NVarChar).Value = row.SelectSingleNode("@RelType", Nav).Value
.Parameters.Add("@method", SqlDbType.NVarChar).Value = row.SelectSingleNode("@Method", Nav).Value
.Parameters.Add("@isinnetwork", SqlDbType.Int).Value = row.SelectSingleNode("@IsInNetwork", Nav).Value
.Parameters.Add("@isleadincluded", SqlDbType.Int).Value = row.SelectSingleNode("@IsLeadIncluded", Nav).Value
.Parameters.Add("@submissiontype", SqlDbType.NVarChar).Value = row.SelectSingleNode("@SubmissionType", Nav).Value

End With

conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

Next
MainDataSource.QueryConnection.Execute()

End Sub

Code

Re: Iterating through rows of repeating table with a For Each loop

Re: Iterating through rows of repeating table with a For Each loop

Iterating through rows of repeating table with a For Each loop