Solved

loop thru a dataset

Posted on 2011-03-15
6
481 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 Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

12 Experts available now in Live!

Get 1:1 Help Now