List the fields in combo box from SQL Server
Hi,
I used PostgreSQL and i want to populate its fields in combo box.
Is anyone know how to make it. .
Please help me. . . it is for my project. . :icon_sad:
Here is my code:
Code:
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim IvAR As Integer
Set conn = New ADODB.Connection
conn.ConnectionString = connString
conn.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=PostgreSQL30;Initial Catalog=PostgreSQL"
Set rst = New ADODB.Recordset
rst.ActiveConnection = conn
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Fields = "inventory"
rst.Open
If rst.BOF = False Then
Combo1.Clear
For IvAR = 0 To rst.Fields.Count - 1
Combo1.AddItem rst.Fields(IvAR).Name
Combo1.ItemData(Combo1.NewIndex) = IvAR
Next IvAR
If Combo1.ListCount > 0 Then
Combo1.ListIndex = 1
End If
End If
, , Is this correct or not. . if not. . please help me to correct the codes. . . :(
I hope you help me. . .
Thanks in advance. . .
Re: List the fields in combo box from SQL Server
The first item in a combobox has an index of 0 so the code you have shown will set to the second item when the count is greater than 0 which would cause an error if there were only one item.
Re: List the fields in combo box from SQL Server
This code will get every field from one table:
PHP Code:
Option Explicit
Private Sub Form_Load()
Dim oConn As ADODB.Connection
Dim oReco As ADODB.Recordset
Dim iCont As Integer
Dim sConnString As String
'
sConnString = "<Connection String>"
cbFields.Clear
'
Set oConn = New ADODB.Connection
Set oReco = New ADODB.Recordset
'
oConn.Open sConnString
oReco.Open "Select * From <Table>", oConn, adOpenStatic
'
For iCont = 0 To oReco.Fields.Count - 1
cbFields.AddItem oReco.Fields(iCont).Name
oReco.MoveNext
Next
'
oReco.Close
oConn.Close
'
Set oReco = Nothing
Set oConn = Nothing
'
If cbFields.ListCount > 0 Then cbFields.ListIndex = 0
'
End Sub
Re: List the fields in combo box from SQL Server
The code checks to see if there is at least 1 item in the list and sets the index to the second item which may or may not exist.
e.g. 1st field is index 0 second field is index 1 as is if there is only one field the count will be one but the max index will be 0 this will cause an error which is what i was pointing out. Of course if there are 2 or more fields no error will occur you will simply be selecting the second field name. If this is what you want to do then I would change the code to
If cbFields.Listcount>1 then .....
That way no error will occur under the unlikely condition of only 1 item in the list
Re: List the fields in combo box from SQL Server
My bad. I've fixed it now.