<%@ LANGUAGE="VBScript" %> <% Server.ScriptTimeout = 600 %> <% ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' System : StoreFront 2000 Version 4.3.5 ' Date : 11.21.99 ' Author : LaGarde, Incorporated ' Description : StoreFront Upgrade Pack 11 Database Upgrade Routines ' Notes : There are no configurable elements in this file. ' ' COPYRIGHT NOTICE ' ' The contents of this file is protected under the United States ' copyright laws as an unpublished work, and is confidential and ' proprietary to LaGarde, Incorporated. Its use or disclosure in ' whole or in part without the expressed written permission of ' LaGarde, Incorporated is expressely prohibited. ' ' (c) Copyright 1998 by LaGarde, Incorporated. All rights reserved. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Response.Write ("

StoreFront 2000 Database Upgrade Script


") If Request("upgrade") <> "1" Then %>
NOTE:It is always a good idea to make a backup copy of your database in a separate
directory before running any script that will alter the database.
Enter the full HTTP path (URL) to the StoreFront Web Store database.
Ex. http://www.yourdomain.com/fpdb/storefront.mdb.
<% End If If Request("upgrade") = "1" Then dbPath = Request("dbPath") '********************* '* Create Connection * '********************* Set conn = Server.CreateObject ("ADODB.Connection") Set cat = Server.CreateObject ("ADOX.Catalog") Set RS = Server.CreateObject ("ADODB.Recordset") Dim dbType dbType = "Microsoft.Jet.oledb.4.0" DomainPath = Request.ServerVariables("HTTP_REFERER") DomainPath = Replace(DomainPath,"/sf2kdb.asp","") dbPath = Replace(dbPath,DomainPath,"") dbPath = Replace(dbPath,"/","\") SPath = Server.MapPath(".") ConnPath = SPath&dbPath conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn On Error Resume Next '********************************************** '* Set up error string and counter variables * '********************************************** strErr = "The Following Errors Occurred
" cntErr = 0 '************************* '* UPGRADE PRODUCT TABLE * '************************* '************************************************* '*** Add LONG_DESCRIPTION column, if necessary *** '************************************************* SQL = "ALTER TABLE product ADD COLUMN LONG_DESCRIPTION Memo NULL" conn.Execute(SQL) If Err.number <> 0 And Err.number <> -2147217887 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 SQL = "UPDATE product SET LONG_DESCRIPTION = ' ' WHERE LONG_DESCRIPTION Is Null" conn.Execute(SQL) End If '************************ '*** Add temp columns *** '************************ Dim prodAdd(3,2) prodAdd(0,0) = "PRICE_TEMP" prodAdd(0,1) = 15 prodAdd(1,0) = "WEIGHT_TEMP" prodAdd(1,1) = 10 prodAdd(2,0) = "SHIP_TEMP" prodAdd(2,1) = 10 For i = 0 to 2 SQL = "ALTER TABLE product ADD COLUMN "&prodAdd(i,0)&" Char ("&prodAdd(i,1)&") NOT NULL" set RSTest = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '******************************************************************** '*** Update message and description columns where values are Null *** '******************************************************************** SQL = "UPDATE product SET MESSAGE = ' ' WHERE MESSAGE Is Null" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "UPDATE product SET DESCRIPTION = ' ' WHERE DESCRIPTION Is Null" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "SELECT PRODUCT_ID, PRICE, WEIGHT, SHIP FROM product" Set RS = conn.Execute(SQL) If Not (RS.EOF and RS.BOF) Then RS.MoveFirst While Not RS.EOF SQL = "UPDATE product SET PRICE_TEMP = '" & RS("PRICE") & "', "_ & "WEIGHT_TEMP = '" & RS("WEIGHT") & "', "_ & "SHIP_TEMP = '" & RS("SHIP") & "' "_ & "WHERE PRODUCT_ID = '" & RS("PRODUCT_ID") & "'" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.MoveNext Wend End If RS.Close Dim prodDrop(3) prodDrop(0) = "PRICE" prodDrop(1) = "WEIGHT" prodDrop(2) = "SHIP" For i = 0 to 2 SQL = "ALTER TABLE product DROP "&prodDrop(i)&"" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '****************************************** '*** Rename and initialize temp columns *** '****************************************** cat.Tables("product").Columns("PRICE_TEMP").Name = "PRICE" cat.Tables("product").Columns("PRICE").Properties("Default").Value = """0""" cat.Tables("product").Columns("PRICE").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("product").Columns("WEIGHT_TEMP").Name = "WEIGHT" cat.Tables("product").Columns("WEIGHT").Properties("Default").Value = """0""" cat.Tables("product").Columns("WEIGHT").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("product").Columns("SHIP_TEMP").Name = "SHIP" cat.Tables("product").Columns("SHIP").Properties("Default").Value = """0""" cat.Tables("product").Columns("SHIP").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("product").Columns("DESCRIPTION").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("product").Columns("DESCRIPTION").Properties("Default").Value = """""" cat.Tables("product").Columns("LONG_DESCRIPTION").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("product").Columns("LONG_DESCRIPTION").Properties("Default").Value = """""" cat.Tables("product").Columns("MESSAGE").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("product").Columns("MESSAGE").Properties("Default").Value = """""" cat.Tables("product").Columns("ShipSwitch").Properties("Default") = """1""" cat.Tables("product").Columns("CTax").Properties("Default") = """1""" cat.Tables("product").Columns("STax").Properties("Default") = """1""" cat.Tables("product").Columns("CATEGORY").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("product").Columns("MFG").Properties("Jet OLEDB:Allow Zero Length").Value = True '******************************************** '*** Write out error report, if necesarry *** '******************************************** Response.Write("
") If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '************************ '* UPGRADE DESIGN TABLE * '************************ SQL = "ALTER TABLE design ADD COLUMN lblLONG_DESCRIPTION Char (50)" conn.Execute(SQL) If Err.number <> 0 And Err.number <> -2147217887 And Err.Number <> -2147467259 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & " " & Err.number & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "ALTER TABLE design ADD COLUMN lblLONG_DESCRIPTION_ACTIVE Char (1)" conn.Execute(SQL) If Err.number <> 0 And Err.number <> -2147217887 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If cat.Tables("design").Columns("lblLONG_DESCRIPTION").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE cat.Tables("design").Columns("lblLONG_DESCRIPTION_ACTIVE").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE '******************************************** '*** Write out error report, if necesarry *** '******************************************** If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '**************************************** '* SET NEW VALUES IN SELECTVALUES TABLE * '**************************************** SQL = "SELECT COLOR FROM SELECTVALUES WHERE COLOR='none'" Set RSColor = conn.Execute (SQL) If RSColor.eof and RSColor.bof Then SQL = "INSERT INTO selectvalues (COLOR, COLOR_CODE) VALUES ('None', '#XXXXXX')" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If End If SQL = "UPDATE SELECTVALUES SET TRANSACTION_METHOD = 'Signio-PayFlowPro' WHERE TRANSACTION_METHOD = 'PaymentNet'" conn.execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "SELECT TRANSACTION_METHOD FROM SELECTVALUES WHERE TRANSACTION_METHOD = 'TPN'" set RSSelect = conn.execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If If RSSelect.eof And RSSelect.bof Then Dim rowVals(2) rowVals(0) = "TPN" rowVals(1) = "LinkPoint" For i = 0 to 1 SQL = "INSERT INTO SELECTVALUES (TRANSACTION_METHOD) VALUES ('"&rowVals(i)&"')" conn.execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next End If If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '************************** '* UPGRADE SHIPPING TABLE * '************************** SQL = "ALTER TABLE shipping ADD COLUMN RATES_TEMP Char (10) NULL" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "SELECT RATES, ID FROM shipping" Set RS = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If If Not (RS.EOF and RS.BOF) Then RS.MoveFirst While Not RS.EOF SQL = "UPDATE shipping SET RATES_TEMP = '" & RS("RATES") & "' WHERE ID = " & RS("ID") Set RSUpdate = Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.MoveNext Wend RS.Close End If SQL = "ALTER TABLE shipping DROP RATES" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If cat.Tables("shipping").Columns("RATES_TEMP").Name = "RATES" cat.Tables("shipping").Columns("RATES").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE cat.Tables("shipping").Columns("ACTIVE").Properties("Default") = """0""" cat.Tables("shipping").Columns("EDIT").Properties("Default") = """1""" If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '************************ '* UPGRADE PRICES TABLE * '************************ dim mtpFlag SQL = "ALTER TABLE prices ADD COLUMN PRICE_TEMP Char (15) NOT NULL" conn.Execute(SQL) If Err.number <> 0 then mtpFlag = False Err.number = 0 Err.description = "" Else mtpFlag = True Err.number = 0 Err.description = "" SQL = "SELECT * FROM PRICES" Set RS = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If RS.MoveFirst If Err.number <> 0 then Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If While Not RS.EOF SQL = "UPDATE prices SET PRICE_TEMP = '" & RS("PRICE") & "' WHERE PRODUCT_ID = '" & RS("PRODUCT_ID") & "' AND QUANTITY = " & RS("QUANTITY") Set RSUpdate = Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If RS.MoveNext Wend RS.Close SQL = "ALTER TABLE prices DROP PRICE" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If cat.Tables("prices").Columns("PRICE_TEMP").Name = "PRICE" cat.Tables("prices").Columns("PRICE").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("prices").Columns("PRICE").Properties("Default") = """0""" End If If (cntErr > 0) And (mtpFlag = True) Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 ElseIf (cntErr = 0) and (mtpFlag = True) Then Response.Write("") End If '******************************* '* UPGRADE VALUESHIPPING TABLE * '******************************* SQL = "CREATE TABLE valueshipping_temp (SHIPPING_A char (10), SHIPPING_B char (10),"_ &" SHIPPING_C char (10), SHIPPING_D char (10), SHIPPING_E char (10), SHIPPING_F char (10),"_ &" SHIPA_AMOUNT char (10), SHIPAB_AMOUNT char (10), SHIPBC_AMOUNT char (10),"_ &" SHIPCD_AMOUNT char (10), SHIPDE_AMOUNT char (10), SHIPEF_AMOUNT char (10),"_ &" SHIPF_UP_AMOUNT char (10), SPECIAL_SHIP_AMOUNT char(10))" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If SQL = "SELECT * FROM valueshipping" RS.Open SQL,conn,1,2 If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If SQL = "INSERT INTO valueshipping_temp (SHIPPING_A, SHIPPING_B, SHIPPING_C, SHIPPING_D,"_ &"SHIPPING_E, SHIPPING_F, SHIPA_AMOUNT, SHIPAB_AMOUNT, SHIPBC_AMOUNT, SHIPCD_AMOUNT,"_ &"SHIPDE_AMOUNT, SHIPEF_AMOUNT, SHIPF_UP_AMOUNT, SPECIAL_SHIP_AMOUNT) VALUES ('"_ & RS("SHIPPING_A") & "', '" & RS("SHIPPING_B") & "', '" & RS("SHIPPING_C") & "', '"_ & RS("SHIPPING_D") & "', '" & RS("SHIPPING_E") & "', '" & RS("SHIPPING_E") & "', '"_ & RS("SHIPA_AMOUNT") & "', '" & RS("SHIPAB_AMOUNT") & "', '" & RS("SHIPBC_AMOUNT") & "', '"_ & RS("SHIPCD_AMOUNT") & "', '" & RS("SHIPDE_AMOUNT") & "', '" & RS("SHIPEF_AMOUNT") & "', '"_ & RS("SHIPF_UP_AMOUNT") & "', '" & RS("SPECIAL_SHIP_AMOUNT") & "')" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.Close SQL = "DROP TABLE valueshipping" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn cat.Tables("valueshipping_temp").Name = "valueshipping" If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '************************** '* UPGRADE CUSTOMER TABLE * '************************** SQL = "INSERT INTO CUSTOMER (GRAND_TOTAL, ORDER_DATE, NAME) VALUES ('Hey', '"&Now()&"', 'SP11 Test')" Connection.Execute(SQL) If Err.number = -2147217913 Then '********************************************************** '*** Delete all customers where grand total equals zero *** '********************************************************** SQL = "DELETE * FROM customer WHERE GRAND_TOTAL = '0'" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If '************************ '*** Add temp columns *** '************************ Dim custAdd(7,2) custAdd(0,0) = "SUB_TOTAL_TEMP" custAdd(0,1) = 20 custAdd(1,0) = "TAX_TEMP" custAdd(1,1) = 20 custAdd(2,0) = "SHIPPING_AMT_TEMP" custAdd(2,1) = 10 custAdd(3,0) = "GRAND_TOTAL_TEMP" custAdd(3,1) = 10 custAdd(4,0) = "ROUTING_NO_TEMP" custAdd(4,1) = 15 custAdd(5,0) = "MAIL_SUBSCRIBED" custAdd(5,1) = 1 custAdd(6,0) = "REMOTE_ADDRESS" custAdd(6,1) = 20 For i = 0 to 6 SQL = "ALTER TABLE customer ADD COLUMN "&custAdd(i,0)&" Char ("&custAdd(i,1)&") NULL" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '*************************************** '*** Select everything from CUSTOMER *** '*************************************** SQL = "SELECT * FROM customer" Set RS = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If '***************************** '*** Update CUSTOMER table *** '***************************** If Not (RS.EOF and RS.BOF) Then RS.MoveFirst While Not RS.EOF SQL = "UPDATE customer SET SUB_TOTAL_TEMP = '"&Trim(RS("SUB_TOTAL"))&"', "_ & "TAX_TEMP = '"&Trim(RS("TAX"))&"', "_ & "SHIPPING_AMT_TEMP = '"&Trim(RS("SHIPPING_AMT"))&"', "_ & "GRAND_TOTAL_TEMP = '"&Trim(RS("GRAND_TOTAL"))&"', "_ & "ROUTING_NO_TEMP = '"&Trim(RS("ROUTING_NO"))&"' "_ & "WHERE CUSTOMER_ID = " & RS("CUSTOMER_ID") Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.MoveNext Wend End If RS.Close '******************** '*** Drop columns *** '******************** Dim custDrop(5) custDrop(0) = "TAX" custDrop(1) = "SHIPPING_AMT" custDrop(2) = "GRAND_TOTAL" custDrop(3) = "ROUTING_NO" custDrop(4) = "SUB_TOTAL" For i = 0 to 4 SQL = "ALTER TABLE customer DROP "&custDrop(i)&"" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If Next '************************** '*** Refresh connection *** '************************** conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn '***************************************** '*** Rename and initialize temp tables *** '***************************************** cat.Tables("customer").Columns("SUB_TOTAL_TEMP").Name = "SUB_TOTAL" cat.Tables("customer").Columns("TAX_TEMP").Name = "TAX" cat.Tables("customer").Columns("TAX").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE cat.Tables("customer").Columns("SHIPPING_AMT_TEMP").Name = "SHIPPING_AMT" cat.Tables("customer").Columns("SHIPPING_AMT").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE cat.Tables("customer").Columns("GRAND_TOTAL_TEMP").Name = "GRAND_TOTAL" cat.Tables("customer").Columns("ROUTING_NO_TEMP").Name = "ROUTING_NO" cat.Tables("customer").Columns("GRAND_TOTAL").Properties("Default").Value = """0""" cat.Tables("customer").Columns("MAIL_SUBSCRIBED").Properties("Default").Value = """0""" cat.Tables("customer").Columns("SUB_TOTAL").Properties("Jet OLEDB:Allow Zero Length").Value = TRUE cat.Tables("customer").Columns("MAIL_SUBSCRIBED").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("customer").Columns("REMOTE_ADDRESS").Properties("Jet OLEDB:Allow Zero Length").Value = True Else Err.number = 0 Err.description = "" SQL = "DELETE * FROM CUSTOMER WHERE NAME = 'SP11 Test'" conn.Execute(SQL) End If '********************************************** '*** Write out error report if any occurred *** '********************************************** If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '*********************** '* UPGRADE ADMIN TABLE * '*********************** '*********************** '*** Add temp tables *** '*********************** dim adminAdd(4,2) adminAdd(0,0) = "HANDLING_TEMP" adminAdd(0,1) = 10 adminAdd(1,0) = "SHIP_MIN_TEMP" adminAdd(1,1) = 10 adminAdd(2,0) = "COD_AMOUNT_TEMP" adminAdd(2,1) = 10 adminAdd(3,0) = "SUBSCRIBE_MAIL" adminAdd(3,1) = 1 For i = 0 to 3 SQL = "ALTER TABLE ADMIN ADD COLUMN "&adminAdd(i,0)&" Char("&adminAdd(i,1)&") NOT NULL" conn.Execute(SQL) If Err.number <> 0 And Err.number <> -2147217887 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & " " & Err.number & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" End If Next '********************************************** '*** Add INTERNET_CASH column, if necesarry *** '********************************************** SQL = "ALTER TABLE ADMIN ADD COLUMN INTERNET_CASH Char(1)" conn.execute(SQL) If Err.number <> 0 And Err.number <> -2147217887 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 Err.description = "" cat.Tables("admin").Columns("INTERNET_CASH").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("admin").Columns("INTERNET_CASH").Properties("Default").Value = """0""" SQL = "UPDATE ADMIN SET INTERNET_CASH = '0'" conn.execute(SQL) End If '************************** '*** Refresh connection *** '************************** conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn SQL = "SELECT MERCHANT_TYPE, HANDLING, SHIP_MIN, COD_AMOUNT FROM ADMIN" Set RS = conn.Execute(SQL) If Err.number <> 0 And Err.number <> -2147467259 Then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & " " & Err.number & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If If RS("MERCHANT_TYPE") <> "authonly" OR RS("MERCHANT_TYPE") <> "authcapture" Then SQL = "UPDATE ADMIN SET MERCHANT_TYPE = 'authonly'" conn.execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If End If SQL = "UPDATE admin SET HANDLING_TEMP = '" & RS("HANDLING") & "', SHIP_MIN_TEMP ='" & RS("SHIP_MIN") & "', "_ &" COD_AMOUNT_TEMP = '"& RS("COD_AMOUNT") & "', SUBSCRIBE_MAIL = '1'" Set RSUpdate = Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.Close '******************** '*** Drop columns *** '******************** Dim adminDrop(3) adminDrop(0) = "HANDLING" adminDrop(1) = "SHIP_MIN" adminDrop(2) = "COD_AMOUNT" For i = 0 to 2 SQL = "ALTER TABLE ADMIN DROP "&adminDrop(i) conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '************************** '*** Refresh connection *** '************************** conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn '****************************************** '*** Rename and initialize temp columns *** '****************************************** cat.Tables("admin").Columns("HANDLING_TEMP").Name = "HANDLING" cat.Tables("admin").Columns("SHIP_MIN_TEMP").Name = "SHIP_MIN" cat.Tables("admin").Columns("COD_AMOUNT_TEMP").Name = "COD_AMOUNT" cat.Tables("admin").Columns("HANDLING").Properties("Default").Value = """0""" cat.Tables("admin").Columns("SHIP_MIN").Properties("Default").Value = """0""" cat.Tables("admin").Columns("COD_AMOUNT").Properties("Default").Value = """0""" cat.Tables("admin").Columns("SUBSCRIBE_MAIL").Properties("Default").Value = """1""" cat.Tables("admin").Columns("HANDLING").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("admin").Columns("SHIP_MIN").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("admin").Columns("COD_AMOUNT").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("admin").Columns("HANDLINGSWITCH").Properties("Default") = """0""" '********************************************** '*** Write out error report if any occurred *** '********************************************** If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '*************************** '* CREATE CREDITCARD TABLE * '*************************** Dim creditFlag SQL = "CREATE TABLE creditcard (CARD_NAME char (30), CARD_ABB char (20), ACTIVE char (1))" conn.Execute (SQL) If Err.number <> 0 then creditFlag = False Err.number = 0 Err.description = "" Else creditFlag = True Err.number = 0 conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn cat.Tables("creditcard").Columns("CARD_NAME").Properties("Default").Value = """CARD_NAME""" cat.Tables("creditcard").Columns("CARD_ABB").Properties("Default").Value = """CARD_ABBREVIATION""" cat.Tables("creditcard").Columns("ACTIVE").Properties("Default").Value = """1""" cat.Tables("creditcard").Columns("CARD_NAME").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("creditcard").Columns("CARD_ABB").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("creditcard").Columns("ACTIVE").Properties("Jet OLEDB:Allow Zero Length").Value = False SQL = "SELECT Count(ACTIVE) AS CCACTIVE FROM creditcard WHERE ACTIVE = '1'" Set RSCC = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If If RSCC("CCACTIVE") < 1 Then Dim creditVals(6,2) creditVals(0,0) = "American Express" creditVals(0,1) = "AMEX" creditVals(1,0) = "Visa" creditVals(1,1) = "Visa" creditVals(2,0) = "Discover" creditVals(2,1) = "Discover" creditVals(3,0) = "MasterCard" creditVals(3,1) = "MasterCard" creditVals(4,0) = "Diners Club" creditVals(4,1) = "JBC" creditVals(5,0) = "Carte Blanche" creditVals(5,1) = "JBC" For i = 0 to 5 SQL = "INSERT INTO creditcard (CARD_NAME, CARD_ABB, ACTIVE) VALUES ('"&creditVals(i,0)&"','"&creditVals(i,1)&"','1')" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next End If Set RSCC = nothing conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn End If '********************************************** '*** Write out error report if any occurred *** '********************************************** If (cntErr > 0) And (creditFlag = True) Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 ElseIf (cntErr = 0) and (creditFlag = True) Then Response.Write("") End If '************************* '* UPGRADE LOCALES TABLE * '************************* '*********************************** '*** Add temp columns to locales *** '*********************************** Dim localesAdd(2,2) localesAdd(0,0) = "CTax_TEMP" localesAdd(0,1) = 10 localesAdd(1,0) = "STax_TEMP" localesAdd(1,1) = 10 For i = 0 to 1 SQL = "ALTER TABLE locales ADD COLUMN "&localesAdd(i,0)&" Char ("&localesAdd(i,1)&") NULL" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '************************************** '*** Select everything from locales *** '************************************** SQL = "SELECT * FROM locales" Set RS = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If '**************************** '*** Update locales table *** '**************************** If Not (RS.EOF and RS.BOF) Then RS.MoveFirst While Not RS.EOF IF Trim(RS("CTax")) > "" Then SQL = "UPDATE locales SET CTax_TEMP = '" & RS("CTax") & "' WHERE Country = '" & RS("Country") & "'" Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If ElseIf Trim(RS("STax")) > "" Then SQL = "UPDATE locales SET STax_TEMP = '" & RS("STax") & "' WHERE State = '" & RS("State") & "'" Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If End IF RS.MoveNext Wend End If RS.Close '******************** '*** Drop columns *** '******************** Dim localesDrop(2) localesDrop(0) = "CTax" localesDrop(1) = "STax" For i = 0 to 1 SQL = "ALTER TABLE locales DROP "&localesDrop(i) conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '**************************************** '*** Insert values into locales table *** '**************************************** Dim localesIns(2,2) localesIns(0,0) = "Country, Country_Abb" localesIns(0,1) = "'Ireland', 'IR'" localesIns(1,0) = "State, State_Abb" localesIns(1,1) = "'Other', 'Other'" For i = 0 to 1 SQL = "INSERT INTO locales ("&localesIns(i,0)&") VALUES ("&localesIns(i,1)&")" conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '************************** '*** Refresh connection *** '************************** conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn '****************************************** '*** Rename and initialize temp columns *** '****************************************** cat.Tables("locales").Columns("CTax_TEMP").Name = "CTax" cat.Tables("locales").Columns("STax_TEMP").Name = "STax" cat.Tables("locales").Columns("CTax").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("locales").Columns("STax").Properties("Jet OLEDB:Allow Zero Length").Value = True cat.Tables("locales").Columns("Active").Properties("Default") = """0""" '********************************************** '*** Write out error report if any occurred *** '********************************************** If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '************************ '* UPGRADE ORDERS TABLE * '************************ '*************************** '*** Create temp columns *** '*************************** Dim ordersAdd(4,2) ordersAdd(0,0) = "PRICE_TEMP" ordersAdd(0,1) = 50 ordersAdd(1,0) = "TOTAL_TEMP" ordersAdd(1,1) = 50 ordersAdd(2,0) = "WEIGHT_TEMP" ordersAdd(2,1) = 10 ordersAdd(3,0) = "SHIP_TEMP" ordersAdd(3,1) = 10 For i = 0 to 3 SQL = "ALTER TABLE orders ADD COLUMN "&ordersAdd(i,0)&" Char ("&ordersAdd(i,1)&") NOT NULL" conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '************************************* '*** Select everything from orders *** '************************************* SQL = "SELECT * FROM orders" Set RS = conn.Execute(SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If '*************************** '*** Update orders table *** '*************************** If Not (RS.EOF and RS.BOF) Then RS.MoveFirst While not RS.EOF SQL = "UPDATE orders SET PRICE_TEMP = '" & RS("PRICE") & "', "_ & "TOTAL_TEMP = '" & RS("TOTAL") & "', "_ & "WEIGHT_TEMP = '" & RS("WEIGHT") & "', "_ & "SHIP_TEMP = '" & RS("SHIP") & "' "_ & "WHERE ID = " & RS("ID") Conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If RS.MoveNext Wend End If RS.Close '******************** '*** Drop columns *** '******************** Dim ordersDrop(4) ordersDrop(0) = "PRICE" ordersDrop(1) = "TOTAL" ordersDrop(2) = "WEIGHT" ordersDrop(3) = "SHIP" For i = 0 to 3 SQL = "ALTER TABLE orders DROP "&ordersDrop(i) conn.Execute (SQL) If Err.number <> 0 then cntErr = cntErr + 1 strErr = strErr & cntErr & ". " & Err.description & "
" Err.number = 0 Err.description = "" Else Err.number = 0 End If Next '************************** '*** Refresh connection *** '************************** conn.close conn.Open "Provider=" & dbType & ";data source=" & ConnPath & ";" cat.ActiveConnection = conn '****************************************** '*** Rename and initialize temp columns *** '****************************************** cat.Tables("orders").Columns("PRICE_TEMP").Name = "PRICE" cat.Tables("orders").Columns("TOTAL_TEMP").Name = "TOTAL" cat.Tables("orders").Columns("WEIGHT_TEMP").Name = "WEIGHT" cat.Tables("orders").Columns("SHIP_TEMP").Name = "SHIP" cat.Tables("orders").Columns("PRICE").Properties("Default").Value = """0""" cat.Tables("orders").Columns("TOTAL").Properties("Default").Value = """0""" cat.Tables("orders").Columns("WEIGHT").Properties("Default").Value = """0""" cat.Tables("orders").Columns("SHIP").Properties("Default").Value = """0""" cat.Tables("orders").Columns("PRICE").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("orders").Columns("TOTAL").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("orders").Columns("WEIGHT").Properties("Jet OLEDB:Allow Zero Length").Value = False cat.Tables("orders").Columns("SHIP").Properties("Jet OLEDB:Allow Zero Length").Value = False '********************************************** '*** Write out error report if any occurred *** '********************************************** If cntErr > 0 Then Response.Write("") strErr = "The Following Errors Occurred
" cntErr = 0 Else Response.Write("") End If '*************************************************** '*** Close and destry connections and recordsets *** '*************************************************** conn.Close Set conn = Nothing Set cat = Nothing Set RS = Nothing End If %>
TABLESTATUS
  • Product
    		• "&strErr&"
  • Product
    		• Upgraded Sucessfully!
  • Design
    		• "&strErr&"
  • Design
    		• Upgraded Sucessfully!
  • SelectValues
    		• "&strErr&"
  • SelectValues
    		• Upgraded Sucessfully!
  • Shipping
    		• "&strErr&"
  • Shipping
    		• Upgraded Sucessfully!
  • Prices
    		• "&strErr&"
  • Prices
    		• Upgraded Sucessfully!
  • ValueShipping
    		• "&strErr&"
  • ValueShipping
    		• Upgraded Sucessfully!
  • Customer
    		• "&strErr&"
  • Customer
    		• Upgraded Sucessfully!
  • Admin
    		• "&strErr&"
  • Admin
    		• Upgraded Sucessfully!
  • CreditCard
    		• "&strErr&"
  • CreditCard
    		• Upgraded Sucessfully!
  • Locales
    		• "&strErr&"
  • Locales
    		• Upgraded Sucessfully!
  • Orders
    		• "&strErr&"
  • Orders
    		• Upgraded Sucessfully!