vb.net process orders in batches

Hello Experts
I am trying to figure out how I can process the orders in batches of 10 orders at a time,
GetOrdersToSaveOnPRP return orders but because I am saving orders by posting to a URL it has own restrictions so I could only save 10 orders at a time without any problem, currently I have to restrict to only pull 10 orders using the SQL but then I have to schedule the script to run next hour to pick the next lot, instead of this I was hoping I could call the script once and it should keep posting to the URL to all the available new orders.
I hope someone can advice on this? please take a look at the code snippet.
many thanks in advance
            ds = GetOrdersToSaveOnPRP()
            If ds.Tables(0).Rows.Count > 0 Then
                Const NumberOfAllowedOrders As Integer = 10

				'''''''''''''''''''''''''''''''''''''''''''''' I want to be able to post 10 orders at at time and post to the URL AND process all orders.
                For Each Row As DataRow In ds.Tables(0).Rows
					OrderID = Row.Item("OrderID")
                    RetailerID  = Row.Item("RetailerID")

                    'Construct the String for the Order Header 
                    OrderStr = "&customer=" & GetIdByRetailer(Row.Item("RetailerId"))
                    OrderStr &= "&tranid=" & String.Format("SO{0}", OrderID)
                    OrderStr &= "&status=1"
                    OrderStr &= "&odate=" & Date.Now.Date
                    Or derStr &= "&sdate=" & Date.Now.Date
                    OrderStr &= "&refnum=Dropship Order"
                    ItemLocation = 1
                    OrderStr &= "&loc=" & ItemLocation
                    OrderStr &= "&salesperson=6807"  '6807

                    'Prepare the JSON string for the Order Details
                    JsonLines = GetProductsFromOrder(RetailerID, ItemLocation, ds.Tables(0))

                    If JsonLines = String.Empty Then
                        PrevRetailerID = RetailerID
                        Continue For
                    End If
                    OrderStr &= "&jsonLines=" & JsonLines

                    ' Configure the URL to post the data

                    ' Update SO post
                    OrderStr = String.Format("https://xxxxxxxxxxxxx.com/app/site/hosting/xxxx?script=163&deploy=1&compid=3333565&h=b491ae5df284ef715a3b{0}", OrderStr)


                    Dim URL As New Uri(OrderStr)
                    'Post the data and expect a SOAP Response     

                    Dim ResponseString As String = SendRequest(URL)
                    Dim Response As String = String.Empty

                    For Each Response In ResponseString.Split(";")
                        'The code here to deal with each line of response string
                        'Example String 
                        If Response.Contains("OK") Then
                           TransactionID = Response.Substring(Response.IndexOf(":") + 1)
                            If (TransactionString <> "") AndAlso (PrevRetailerID <> RetailerID) Then
                                OrderStatusUpdateByRetailer(RetailerID, ds.Tables(0), TransactionString)
                            End If

                        ElseIf Response.Contains("OOS") Then
                            LineItemID = Response.Substring(Response.IndexOf(":") + 1, Response.IndexOf("-") - Response.IndexOf(":") - 1)
                            LineItemQty = Response.Substring(Response.IndexOf("-") + 5)
                            If (LineItemID <> "") AndAlso (LineItemQty <> "") Then
                                OutOfStockStatusUpdateByRetailer(RetailerID, ds.Tables(0), LineItemID, LineItemQty)
                            End If         
                        End If
                    PrevOrderID = OrderID
					''''''''''''''''''''''''''''''''''''''''''''''''''' end
	 Private Shared Function GetProductsFromOrder(ByVal RetailerId As Integer, ByVal ItemLocation As Integer, ByVal dt As DataTable) As String
             dt.DefaultView.RowFilter = "RetailerID=" & RetailerId
            dt.DefaultView.Sort = "DropShipOrderID ASC"
            PrevProdID = ""
            'For Each row As DataRowView In dt.defaultview.row
            For i As Integer = 0 To dt.DefaultView.Count - 1
                Dim row As DataRowView = dt.DefaultView.Item(i)
                ProdID = Trim(row.Item("GiftProductCodeID"))
                OrderID = Trim(row.Item("DropShipOrderID"))
                IsUSACustomer = row.Item("RetailerIsUSADropShipCustomer")
                DropShipRetailerOrderID = row.Item("DropShipRetailerOrderID")

                'If ProdID <> PrevProdID Then
                If Cntr > 0 Then
                    RetVal &= ","
                End If

                If (OrderID <> PrevOrderID) Then

                     ProdInternalID = GetNetsuiteIDByProdID(ProdID)

                    ProdPrice = GetDropShipItemNetPrice(ProdInternalID)
                    ShippingPrice = 5.25

                RetVal &= "{item:" & ProdInternalID & ",qty:" & row.Item("DropShipQty") & ",rate:0,taxcode:1,shippping:" & ShippingPrice & ",dsid:'" & DropShipRetailerOrderID & "'}"

                              PrevOrderID = OrderID
                PrevProdID = ProdID

            RetVal &= "]"
            Return RetVal
        Catch ex As Exception
                     Return String.Empty
        End Try

    End Function

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
I am trying to figure out how I can process the orders in batches of 10 orders at a time,

Could you provide more information as I'm trying to understand your limitation SQL will gladly return all entries or entries from a specific date/time.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I cannot see where you implement that limit of 10 entries (aside from defining a constant).
The traditional approach is to count your current row number, check if   (ownum mod 10 = 0 or rownum = lastrow). and if then send the request, reset the build request string, and continue in the loop.
merajohnAuthor Commented:
Hello Both, thank you for looking into this for me, apologies for not really very clear on this. Actually I am trying to figure out how I can restrict the post to only include first 10 set of orders, post to the URL and then carry on with the next set ..

>>   ds = GetOrdersToSaveOnPRP()

ds.Tables(0).Rows.Count = 25

you are right I only have a constant declared, I can increment it but the problem is I am sending the complete dataset to the sub routine: GetProductsFromOrder to prepare the jSon string.

I have to reduce the dataset to 10 before passing it through so that I can have the Json for 10 orders and rest of the steps can be the same, and after receiving the response, it can loop it through to the rest 10 orders pass it to the GetProductsFromOrder, prepare Json etc and finally last set of 5 orders to prepare the Json and complete the process.

That is what I want it to do but I am unable to make the code right for this, not sure where or how I can break the process?

Your advice on this will greatly be appreciated.


 Public Shared Function GetOrdersToSaveOnPRP() As DataSet
            Dim Conn As New SqlConnection(My.Settings.WebConnectionString)
            Dim Adap As New SqlDataAdapter()
            Dim Cmd As New SqlCommand
            Cmd.CommandText = "SELECT * FROM [viewOrdersToSave]"
            Cmd.CommandType = Data.CommandType.Text
            Cmd.Connection = Conn

            Adap.SelectCommand = Cmd
            Dim DS As New System.Data.DataSet
            Return DS
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

Open in new window

Bob LearnedCommented:
If you are using an SqlDataAdapter, then you can use the Fill method that takes start and max records to get batches of 10:

DbDataAdapter.Fill Method (DataSet, Int32, Int32, String)


Public Sub GetRecords()
    ' ... 
    ' create dataSet and adapter 
    ' ...
    adapter.Fill(dataSet, 9, 15, "Categories")
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
merajohnAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.