Member Avatar for rashu007

Hello.

I am not getting the right idea to the oracle table in the list box so can anyone please tell me how can i list the oracle sql plus table's column in the listbox.

Thanks in Advance.

Edited by rashu007 because: wrongly typed
  • 5 Contributors
  • 5 Replies
  • 229 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

Short practical plan (what to do and why). Populate one control with table names and a second with that table's columns, then load the rows/values you want into the final listbox. Use the appropriate Oracle data-dictionary view depending on scope (your schema vs all accessible tables vs every table in the database) rather than relying on SQL*Plus shortcuts; that avoids surprises when the connected user has no objects in the current schema. ’s recordset loop is the common pattern for VB/ADO, and was right to point at the data-dictionary approach. (docs.oracle.com) (stackoverflow.com)

VB6 / ADO pattern (fast and safe): open a connection, query the dictionary or the column values you need, pull the result set into an array (GetRows) and populate the listbox. Using GetRows reduces round-trips and is usually faster than AddItem per row for large results; still clear the control before filling and always check EOF. Example pattern (replace the placeholders with your connection string, SQL and control names):

' VB6 + ADO: simplified pattern
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim data As Variant
Dim i As Long

Set cn = New ADODB.Connection
cn.Open myConnString

Set rs = New ADODB.Recordset
rs.Open mySql, cn, adOpenForwardOnly, adLockReadOnly

If Not rs.EOF Then
  data = rs.GetRows()   ' data(fieldIndex, rowIndex)
  lstMyListBox.Clear
  For i = LBound(data, 2) To UBound(data, 2)
    lstMyListBox.AddItem CStr(data(0, i))
  Next
End If

rs.Close
cn.Close

Use server-side ORDER BY to control sort order (or set the listbox Sorted property for small, simple lists). Remember Oracle stores unquoted identifiers in uppercase and that querying columns of a table in another schema requires owner qualification and SELECT privileges. For new projects prefer a supported .NET provider (ODP.NET) instead of legacy VB6 ADO when moving to modern platforms. (learn.microsoft.com)

Troubleshooting tips: empty the list before filling, guard against Null values, trim strings, test with a simple known table first, and log the SQL you generate (verify casing and owner). If results look wrong, run the same dictionary query in SQL*Plus/SQL Developer to confirm the rows returned before wiring them into the UI.

Recommended Answers

All 5 Replies

Member Avatar for vb5prgrmr

Well this can be done one of two ways... The first is to use an ORDER BY statement when you retrieve the records from the oracle table or you can set the sorted property of the listbox to true...

Now, if you need help in retrieving records from an oracle database. Use your friends (yahoo, google, ask, answers, bing) and search for vb6 ado tutorial and see http://www.connectionstrings.com if you want to go with a DNS Less connection...

Good Luck

Member Avatar for ankush.mukherje

Hi
Try this code

Private Sub software_prim()
    strSQL = "select distinct Primavera from Master_final"
    rs.Open strSQL, cn
    If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do While Not rs.EOF
    primavera = rs.Fields(0).Value
    lstsoftware12.AddItem (primavera)
    rs.MoveNext
    Loop
    End If
    rs.Close
    End Sub

here cn is the connection
rs is the recordsouce
This has definitely worked for me
Hope it works for you to.
Happy Programming

Member Avatar for QVeen72

Hi,

"Select * From Tab"

gives list of all the Tables in oracle database..

Regards
Veena

Edited by QVeen72 because: n/a
Member Avatar for ankush.mukherje

Hi,

"Select * From Tab"

gives list of all the Tables in oracle database..

Regards
Veena

Hi Qveen
I dont think he needs to select all the tables.
He needs the columns of a particular table.
If I am wrong then please do correct me
Thanks
Happy Programming

Member Avatar for debasisdas

to get the list of oracle tables use the following

select table_name from user_tables order by table_name

to get the list of columns of a oracle tables use the following

select column_name from user_tab_cols where table_name=upper('table_name') order by column_name

You can use 2 combo boxes one to populate the list of tables and the other for the list of columns of a table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.