Member Avatar for pranto157

in my project i used vb6 and oracle. the connection method is DAO. i can execute query by writing --- Set rs = db.OpenRecordset("select * from patient_profile", dbOpenDynaset, dbExecDirect, dbOptimisticValue)--- but when i am going to execute -- Set rs = db.OpenRecordset("select max(id) from patient_profile", dbOpenDynaset, dbExecDirect, dbOptimisticValue)-- it gives me error, and also when i used to distnict function its show error. plz help me. how can i solve it?
plz reply me as soon as possible.

  • 2 Contributors
  • 2 Replies
  • 104 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pranto157

Dani AI

Generated

Summary: reported that basic selects worked but aggregate and DISTINCT queries raised errors when using VB6 + Oracle via DAO; asked for the error and later said the problem was solved and offered to share code. The notes below capture the common causes and practical fixes that apply to this scenario.

A frequent cause is trying to open a DAO dynaset against a query that is not updateable (aggregates, DISTINCT, GROUP BY, UNION, etc.) over an ODBC/Oracle connection. DAO expects an updateable rowset for a dynaset; when the SQL returns a non-updateable result the engine (or the ODBC driver) can fail. A safe approach is to treat those results as read-only or let Oracle return the result directly.

Practical troubleshooting and fixes:

  • Verify the same SQL in an Oracle client (SQL*Plus/SQL Developer) to confirm server behavior and column names.
  • Open the result as a read-only recordset (snapshot/forward-only) instead of an updateable dynaset, or use a pass-through query so Oracle executes the statement directly.
  • Give computed/aggregate columns an explicit alias so the client receives a usable field name.
  • Avoid update/locking flags when the query is intended to be read-only.
  • Consider switching to ADO with an Oracle OLE DB provider for more robust server-side SQL support from VB6.

Reference: Microsoft documentation for DAO Database.OpenRecordset explains recordset types and when a dynaset is not appropriate: Database.OpenRecordset method (DAO).

These patterns match the typical fixes for the behavior described by and are applicable to similar VB6+Oracle+DAO setups encountered later.

Member Avatar for ReeciePoo

what is the error?

Member Avatar for pranto157

i have solved my problem. thnak u. if u want that code i can give u. so thnx again

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.