Solved

loop thru a dataset

Posted on 2011-03-15
6
512 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:zachvaldez
[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
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35137920
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
 

Author Comment

by:zachvaldez
ID: 35140476
It should be one table and the keyfield is permID, Id like to go to each row and runf run s stored procedure
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 35142496
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
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!

 

Author Comment

by:zachvaldez
ID: 35150373
I just want to pass the permID and it would loop thru the table.
I got the sp done.
0
 
LVL 19

Accepted Solution

by:
Shahan Ayyub earned 250 total points
ID: 35151012
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
 

Author Closing Comment

by:zachvaldez
ID: 35177619
The soluiton was'nt perfect but gave me some ideas how to proceed.
0

Featured Post

Technology Partners: 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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

717 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