loop through data set to create Web service items

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")
        'ws.Data(item)

Open in new window

DotNetNoobyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
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)
Next
 
Dim ws As New TestWS.DataService
ws.Credentials = New Net.NetworkCredential("UserName", "Password")
ws.Data(items)

Open in new window

0
 
rdoran0928Commented:
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

        Try
            queryConn.Open()
            queryAdapter.Fill(queryDataSet, "QueryTable")

        Catch ex As Exception
             Put your exception handling code here

        Finally
            queryConn.Close()

        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")

        Next


Best of luck!
0
 
CodeCruiserCommented:
@rdoran0928
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")

Next
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rdoran0928Commented:
Just for clarity, assuming a potentially more complex solution involving multiple columns.
0
 
DotNetNoobyAuthor Commented:
When I run this it throws an exception: Unable to cast object of type 'System.String' to type 'TestWS.DataItem'
0
 
DotNetNoobyAuthor Commented:
worked great, Thanks!
0
 
CodeCruiserCommented:
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)
0
All Courses

From novice to tech pro — start learning today.