Solved

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

Posted on 2006-06-20
14
629 Views
Last Modified: 2007-12-19
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"%>
<!--#include file="../../WA_eCart/WA_eCart_Definition_VB.asp" -->
<!--#include file="../../WA_eCart/WA_eCart_Database_VB.asp" -->
<!--#include file="../../WA_eCart/dealsDirectCart_VB.asp" -->
<!--#include file="../../Connections/connInvoices.asp" -->

<!--#include file="../../Connections/connAuctions.asp" -->
<!--#include file="../../Connections/connCustomers.asp" -->
<!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
%>
0
Comment
Question by:Andrew Angell
  • 8
  • 6
14 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16949779
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.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16955071
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!
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16965066
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!


0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16965159
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.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16965167
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16967533
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 ?
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16969681
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 16970940
I think you need to look at this block:

        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

Put in some debug to see which branch is executing and which SQL statement is running and what the SQL statement says.

You should probably also watch this 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

As it appears to try and get the InvoiceID that was last inserted and store it in the session ready for the next iteration. So it may be that this part is causing everything to be inserted under the same InvoiceID. But that may also depend on what indexFieldIndex is doing.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16970999
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.  
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16971027
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 ?
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16971047
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.  
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16971241
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.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16971336
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.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16971605
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now