Solved

loop thru a dataset

Posted on 2011-03-15
6
490 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
  • 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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