database connection string

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

database connection string

Logged in as: Guest

arrSession:exec spGetSession 2,2,59521

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> database connection string
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

database connection string - 4/25/2008 9:38:39 AM

cdiltz

Posts: 3
Score: 0
Joined: 4/21/2008
Status: offline

I have a string connection in my script to an ODBC connection.
I want to multiply two fields in my connection string to return results.
One of the fields is a text (char) field and I need to convert this to complete the calculation.

I have tested my syntax in SQL but I cannot get the vbScript to run. It gives the common
elexical element not found. Here is the connection string.

'strSQL = "SELECT Sum(CASE when CI_Item.ShipWeight = ' ' then 0" & _
'"when CI_Item.ShipWeight > 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _
'"when CI_Item.ShipWeight < 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _
'"when CI_Item.ShipWeight = 0 Then 0 " & _
'"when CI_Item.ShipWeight = Null Then 0 " & _
'"Else 0 End) as TotalQuantity FROM SO_SalesOrderDetail, CI_Item " & _
"where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode " & _
'" and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"'))"

'objRS.open strSQL, objConn

'TotalQty = trim(objRS.fields.Item("TotalQuantity").value)

Post #: 1

RE: database connection string - 4/25/2008 4:22:32 PM

ehvbs

Posts: 2012
Score: 48
Joined: 6/22/2005
From: Germany
Status: offline

Hi cdiltz,

(1) what you showed isn't a connection string, but an SQL statement; if this is just
     a kind of typo and you can connect to your DB and execute simple statements
     ignore (1); otherwise post more of your code

(2) if your

       'strSQL = "SELECT S...

    (mark the ') isn't a copy & paste artefact, remove the ' and try again

(3) if (2) helps, double check your statement, e.g.

    ... then 0" & _
    '"when CI_Item.ShipWeight ...

    will result in ... then0when ...

Good luck!

ehvbs

(in reply to cdiltz)

Post #: 2

RE: database connection string - 4/28/2008 3:04:41 AM

cdiltz

Posts: 3
Score: 0
Joined: 4/21/2008
Status: offline

Here is all my my code. I reviewed for spaces, etc and still get a element not found message.
I have set a value for a string variable called strCASE which is inserted into the strSQL statement.
This is set inside a much larger script that declares and passes other values like
SO_SalesOrder_bus_SalesOrderNo.

I created a message box when I run my script to look at the strCASE and strSQL once they are
put together. It all looks good. So I'm wondering if I'm failing on the CAST language or using
the CASE statement.

  '*********************************************
  'Set Value - - > TotalWeight
  '
  '*********************************************
  Dim strCASE, TotalQty, TotalWeight, SO_SalesOrder_bus_SalesOrderNo
TotalWeight = 0
TotalQty = 0
SO_SalesOrder_bus_SalesOrderNo = '9998'

  strCASE = "'Total' = CASE"& chr(13) & _
"when CI_Item.ShipWeight = '' then 0 " & chr(13) & _
   "when CI_Item.ShipWeight > 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _
"when CI_Item.ShipWeight < 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _
   "when CI_Item.ShipWeight = 0 Then 0 " & chr(13) & _
  "when CI_Item.ShipWeight = Null Then 0 " & chr(13) & _
  "Else 0 End" & chr(13)

MsgBox(strCASE)

  strSQL = "SELECT Sum("& chr(13)& strCASE & chr(13)& ") as TotalQuantity FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode " & _
  " and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo + "'))"

  MsgBox("String: " & strSQL)

  objRS.open strSQL, objConn

  TotalQty = trim(objRS.fields.Item("TotalQuantity").value)

  If TotalQty <> 0 Then
  TotalWeight = TotalQty
  Else TotalWeight = 0
  End If

  objRS.Close

< Message edited by cdiltz -- 4/28/2008 3:06:10 AM >

(in reply to ehvbs)

Post #: 3

RE: database connection string - 4/28/2008 3:09:02 AM

ebgreen

Posts: 4595
Score: 29
Joined: 7/12/2005
Status: offline

If this is all of your code then I don't see anywhere that you create objRS or objConn

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to cdiltz)

Post #: 4

RE: database connection string - 4/28/2008 4:17:15 AM

cdiltz

Posts: 3
Score: 0
Joined: 4/21/2008
Status: offline

I create those objects in the code prior to that. It's alot of code, but below is all of my VBS file.

Please search down to section: 'Set Value - - > TotalWeight

' 
' language VBScript
' 
' panel variables
' SO_SalesOrder_bus_SalesOrderNo
' SO_SalesOrder_bus_UDF_HAZMAT
' SO_SalesOrder_bus_UDF_ADDRESSTYPE2
' SO_SalesOrder_bus_UDF_DGROUND
' SO_SalesOrder_bus_UDF_DNEXTDAYAIRSAVER
' SO_SalesOrder_bus_UDF_DNEXTDAYAIRSAVERAM
' SO_SalesOrder_bus_UDF_DSECONDDAYAIR
' SO_SalesOrder_bus_UDF_DTHREEDAYSELECT
' SO_SalesOrder_bus_UDF_HAZMATFEE
' SO_SalesOrder_bus_UDF_HAZMATGROUND
' SO_SalesOrder_bus_UDF_RNEXTDAYAIR
' SO_SalesOrder_bus_UDF_HAZMATFEEPER1GAL
' SO_SalesOrder_bus_UDF_HAZMATFEEPER5GAL
' SO_SalesOrder_bus_UDF_HAZMATFEEPERKIT
' SO_SalesOrder_bus_ShipToCity
' SO_SalesOrder_bus_ShipToCountryCode
' SO_SalesOrder_bus_ShipToName
' SO_SalesOrder_bus_ShipToState
' SO_SalesOrder_bus_ShipToZipCode
' SO_SalesOrder_bus_ShipVia
' SO_SalesOrder_bus_ShipWeight
' SO_SalesOrder_bus_WarehouseCode
' SO_SalesOrder_bus_FreightAmt
' SO_SalesOrder_bus_Weight
' 
' system variables
' MAS_SCR_CMP : company code
' MAS_SCR_USR : user code
' MAS_SCR_MOD : module code
' MAS_SCR_DTE : current app date
' MAS_SCR_LIB : library
' MAS_SCR_PNL : panel
' MAS_SCR_OBJ : control [BT_LINK_x]
' MAS_SCR_CS  : 1 if running Sage MAS 200 on client
' MAS_SCR_DBG : 1 to show script before and following execution
'

'*********************************************
' RUN CALCULATION ON ORDER FOR FREIGHT CHARGES
'
'*********************************************
'Revised version of script -- password issue is all that remains
' diminsion local variables for im inq on this script
Dim objConn ' Connection Name
Dim objRS ' Recordset Variable
Dim strSQL ' variable for SQL Statement
Dim TotalHazQty5GAL ' Quantity Variable for Hazmat By Piece Items (5gal)
Dim TotalHazQty1GAL ' Quantity Variable for Hazmat By Order Items (1gal)
Dim TotalHazQtyKIT ' Quantity Variable for Hazmat By Order Items (KIT)
Dim TotalHazMatFee   ' Total of All HazMat Fees (#)
Dim HazmatGroundOnly ' Is this a Ground Only Shipment Y/N
Dim HazmatOrder      ' Does this order have HazMat Items Y/N
Dim TotalQty   ' Qty for sum calculations
Dim TotalWeight   ' Calculate weight on items
TotalQty = 0 
TotalWeight = 0 
TotalHazQtyPiece = 0
TotalHazQtyOrder = 0
TotalHazMatFee = 0
HazmatGroundOnly = "N"
HazmatOrder = "N"

'qt = SO_SalesOrder_bus_OrderType
'orderno = SO_SalesOrder_bus_SalesOrderNo
' msgbox("Match Count In Process for Quote: " + orderno)
'*********************************************
' CONNECT TO MAS200 DATABASE
'
'*********************************************
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
'Try this for 4.10:
objConn.Open "DSN=SOTAMAS90; UID=BKD; PWD=SAGEkids1; Company=EAC"
'*********************************************
'Set Value - - > HazmatOrder
'
'*********************************************
strSQL = "SELECT Sum(SO_SalesOrderDetail.QuantityOrdered) AS TotalQuantity FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"')) AND CI_ITEM.UDF_HAZMAT = 'Y'"
objRS.open strSQL, objConn
TotalQty = TotalQty + trim(objRS.fields.Item("TotalQuantity").value)
If TotalQty <> 0 Then
HazmatOrder = "Y"
Else HazmatOrder = "N"
End If
'MsgBox ("Hazmat Order Qty: " & TotalQty)
'MsgBox ("Hazmat Order: " & HazmatOrder)

TotalQty = 0
objRS.Close
'*********************************************
'Set Value - - > HazmatGroundOnly
'
'*********************************************
strSQL = "SELECT Sum(SO_SalesOrderDetail.QuantityOrdered) AS TotalQuantity FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"')) AND CI_ITEM.UDF_HAZMATGROUND = 'Y'"
objRS.open strSQL, objConn
TotalQty = trim(objRS.fields.Item("TotalQuantity").value)
If TotalQty <> 0 Then
HazmatGroundOnly = "Y"
Else HazmatGroundOnly = "N"
End If
'MsgBox ("Hazmat Ground Order Qty: " & TotalQty)
'MsgBox ("Hazmat Ground Order: " & HazmatGroundOnly)
TotalQty = 0
objRS.Close
'*********************************************
'Set Value - - > TotalHazQty5GAL
'
'*********************************************
strSQL = "SELECT Sum(SO_SalesOrderDetail.QuantityOrdered) AS TotalQuantity" & _
" FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode" & _
" and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"'))" & _
" AND CI_ITEM.UDF_HAZMATFEETYPE = '5GAL'"
objRS.open strSQL, objConn
TotalQty = trim(objRS.fields.Item("TotalQuantity").value)
If TotalQty <> 0 Then
TotalHazQty5GAL = TotalQty
Else TotalHazQty5GAL = 0
End If
TotalQty = 0
objRS.Close

'*********************************************
'Set Value - - > TotalHazQty1GAL
'
'*********************************************
strSQL = "SELECT Sum(SO_SalesOrderDetail.QuantityOrdered) AS TotalQuantity" & _
" FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode" & _
" and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"'))" & _
" AND CI_ITEM.UDF_HAZMATFEETYPE = '1GAL'"
objRS.open strSQL, objConn
TotalQty = trim(objRS.fields.Item("TotalQuantity").value)
If TotalQty <> 0 Then
TotalHazQty1GAL = TotalQty
Else TotalHazQty1GAL = 0
End If
TotalQty = 0
objRS.Close
'*********************************************
'Set Value - - > TotalHazQtyKIT
'
'*********************************************
strSQL = "SELECT Sum(SO_SalesOrderDetail.QuantityOrdered) AS TotalQuantity" & _
" FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode" & _
" and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo +"'))" & _
" AND CI_ITEM.UDF_HAZMATFEETYPE = 'KIT'"
objRS.open strSQL, objConn
TotalQty = trim(objRS.fields.Item("TotalQuantity").value)
If TotalQty <> 0 Then
TotalHazQtyKIT = TotalQty
Else TotalHazQtyKIT = 0
End If
TotalQty = 0
objRS.Close
'*********************************************
'Set Value - - > TotalWeight
'
'*********************************************
Dim strCASE

SO_SalesOrder_bus_SalesOrderNo = '9998'

strCASE = "'Total' = CASE"& chr(13) & _ 
"when CI_Item.ShipWeight = '' then 0 " & chr(13) & _ 
[size=2][size=2]   "when CI_Item.ShipWeight > 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _ 
[/size][/size][size=2]  "when CI_Item.ShipWeight < 0 Then (Cast(CI_Item.ShipWeight as Numeric)*(SO_SalesOrderDetail.QuantityOrdered))+0 " & _ 
[/size]   "when CI_Item.ShipWeight = 0 Then 0 " & chr(13) & _ 
"when CI_Item.ShipWeight = Null Then 0 " & chr(13) & _ 
"Else 0 End" & chr(13) 
 
MsgBox(strCASE)

strSQL = "SELECT Sum("& chr(13)& strCASE & chr(13)& ") as TotalQuantity FROM SO_SalesOrderDetail, CI_Item where SO_SalesOrderDetail.ItemCode = CI_Item.ItemCode " & _ 
" and (((SO_SalesOrderDetail.SalesOrderNo)='"+ SO_SalesOrder_bus_SalesOrderNo + "'))"

MsgBox("String: " & strSQL)

objRS.open strSQL, objConn

TotalQty = trim(objRS.fields.Item("TotalQuantity").value)

If TotalQty <> 0 Then 
TotalWeight = TotalQty     
Else TotalWeight = 0 
End If

objRS.Close

'*********************************************
'ASSIGN VALUES TO RETURN TO ORDER IN MAS200
'
'*********************************************
Dim HazFee5GAL, HazFee1GAL, HazFeeKIT
HazFee5GAL = 0
HazFee1GAL = 0
HazFeeKIT = 0
If TotalHazQty5GAL >= 4 Then 
HazFee5GAL = HazFee5GAL + 0
Else HazFee5GAL = HazFee5GAL + (30 * TotalHazQty5GAL)
End if
If TotalHazQty1GAL >= 4 Then 
HazFee1GAL = HazFee1GAL + 0
Else HazFee1GAL = HazFee1GAL + 30
End if
If TotalHazQtyKIT >= 4 Then 
HazFeeKIT = HazFeeKIT + 0
Else HazFeeKIT = HazFeeKIT + 30
End If
TotalHazMatFee = HazFee5GAL + HazFee1GAL + HazFeeKIT

Set objRS = Nothing
objConn.Close
Set objConn = Nothing

'******************************************************
'******************************************************
'******************************************************
'
'   UPS RATE SHOPPING STARTS HERE
'
'******************************************************
'******************************************************
'****************************************************** 
' VARIABLES USED FROM ABOVE
' Dim TotalHazMatFee   ' Total of All HazMat Fees (#)
' Dim HazmatGroundOnly ' Is this a Ground Only Shipment Y/N
' Dim HazmatOrder      ' Does this order have HazMat Items Y/N
' 
'-----------------------------------------------------------------------
' Define Variables for UPS Integration
'-----------------------------------------------------------------------
Dim xmlDoc, XMLhttp, xmlDoc2, Response_Doc, Test_Doc
Dim strErrorMessage, strFailed, strGroundOnly
Dim ShipToCity, ShipToState, ShipToZipCode, ShipToCountryCode, RFlag
Dim ShipperCity, ShipperState, ShipperZipCode, ShipperCountryCode
Dim ShipperWhse, ShipperCheck
Dim UpRate, UPSWeight
strFailed = "N"
strGroundOnly = HazmatGroundOnly
UpRate = 1.12
UPSWeight = SO_SalesOrder_bus_ShipWeight

'-----------------------------------------------------------------------
' Check Ship To Address
'-----------------------------------------------------------------------
Dim AddressCityYN, AddressStateYN, AddressZipCodeYN
AddressCityYN = "Y"
AddressStateYN = "Y"
AddressZipCodeYN = "Y"
if LEN(SO_SalesOrder_bus_ShipToCity) = 0 then AddressCityYN = "N"
if (SO_SalesOrder_bus_ShipToCity) = " " then AddressCityYN = "N"
if LEN(SO_SalesOrder_bus_ShipToState) = 0 then AddressStateYN = "N"
if (SO_SalesOrder_bus_ShipToState) = " " then AddressStateYN = "N"
if LEN(SO_SalesOrder_bus_ShipToZipCode) = 0 then AddressZipCodeYN = "N"
if (SO_SalesOrder_bus_ShipToZipCode) = " " then AddressZipCodeYN = "N"
if AddressCityYN = "N" then
FailAndExit("City Missing in Ship To Address!!")
end if
if AddressStateYN = "N" then
FailAndExit("State Missing In Ship To Address!!")
end if
if AddressZipCodeYN = "N" then 
FailAndExit("Zip Code Missing in Ship To Address!!")
end if
if SO_SalesOrder_bus_ShipToCountryCode = "USA" then
ShipToCountryCode = "US"
else 
FailAndExit("Country Code Invalid in Ship To Address!!")
end if
If SO_SalesOrder_bus_UDF_ADDRESSTYPE2 = "Residential" Then
RFlag = "Y"
End If
'MSGBOX("Residential: " & SO_SalesOrder_bus_ResidentialAddress)

'-----------------------------------------------------------------------
' Assign Order Values for Ship To Address
'-----------------------------------------------------------------------

ShipToCity = SO_SalesOrder_bus_ShipToCity
ShipToState = SO_SalesOrder_bus_ShipToState
ShipToZipCode = SO_SalesOrder_bus_ShipToZipCode
ShipperWhse = SO_SalesOrder_bus_WarehouseCode
'-----------------------------------------------------------------------
' Assign Ship From Warehouse
'-----------------------------------------------------------------------
ShipperCheck = "N"
If ShipperWhse = "MW1" then ShipperCheck = "Y"
If ShipperWhse = "FL1" then ShipperCheck = "Y"
If ShipperCheck = "N" Then
FailAndExit("You Have Chosen and Invalid Warehouse: " & ShipperWhse)
end if
If ShipperWhse = "FL1" Then 
ShipperCity = "Bradenton"
else ShipperCity = "Mansfield"
end if
If ShipperWhse = "FL1" Then 
ShipperState = "FL"
else ShipperState = "MO"
end if

If ShipperWhse = "FL1" Then 
ShipperZipCode = "FL"
else ShipperZipCode = "65704"
end if
If ShipperWhse = "FL1" Then
ShipperCountryCode = "US"
else ShipperCountryCode = "US"
end if

' Generate xmlString 
' Send account login information
xmlString =             "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
                      "<AccessRequest xml:lang=""en-US"">" & _
                      "    <AccessLicenseNumber>5C2321E5FCCBB518</AccessLicenseNumber>" & _
                      "    <UserId>engraveacrete</UserId>" & _
                      "    <Password>ship123</Password>" & _
                      "</AccessRequest>"
' Set up request type
xmlString = xmlString & "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
                      "<RatingServiceSelectionRequest xml:lang=""en-US"">" & _
                      "    <Request>" & _
                      "        <RequestAction>Rate</RequestAction>" & _
                      "        <RequestOption>Shop</RequestOption>" & _
                      "    </Request>"
' Some code thing...
xmlString = xmlString & "    <PickupType>" & _
                      "        <Code>01</Code>" & _
                      "    </PickupType>"                        
' Define Customer Classification...
xmlString = xmlString & "    <CustomerClassification>" & _
                      "        <Code>01</Code>" & _
                      "    </CustomerClassification>"                                                
' Define Shipper Information
xmlString = xmlString & "    <Shipment>" & _
                      "        <Shipper>" & _
                      "            <ShipperNumber>489Y77</ShipperNumber>" & _   
                      "            <Address>" & _
                      "                <City>Mansfield</City>" & _
                      "                <StateProvinceCode>MO</StateProvinceCode>" & _
                      "                <PostalCode>65704</PostalCode>" & _
                      "                <CountryCode>US</CountryCode>" & _
                      "            </Address>" & _
                      "        </Shipper>"

' Define Ship To Information
xmlString = xmlString & "        <ShipTo>" & _
                      "            <Address>" & _
                      "                <City>" & ShipToCity & "</City>" & _
                      "                <StateProvinceCode>" & ShipToState & "</StateProvinceCode>" & _
                      "                <PostalCode>" & ShipToZipCode & "</PostalCode>" & _
                      "                <CountryCode>" & ShipToCountryCode & "</CountryCode>" & _
                      "                <ResidentialAddressIndicator>" & RFlag & "</ResidentialAddressIndicator>" & _
                      "            </Address>" & _
                      "        </ShipTo>"
' Shipping from Missouri Warehouse (Alternate Codes from MAS)
xmlString = xmlString & "        <ShipFrom>" & _
                      "            <Address>" & _
                      "                <City>" & ShipperCity & "</City>" & _
                      "                <StateProvinceCode>" & ShipperState & "</StateProvinceCode>" & _
                      "                <PostalCode>" & ShipperZipCode & "</PostalCode>" & _
                      "                <CountryCode>" & ShipperCountryCode & "</CountryCode>" & _
                      "            </Address>" & _
                      "        </ShipFrom>"
                      
' DEFINE DELIVERY TYPE (DEFAULT 03 FOR GROUND)
'xmlString = xmlString & "        <Service>" & _
'                        "            <Code>03</Code>" & _
'                        "        </Service>"
' DEFINE USE NEGOTIATED RATES
xmlString = xmlString & "        <RateInformation>" & _
                      "            <NegotiatedRatesIndicator>Y</NegotiatedRatesIndicator>" & _
                      "        </RateInformation>"
' DEFINE THE PACKAGE INFORMATION
xmlString = xmlString & "        <Package>" & _
                      "            <PackagingType>" & _
                      "                <Code>00</Code>" & _   
                      "            </PackagingType>"

' Set Package Weight
xmlString = xmlString & "            <PackageWeight>" & _
                      "                <UnitOfMeasurement>" & _
                      "                    <Code>LBS</Code>" & _
                      "                </UnitOfMeasurement>" & _
                      "                <Weight>" & UPSWeight & "</Weight>" & _
                      "            </PackageWeight>" & _
                      "        </Package>" & _
                      "    </Shipment>" & _
                      "</RatingServiceSelectionRequest>"

'***************************************************************
' Write Request to a File
'***************************************************************
Dim Rfso
Dim Rf
Set Rfso = CreateObject("scripting.filesystemobject")
Set Rf = Rfso.CreateTextFile("c:\xml_request.txt")
Rf.Write xmlString
Rf.Close

'***************************************************************
' Define and Setup Communication Objects for Post
'***************************************************************
' 
Dim oXMLHttp 
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")

' prepare the HTTP POST request
oXMLHttp.Open "POST", "[link=https://wwwcie.ups.com/ups.app/xml/Rate]https://wwwcie.ups.com/ups.app/xml/Rate[/link]", False
oXMLHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
'-----------------------------------------------------------------------
' Send XML request
'-----------------------------------------------------------------------
' Testing site URL: [link=https://wwwcie.ups.com/ups.app/xml/Rate]https://wwwcie.ups.com/ups.app/xml/Rate[/link]
' Live site URL: [link=https://www.ups.com/ups.app/xml/Rate]https://www.ups.com/ups.app/xml/Rate[/link]
oXMLHttp.Send xmlString
'-----------------------------------------------------------------------
' Get XML response from xmlSvr
'-----------------------------------------------------------------------
Response_Doc = oXMLHttp.responseText
Dim fso
Dim f
Set fso = CreateObject("scripting.filesystemobject")
Set f = fso.CreateTextFile("c:\xml_Response.txt")
f.Write Response_Doc
f.Close

' msgbox("finished with send")
'----------------------------------------------
' BEGIN PARSE OF XML RESPONSE
'----------------------------------------------
Dim xmlDocB
Dim objNodeList, objNodeAmount
Dim plot, ship, fees 
Set xmlDocB = CreateObject("Msxml2.DOMDocument") 
xmlDocB.load("c:\xml_Response.txt") 
Set objNodeList = xmlDocB.getElementsByTagName("RatingServiceSelectionResponse/RatedShipment") 
'Define Variables to assign UPS amounts too.
Dim dNextDayAir, dSecondDayAir, dGround, dThreeDaySelect, dNextDayAirSaver, dNextDayAirSaverAM
Dim rNextDayAir, rSecondDayAir, rGround, rThreeDaySelect, rNextDayAirSaver, rNextDayAirSaverAM
'Below are the Codes for UPS Shipping Options Used in the For Each Loop Below
'01 UPS Next Day Air�
'02 UPS Second Day Air�
'03 UPS Ground
'12 UPS Three-Day Select�
'13 UPS Next Day Air Saver�
'14 UPS Next Day Air� Early A.M.

'Loop Through RatedShipment Tree and Assign Service Charges
For each x in objNodeList
Set ship = x.selectSingleNode("Service/Code")
Set fees = x.selectSingleNode("TotalCharges/MonetaryValue")

plot=ship.text
plotfees = fees.text
'MsgBox ("Plot: " + plot + "  $" + plotfees)

If plot = "01" then 
dNextDayAir = "UPS Next Day Air�"
rNextDayAir = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox("01: " + dNextDayAir + "  " + rNextDayAir)
End If

If plot = "02" then 
dSecondDayAir = "UPS Second Day Air�"
rSecondDayAir = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox("02: " + dSecondDayAir + "  " + rSecondDayAir)
End If

If plot = "03" then 
dGround = "UPS Ground"
rGround = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox("03: " + dGround + "  " + rGround)
End if

If plot = "12" then 
dThreeDaySelect = "UPS Three-Day Select�"
rThreeDaySelect = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox("12: " + dThreeDaySelect + "  " + rThreeDaySelect)
End if

If plot = "13" then 
dNextDayAirSaver = "UPS Next Day Air Saver�"
rNextDayAirSaver = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox("13: " + dNextDayAirSaver + "  " + rNextDayAirSaver)
End if

If plot = "14" then 
dNextDayAirSaverAM = "UPS Next Day Air� Early A.M."
rNextDayAirSaverAM = ((plotfees) * UpRate) + TotalHazMatFee
'MsgBox(dNextDayAirSaverAM + "  " + rNextDayAirSaverAM)
End If

'-----------------------------------------------------------------------
' Ask User to Select Shipping Type
'-----------------------------------------------------------------------
Dim Input
if strFailed = "N" And strGroundOnly = "N" then
Input = InputBox("ENTER THE # OF YOUR SHIPPING CHOICE" & chr(13) & _
"     " & chr(13) & _ 
"1: " & dGround & ":  $" & rGround & chr(13) & _
"    Delivery in 3-5 Business Days." & chr(13) & _
"     " & chr(13) & _ 
"2: " & dThreeDaySelect & ":  $" & rThreeDaySelect & chr(13) & _
"    Delivery by 10AM 3rd Day." & chr(13) & _
"     " & chr(13) & _ 
"3: " & dSecondDayAir & ":  $" & rSecondDayAir & chr(13) & _
"    Delivery by 10AM 2nd Day." & chr(13) & _
"     " & chr(13) & _ 
"4: " & dNextDayAirSaver & ":  $" & rNextDayAirSaver & chr(13) & _
"    Delivery by 5PM Next Day." & chr(13) & _
"     " & chr(13) & _
"5: " & dNextDayAir & ":  $" & rNextDayAir & chr(13) & _
"    Delivery by 2PM Next Day." & chr(13) & _
"     " & chr(13) & _ 
"6: " & dNextDayAirSaverAM & ":  $" & rNextDayAirSaverAM & chr(13) & _
"    Delivery by 10AM Next Day." & chr(13) & _
"     " & chr(13) & _
"     " & chr(13) & _
"    * IF THE AMOUNT IS ZERO, THE OPTION IS NOT AVAILABLE. ", "UPS Freight Shopping")

ElseIf strFailed = "N" And strGroundOnly = "Y" then
Input = InputBox("This Order Can Only Ship Ground:" & chr(13) & _
"     " & chr(13) & _ 
"ENTER ""1"" FOR YOUR SHIPPING CHOICE" + chr(13) & _
"     " & chr(13) & _ 
"1: " & dGround & ":  $" & rGround & chr(13) & _
"     " & chr(13) , "UPS Freight Shopping", "1")

Else 
MsgBox ("Please Check Errors and ReRun Freight")
end if
'*********************************************
'ASSIGN VALUES TO RETURN TO ORDER IN MAS200
'
'*********************************************
If Input = "1" then
SO_SalesOrder_bus_ShipVia = "UPS_GROUND"
SO_SalesOrder_bus_FreightAmt = rGround
ElseIf Input = "2" Then
SO_SalesOrder_bus_ShipVia = "UPS_3DAY_SELECT"
SO_SalesOrder_bus_FreightAmt = rThreeDaySelect
ElseIf Input = "3" Then 
SO_SalesOrder_bus_ShipVia = "UPS_2ND_AIR"
SO_SalesOrder_bus_FreightAmt = rSecondDayAir
ElseIf Input = "4" Then
SO_SalesOrder_bus_ShipVia = "UPS_NEXTAIR_SAV"
SO_SalesOrder_bus_FreightAmt = rNextDayAirSaver
ElseIf Input = "5" Then
SO_SalesOrder_bus_ShipVia = "UPS_NEXTAIR"
SO_SalesOrder_bus_FreightAmt = rNextDayAir
ElseIf Input = "6" Then
SO_SalesOrder_bus_ShipVia = "UPS_NEXTAIR_AM"
SO_SalesOrder_bus_FreightAmt = rNextDayAirSaverAM
Else 
MsgBox ("Invalid Selection.")
End If

If SO_SalesOrder_bus_UDF_ADDRESSTYPE2 = "Residential" Then
SO_SalesOrder_bus_ResidentialAddress = "Y"
End If
SO_SalesOrder_bus_UDF_DGROUND = rGround
SO_SalesOrder_bus_UDF_DNEXTDAYAIRSAVER = rNextDayAirSaver
SO_SalesOrder_bus_UDF_DNEXTDAYAIRSAVERAM = rNextDayAirSaverAM
SO_SalesOrder_bus_UDF_DSECONDDAYAIR = rSecondDayAir
SO_SalesOrder_bus_UDF_DTHREEDAYSELECT = rThreeDaySelect
SO_SalesOrder_bus_UDF_RNEXTDAYAIR = rNextDayAir
SO_SalesOrder_bus_UDF_HAZMAT = HazmatOrder
SO_SalesOrder_bus_UDF_HAZMATFEEPER5GAL = TotalHazQty5GAL
SO_SalesOrder_bus_UDF_HAZMATFEEPER1GAL = TotalHazQty1GAL
SO_SalesOrder_bus_UDF_HAZMATFEEPERKIT = TotalHazQtyKIT
SO_SalesOrder_bus_UDF_HAZMATGROUND = HazmatGroundOnly
SO_SalesOrder_bus_UDF_HAZMATFEE = TotalHazMatFee
'=============================================================================================================
' Prints the given error message, frees all global objects, exits the script with an error status code
Sub FailAndExit(strErrorMessage) 
msgbox(strErrorMessage)
strFailed = "Y"
'shell.Quit(PROG_ERROR)
End Sub

EDIT - EBGREEN - Code tags are fun.

< Message edited by ebgreen -- 4/30/2008 1:45:02 AM >

(in reply to ebgreen)

Post #: 5

RE: database connection string - 4/30/2008 1:47:40 AM

ebgreen

Posts: 4595
Score: 29
Joined: 7/12/2005
Status: offline

This line:

SO_SalesOrder_bus_SalesOrderNo = '9998'

is syntactically incorrect. Try:

SO_SalesOrder_bus_SalesOrderNo = "'9998'"

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to cdiltz)

Post #: 6

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> database connection string

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts