loop through data set to create Web service items

Posted on 2009-05-12
Last Modified: 2012-06-21
I would like to Loop through a data set to create web service items.  

Below is an example of how I hard code items to be sent to the web service.  

I have a data set created from an SQL query, how can II loop through that data set and take the value of the first column in every data row and set that to the PERNR for a new item?

'Dim item(1) As TestWS.DataItem
        'item(0) = New TestWS.DataItem
        'item(0).PERNR = "00012191"
        'item(1) = New TestWS.DataItem
        'item(1).PERNR = "00011382"
        'Dim ws As New TestWS.DataService
        'ws.Credentials = New Net.NetworkCredential("UserName", "Password")

Open in new window

Question by:DotNetNooby
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
  • 2
LVL 83

Accepted Solution

CodeCruiser earned 500 total points
ID: 24364485
Try the following code
Dim Items(dSet.Tables(0).Rows.Count) as New TestWS.DataItem
For i as integer = 0 to dSet.Tables(0).Rows.Count - 1
    Items(i) = dSet.Tables(0).Rows(i).Item(0)
Dim ws As New TestWS.DataService
ws.Credentials = New Net.NetworkCredential("UserName", "Password")

Open in new window


Expert Comment

ID: 24364548
You could do something like the following:

        Dim queryConnString As String = ConfigurationManager.ConnectionStrings("yourConnectionString").ConnectionString
        Dim queryConn As SqlConnection = New SqlConnection(queryConnString)
        Dim querySelect = SELECT pernrColumn FROM table
        Dim queryCmd As SqlCommand = New SqlCommand(querySelect, queryConn)
        Dim queryAdapter As SqlDataAdapter = New SqlDataAdapter(queryCmd)
        Dim queryDataSet As DataSet = New DataSet

            queryAdapter.Fill(queryDataSet, "QueryTable")

        Catch ex As Exception
             Put your exception handling code here


        End Try

        ' traverse records and set PERNR in the item
        Dim queryRows As DataRowCollection = queryDataSet.Tables("QueryTable").Rows
        Dim i As Integer
        Dim curRow As DataRow

        For i = 0 To queryRows.Count - 1

            curRow = queryRows(i)

            item(i).PERNR = curRow("pernrColumn")


Best of luck!
LVL 83

Expert Comment

ID: 24364636
It is inefficient code. Why do you need to use a curRow variable? Why not

For i = 0 To queryRows.Count - 1

            item(i).PERNR = queryRows(i)("pernrColumn")

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


Expert Comment

ID: 24364860
Just for clarity, assuming a potentially more complex solution involving multiple columns.

Author Comment

ID: 24366559
When I run this it throws an exception: Unable to cast object of type 'System.String' to type 'TestWS.DataItem'

Author Closing Comment

ID: 31580520
worked great, Thanks!
LVL 83

Expert Comment

ID: 24372101
Yeah because the following statement
Items(i) = dSet.Tables(0).Rows(i).Item(0)

is asssigning a string to DataItem. Do you know the structure of TestWS.DataItem? May be you should assign value to a property inside it like
Items(i).Text = dSet.Tables(0).Rows(i).Item(0)

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

615 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