loop thru a dataset

I have 2 tables which I would like to build a dataset.They will be link by common field "permID". Whatever the dataset returns,I'd like to loop in each row and run a stored procedure. Can someone bring in sample code how to do it?
zachvaldezAsked:
Who is Participating?
 
Shahan AyyubConnect With a Mentor Senior Software Engineer - iOSCommented:
So code should look like this:

        Dim dt As New DataTable()
        dt = Db2DataSet.tblStd   ' For a particular table, if more then one use a loop
        Dim Parms As SqlParameter
        Dim _cmdSqlCommand As New SqlCommand

        For Each row As DataRow In dt.Rows
            With _cmdSqlCommand
                .CommandType = CommandType.StoredProcedure      'Set type to StoredProcedure
                .CommandText = "ProcedureName"                    'Specify stored procedure to run

                Parms = New SqlParameter("@PermID", row("PermID")) ' here permID field's value of each
                                                                                                          'row will be used to pass as a 
                                                                                                          'parameter to SP.

                ' Clear any previous parameters from the Command object
                Call .Parameters.Clear()

                ' Loop through parmameter collection adding parameters to the command object
                If Not (Parms Is Nothing) Then
                        _cmdSqlCommand.Parameters.Add(sqlParm)
                End If
                _cmdSqlCommand.ExecuteNonQuery()  ' ExecuteNonQuery used, but depends what you want from Stored Proc
                ''''''
                'Initialize dsDataSet as New DataSet
                '_adpAdapter.SelectCommand = _cmdSqlCommand
                '_adpAdapter.Fill(dsDataSet)      ' If Stored proc returns some results
                ''''''
            End With
        Next

Open in new window

0
 
Asim NazirCommented:
You want to build DataSet after executing stored procedure or what?

In order to loop through DataSet, you need to loop through dataset tables, then rows to get cell values.

Please clarify what exactly you want todo.

Asim
0
 
zachvaldezAuthor Commented:
It should be one table and the keyfield is permID, Id like to go to each row and runf run s stored procedure
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Shahan AyyubSenior Software Engineer - iOSCommented:
Hi!

Please see this: (Not tested, but give you an idea how to start)

        Dim dt As New DataTable()
        dt = Db2DataSet.tblStd   ' For a particular table, if more then one use a loop
        Dim Parms(6) As SqlParameter
        Dim _cmdSqlCommand As New SqlCommand

        For Each row As DataRow In dt.Rows
            With _cmdSqlCommand
                .CommandType = CommandType.StoredProcedure      'Set type to StoredProcedure
                .CommandText = "ProcedureName"                    'Specify stored procedure to run

                Parms(0) = New SqlParameter("@clientid", row(0))
                Parms(1) = New SqlParameter("@firstname", row(1))
                Parms(2) = New SqlParameter("@lastname", row(2))
                Parms(3) = New SqlParameter("@middlename", row(3))
                Parms(4) = New SqlParameter("@SSN", row(4))
                Parms(5) = New SqlParameter("@Birthdate", row(5))
                Parms(6) = New SqlParameter("@Gender", row(6))

                ' Clear any previous parameters from the Command object
                Call .Parameters.Clear()

                ' Loop through parmameter collection adding parameters to the command object
                If Not (Parms Is Nothing) Then
                    For Each sqlParm As SqlParameter In Parms
                        _cmdSqlCommand.Parameters.Add(sqlParm)
                    Next
                End If
                _cmdSqlCommand.ExecuteNonQuery()  ' ExecuteNonQuery used, but depends what you want from Stored Proc
                ''''''
                'Initialize dsDataSet as New DataSet
                '_adpAdapter.SelectCommand = _cmdSqlCommand
                '_adpAdapter.Fill(dsDataSet)      ' If Stored proc returns some results
                ''''''
            End With
        Next

Open in new window


See this as well:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21415723.html
0
 
zachvaldezAuthor Commented:
I just want to pass the permID and it would loop thru the table.
I got the sp done.
0
 
zachvaldezAuthor Commented:
The soluiton was'nt perfect but gave me some ideas how to proceed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.