?
Solved

loop thru a dataset

Posted on 2011-03-15
6
Medium Priority
?
523 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
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!

 

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 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

755 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