KiXforms Forum Index KiXforms
The Forum for the KiXforms Community
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 Quick Links 
Site News
Downloads
Documentation
Donations
Script Archive
Tracking Systems

sql select list returned as a combobox - kixforms 2.46

 
Post new topic   Reply to topic    KiXforms Forum Index -> Starters Code Clinic
View previous topic :: View next topic  
Author Message
mikef001
KiXforms Dabbler
KiXforms Dabbler


Joined: 09 May 2003
Posts: 6
Location: Portland, Oregon

PostPosted: Fri Jan 31, 2014 7:10 pm    Post subject: sql select list returned as a combobox - kixforms 2.46 Reply with quote

I have the basic form worked out and the lists are static right now. I want to select the current values from the sql table and have them in the combobox. I am attempting to click an update form button to populate the pull down menus.
my variable $pCodeList returns "Prop1", "Prop2", "Prop3" when I print it to the dos box in the backgroud. I am assuming that it should pass to the control but it does not. ($cbbProperty.List = $pCodeList vs. $cbbProperty.List = "Prop1", "Prop2", "Prop3")


$form = CreateObject("KiXtart.Form")
$Form.Size = 700, 529
$cbbProperty = $Form.combobox("", 100, 40, 200, 20)
$cbbProperty.List = $pCodeList

$btnReset = $form.ToolButton("UPDATE FORM", 430, 420, 100, 60)
$btnReset.ToolTip = "This button will populate the menus ofthe form."
$btnReset.FontSize = 14
$btnReset.OnClick = "UpdateForm_Click"
$btnReset.FontBold = "False"
$btnReset.FontName = "Times New Roman"

$form.center
$Form.Show
While $Form.Visible
$_ = Execute($form.Doevents)
Loop

Exit
Function UpdateForm_Click

$ConnDSN = ('DRIVER={SQL Server};SERVER=xxxxx;UID=xxxx;PWD=xxxx*U6;DATABASE=xxxx')


$sql = ("SELECT pscode from availablespace")

$recordset = DBCommand($ConnDSN, $sql)

For $row = 0 to UBound($recordset, 1)
For $column = 0 to UBound($recordset, 2)
If $row = 0 And $column = 0 $pCodeList = Chr(34) + RTrim($recordset[$row, $column]) + Chr(34)
Else $pCodeList = $pCodeList + ', ' + Chr(34) + RTrim($recordset[$row, $column]) + Chr(34)
EndIf
Next
Next
$cbbProperty.List = $pCodeList
EndFunction
Back to top
View user's profile Send private message Send e-mail
enahsyemotp
KiXforms Regular
KiXforms Regular


Joined: 22 Sep 2010
Posts: 40
Location: Tulsa, OK

PostPosted: Fri Jan 31, 2014 11:10 pm    Post subject: Reply with quote

Not sure I understand the problem. I simplified your code, since I dont have a sql server to use, and the .List property seems to populate just fine. The below works as I would expect it to.

Code:
$form = CreateObject("KiXtart.Form")
$Form.Size = 700, 529
$cbbProperty = $Form.combobox("", 100, 40, 200, 20)
$cbbProperty.List = $pCodeList

$btnReset = $form.ToolButton("UPDATE FORM", 430, 420, 100, 60)
$btnReset.ToolTip = "This button will populate the menus ofthe form."
$btnReset.FontSize = 14
$btnReset.OnClick = "UpdateForm_Click()"
$btnReset.FontBold = "False"
$btnReset.FontName = "Times New Roman"

$form.center
$Form.Show
While $Form.Visible
   $_ = Execute($form.Doevents)
Loop

Exit 0

Function UpdateForm_Click()
   $pCodeList = "Prop1", "Prop2", "Prop3"
   $cbbProperty.List = $pCodeList
EndFunction
Back to top
View user's profile Send private message
mikef001
KiXforms Dabbler
KiXforms Dabbler


Joined: 09 May 2003
Posts: 6
Location: Portland, Oregon

PostPosted: Mon Feb 03, 2014 8:29 pm    Post subject: working version of sql select to Combobox drop down menu Reply with quote

I finally got this to work. Below is the code to create a form with a combobox that will populate the pull-down menu with the results of the select statement.
You will need to have a sql server. You will also need to populate the sql variables and the sql select statement in the "UpdateForm_Click" function.
I am pulling those values from other controls in my form. They are hard-coded here as an example.

$form = CreateObject("KiXtart.Form")
$Form.Size = 700, 529
$cbbProperty = $Form.combobox("", 100, 40, 200, 20)

$btnReset = $form.ToolButton("UPDATE FORM", 430, 420, 100, 60)
$btnReset.ToolTip = "This button will populate the menus ofthe form."
$btnReset.FontSize = 14
$btnReset.OnClick = "UpdateForm_Click()"
$btnReset.FontBold = "False"
$btnReset.FontName = "Times New Roman"

$form.center
$Form.Show
While $Form.Visible
$_ = Execute($form.Doevents)
Loop

Exit 0


Function UpdateForm_Click


$SQLUser = "????"
$SQLPassword = "?????????"
$SQLDatabase = "??????"
$cbbSQLServer = "??????????????"
$cbbSQLTable = "AvailableSpace"

$ConnDSN = ('DRIVER={SQL Server};SERVER=' + $cbbSQLServer + ';UID=' + $SQLUser + ';PWD=' + $SQLPassword + ';DATABASE=' + $SQLDatabase)

$sql = ('SELECT distinct pscode from ' + $cbbSQLTable + ' order by 1')

$recordset = DBCommand($ConnDSN, $sql)

For $row = 0 to UBound($recordset, 1)
For $column = 0 to UBound($recordset, 2)
$pCodeList = RTrim($recordset[$row, $column])
$cbbProperty.AddItem($pCodeList)
$cbbProperty.ListIndex = $ComboBox.ListCount - 1
Next
Next
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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    KiXforms Forum Index -> Starters Code Clinic All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


Powered by phpBB © 2001, 2005 phpBB Group