mikef001
Fresh Scripter
Registered: 2003-05-08
Posts: 5
Loc: Portland, Oregon
|
Ok, so my first problem is I didn't know which version I was dealing with. Evidently AdminScriptEditor uses KixForms.net.
Here is my script
Break On
$System = CreateObject("KiXtart.System")
;~~< Form1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Form1
$Form1 = $System.Form()
$Form1.Size = 764, 529
$Form1.Text = "Database Tools"
;~~< Settings >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Settings
$Settings = $Form1.Controls.GroupBox()
$Settings.Text = "Settings"
$Settings.Font = $System.Font("Tahoma",10.0,1)
$Settings.Size = 318, 176
$Settings.Location = 7, 52
$Settings.TabStop = 0
;~~< Label1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Label1
$Label1 = $Settings.Controls.Label()
$Label1.Text = "Select the SQL Server to be updated."
$Label1.Font = $System.Font("Tahoma",8.25,1)
$Label1.Size = 288, 15
$Label1.Location = 14, 25
;~~< Label2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Label2
$Label2 = $Settings.Controls.Label()
$Label2.Text = "Select the web property associated with the unit."
$Label2.Font = $System.Font("Tahoma",8.25,1)
$Label2.Size = 288, 15
$Label2.Location = 14, 72
;~~< Label3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Label3
$Label3 = $Settings.Controls.Label()
$Label3.Text = "Select the unit code."
$Label3.Font = $System.Font("Tahoma",8.25,1)
$Label3.Size = 288, 15
$Label3.Location = 14, 120
;~~< ComboBox1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $ComboBox1
$ComboBox1 = $Settings.Controls.ComboBox()
$ComboBox1.Size = 288, 24
$ComboBox1.SelectedIndex = -1
$ComboBox1.List = "w_1obp ", "w_1sbp ", "w_1tbc ", "w_2obp ", "w_2sbp ", "w_2tbc ", "w_3obp ", "w_arbore", "w_bcc ", "w_bip ", "w_braker", "w_burke ", "w_carmel", "w_cbdc ", "w_cccc ", "w_ccs ", "w_cep1 ", "w_cep2 ", "w_cep3 ", "w_cep4 ", "w_cep5 ", "w_colina", "w_cos ", "w_cs ", "w_ctc ", "w_dip ", "w_ebp ", "w_foak ", "w_fobp ", "w_gatewa", "w_glip ", "w_hawth ", "w_icc ", "w_mp ", "w_mpbc ", "w_mpexec", "w_ocic ", "w_olytri", "w_ostc ", "w_otc ", "w_pacgat", "w_pbc ", "w_pbcs ", "w_pbpn ", "w_pbps ", "w_pcc ", "w_ppc ", "w_pwcc ", "w_rrcc ", "washcal ", "wb-land ", "wb400 ", "wb401 ", "wb402 ", "wb403 ", "wb404 ", "wb405 ", "wb406 ", "wball ", "wcop-ga ", "wiit-ar ", "wiit-sch", "wiitala "
$ComboBox1.Location = 14, 90
;~~< ComboBox2 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $ComboBox2
$ComboBox2 = $Settings.Controls.ComboBox()
$ComboBox2.Size = 288, 24
$ComboBox2.SelectedIndex = -1
$ComboBox2.List = "Opus SQL Server", "Yardi SQL Server", "PacTrust SQL Server"
$ComboBox2.Location = 14, 44
;~~< ComboBox3 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $ComboBox3
$ComboBox3 = $Settings.Controls.ComboBox()
$ComboBox3.Size = 288, 24
$ComboBox3.SelectedIndex = -1
$ComboBox3.Location = 14, 138
;~~< UnitDescription >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $UnitDescription
$UnitDescription = $Form1.Controls.GroupBox()
$UnitDescription.Text = "Unit Description"
$UnitDescription.Font = $System.Font("Tahoma",10.0,1)
$UnitDescription.Size = 396, 369
$UnitDescription.Location = 331, 52
$UnitDescription.TabStop = 0
$nul = $form1.controls.add($UnitDescription)
;~~< SelectButton >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $SelectButton
$SelectButton = $Form1.Controls.Button()
$SelectButton.Text = "Select"
$SelectButton.Font = $System.Font("Tahoma", 18.0, 1)
$SelectButton.Size = 158, 43
$SelectButton.ForeColor = "Red"
$SelectButton.Location = 127, 436
$SelectButton.OnClick = "SelectButtonOnClick()"
;~~< UpdateButton >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $UpdateButton
$UpdateButton = $Form1.Controls.Button()
$UpdateButton.Text = "Update"
$UpdateButton.Font = $System.Font("Tahoma",18.0,1)
$UpdateButton.Size = 158, 43
$UpdateButton.ForeColor = "Red"
$UpdateButton.Location = 348, 436
$UpdateButton.OnClick = "UpdateButtonOnClick()"
;~~< Header >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $Header
$Header = $Form1.Controls.Label()
$Header.Text = "Database Update Tool"
$Header.Font = $System.Font("Tahoma",14.0,1)
$Header.Size = 233, 28
$Header.Location = 12, 9
;~~< CloseButton >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $CloseButton
$CloseButton = $Form1.Controls.Button()
$CloseButton.Text = "Close"
$CloseButton.Font = $System.Font("Tahoma",18.0,1)
$CloseButton.Size = 158, 43
$CloseButton.ForeColor = "ControlText"
$CloseButton.Location = 569, 436
$CloseButton.OnClick = "CloseButtonOnClick()"
;~~< RichTextBox1 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim $TextBox1
$TextBox1 = $UnitDescription.Controls.TextBox()
$TextBox1.Location = 17, 25
$TextBox1.BackColor = "White"
$TextBox1.Size = 367, 333
$form1.center
$Form1.Show
While $Form1.Visible
$ = Execute($System.Application.DoEvents)
Loop
Function SelectButtonOnClick()
? 'Select Button has been pressed'
? 'TextBox1=' $TextBox1.text
? 'Combobox1=' $ComboBox1.text
? 'Combobox2=' $ComboBox2.text
EndFunction
Function CloseButtonOnClick()
Quit()
EndFunction
Function UpdateButtonOnClick()
$sqlServerTable = 'AvailableSpace'
$ConnDSN = ('DRIVER={SQL Server};SERVER=XXXXX;UID=XX;PWD=XXXXXXXX;DATABASE=PacTrust_Web')
;the next two variables need to come from the pulldown menus- combobox1 and combobox3.
;ultimatly, I would like to have the combobox pull down menu be dynamic from a select list.
$PsCode = '1obp108'
$UsCode = '02-16200'
$sql = ("UPDATE " + $sqlServerTable + " Set Caption3 = " + Chr(39) + $Description + Chr(39) + " where uscode = " + Chr(39) + $UsCode + Chr(39) + " and pscode = " + Chr(39) + $PsCode + "'")
? $sql
$recordset = DBCommand($ConnDSN, $sql)
EndFunction
Function DBCommand($ConnDSN, $sql)
Dim $objConn, $adStateOpen
Dim $Conntimeout, $CmdTimeout
Dim $cmdCommand, $rsRecordset
Dim $Records, $FinalRecords
Dim $adCmdText, $adLockReadOnly, $adOpenStatic
Dim $row, $rows, $column, $columns
$ConnDSN = Trim($ConnDSN)
If Not $ConnDSN
Exit 87
EndIf
$sql = Trim($sql)
If Not $sql
Exit 87
EndIf
; default database parameters
$adStateOpen = 1
$ConnTimeout = 15
$CmdTimeout = 30
$adCmdText = 1
$adOpenStatic = 3
$adLockReadOnly = 1
; open the database connection
$objConn = CreateObject("ADODB.Connection")
If @ERROR
Exit @ERROR
EndIf
$objConn.ConnectionTimeout = $ConnTimeout
If @ERROR
Exit @ERROR
EndIf
$objConn.CommandTimeout = $CmdTimeout
If @ERROR
Exit @ERROR
EndIf
$objConn.Open($ConnDSN)
If @ERROR
Exit @ERROR
EndIf
If Not $objConn.State = $adStateOpen
$objConn = ''
$DBCommand = ''
Exit @ERROR
EndIf
; create the database command object
$cmdCommand = CreateObject('ADODB.Command')
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.ActiveConnection = $objConn
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.CommandType = $adCmdText
If @ERROR
Exit @ERROR
EndIf
$cmdCommand.CommandText = $sql
If @ERROR
$DBCommand = @ERROR
Exit @ERROR
EndIf
If InStr($sql, 'SELECT') = 1
; create the recordset object
$rsRecordSet = CreateObject('ADODB.Recordset')
If @ERROR
Exit @ERROR
EndIf
$rsRecordset.CursorType = $adOpenStatic
If @ERROR
Exit @ERROR
EndIf
$rsRecordset.LockType = $adLockReadOnly
If @ERROR
Exit @ERROR
EndIf
$rsRecordset.Open($cmdCommand)
If @ERROR
Exit @ERROR
EndIf
If $rsRecordset.EOF And $rsRecordSet.BOF
; recordset is empty
$FinalRecords = ''
Else
If @ERROR
Exit @ERROR
EndIf
; retrieve all records at once and transpose into tabular format
$Records = $rsRecordset.GetRows()
$columns = UBound($records, 1)
$rows = UBound($records, 2)
ReDim $FinalRecords[$rows, $columns]
For $row = 0 to $rows
For $column = 0 to $columns
$FinalRecords[$row, $column] = $records[$column, $row]
Next
Next
EndIf
; close recordset
If $rsRecordset.state = $adStateOpen
$rsRecordset.Close()
If @ERROR
Exit @ERROR
EndIf
EndIf
$rsRecordset = ''
$cmdCommand = ''
$DBCommand = $FinalRecords
Else
$rsRecordset = $cmdCommand.Execute()
$cmdCommand = ''
$rsRecordset = ''
If @ERROR
Exit @ERROR
EndIf
$DBCommand = 0
EndIf
; close the database connection
If $objConn.State = $adStateOpen
$objConn.Close()
If @ERROR
Exit @ERROR
EndIf
EndIf
$objConn = ''
Exit 0
EndFunction
|
