asked on

I've got a pretty complicated loop that seems to be looping for all but one small peice...??? Involves XML and lots of updates/inserts. 500 pts!!

I've got a script that grabs data from the eBay API via XML response and parses out the data within a loop. Also within this loop I've added lots of inserts and updates to the database. The idea behind the script is that it's pulling all of the orders from eBay and inserting them into this database system. I'll put it in my own words and then I'm going to supply the entire script because I can't think of another way to explain or show what's going on to get help.

Basically, the script generates an XML request and sends it off to eBay and parses the response within a loop. There are lots of other things that happen within the loop. First, all of the auction records get updated with their status information, high bidder, price, etc. After that, an invoice needs to be inserted for the order into the database. The first step here is to check the customers table to see if the phone number (what's being used as customerID) already exists. If it does, update the info with the data received. If it does not exist, create a new customer record with the data received. Moving on from there, the invoice information is inserted into the database (using 3rd party WebAssist extensions in Dreamweaver) which populates the basic information and also stores the new invoice number into a session variable which is used to populate the related field in the InvoiceItems table.

Everything is working great except that all of the items end up on one single Invoice in the system. At first I had the assumption that the Session wasn't getting updated, therefore all of the items ended up on the same invoice. However, only 1 single invoice is getting created. There should be a new invoice being created for each time through the loop and that doesn't seem to be happening.

Everything else works great. Each auction record gets updated fine. The customer information gets inserted/updated accordingly. I can't figure out why the Insert isn't looping...???

Any information would be greatly appreciated. Here is the script. Go easy on me with the sloppiness of it...I'm merely intermediate.

_____________________________________________________

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>







<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<%
sandboxTest = 1

If sandboxTest = 1 Then
      sURL = "https://api.sandbox.ebay.com/ws/api.dll"
      DevID = "DEVID"
      AppID = "APPID"
      Cert = "CERT"
      AuthToken = "TOKEN"
Else
      sURL = "https://api.ebay.com/ws/api.dll"
      DevID = "DEVID"
      AppID = "APPID"
      Cert = "CERT"
      AuthToken = "TOKEN
End If

'Create eBay GetItem call
'Set buffer and expiration values
      Response.Buffer = true
      Response.Expires = -1500

'Send POST via MS WinHTTP Services, version 5.1
      Set oWinHttp = Server.CreateObject("WinHttp.WinHttpRequest.5.1")

'Code to grab address and phone number info
'function used to add the 0 to the front of the days in the date
      Function longNow(item)
      If len(item) = 1 then
      longNow = "0" & item
      Else
      longNow = item
      End if
      End function

'Set the From and To times so that the script will get transactions for the past 24 hours
      ModTimeFrom = year(date) & "-" & longNow(month(date)) & "-" & longNow(day(date)) - 1 & " " & longNow(hour(time)) & ":" & longNow(minute(time)) & ":" & longNow(second(time))
      ModTimeTo = year(date) & "-" & longNow(month(date)) & "-" & longNow(day(date)) & " " & longNow(hour(time)) & ":" & longNow(minute(time)) & ":" & longNow(second(time))

'Create GetSellerTransactions Call
      GetSellerTransactionsRequest = "<?xml version=""1.0"" encoding=""utf-8""?>" &_
                                                  "<GetSellerTransactionsRequest xmlns=""urn:ebay:apis:eBLBaseComponents"">" &_
                                                  "<RequesterCredentials>" &_
                                           "<eBayAuthToken>"&AuthToken&"</eBayAuthToken>" &_
                                                  "</RequesterCredentials>" &_
                                                  "<DetailLevel>ReturnAll</DetailLevel>" &_
                                                  "<IncludeFinalValueFee>1</IncludeFinalValueFee>" &_
                                                  "<ModTimeFrom>"&ModTimeFrom&"</ModTimeFrom>" &_
                                                  "<ModTimeTo>"&ModTimeTo&"</ModTimeTo>" &_
                                                  "</GetSellerTransactionsRequest>"

'Open POST
      oWinHttp.Open "POST", sURL, False

'Set necessary headers
      oWinHttp.SetRequestHeader "Content-Type","text/xml"
      oWinHttp.SetRequestHeader "X-EBAY-API-COMPATIBILITY-LEVEL", "465"
      oWinHttp.SetRequestHeader "X-EBAY-API-SESSION-CERTIFICATE", DevID & ";" & AppID &";" & Cert
      oWinHttp.SetRequestHeader "X-EBAY-API-DEV-NAME", DevID
      oWinHttp.SetRequestHeader "X-EBAY-API-APP-NAME", AppID
      oWinHttp.SetRequestHeader "X-EBAY-API-CERT-NAME", Cert
      oWinHttp.SetRequestHeader "X-EBAY-API-CALL-NAME", "GetSellerTransactions"
      oWinHttp.SetRequestHeader "X-EBAY-API-SITEID", "0"
      oWinHttp.SetRequestHeader "X-EBAY-API-DETAIL-LEVEL", "0"

'Set time out to 60 seconds
      oWinHttp.SetTimeouts 60000, 60000, 60000, 60000

'Send XML request to eBay and store response in AddItemResponse
      oWinHttp.Send GetSellerTransactionsRequest
      GetSellerTransactionsResponse = oWinHttp.ResponseText

'Write contents of the response to a file on the server
      set fs=Server.CreateObject("Scripting.FileSystemObject")
      GetSellerTransactionsResponseXML = (server.MapPath("eBayResponses/GetSellerTransactionsResponse.xml"))
      set tfile=fs.CreateTextFile(GetSellerTransactionsResponseXML)
      tfile.WriteLine(GetSellerTransactionsResponse)
      tfile.close
      set tfile=nothing
      set fs=nothing

'open the XML document
      Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
      xmlDoc.Async = false
      xmlDoc.Load(GetSellerTransactionsResponseXML)

'Get the value of Ack - either Success or Failure. store it in AckStatus
      Set GetSellerTransactionsResponseNode = xmlDoc.selectNodes("/GetSellerTransactionsResponse")
            For Each node In GetSellerTransactionsResponseNode
                  AckStatus = node.selectSingleNode("Ack").text
            Next
      Set GetSellerTransactionsResponseNode = Nothing

'Check if the call was successful. If failed, create a file
'and print the reason inside it. Store the file in failed folder
      If AckStatus = "Failure" Then
            Set fs = Server.CreateObject("Scripting.FileSystemObject")
            filename = Server.MapPath("eBayResponses\failed\GetSellerTransactionsResponse_"&eBayItemID&".asp")
            Set tfile = fs.CreateTextFile(filename)

            Set Failed = xmlDoc.selectNodes("/GetSellerTransactionsResponse/Errors")

            For Each errorNode in Failed
                  tfile.WriteLine errorNode.selectSingleNode("ShortMessage").text & "<br>" & errorNode.selectSingleNode("LongMessage").text & "<p>"
                  response.Write(errorNode.selectSingleNode("ShortMessage").text &"<br>"& errorNode.selectSingleNode("LongMessage").text)
            Next

            Set Failed = Nothing
            Set xmlDoc = Nothing
            tfile.close
            Set tfile = Nothing
            Set fs = Nothing

            response.end
      Else

            'Create a function to give the variables a value of "" if the node does not exist
            'in the XML response
            Function TryNode(Node, Loc)
     On Error Resume Next
     TryNode = ""
     TryNode = Node.selectSingleNode(Loc).Text
            End Function

            'Create function to generate random number in the case that a phone number isn't returned in the Shipping information
            Function getRandomNumber(Length)
           Dim Digit
           Randomize
           Do While Length > 0
      Length = Length - 1
      Digit = Rnd * 9.9
      Digit = mid(Digit, 1, instr(Digit, ".") - 1)
      getRandomNumber = getRandomNumber & Digit
           Loop
            End Function

            'Grab the transactions data from the response and update the auction records
            'for all transactions returned in the response XML
            Set TransactionInfo = xmlDoc.selectNodes("/GetSellerTransactionsResponse/TransactionArray/Transaction")
For Each node In TransactionInfo
AmountPaid = tryNode(node, "AmountPaid")
BuyerEmail = tryNode(node, "Buyer/Email")
                              TransactionPrice = tryNode(node, "TransactionPrice")
BuyerFeedbackScore = tryNode(node, "Buyer/FeedbackScore")
BuyerStatus = tryNode(node, "Buyer/Status")
BuyerEbayID = tryNode(node, "Buyer/UserID")
BuyerName = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/Name")
BuyerStreet1 = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/Street1")
BuyerStreet2 = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/Street2")
BuyerCityName = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/CityName")
BuyerStateOrProvince = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/StateOrProvince")
                              BuyerPostalCode = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/PostalCode")
BuyerCountry = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/CountryName")
BuyerPhoneNumber = tryNode(node, "Buyer/BuyerInfo/ShippingAddress/Phone")
                              If BuyerPhoneNumber <> "" Then
                                    BuyerPhoneNumber = Replace(BuyerPhoneNumber,")","-")
                                    BuyerPhoneNumber = Replace(BuyerPhoneNumber,"(","")
                                    BuyerPhoneNumber = Replace(BuyerPhoneNumber," ","")
                              Else
                                    'Generate Random Number to use so you can insert the order
                                    BuyerPhoneNumber = getRandomNumber(10)
                              End If
eBayItemID = tryNode(node, "Item/ItemID")
ListingStatus = tryNode(node, "Item/SellingStatus/ListingStatus")
                              AuctionEndTime = tryNode(node, "Item/ListingDetails/EndTime")
                              AuctionEndTime = (Left(AuctionEndTime,10))
FinalValueFee = tryNode(node, "FinalValueFee")
                              ShippingServiceCost = tryNode(node, "ShippingServiceSelected/ShippingServiceCost")
                              QuantitySold = tryNode(node, "Item/SellingStatus/QuantitySold")

                              'Update the auction record in Filemaker with the details returned above
                              SQL = "UPDATE Auctions Set WinningBidderEmailAddress = '"&BuyerEmail&"', "&_
                                      "WinningBidderFeedbackLevel = '"&BuyerFeedbackScore&"', " &_
                                      "WinningBidderID = '"&BuyerEbayID&"', AuctionPrice = '"&TransactionPrice&"', " &_
                                      "AuctionEndDate = '"&AuctionEndTime&"', AuctionStatus = 'Winner', " &_
                                      "Notes = Notes + '"&chr(13)&BuyerName&chr(13)&BuyerStreet1&" "&BuyerStreet2&chr(13)&BuyerCityName&", " &_
                                      ""&BuyerStateOrProvince&" "&BuyerPostalCode&chr(13)&BuyerCountry&chr(13)&BuyerPhoneNumber&chr(13)&"' "&_
                                      "WHERE AuctionNumber='"&eBayItemID&"'"

                              Set conn = CreateObject("ADODB.Connection")
                              conn.open MM_connAuctions_STRING
                              conn.execute SQL
                              conn.close
                              Set conn = Nothing

                              'Create an invoice for each transaction using the returned data
                              'Create a recordset grabbing customer information based on the phone number
                              'returned (or assigned) above. If the number does not exist, create a customer record
                              'with the entered data. If the number does exist, update the current billing information
                              'with what the user entered.
                              Set rsCustomerData = Server.CreateObject("ADODB.Recordset")
                              rsCustomerData.ActiveConnection = MM_connCustomers_STRING
                              rsCustomerData.Source = "SELECT * FROM Customers WHERE BillingPhone = '"&BuyerPhoneNumber&"'"
                              rsCustomerData.CursorType = 0
                              rsCustomerData.CursorLocation = 2
                              rsCustomerData.LockType = 1
                              rsCustomerData.Open()
                              rsCustomerData_numRows = 0

                              If rsCustomerData.EOF = True Then
                                    insertCustomerSQL = "INSERT INTO Customers (BillingName, BillingAddress1, BillingAddress2, BillingCity, BillingState, BillingZipCode, " &_
                                                                  "BillingCountry, BillingPhone, ShippingPhone, ShippingName, ShippingAddress1, ShippingAddress2, " &_
                                                                  "ShippingCity, ShippingState, ShippingZipCode, ShippingCountry, EmailAddress) VALUES ('"&BuyerName&"', " &_
                                                                  "'"&BuyerStreet1&"', '"&BuyerStreet2&"', '"&BuyerCityName&"', '"&BuyerStateOrProvince&"', "&BuyerPostalCode&", " &_
                                                                  "'"&BuyerCountry&"', '"&BuyerPhoneNumber&"', '"&BuyerPhoneNumber&"', '"&BuyerName&"', " &_
                                                                  "'"&BuyerStreet1&"', '"&BuyerStreet2&"', '"&BuyerCityName&"', '"&BuyerStateOrProvince&"', '"&BuyerPostalCode&"', " &_
                                                                  "'"&BuyerCountry&"', '"&BuyerEmail&"')"
                                    set cmdInsertCustomer = Server.CreateObject("ADODB.Command")
                                    cmdInsertCustomer.ActiveConnection = MM_connCustomers_STRING
                                    cmdInsertCustomer.CommandText = insertCustomerSQL
                                    cmdInsertCustomer.CommandType = 1
                                    cmdInsertCustomer.CommandTimeout = 0
                                    cmdInsertCustomer.Prepared = true
                                    cmdInsertCustomer.Execute()
                                    set cmdInsertCustomer = Nothing
                              Else
                                    set updateCustomer = Server.CreateObject("ADODB.Command")
                                    updateCustomer.ActiveConnection = MM_connCustomers_STRING
                                    updateCustomer.CommandText = "UPDATE Customers SET BillingPhone = '"&BuyerPhoneNumber&"', BillingName = '"&BuyerName&"'," &_
                                                                               " BillingAddress1 = '"&BuyerStreet1&"', BillingAddress2 = '"&BuyerStreet2&"', BillingCity = '"&BuyerCityName&"'," &_
                                                                              " BillingState = '"&BuyerStateOrProvince&"', BillingZipCode = '"&BuyerPostalCode&"', BillingCountry = '"&BuyerCountry&"'," &_
                                                                               " ShippingPhone = '"&BuyerPhoneNumber&"', ShippingName = '"&BuyerName&"'," &_
                                                                               " ShippingAddress1 = '"&BuyerStreet1&"', ShippingAddress2 = '"&BuyerStreet2&"', ShippingCity = '"&BuyerCityName&"',"&_
                                                                               " ShippingState = '"&BuyerStateOrProvince&"', ShippingZipCode = '"&BuyerPostalCode&"', ShippingCountry = '"&BuyerCountry&"',"&_
                                                                               " EmailAddress = '"&BuyerEmail&"' WHERE BillingPhone = '"&BuyerPhoneNumber&"'"
                                    updateCustomer.CommandType = 1
                                    updateCustomer.CommandTimeout = 0
                                    updateCustomer.Prepared = true
                                    updateCustomer.Execute()
                                    set updateCustomer = Nothing
                              End If
                              %>

                              <%' WA eCart Store Cart Summary in Db
                              if (WA_eCart_EOF(dealsDirectCart)) then
                               WA_connection = MM_connInvoices_STRING
                               WA_table = "Invoices"
                               WA_redirectURL = ""
                               WA_indexField = "InvoiceNumber"
                               WA_fieldNamesStr = "ShipVia|Terms|SalespersonName|BillingPhone|WebOrderNumber|LeadSource|ShippingOverride|ShippingOverrideYesNo|MiscChargeYesNo|MiscChargeOverrideAmt|MiscChargeOverride"
                               WA_fieldValuesStr = "FedEx Ground" & "|" & "PayPal" & "|" & "Chris Myers" & "|" & "" & cStr(BuyerPhoneNumber) & "" & "|" & "" & cStr(eBayItemID) & "" & "|" & "eBay" & "|" & "" & cStr(ShippingServiceCost) & "" & "|" & "Yes" & "|" & "No" & "|" & "0" & "|" & "No"
                               WA_columnTypesStr = "',none,''|',none,''|',none,''|',none,''|none,none,NULL|',none,''|none,none,NULL|',none,''|',none,''|none,none,NULL|',none,''"
                               WA_sessionName = "InvoiceID"
                               indexFieldIndex = -1
                               WA_fieldValues = Split(WA_fieldValuesStr,"|")
                               WA_fieldNames = Split(WA_fieldNamesStr,"|")
                               WA_columns = Split(WA_columnTypesStr,"|")
                               set WA_editCmd = Server.CreateObject("ADODB.Command")
                               updateFieldValue = ""
                               WA_editCmd.ActiveConnection = WA_connection
                               if (WA_redirectURL <> "" AND Request.QueryString <> "" AND Request.QueryString.Count > 0) then
                                    if (inStr(WA_redirectURL,"?") > 0) then
                                      WA_redirectURL = WA_redirectURL & "&"
                                    else
                                      WA_redirectURL = WA_redirectURL & "?"
                                    end if
                                    WA_redirectURL = WA_redirectURL & Request.QueryString
                               end if
                               for i = 0 to Ubound(WA_fieldNames)
                                    if (WA_indexField = WA_fieldNames(i)) then
                                      indexFieldIndex = i
                                      exit for
                                    end if
                               next
                               if (indexFieldIndex >= 0) then updateFieldValue = WA_fieldValues(indexFieldIndex)
                               if (updateFieldValue = "") then updateFieldValue = cStr(Session(WA_sessionName))
                               updateColType = "none,none,NULL"
                               if (indexFieldIndex >= 0) then
                                     updateColType = WA_columns(indexFieldIndex)
                               end if
                               if (updateFieldValue <> "" AND updateFieldValue <> "undefined") then
                                      valueForWhere = WA_generateInsertParams(Array(WA_indexField), Array(updateColType), Array(updateFieldValue), -1)
                                      sqlstr = "select " & WA_indexField & " from " & WA_table & " where " & WA_indexField & " = " & valueForWhere(2) & " order by " & WA_indexField & " DESC"
                                      set WA_eCartRecordset = Server.CreateObject("ADODB.Recordset")
                                      WA_eCartRecordset.ActiveConnection = WA_connection
                                      WA_eCartRecordset.Source = sqlstr
                                      WA_eCartRecordset.CursorType = 0
                                      WA_eCartRecordset.CursorLocation = 2
                                      WA_eCartRecordset.LockType = 1
                                      WA_eCartRecordset.Open()
                                      if (NOT WA_eCartRecordset.EOF) then
                                          updateFieldValue = cStr(WA_eCartRecordset.Fields.Item(WA_indexField).Value)
                                      else
                                          updateFieldValue = ""
                                      end if
                                      WA_eCartRecordset.Close()
                               end if
                               if (updateFieldValue <> "" AND updateFieldValue <> "undefined") then
                                    updateParamsObj = WA_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, indexFieldIndex)
                                      valueForWhere = WA_generateInsertParams(Array(WA_indexField), Array(updateColType), Array(updateFieldValue), -1)
                                      WA_editCmd.CommandText = "update " & WA_table & " SET " & updateParamsObj(3) & " where " & WA_indexField & " = " & valueForWhere(2)
                                    WA_editCmd.Execute()
                                      WA_editCmd.ActiveConnection.Close()
                               else
                                    updateFieldValue = ""
                                    insertParamsObj = WA_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1)
                                    WA_editCmd.CommandText = "insert into " & WA_table & " (" & insertParamsObj(1) & ") values (" & insertParamsObj(2) & ")"
                                    WA_editCmd.Execute()
                                    WA_editCmd.ActiveConnection.Close()
                               end if
                               if (indexFieldIndex < 0) then
                                    if (updateFieldValue = "") then
                                      obj = WA_generateWhereClause(WA_fieldNames, WA_columns, WA_fieldValues, indexFieldIndex)
                                      sqlstr = "select " & WA_indexField & " from " & WA_table & " where " & obj & " order by " & WA_indexField & " DESC"
                                      set WA_eCartRecordset = Server.CreateObject("ADODB.Recordset")
                                      WA_eCartRecordset.ActiveConnection = WA_connection
                                      WA_eCartRecordset.Source = sqlstr
                                      WA_eCartRecordset.CursorType = 0
                                      WA_eCartRecordset.CursorLocation = 2
                                      WA_eCartRecordset.LockType = 1
                                      WA_eCartRecordset.Open()
                                      if (NOT WA_eCartRecordset.EOF) then Session(WA_sessionName) = WA_eCartRecordset.Fields.Item(WA_indexField).Value
                                      WA_eCartRecordset.Close()
                                    else
                                      Session(WA_sessionName) = updateFieldValue
                                    end if
                               else
                                    Session(WA_sessionName) = WA_fieldValues(indexFieldIndex)
                               end if
                               if (WA_redirectURL <> "") then
                                    Response.Redirect(WA_redirectURL)
                               end if
                              end if
%>
<%
                              'Get the ItemNumber and the ControlNumber of the item sold from the auction record
                              Set rsItemData = Server.CreateObject("ADODB.Recordset")
                              rsItemData.ActiveConnection = MM_connAuctions_STRING
                              rsItemData.Source = "SELECT ItemNumber, InventoryCode FROM Auctions WHERE AuctionNumber = '"&eBayItemID&"'"
                              rsItemData.CursorType = 0
                              rsItemData.CursorLocation = 2
                              rsItemData.LockType = 1
                              rsItemData.Open()
                              rsItemData_numRows = 0

                              ItemNumber = rsItemData("ItemNumber")
                              ControlNumber = rsItemData("InventoryCode")
%>
<%
                              'Store cart details
                              if (WA_eCart_EOF(dealsDirectCart)) then

                              insertInvoiceItemsSQL = "INSERT INTO InvoiceItems (InvoiceNumber, ItemNumber, Quantity, Price, ScanCode) VALUES ('"&Session("InvoiceID")&"', '"&ItemNumber&"', '"&QuantitySold&"', '"&TransactionPrice&"', '"&ControlNumber&"')"

                              set cmdInsertInvoiceItems = Server.CreateObject("ADODB.Command")
                              cmdInsertInvoiceItems.ActiveConnection = MM_connInvoices_STRING
                              cmdInsertInvoiceItems.CommandText = insertInvoiceItemsSQL
                              cmdInsertInvoiceItems.CommandType = 1
                              cmdInsertInvoiceItems.CommandTimeout = 0
                              cmdInsertInvoiceItems.Prepared = true
                              cmdInsertInvoiceItems.Execute()
                              Set cmdInsertInvoiceItems = Nothing

                              End If
%>
<%
     Next
Set TransactionInfo = Nothing

      End If
%>
<%
' WA eCart Redirect
if (dealsDirectCart_redirStr <> "") then
Response.Redirect(dealsDirectCart_redirStr)
end if
%>
</body>
</html>
<%
rsCustomerData.Close()
Set rsCustomerData = Nothing
%>
<%
rsItemData.Close()
Set rsItemData = Nothing
%>

Carl Tawn

The first thing to do, if you haven't already, is to track what Session("InvoiceID") contains each time through the loop to see if it is actually changing. Do that by adding the following:

Response.Write "InvoiceID: " & Session("InvoiceID") & "<br>": Response.Flush

Immediately before the line:

insertInvoiceItemsSQL = "INSERT INTO InvoiceItems (InvoiceNumber, ItemNumber, Quantity, Price, ScanCode) VALUES ('"&Session("InvoiceID")&"', '"&ItemNumber&"', '"&QuantitySold&"', '"&TransactionPrice&"', '"&ControlNumber&"')"

It should make it easier to track down the problem once we know if that value changes or not.

Andrew Angell

ASKER

Ok, it's gonna be later tonight before I can get back to this and try it, and I definitely will. The thing is, if the problem is simply that the Session isn't getting populated correctly each time, wouldn't each invoice still be getting created? It seems to me that in a case where the session wasn't getting updated I would end up with multiple invoices, but all of the products on the first one it created and none of the others. The only place that gets used is to insert the InvoiceNumber into the InvoiceItems table so does that really effect the creation of a new invoice all together during the loop?

When I get home tonight I'll try your suggestion. My only worry is that it'll print the same InvoiceID multiple times making us think the value isn't getting updated, when in fact it's just getting pulled from the same invoice over and over updating itself with the same number.

I could be thinking incorrectly...that's obviously why I'm here. I'll update you later tonight when I give this a shot. Thanks!

Andrew Angell

ASKER

Ok, I need to explain a little bit more I think before I can say what happened...

In the part of the script that generates the XML request, it creates the ModTimeFrom and ModTimeTo variables and stores the date format inside them.

ModTimeFrom = year(date) & "-" & longNow(month(date)) & "-" & longNow(day(date)) - 2 & " " & longNow(hour(time)) & ":" & longNow(minute(time)) & ":" & longNow(second(time))
ModTimeTo = year(date) & "-" & longNow(month(date)) & "-" & longNow(day(date)) + 1 & " " & longNow(hour(time)) & ":" & longNow(minute(time)) & ":" & longNow(second(time))

It returns all transactions within these 2 days, so for ModTimeFrom you subtract how many days back you want to go, and then I just add a day to the ModTimeTo so that I'm guaranteed to get all the latest transactions.

Ok, yesterday when I was playing around with this I had it set to longNow(day(date)) - 1 so that it would basically return the past 24 hours worth of transactions. I had put in 2 test transactions in eBay sandbox within that time, so they were both coming back and looping through my script as I ran it yesterday.

Today I sit down and I added in the line you said to add for testing and ran it. Well, one of those transactions got out of the time frame, so only 1 <transaction> element was returned in the XML...so, only Invoice ID was displayed to me on screen and only 1 of the auctions was updated in the database. The invoice was created successfully, so it actually worked perfectly for that one transaction since the loop only ran one time.

Well, I changed it to go back 2 days so that it would grab both transactions again. I then ran the script again, but I hadn't deleted the invoice that was just created yet. This time it grabbed both transactions and printed the InvoiceID to the screen 2 times, both of which were the same...and they were the same as the one that had just been created by the previous run!? All 3 transactions ended up on that 1 invoice in the database.

Do you have any idea what's going on here? I'm lost. appreciate any help I can get. thanks!

Andrew Angell

ASKER

It seems to me that for some reason the 3rd party behavior i'm using is only creating the 1 invoice no matter how many times through the loop it goes. Does that make any sense? I can't figure out why that would be happening, though.

Andrew Angell

ASKER

The 3rd party Insert Cart Summary behavior for dreamweaver that I'm using is actually empty during this run-time. Therefore, in the behavior options I selected to only run the insert if the eCart is empty, hense the line it creates:

if (WA_eCart_EOF(dealsDirectCart)) then

Is the cart getting populated with something along the way after the first insert, and therefore not inserting a new invoice aftewards?

Carl Tawn

Can you post a snippet of the XML ?

Just to clarify, your XML contains mulitple items. Does each item belong to a seperate invoice or does the XML contain multiple items that may or may not belong to the same invoice ? If there are items for different invoices which node in the XML differentiates between invoices ?

Andrew Angell

ASKER

Here's the entire XML that came back which includes 2 transactions:

http://angelleye.sytes.net/dealsdirect/eBayApps/auctionTools/eBayResponses/getSellerTransactionsResponse.xml

The way this particular call works, it simpy returns all transactions with auction/item/buyer information. There are different calls for creating "orders" within eBay to combine multiple transactions from the same buyer. I'll move on to that after I get this problem figured out.

For now, I'm simply trying to make this script insert a new invoice for every transaction that comes back.

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrew Angell

ASKER

Ok, you're getting me close. The first time through it's running the insert just like it should, then the second time through the loop it's running the update.

Carl Tawn

Does it drop into the second block ?

if (indexFieldIndex < 0) then
if (updateFieldValue = "") then
obj = WA_generateWhereClause(WA_fieldNames, WA_columns, WA_fieldValues, indexFieldIndex)
sqlstr = "select " & WA_indexField & " from " & WA_table & " where " & obj & " order by " & WA_indexField & " DESC"
set WA_eCartRecordset = Server.CreateObject("ADODB.Recordset")
WA_eCartRecordset.ActiveConnection = WA_connection
WA_eCartRecordset.Source = sqlstr
WA_eCartRecordset.CursorType = 0
WA_eCartRecordset.CursorLocation = 2
WA_eCartRecordset.LockType = 1
WA_eCartRecordset.Open()
if (NOT WA_eCartRecordset.EOF) then Session(WA_sessionName) = WA_eCartRecordset.Fields.Item(WA_indexField).Value
WA_eCartRecordset.Close()
else
Session(WA_sessionName) = updateFieldValue
end if

And, if so, does it drop into the If or the Else ?

Andrew Angell

ASKER

Ok, so based on that I changed the line right above all that to:

if (NOT WA_eCartRecordset.EOF) then
                                          updateFieldValue = ""
                                      else
                                          updateFieldValue = ""
                                      end if
                                      WA_eCartRecordset.Close()

And that did work! it ran INSERT INTO for both times through the loop and created seperate invoices just like it's suppose to. So, apparently the firs ttime through the loop WA_eCartRecordset was getting assigned some value? Can you tell if this is going to cause a problem for me by leaving it set to blank for both instances? I don't think it should cuz I'm not using eCart data anyway, but obviously I can't tell for sure.

Carl Tawn

The honest answer is "I have no idea". All that eCart stuff is complicated and messy, it looks like its intended to handle multiple items for the same invoice in a single file rather than multiple invoices. What you have done is basically short-circuit it to get it to do what you want.

You may run into problems later, but then again you might not. If you do then you can always come up with a more robust solution.

Andrew Angell

ASKER

Maybe you can answer this for me. How in the heck is it grabbing the value of the InvoiceID for the brand new Invoice that it just created? That's the only reason I'm using this eCart extension to begin with...it takes care of that for me. Otherwise I could do a more simple insert like I have for the InvoiceItems.

Carl Tawn

You'd need to look at this line to see what it is putting in the WHERE clause:

sqlstr = "select " & WA_indexField & " from " & WA_table & " where " & obj & " order by " & WA_indexField & " DESC"

The WA_indexField at this point will probably say "InvoiceID" so I would guess that this is the query that grabs it. Its difficult to tell from the code what it might be putting in the WHERE clause, so a bit of debug to see what it says might answer the question.