List the fields in combo box from SQL Server

[thwias1b3008]

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:

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. . .

[DataMiser]

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.

[Ash Katchup]

This code will get every field from one table:

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 

[DataMiser]

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

[Ash Katchup]

My bad. I've fixed it now.